Formato personalizado en Excel

En los exámenes de Excel para oposiciones suelen incluir preguntas donde nos proponen aplicar un formato concreto al contenido de una celda. Este formato en ocasiones no se adecua a los predeterminados que nos proporciona la aplicación, con lo que tendremos que usar la opción formato personalizado en Excel.

Esta opción nos permite aplicar formatos especiales, casi lo que permita nuestra imaginación, o mas bien la imaginación de los miembros del tribunal.

Para acceder al formato personalizado nos situamos en la celda en cuestión y pulsamos botón derecho - formato de celdas. También podemos de forma más ágil acceder con la combinación de teclas Ctrl+1.

En la pestaña número vemos un listado donde la última opción es personalizada. Si accedemos a la opción nos aparece otro listado con una serie de formatos "por defecto" que podríamos usar si casan con nuestras necesidades. Como podemos ver en un primer vistazo, podemos aplicar formatos de todo tipo, tanto numéricos, de fecha, de texto, etc.

Lo normal es que tengamos que crear el formato desde cero, partiendo de las especificaciones que nos proporcionen en el examen. Para ello, usaremos una serie de valores comodines o códigos que representan unos símbolos determinados. Veamos esos códigos:

  • # : representa un número sin considerar ceros a la izquierda. Es decir, si la celda tiene el valor 0, aparecerá vacía.
  • ? : deja el espacio para los caracteres especificados
  • 0 : despliega ceros a la izquierda para rellenar el formato. Si la celda tiene el valor 0, aparecerá un 0.
  • ; : no se trata de un código propiamente dicho. Se usa como separador para representar distintos formatos siguiendo la siguiente directiva: <POSITIVO>;<NEGATIVO>;<CERO>;<TEXTO>
  • . : despliega un punto decimal
  • % : despliega el símbolo de porcentaje
  • , : despliega el separador de miles
  • E+ e+ E- e- : despliega la notación científica
  • + – / () : $ : se muestra este carácter
  • carácter : despliega el carácter especificado
  • *carácter : repite carácter hasta llenar el ancho de la columna.
  • _ :deja un espacio del mismo ancho que el siguiente carácter
  • “texto” : despliega el texto dentro de las dobles comillas
  • @ : representa un texto
  • [color] : especifica el color de la fuente que puede ser: Negro, Azul, Cian, Verde, Magenta, Rojo, Blanco, Amarillo.
  • [COLOR n] : muestra el color correspondiente de la paleta de colores donde n es un número entre 0 y 56.
  • dd mm aa: representa día, mes y año en número. Si colocamos aaaa veremos el año con 4 cifras. Con mmm veremos los tres primeros caracteres del mes en texto y con mmmm nos aparece el mes completo. Igual para el día.
  • h mm ss: representa hora, minuto y segundos con dos dígitos.

Para aplicar un formato personalizado tendremos que combinar estos códigos de forma que obtengamos el formato que necesitamos. Vamos a ver algunos ejemplos respresentativos para entenderlo un poco mejor:

  • #.##0,00: aquí estamos diciendo que queremos representar un número con el separador de miles y con dos decimales. Por ejemplo el número 1234 se vería como 1.234,00.
  • #.##0 €;[Rojo]-#.##0 €: con este formato representamos un número con el separador de miles y el símbolo del euro. En caso que el valor sea negativo el número aparecerá en rojo.
  • # ??: Para el número 1234 veríamos en la celda lo siguiente: 12 34. Es decir, deja un espacio de dos caracteres. Si cambiamos el ejemplo a # ??? el formato cambia a 1 234.

Si estás interesado en realizar test de informática que incluyen preguntas de Excel, informática básica, Internet y otras aplicaciones ofimáticas, puedes descargar nuestros test de informática para oposiciones.


Si consultamos exámenes de años anteriores de administrativo y auxiliar administrativo podemos ver algunas preguntas en las que tenemos que aplicar el formato personalizado. A continuación mostramos dos de esas preguntas:

  • Segundo Llamamiento examen excel Administrativo 2017. En la pregunta 20 nos indican que tenemos que coger una fecha de una celda, restarle 75 días y mostrarla con el formato Año.Mes.Dia con cuatro cifras para el año, dos para el mes y una o dos para el día. En la siguiente captura vemos la solución:

