Formatos personalizados de números en Excel

Los formatos personalizados de números en Excel nos permiten crear diferentes tipos de formato que se adapten a nuestras necesidades. Cuando ninguno de los formatos prestablecidos es adecuado la mejor opción es crear un formato personalizado.

Crear formatos personalizados en Excel

Muchos usuarios de Excel, incluso algunos que llevan años de utilizar la herramienta, evitan crear formatos personalizados porque creen que es una tarea muy complicada. En realidad los formatos personalizados de números se ven más complejos de lo que en realidad son.

La manera más fácil de crear un formato personalizado es pulsando la combinación de teclas Ctrl + 1 para desplegar el cuadro de diálogo Formato de celdas y asegurarnos de que estamos en la sección Número. Es entonces cuando seleccionamos la categoría Personalizada.

Formatos personalizados de números en Excel

Un formato personalizado se compone de una serie de códigos los cuales se especifican en la caja de texto Tipo de manera que el valor de una celda adopte el nuevo formato.

Partes de un formato personalizado

Un formato personalizado nos permite especificar 4 códigos dentro de la misma cadena de texto: valores positivos, valores negativos, valores cero y texto. Cada código debe ser separado por un punto y coma. Considera el siguiente ejemplo de un formato personalizado:

[Verde]Estándar;[Rojo]Estándar;[Negro]Estándar;[Azul]Estándar

En primer lugar observa que los 4 códigos están separados por un punto y coma. El primero código indica que los valores positivos serán de color verde. El segundo código nos dice que los valores negativos serán de color rojo. El tercer código hará que los valores que sean cero tengan un color negro y el último código hace que todas las celdas que sean de tipo texto deberán ser de color azul. Observa cómo se comporta este formato personalizado al aplicarlo a celdas con diferentes valores:

Crear un formato de números personalizado en Excel

La palabra Estándar que aparece dentro del formato personalizado que acabamos de crear significa que el número se mostrará en un formato estándar y solamente le aplicaremos un color. Es importante notar que el ejemplo mostrado hace uso de los nombres de colores los cuales podemos utilizar en nuestros formatos personalizados.

Omitir un código en el formato personalizado

Aunque en el ejemplo mostrado especifiqué los 4 códigos, no siempre es necesario hacerlo. Podemos omitir algunos de los códigos del formato personalizado tomando en consideración lo siguiente:

  • Si solamente especificamos un solo código entonces se aplicará para todos los valores (positivo, negativo, cero y texto)
  • Si especificamos dos códigos, el primero aplicará para los valores positivos, los ceros y el texto, mientras que el segundo código será para los valores negativos.
  • Si especificamos tres códigos, el primero será para los valores positivos y el texto, el segundo código para los valores negativos y el tercer código para los ceros.

Observa el resultado de aplicar un formato personalizado con tres secciones solamente:

[Verde]Estándar;[Rojo]Estándar;[Negro]Estándar

Definir formatos personalizados de números en Excel

Por último debo decirte que cuando creas un formato personalizado en Excel frecuentemente hacemos varios intentos hasta llegar al formato que deseamos. Cada vez que editamos la cadena de texto del formato personalizado, Excel la agrega a la lista como un nuevo formato disponible. Así que cuando hayas logrado el formato personalizado que necesitas  asegúrate de eliminar aquellos formatos que solamente fueron pruebas y que no necesitarás de nuevo.

En el siguiente artículo mostraré el detalle de los códigos que podemos utilizar en nuestros formatos personalizados.

