En Excel Efficiency, pasamos mucho tiempo explicando cómo acelerar el formato en Microsoft Excel.
Por ejemplo, puedes leer este resumen de atajos para acelerar los atajos de mejor formato.
Y hay algunos consejos de formato en nuestra lista de formas de hacer tus hojas de cálculo a prueba de idiotas, también.
Estos atajos y consejos cubren los formatos de números predefinidos en Excel, que probablemente son suficientes para alrededor del 95% de tu trabajo.
Pero si las opciones de formato por defecto de Excel no permiten presentar su trabajo de la manera correcta, hay una solución.
Glosario
Introduciendo el Formato de Número Personalizado en Excel
El formato de número personalizado es una opción de formato flexible que le permite crear sus propios tipos de formato específicos.
Cómo aplicar un formato de número personalizado
Para aprovechar todo lo bueno de esta guía, tendrás que encontrar dónde está esta mágica opción de formato de número personalizado.
Primero, pulsa Ctrl + 1 para abrir el cuadro de diálogo Formato de celdas.
A continuación, en la pestaña de números, debería ver «Personalizado» en la lista de categorías de la izquierda:
Whoa – hay un montón de personajes de aspecto loco en ese boxon de la derecha!
Está bien. Todos se refieren a formas específicas de formatear tus datos en Excel, y cuando termines de leer este post, entenderás lo que significan todos esos caracteres.
Lo importante es que puedes crear tu propio formato de número personalizado completamente único en su lugar .
¿Por qué usar el formato de número personalizado de todos
Pero antes de que nos sumerjamos en los ejemplos, ¿por qué molestarse con el formato de número personalizado de todos modos?
La principal ventaja es que puede alterar la apariencia de sus datos sin cambiar los valores reales en sí .
Si usted está en cualquier tipo de papel de análisis donde la integridad de sus datos es importante, entonces este es un gran compromiso entre una buena presentación y la preservación de sus datos en bruto.
También significa que si estás compartiendo tu trabajo con otras personas, entonces el formato puede ser fácilmente reajustado o modificado .
Si usted realmente quiere cambiar sus datos, considere crear una columna adicional, usando la función TEXT() para cambiar sus valores originales. Todos los trucos de esta guía se pueden usar con TEXT().
Sin embargo, si actualizas tus datos a través de la función TEXT() no podrás realizar ningún cálculo sobre los datos, ya que ahora está en formato de texto.
Lo que aprenderás
El resto de este post te pondrá al día con una amplia gama de situaciones que el formato de número personalizado puede manejar.
He buscado en la comunidad del sitio web de Excel para encontrar muchos de estos consejos útiles, y puedes visitar sus sitios directamente para saber más mientras lees cada sección.
Y como un bono adicional, al final de este post hay una tabla detallada que enumera cada uno de los caracteres que pueden ser insertados en cualquier formato de número personalizado (#, 0, @, etc.) y explica cómo funciona cada carácter.
Mostrar los números como unidades (o añadir cualquier texto al principio o al final de una celda)
(Fuentes: Exceltip.comy Stack Overflow)
Comencemos esta lista con un ejemplo bastante simple.
Si necesita presentar sus datos en una unidad de medida aproximada (digamos, «pies» o «segundos»), puede dar formato a sus datos para mostrar algún texto después del número en la celda.
Escriba la siguiente línea en la sección Tipo: del cuadro de diálogo Formato de número personalizado:
#»ft»
Así es como se ve:
Explicación: El símbolo hashtag(#) indica un número, y el texto dentro de las comillas es el que aparece después. El espacio entre los símbolos # y » es importante también -Excel añadirá el espacio (¡o múltiples espacios si lo desea!) en el formato.
Cuando se observa cada uno de los valores de ambas columnas en la barra de fórmulas, se notará que sólo aparece el valor . Sin embargo, las celdas de la derecha aparecen con el texto «ft» al final porque humedece el formato de Excel de esa manera .
Añade los decimales al número
¿Notan el valor de tercera, donde el valor 12.4 sólo se muestra como «12 pies»? Hemos perdido el decimal porque no lo incluimos en el formato de los números. Para arreglar esto, prueba este formato en su lugar:
#.0 «ft»
Explicación: Con los formatos de números personalizados, el número 0 indica un número igual al símbolo #, pero significa que el dígito siempre aparecerá, incluso si el número no lo requiere. De esta manera, los números se alinean de manera consistente. Si elegimos usar el formato #.# «ft» , entonces la primera línea se mostraría como 14. ft lo cual no parecería consistente (¡y es matemáticamente erróneo!).
Incluso puedes añadir texto al principio de la celda de la misma manera – cualquiera de los siguientes funcionará (cambia el texto a cualquier cadena de texto que desees):
Para los números: «Texto» #
Para el texto: «Texto» @
Hacer que los ceros aparezcan como espacios en blanco
(Fuente: Mike’s Macros)
Bien, hagamos las cosas un poco más complicadas ahora.
Tienes una lista de números, algunos de los cuales son positivos, otros son negativos y otros son cero.
Pero no te importan las líneas con ceros, quieres que aparezcan en blanco.
Introduzca esto en la sección Tipo: del cuadro de diálogo Formato de número personalizado:
0;(0);
Como pueden ver, los valores negativos aparecen con paréntesis alrededor de ellos.
Explicación: Los formatos de números personalizados en Excel pueden tener hasta cuatro secciones separadas por punto y coma (;). En orden, se refieren a valores positivos , valores negativos , valores cero y texto . Así que la primera sección es «0» que simplemente representa un número, y la segunda sección «(0) » formatea los valores negativos como un número con paréntesis alrededor de ellos.
El truco de que los ceros aparezcan en blanco se debe a que hay un segundo punto y coma al final de la segunda sección. Esto significa que hemos especificado una tercera sección del formato personalizado, pero sincero caracteres adicionales se enumeran, Excel no muestra nada (es decir: la celda aparece en blanco).
Si quieres presentar valores negativos como son normalmente, puedes probar este formato de número en su lugar:
0;-0;
Ahora, si quieres que los ceros aparezcan siempre en blanco, puedes deshabilitar esta opción de Excel en la pestaña Avanzado:
Ajuste sus opciones de Excel exactamente como le gustan, con esta Opción de Excel de 5 pasos Healthcheck .
Añadir ceros a los valores
(Fuente: Campus Excel)
Ya hemos hablado de la diferencia entre incluir un # y un 0 en tu formato de número personalizado.
Ambos significan que estás mostrando un número, pero el «0» significa que tiene que ser mostrado.
Si añades símbolos de «0» extra al formato de los números, se añadirán dígitos extra al formato.
Si desea añadir ceros a la izquierda de sus números para que siempre se muestren cinco dígitos, ingrese esto como su formato de número personalizado:
00000
Puedes ver que los números más pequeños tienen ceros añadidos al principio. Esto se debe a que hemos requerido que se incluyan cinco dígitos en el formato.
Esto puede funcionar con decimales también – si quieres un número específico de decimales puedes tenerlos, pero Excel no añadirá ningún extra.
0.0000
Mostrar los valores en miles o millones
(Fuente: Excel Easy)
Si se trata de un número muy grande, se puede reducir fácilmente a valores más fáciles de entender.
Sólo hay que añadir una coma después del número para dividirlo por 1000.
Veamos un ejemplo:
0, «mil»
Cada coma adicional dividirá el valor por un factor de 1000, así que para el examen «0», dividirá el número por un millón. El «mil» es sólo un texto extra que se coloca después del número… puedes poner cualquier texto que quieras ahí.
Debes tener en cuenta que la coma tiene un uso alternativo en los formatos de números personalizados.
Si la coma está rodeada por un marcador de posición numérica (ya sea 0 o #) a cada lado, el número se mostrará con el separador de miles. Veamos un ejemplo de eso:
0,0 
Llenar las células con un carácter específico y repetido
(Fuente: Excel Easy)
Admito que no será una opción muy común para la mayoría de los usuarios de Excel, pero nunca sabrás cuando lo necesites.
He visto algunas plantillas internas de empresas anteriores en las que trabajé, que tenían este tipo de células:
Y se puede decir que alguien ha mantenido pulsada la tecla de subrayado para rellenar el resto del espacio blanco de cada celda.
Esto está bien – hasta cierto punto – hasta que necesite reorganizar su plantilla o cambiar el tamaño de algunas columnas.
Entonces estas células se verán feas e inacabadas.
Así que si quieres llenar cualquier espacio en blanco en la celda con un carácter específico, este formato de número personalizado te ayudará:
@ *_
Explicación: El símbolo «@» es un marcador de posición para el texto, y el símbolo después del asterisco es con lo que se llenará el espacio en blanco.
Si se cambia el tamaño de la columna en Excel, se puede ver que los subrayados se reducen en consecuencia:
Definitivamente es útil si estás creando formularios de usuario.
Transformar una fecha en su día de la semana
(Fuente: Extend Office)
Si quieres mostrar el día de la semana de una fecha determinada, puedes utilizar el formato de número personalizado en Excel para mostrarlo.
Por defecto, las fechas aparecerán en formato «01/05/2015» o «01-May-2015», de modo que siempre se puede seleccionar la opción de formato «Long Date» en la cinta de Excel para mostrar fácilmente el día de la semana. Si esto es suficiente para usted, continúe con la siguiente sección. Si no es así, ¡siga leyendo!
Para mostrar sólo el día de la semana de una fecha determinada, utilice cualquiera de los siguientes formatos de números personalizados:
ddd (eg: Sat)
dddd (ej: Sábado)
No es muy común mostrar sólo el día de la semana – estás sacrificando mucha información de fecha/hora.
Un uso común de esto es dar formato a las fechas en las Tablas Pivotantes con los valores de sus días de la semana.
De esta manera, puedes mejorar el valor de tu Tabla Pivotante agrupando los datos según su día de la semana. ¡Inténtalo!
¿Quieres un curso intensivo de aprendizaje de las mesas giratorias? Prueba el Tutorial de 7 minutos de PivotTable .
Lista completa de códigos de fecha y hora para formatos de números personalizados
Si desea combinar varios elementos de fecha/hora en su propio formato de número personalizado de Excel, consulte la siguiente tabla.
GroupCodeDescriptionExampleYearsyyyy Muestra el año como un número de cuatro dígitos.1995Yearsyy Muestra el año como un número de dos dígitos.95Monthsm Muestra el mes como un número sin cero inicial.7 (para Julio)Monthsmm Muestra el mes con un cero inicial.07 (para Julio)MessmmmMuestra el mes como texto, como una abreviatura.JulMonthsmmmmMuestra el mes como texto.JulioMonthsmmmmmMuestra el mes como un solo caracterJDaysdMuestra el día como un número, sin un cero a la izquierda.4 Díasd Muestra el día como un número, con un cero a la izquierda.04 Díasd Muestra el día como un día de la semana, como una abreviatura.VieDíad Muestra el día como un día de la semana, sin abreviaturaViernesHoraD Muestra la hora sin un cero a la izquierda.6 Hourshh Muestra la hora con un cero a la izquierda.06Hours[h]Muestra el tiempo transcurrido en horas (se utilizará cuando el valor del tiempo supere las 24 horas).32Minutesm Muestra el minuto sin un cero a la izquierda.3Minutesmm Muestra el minuto con un cero a la izquierda.03 Minutos[m]Muestra el tiempo transcurrido en minutos (se utilizará cuando el valor del tiempo exceda de 60 minutos).105SegundossMuestra el segundo sin un cero inicial.9Segundossscon un cero inicial.09Segundos[s]Muestra el tiempo transcurrido en segundos (se utilizará cuando el valor del tiempo exceda de 60 segundos).74TimeAM/PM am/pm A/P a/p Convierte a tiempo de 12 horas. Muestra AM/am/A/a o PM/pm/P/p según la hora del día.4:14 PM
Desde la página de ayuda de Excel aquí.
¡Mejor y más rápido que usar las complicadas funciones IF() después de usar la función WEEKDAY()!
Alguna jerga sobre la revisión de datos y el deseo de entender la distribución de las fechas.
Especificar el formato alternativo para los valores negativos, cero y de texto (incluyendo los colores)
(Fuente: Excel Tactics)
Anteriormente en el post, hablamos brevemente sobre la fijación de diferentes valores cuando un valor es negativo o cero.
Para reiterar, puede tener hasta cuatro secciones del formato de número personalizado, y deben ser introducidas en este orden:
- Valores positivos
- Valores negativos
- Valores cero
- Texto
Si alguna sección no está especificada en el formato de número personalizado, asume el formato de la primera sección.
Aquí hay un ejemplo de un formato numérico bastante complejo que utiliza las cuatro secciones:
[Verde]000;[Rojo](000);[Azul]»NULL»;General
Explicación: He especificado los colores que deben aplicarse a cada sección del formato de números – ten en cuenta que el color debe estar al principio, de lo contrario no funcionará. Para los valores cero, también he elegido mostrar el texto «NULL». Y para la sección de texto, puedo incluir la palabra «General» (sin comillas) para mostrar el tipo de formato de número General.
La lista completa de colores que se pueden utilizar son:
- [Negro]
- [Verde]
- [Blanco]
- [Azul]
- [Magenta]
- [Amarillo]
- [Cyan]
- [Rojo]
Añade una sangría (espacio en blanco) a la izquierda o a la derecha
(Fuente: MBA Excel)
Es posible que desee incluir un pequeño buffer o sangría al principio o al final de sus datos.
Si se añaden estos sangrados, los valores pueden alinearse mejor, si se quiere que los valores positivos o negativos aparezcan de forma diferente.
El código para añadir una sangría o un espacio en blanco es un guión bajo (_), seguido de cualquier carácter . El ancho de ese carácter adicional será el tamaño de la sangría.
Pruebe este formato de número personalizado para añadir un espacio en blanco sólo para los valores positivos:
_(#_);(#)
Explicación: El código «_(» crea un espacio en blanco igual al ancho del símbolo «(«. Lo mismo para el código «_)», que se coloca después del número. Con el formato de número anterior, los valores positivos tendrán paréntesis «invisibles» al principio y al final, mientras que los valores negativos mostrarán realmente los paréntesis. Como resultado, los números aparecen alineados, independientemente de si las celdas están alineadas a la izquierda o a la derecha.
Puedes usar cualquier personaje después del guión bajo. Si quieres una sangría más grande o un espacio en blanco, considera un carácter como la «m» o la «w».
Mostrar los números como números de teléfono o fax
(Fuente: página de ayuda de Microsoft Office)
Si alguna vez ha descargado un volcado de datos en bruto de información de contacto (por ejemplo, sus clientes o proveedores), es muy probable que reciba los números de teléfono en un formato de número estándar, sin guiones, espacios o cualquier otro tipo de marca.
A continuación se presentan algunos de los formatos de números más comunes que puede utilizar:
EE.UU./Canadá: (###) ###-####
Reino Unido (Londres): (###) ########
Australia: (##) #### ####
India: ####-######
(Los valores anteriores son todos generados al azar, así que espero que ninguno de ellos sean verdaderos números de teléfono de América del Norte. ¡Por favor no llames a estos números!)
Como alternativa, puedes usar la categoría «Especial» en el cuadro de diálogo «Formato de las células». Para los números de teléfono de EE.UU., el formato de número de teléfono por defecto está listado allí bajo la localidad inglesa (EE.UU.).
Lista completa de los caracteres del formulario de números personalizados
A continuación se encuentra la lista completa de caracteres especiales que puede incluir en el campo Formato de número personalizado:
CharacterDescriptionInputCustom FormatOutput0Placeholder para dígitos (números). Mostrará ceros a la izquierda si hay menos dígitos que los caracteres ‘0’.1230000000123#Placeholder para dígitos (números). No añade ningún cero a la izquierda. 123#####123? Marcador de posición para dígitos (números). Añade espacios en blanco para ceros innecesarios al principio o al final del valor, para que los puntos decimales siempre aparezcan en el mismo espacio en la columna.</t