Formatos de números personalizados de Excel

Introducción

Los formatos numéricos controlan cómo se muestran los números en Excel. El beneficio clave de los formatos numéricos es que cambian la apariencia de un número sin cambiar ningún dato. Son una excelente manera de ahorrar tiempo en Excel porque realizan una gran cantidad de formateo automáticamente. Como beneficio adicional, hacen que las hojas de trabajo se vean más consistentes y profesionales.

Video: ¿Qué es un formato numérico?

¿Qué se puede hacer con los formatos numéricos personalizados?

Los formatos numéricos personalizados pueden controlar la visualización de números, fechas, horas, fracciones, porcentajes y otros valores numéricos. Con formatos personalizados, puede hacer cosas como formatear fechas para mostrar solo los nombres de los meses, formatear números grandes en millones o miles y mostrar números negativos en rojo.

¿Dónde puede utilizar formatos numéricos personalizados?

Muchas áreas en Excel admiten formatos de números. Puede usarlos en tablas, gráficos, tablas dinámicas, fórmulas y directamente en la hoja de trabajo.

• Hoja de trabajo - diálogo de formato de celdas
• Tablas dinámicas: a través de la configuración del campo de valor
• Gráficos: etiquetas de datos y opciones de ejes
• Fórmulas - a través del Función TEXTO

¿Qué es un formato numérico?

Un formato de número es un código especial para controlar cómo se muestra un valor en Excel. Por ejemplo, la siguiente tabla muestra 7 formatos de números diferentes aplicados a la misma fecha, 1 de enero de 2019:

La clave para entender es que los formatos numéricos cambian la forma en que se muestran los valores numéricos. desplegado , pero ellos no cambies los valores reales.

¿Dónde puede encontrar formatos de números?

En la pestaña de inicio de la cinta, encontrará un menú de formatos de números incorporados. Debajo de este menú a la derecha, hay un pequeño botón para acceder a todos los formatos de números, incluidos los formatos personalizados:

Este botón abre el cuadro de diálogo Formato de celdas. Encontrará una lista completa de formatos de números, organizados por categoría, en la pestaña Número:

Nota: puede abrir el cuadro de diálogo Formato de celdas con el método abreviado de teclado Control + 1.

General es predeterminado

De forma predeterminada, las celdas comienzan con el formato General aplicado. La visualización de números utilizando el formato de número general es algo 'fluida'. Excel mostrará tantos lugares decimales como permita el espacio, redondeará decimales y utilizará el formato de números científicos cuando el espacio sea limitado. La siguiente pantalla muestra los mismos valores en las columnas B y D, pero D es más estrecha y Excel hace ajustes sobre la marcha.

Cómo cambiar los formatos de números

Puede seleccionar formatos de números estándar (General, Número, Moneda, Contabilidad, Fecha corta, Fecha larga, Hora, Porcentaje, Fracción, Científico, Texto) en la pestaña de inicio de la cinta usando el menú Formato de número.

Nota: A medida que ingresa datos, Excel a veces cambia los formatos numéricos automáticamente. Por ejemplo, si ingresa una fecha válida, Excel cambiará al formato 'Fecha'. Si ingresa un porcentaje como 5%, Excel cambiará a Porcentaje, y así sucesivamente.

Accesos directos para formatos de números

Excel proporciona una serie de atajos de teclado para algunos formatos comunes:

Ver también: 222 accesos directos de Excel para Windows y Mac

Dónde ingresar formatos personalizados

En la parte inferior de los formatos predefinidos, verá una categoría llamada personalizada. La categoría Personalizado muestra una lista de códigos que puede usar para formatos de números personalizados, junto con un área de entrada para ingresar códigos manualmente en varias combinaciones.

Cuando seleccione un código de la lista, verá que aparece en el cuadro de entrada Tipo. Aquí puede modificar el código personalizado existente o ingresar sus propios códigos desde cero. Excel mostrará una pequeña vista previa del código aplicado al primer valor seleccionado encima del área de entrada.

Nota: Los formatos de números personalizados se encuentran en un libro de trabajo, no en Excel en general. Si copia un valor formateado con un formato personalizado de un libro a otro, el formato de número personalizado se transferirá al libro junto con el valor.

Cómo crear un formato de número personalizado

Para crear un formato de número personalizado, siga este sencillo proceso de 4 pasos:

1. Seleccione la (s) celda (s) con los valores que desea formatear
2. Control + 1> Números> Personalizado
3. Ingrese los códigos y vea el área de vista previa para ver el resultado
4. Presione OK para guardar y aplicar

Consejo: si desea basar su formato personalizado en un formato existente, primero aplique el formato base, luego haga clic en la categoría 'Personalizado' y edite los códigos como desee.

Cómo editar un formato de número personalizado