31 comentarios en “Formatos personalizados de números en Excel

  1. Maarcoos

    Hola Moises , tengo una duda el signo # que funcion tiene dentro del formato , es lo mismo #,## que #,#
    Quisiera que me saques de esa duda…
    Gracias

    1. Moisés Ortíz Autor

      Hola Marcos, el signo # representa la posición que ocupará un número por lo que #,## es muy diferente a #,#. Si tienes una celda con el valor 100.12345 y le aplicas el primer formato personalizado obtendrás como resultados 100.12 pero si aplicas el segundo formato obtendrás como resultado 100.1

  2. Martin Talavera

    Que tal moises, no sabras alguna de manera de ponerle color a la celda segun la fecha?
    ejemplo. si tengo 9-jan en una celda y en otra voy a poner la fecha en la que se cumplio el objetivo, lo que quiero es que si la fecha en que se cumplio es 9-jan se ponga verde la celda y si se paso que se ponga roja.
    Espero me hayas entendido.
    Gracias

  3. FabiolaG

    Hola Moisés, tengo una duda, si tengo un intervalo de -1.5 a 1.5 y quiero que esos numeros sean en verde (incluyendo el 0) y si me salgo de ese intervalo, necesito que esten en rojo, como le hago?

  4. Mari Carmen

    Hola Moisés, tengo un problema. tengo excel 2007, y necesito cambiar las comas del sistema decimal español por puntos (inglés) pero que esto me aparezca como tan el los respectivos ejes. Consigo cambiarlo en la tabla de datos, pero al representarlos, los ejes siguen en sistema español y mi necesidad es que en los ejes aparezcan los números decimales como puntos y no como comas (en vez de 1,2 quiero 1.2)
    Muchas gracias!

    1. Moisés Ortíz Autor

      Hola Mari Carmen, esto no es un cambio de Excel si no de Windows y debes hacerlo desde el Panel de Control y la opción Configuración regional y de idioma. Consulta el siguiente artículo que, aunque el tema principal es sobre el separador de argumentos de funciones, te dará una idea de dónde debes hacer la modificación en tu equipo para el símbolo decimal:
      http://exceltotal.com/separador-de-argumentos-de-funciones-en-excel/

  5. Rony

    Buen día, quisiera personalizar las celdas para efectos de presentación, especificamente el número de R.I.F. de empresas que en un archivo lo tengo todo pegado ejemplo JXXXXXXXXX pero, quiero aplicarle formato personalizado para que me lo muestre J-XXXXXXX-X. Ayuda pleaseee. Gracias de antemano

    1. Moisés Ortíz Autor

      Rony, el formato de celdas no te ayudará en esto. Te recomiendo utilizar una fórmula. No conozco las reglas para dar formato a un número R.I.F. pero para el ejemplo que explicas puedes utilizar la siguinete fórmula:
      =IZQUIERDA(A1,1) & “-” & EXTRAE(A1,2,7) & “-” & DERECHA(A1,1)

  6. PAULA

    hola como hago para que excel 2010 me pinte de un color una fila de una planilla según el estado de la misma.
    desde muchas gracias

  7. Lupita

    Sus artículos son excelentes!! sigan haciendo más, gracias! saludos.

  8. CRISTINA

    Buenos días,
    desde el sistema de mi empresa debo exportar datos a un excel pero cuando quiero aplicar la fórmula de BUSCARV (VALOR) el excel no puede leer los datos exportados desde mi sistema por lo que me da un aviso de #N/A.
    he intentado hacer las celdas tengan el mismo formato (general, personalizado, etc)he incluso he multiplicado x1 los valores para asegurarme que se “convierten” en número pero no obtengo resultados con la fórimula BUSCARV. ¿qu´puedo hacer ?

    1. Moisés Ortíz Autor

      Hola Cristina, definitivamente tiene que ser un problema con el formato. Siempre que se busca un valor que no coincide con el tipo de la columna de búsqueda, la función BUSCARV devuelve #N/A.
      Es importante que el formato de las celdas lo apliques antes y no después de trabajar con los datos. No sé como estás haciendo la multiplicación por 1, pero lo que a mi me funciona es utilizar el Pegado especial con multiplicación, para saber más de este método consulta el siguiente artículo:
      http://exceltotal.com/multiplicar-con-pegado-especial/

  9. Miguelangel

    Una pregunta ya pude hacer que colocara el color dependiendo del resultado (positivo o negativo), pero como hago que me logre dar los decimales?, Gracias

    1. Moisés Ortíz Autor

      Hola Miguelangel, debes agregar el punto decimal seguido por el carácter # el número de posiciones que deseas mostrar, por ejemplo: #.## para mostrar dos decimales. Puedes leer un poco más sobre el formato personalizado de celdas en el siguiente artículo:
      http://exceltotal.com/formato-personalizado-de-celdas-en-excel/

  10. Dania

    Hola, Moisés:
    He encontrado muy interesantes y útiles tus artículos sobre el formato personalizado – este y el titulado “Formato personalizado de celdas en Excel” -. Sin embargo, después de la lectura de ambos sigo sin solucionar mi problema: quiero que los títulos del eje vertical de mi gráfica incluyan el signo positivo para los valores mayores o iguales que cero y el signo negativo para los valores menores que cero, pero los formatos personalizados que he probado:
    +#;-#
    +#;-#;+#
    +#;-#;+”0″
    muestran como quiero los números mayores y menores que cero, pero en el caso del 0, en el eje vertical sólo aparece el signo +, no +0 como deseo. ¿Alguna idea sobre cómo conseguirlo? Muchas gracias y enhorabuena por tu blog.

    1. Moises Ortiz Autor

      Hola Dania, el formato personalizado correcto sería el siguiente:
      +#;-#;+#0
      Solo te faltaba colocar el cero al final.

  11. André Noriega

    Hola Moises, una pregunta. Si por ejemplo me piden que cada vez que escriba un número el excel me bote en un formato personalizado de la siguiente manera: Por ejemplo escribo 46 y quiero que el excel me bote 00004-00000000006 como lograria esto. Gracias
    Lo he intentado dando el formato siguiente 0000#-00000# pero me bota los números juntos cuando lo que yo quiero es separarlos.

    1. Moisés Ortíz Autor

      Hola André, eso no podrás lograrlo con el formato personalizado. Solo podrás hacerlo con fórmulas:
      =TEXTO(IZQUIERDA(A1, 1), “00000”) & “-” & TEXTO(DERECHA(A1, 1), “00000000000”)

      1. André Noriega

        Muchisimas gracias, me sirvio bastante. Lo que pasa es que estoy trabajando con un data sheet y necesitaba crear un formato para que al momento de poner 12-9325 me salga automáticamente 0012-0000009325

  12. Pedro León

    Excelentes tutoriales¡¡¡¡¡ He aprendido bastante, gracias.

  13. Ponxo Beltrán

    Hola Moisés, revisando los formatos personalizados no he encontrado la utilidad o diferencia entre las funciones #,##0;-#,##0 y #,##0. Dado que si escribo un número negativo utilizando cualquiera de los dos formatos lo pone igual. Ojalá puedas mostrarme cual sería la diferencia. Me llama la atención sobretodo porque en el cuadro “personalizado” este caso corresponde a la tercera y quinta opción, así como la cuarta y séptima opción que trae por “default”. Ojalá puedas resolver mi duda, que me esta matando lentamente… aggg. Gracias.

    1. Moisés Ortíz Autor

      Hola Ponxo, no existe ninguna diferencia ya que al utilizar el formato #,##0 y omitir una especificación para los valores negativos, Excel aplicará el formato por defecto a los números negativos que es precisamente -#,##0

  14. Seba

    Buenos dias, una consulta, yo estoy editando los valores de las etiquetas de datos de un gráfico en excel 2010, me cree este modo de personalización
    #,##+0.0;[Rojo](#,##0.0);0

    1. Seba

      Lo que necesito es que los positivos esten de color verde oscuro, si pongo el código [verde] adelante quedan en un verde muy claro y no me termina siendo útil, cuales son los codigos o formas para poder poner otros colores??

      1. Moisés Ortíz Autor

        Hola Seba, solo se puede elegir un color dentro de 56 opciones posibles. Para indicar cada uno de esos colores debes utilizar la siguiente nomenclatura:
        #,##+0.0;[Color10](#,##0.0);0

        Podrás elegir desde [Color1] hasta [Color56].

  15. Víctor

    Hola Moisés, muchas gracias por todos los artículos, los que me han sido de mucha utilidad. En relación al formato personalizado he tratado, en formato de contabilidad, de hacer una diferencia entre valores en $ normales y otros en dólares, tratando de que aparezcan como US$, lo que me ha sido imposible configurar. Desde ya te agradezco cualquier ayuda que me puedas brindar.

    1. Moisés Ortíz Autor

      Hola Víctor, para agregar las letras “US” antes del símbolo de $ debes incluir en el formato personalizado la cadena “U\S”. Por ejemplo:
      U\S$#,##0.00

      1. Víctor

        Excelente, muchas gracias.