En la celda D2 tenemos la fecha de hoy y en la celda D3 obtenemos la solución. Para restar los días basta con hacer la operación de resta directamente sobre la fecha (D2 - 75). Para el formato, como no lo tenemos en los formatos de fecha por defecto, tenemos que usar el formato personalizado tal y como se muestra. Como vemos, para que el día se vea con uno o dos dígitos debemos poner solo una "d". Si incluyéramos dos, para el ejemplo, veríamos un 0 delante del 4.

  • Tercer Llamamiento examen excel auxiliar administrativo 2017. En la pregunta 8 nos piden dar formato a dos columnas de valores (valores aleatorios en el ejemplo) y nos indican el formato exacto. Este ejemplo es más sencillo puesto que basta con poner en el cuadro tipo de formato personalizado los caracteres tal y como lo indican en el enunciado. Lo vemos:

En la captura se ve como rellenamos el formato personalizado para la columna INVENTARIO2. Para INVENTARIO1, haríamos lo mismo introduciendo los siguientes caracteres, según nos indica el ejercicio: #.###0,00%

Como podéis ver, las preguntas de formatos son sencillas en los exámenes de excel para oposiciones. Si tenemos claro el concepto, no será complicado aplicar lo que nos pregunten.

Y eso sería todo en esta entrada de formatos personalizados en excel. Si te surge alguna duda o pregunta puedes consultarme a través de los comentarios y estaré encantado de ayudarte.  ¡Hasta la próxima!