Realmente no puede editar un formato de número personalizado per se. Cuando cambia un formato de número personalizado existente, nuevo Se crea el formato y aparecerá en la lista en la categoría Personalizado. Puede utilizar el botón Eliminar para eliminar formatos personalizados que ya no necesita.

Advertencia: no se puede deshacer después de eliminar un formato de número personalizado.

Estructura y referencia

Los formatos de números personalizados de Excel tienen una estructura específica. Cada formato de número puede tener hasta cuatro secciones, separadas por punto y coma de la siguiente manera:

Esta estructura puede hacer que los formatos numéricos personalizados parezcan abrumadoramente complejos. Para leer un formato de número personalizado, aprenda a detectar los puntos y coma y analice mentalmente el código en estas secciones:

1. Valores positivos
2. Valores negativos
3. Valores cero
4. Valores de texto

No se requieren todas las secciones

Aunque un formato de número puede incluir hasta cuatro secciones, solo se requiere una sección. De forma predeterminada, la primera sección se aplica a los números positivos, la segunda sección se aplica a los números negativos, la tercera sección se aplica a los valores cero y la cuarta sección se aplica al texto.

• Cuando solo se proporciona un formato, Excel usará ese formato para todos los valores.
• Si proporciona un formato numérico con solo dos secciones, la primera sección se usa para números positivos y ceros, y la segunda sección se usa para números negativos.
• Para omitir una sección, incluya un punto y coma en la ubicación adecuada, pero no especifique un código de formato.

Caracteres que se muestran de forma nativa

Algunos caracteres aparecen normalmente en formato numérico, mientras que otros requieren un manejo especial. Los siguientes caracteres se pueden utilizar sin ningún tratamiento especial:

Personajes que escapan

Algunos caracteres no funcionarán correctamente en un formato de número personalizado sin tener que escapar. Por ejemplo, los caracteres de asterisco (*), hash (#) y porcentaje (%) no se pueden usar directamente en un formato de número personalizado; no aparecerán en el resultado. El carácter de escape en los formatos numéricos personalizados es la barra invertida (). Al colocar la barra invertida antes del carácter, puede usarlos en formatos de números personalizados:

Marcadores de posición

Ciertos caracteres tienen un significado especial en los códigos de formato numérico personalizado. Los siguientes personajes son bloques de construcción clave:

cómo encontrar celdas repetidas en Excel

Cero (0) se utiliza para forzar la visualización de ceros insignificantes cuando un número tiene menos dígitos que ceros en el formato. Por ejemplo, el formato personalizado 0,00 mostrará cero como 0,00, 1,1 como 1,10 y 0,5 como 0,50.

Signo de Libra (#) es un marcador de posición para dígitos opcionales. Cuando un número tiene menos dígitos que # símbolos en el formato, no se mostrará nada. Por ejemplo, el formato personalizado #. ## mostrará 1,15 como 1,15 y 1,1 como 1,1.

Signo de interrogación (?) se utiliza para alinear dígitos. Cuando un signo de interrogación ocupa un lugar que no se necesita en un número, se agregará un espacio para mantener la alineación visual.

Período (.) es un marcador de posición para el punto decimal en un número. Cuando se utiliza un período en un formato de número personalizado, siempre se mostrará, independientemente de si el número contiene valores decimales.

Coma (,) es un marcador de posición para los separadores de miles en el número que se muestra. Se puede utilizar para definir el comportamiento de los dígitos en relación con los miles o millones de dígitos.

Asterisco (*) se utiliza para repetir caracteres. El carácter que sigue inmediatamente a un asterisco se repetirá para llenar el espacio restante en una celda.

Guion bajo (_) se utiliza para agregar espacio en un formato numérico. El carácter que sigue inmediatamente a un carácter de subrayado controla cuánto espacio agregar. Un uso común del carácter de subrayado es agregar espacio para alinear valores positivos y negativos cuando un formato numérico agrega paréntesis solo a números negativos. Por ejemplo, el formato de número '0 _) (0)' agrega un poco de espacio a la derecha de los números positivos para que permanezcan alineados con los números negativos, que están entre paréntesis.

A (@) - marcador de posición para texto. Por ejemplo, el siguiente formato numérico mostrará los valores de texto en azul:

Consulte a continuación para obtener más información sobre el uso del color.

Redondeo automático

Es importante comprender que Excel realizará un 'redondeo visual' con todos los formatos numéricos personalizados. Cuando un número tiene más dígitos que marcadores de posición en el lado derecho del punto decimal, el número se redondea al número de marcadores de posición. Cuando un número tiene más dígitos que marcadores de posición en el lado izquierdo del punto decimal, se muestran dígitos adicionales. Este es un efecto visual, solo los valores reales no se modifican.

Formatos numéricos para TEXTO

Para mostrar ambos textos junto con números, encierre el texto entre comillas dobles (''). Puede utilizar este método para agregar o anteponer cadenas de texto en un formato de número personalizado, como se muestra en la tabla siguiente.

Formatos de números para FECHAS

Las fechas en Excel son solo números, por lo que puede usar formatos numéricos personalizados para cambiar la forma en que se muestran. Excel muchos códigos específicos que puede utilizar para mostrar los componentes de una fecha de diferentes formas. La siguiente pantalla muestra cómo Excel muestra la fecha en D5, 3 de septiembre de 2018, con una variedad de formatos de números personalizados:

Formatos de números para TIME

Los tiempos en Excel son partes fraccionarias de un día. Por ejemplo, 12:00 p. M. Es 0.5 y 6:00 p. M. Es 0.75. Puede utilizar los siguientes códigos en formatos de hora personalizados para mostrar los componentes de una hora de diferentes formas. La siguiente pantalla muestra cómo Excel muestra la hora en D5, 9:35:07 a.m., con una variedad de formatos de números personalizados:

Nota: my mm no se pueden usar solos en un formato de número personalizado, ya que entran en conflicto con el código de número de mes en los códigos de formato de fecha.

Formatos de números para TIEMPO TRANSCURRIDO

El tiempo transcurrido es un caso especial y necesita un manejo especial. Al usar corchetes, Excel proporciona una forma especial de mostrar las horas, minutos y segundos transcurridos. La siguiente pantalla muestra cómo Excel muestra el tiempo transcurrido según el valor en D5, que representa 1,25 días:

Formatos numéricos para COLORES

Excel proporciona soporte básico para colores en formatos numéricos personalizados. Los siguientes 8 colores se pueden especificar por nombre en un formato de número: [negro] [blanco] [rojo] [verde] [azul] [amarillo] [magenta] [cian]. Los nombres de los colores deben aparecer entre paréntesis.

cómo encontrar el tercer cuartil en Excel

Colores por índice

Además de los nombres de los colores, también es posible especificar los colores mediante un número de índice (Color1, Color2, Color3, etc.). Los ejemplos siguientes utilizan el formato de número personalizado: [ColorX] 0 '▲ ▼', donde X es un número entre 1-56:

Los símbolos de triángulos se han agregado solo para que los colores sean más fáciles de ver. La primera imagen muestra los 56 colores sobre un fondo blanco estándar. La segunda imagen muestra los mismos colores sobre un fondo gris. Tenga en cuenta que los primeros 8 colores mostrados corresponden a la lista de colores nombrada anteriormente.

Aplicar formatos numéricos en una fórmula

Aunque la mayoría de los formatos numéricos se aplican directamente a las celdas de una hoja de trabajo, también puede aplicar formatos numéricos dentro de una fórmula con la función TEXTO. Por ejemplo, con una fecha válida en A1, la siguiente fórmula mostrará solo el nombre del mes:

El resultado de la función TEXT siempre es texto, por lo que puede concatenar el resultado de TEXT con otras cadenas:

La siguiente pantalla muestra los formatos numéricos de la columna C que se aplican a los números de la columna B mediante la función TEXTO:

Condicionales

Los formatos numéricos personalizados también tienen hasta dos condiciones, que se escriben entre corchetes como [> 100] o [<=100]. When you use conditionals in custom number formats, you override the standard [postive][negative][zero][text] structure. For example, to display values below 100 in red, you can use:

[Red][<100]00

Para mostrar valores mayores o iguales a 100 en azul, puede extender el formato de esta manera:

[Red][=100]0

Para aplicar más de dos condiciones o cambiar otros atributos de celda, como el color de relleno, etc., deberá cambiar a Formato condicional , que puede aplicar formato con mucha más potencia y flexibilidad usando fórmulas.

Etiquetas de texto plural

Puede usar condicionales para agregar una 's' a etiquetas mayores que cero con un formato personalizado como este:

[= 1] 0 'día' 0 'días'

Números telefónicos

Los formatos de números personalizados también se pueden utilizar para números de teléfono, como se muestra en la siguiente pantalla:

Observe que los ejemplos tercero y cuarto usan un formato condicional para verificar los números que contienen un código de área. Si tiene datos que contienen números de teléfono con puntuación codificada (paréntesis, guiones, etc.), deberá limpiar los números de teléfono primero para que solo contengan números.

Ocultar todo el contenido

De hecho, puede usar un formato de número personalizado para ocultar todo el contenido en una celda. El código es simplemente tres puntos y coma y nada más.

Para revelar el contenido nuevamente, puede usar el atajo de teclado Control + Shift + ~, que aplica el formato General.

Otros recursos

• El desarrollador Bryan Braun construyó un buena herramienta interactiva para crear formatos numéricos personalizados