28 comentarios en «Formato personalizado en Excel»

  1. Buenos días, como hago para personalizar las celdas y que me salgan las fracciones en pulgadas? osea si yo pongo 1/2(media pulgada) en la celda me aparezca 1/2", no me acepta las comillas dobles cuanto personalizo la celda como 0" "

    Responder
  2. buenos dias, en las celdas tengo numeros con sus respectivos signos (ejemplo -3, +0, +8), y quiero que al convertir la celda a texto me mantengan los signos (lo hace para los valores negativos pero no para los positivos). Gracias

    Responder
  3. Hola, existe alguna manera para ingresar códigos con letras? por ejemplo:

    A / 1 -2

    "A" es un ejemplo, la idea es ingresar cualquier letra del abecedario como primer código, los numero ya se.

    Y yo sólo ingresar cualquier letra y aparezca ese formato

    Escribir A12

    Responder
  4. Buenas tardes, tengo una duda, en una celda en C1 tengo el formato dd-mmm-aa, y tengo 01-feb-20, de esta celda quiero 40 días laborales menos, en otra columna pongo =DIA.LAB.INTL(C1;-40;11) pongo11 porque solamente es festivo los domingos y cuando pulso enter me sale 04/02/2020 no resta bien, si lo hago sin el formato dd-mmm-aa si resta bien y pone 17/12/2019 como puedo conseguir hacerlo con el formato de la celda C1. Muchas gracias

    Responder
    • Hola Teresa, qué raro, acabo de probar lo que comentas y funciona correctamente. ¿Cómo pones el formato? ¿Te has asegurado que ambas celdas son de tipo Fecha?

      Responder
  5. Muy buenas:
    Me gustaría aplicar dos formatos diferentes de la siguiente manera:
    Si es un numero entero lo muestra con punto de miles pero sin decimales
    Por ejemplo 1349 = 1.349
    Pero si es un numero con decimales quiero que muestre un máximo de dos:
    Por ejemplo 1349,361 = 1.349,36
    podría ser con símbolo de € al final o no.
    el formato #.##0,00 no sirve porque queda 1200 = 1.200,
    con esa coma colgando.
    Lo tengo medio resuelto con formatos condicionales, pero me gustaría que fuera con formato normal.
    Muchas gracias

    Responder
    • Hola Gerardo, esto no se puede hacer simplemente con formato normal. Tienes que aplicar formato condicional que tome el valor de una fórmula.

      1. Selecciona la columna de los datos que quieres formatear y aplica el tipo Número con dos decimales y separador de miles.
      2. Vuelve a seleccionarla y ve a Formato condicional > Nueva regla.
      3. Ahí elige la opción "Utilice una fórmula que determine..."
      4. Introduce esta fórmula (sustituye $A1 por la primera celda de la columna en la que quieras aplicar el formato): =RESIDUO($A1;1)=0
      5. Pulsa Formato... y en la pestaña Número, opción Número selecciona separador de miles y quita los decimales.
      6. Acepta todo y debe funcionarte correctamente.

      Un saludo.

      Responder
  6. Hola,
    Ayuda, necesito que el texto que ingrese en la celda, me que sirmpre en Mayusculas, aun cuanyo se haya escrito en minusculas, se puede hacer desde formato personalizado?

    Muchas gracias

    Responder
    • Hola Edwin, no es posible, tendrías que usar una macro si quieres que se cambie sobre la propia celda. Si puede ser en otra celda puedes usar la función MAYUSC.

      Un saludo.

      Responder
  7. BUENAS!! Necesitaría dar formato a una celda de texto con un espacio (o un guión medio) cada 4 caracteres para números de cuenta, es decir:
    ES12 1234 1234 1234 1234 12324
    He probado con el formato: "ES"## #### #### #### #### #### pero me da error
    Por eso he pensado en hacer todo como texto. Espero que me puedan ayudar. UN SALUDO!

    Responder
    • Hola Verónica, el formato personalizado no te va a funcionar para este caso, ya que Excel admite como máximo 15 dígitos numéricos. Puedes hacerlo aplicando la siguiente fórmula. Tienes que introducir el CCC en la celda (en este caso J10) que debe estar previamente en formato TEXTO:

      =UNIRCADENAS(" ";VERDADERO;"ES"&IZQUIERDA(J10;2);EXTRAE(J10;3;4);EXTRAE(J10;7;4);EXTRAE(J10;11;4);EXTRAE(J10;15;4))

      Si prefieres guiones a espacios, puedes cambiar el primer argumento por "-".

      Un saludo.

      Responder
  8. Tengo una duda que no se si me podéis ayudar.
    Tengo los siguientes números, cada uno en una celda: 1,2,3,4,5,6,7,8,9,10... y me piden que usando formato personalizado aparezcan con 3 dígitos añadiendo 0, es decir, 001, 002...010..
    Yo lo que he hecho poniendo en formato personalizado 00## pero el problema es que al llegar al 10 no lo refleja como piden ya que aparecería 0010 y tiene que aparecer 010.
    ¿Alguna idea de como hacerlo?
    Muchas gracias!

    Responder
    • Hola Catalina, entendiendo que los valores de las celdas tienen 1 ó 2 dígitos, tendrías que aplicar una condición, quedando el formato de esta forma:

      [<10]00#;[>=10]0##

      Si el número es menor que 10, incluimos dos ceros y el número en cuestión. Si es mayor o igual, incluimos sólo un cero y los dos dígitos del numero.

      Un saludo.

      Responder
  9. Gracias Miguel. Si. Ya pensé en eso. Pero pensé que habría alguna función para hacerlo de manera elegante. En cualquier caso gracias por la molestia.

    Responder
  10. hola Carrasco, muchas gracias por tus explicaciones.
    ¿Podemos usar los formatos personalizados utilizando funciones en lugar de los códigos que indicas? Me gustaría que al escribir una celda se añadiese una letra que existe en otra celda B. La celda B puede tener cualquier letra en cada momento. Gracias!!!

    Responder
    • Hola Rober, de nada, para eso estamos.

      Para lo que tu indicas tendrías que usar una tercera celda en la que pivotar, usando el operador de concatenación, por ejemplo:

      A1: texto a introducir
      B1: letra que quiero añadir
      C1: =A1&B1

      Responder
  11. Para un formato de este estilo 000+000 (letra): ejemplo 49+543 D, cual seria el formato? Tengo hasta 0+000 pero no puedo hacer que me aparezca la letra

    Muchas gracias de antemano.

    Responder
      • Ejemplo: 354A = 000+354 A
        O también puede ser 1234 A = 001+234 A cualquiera de los 2 me funcionaria.

        La diferencia sería el espacio entre los números y letras en la entrada.
        Muchas gracias.

        Responder
        • Al incluir texto con números dentro de una celda ya no podemos aplicarle el formato de número personalizado. Puedes probar usando funciones de texto que modifiquen los valores de entrada para adaptarlos a la salida que necesitas.

          Responder
  12. como puedo capturar el contenido de una celda con formato personalizado.
    Es decir si en una celda tengo "1,8 Mg" donde el formato aplicado es "0,0 Mg".
    ¿como puedo captura lo mostrado en la celda, es decir 1,8 Mg.

    Responder
    • Hola Francisco, ¿a qué te refieres con capturar? Si lo que quieres es poder copiar las celdas que contienen esos y datos y que, al pegarlas, mantengan el formato tienes dos opciones:

      1. Que las celdas de destino tengan el mismo formato que las de origen
      2. Que en lugar de usar formato personalizado, metas el texto "Mg" concatenado al valor de la celda, por ejemplo en A2 poner =A1 & "Mg". En A1 pones el valor 1,8. Después copias la celda A2 y pegas VALORES.

      Si es otra cosa me cuentas

      Un saludo

      Responder

Responder a francisco Cancelar la respuesta