Los formatos personalizados de Excel te permitirán controlar el aspecto con el que deseas mostrar los datos. De esta forma no tendrás por qué restringir tus posibilidades a los diferentes formatos estandarizados que te ofrece Excel.
En muchas ocasiones te verás en la necesidad de especificar el tipo de unidad al que se refiere un dato de una celda determinada, o que aparezca un texto en lugar de un cero. Utilizar las propiedades del formato personalizado te permitirá ofrecer una información adicional que dará a tus documentos un aspecto más profesional. Deberías controlar los formatos personalizados para darle un "toque personal" a tu Excel.
En muchas ocasiones te habrás encontrado con la necesidad de especificar a qué se refiere el dato que contiene una celda.
Imaginemos que tienes una tabla con las características de vehículos. En cada celda de una columna escribes los datos específicos de cada modelo que contiene la tabla.
Por ejemplo, la potencia (cv), la cilindrada (cc), las dimensiones (mm), capacidad del depósito en litros (l), la aceleración de 0-100 (s), la velocidad máxima (Km/h)...
Entre paréntesis he puesto las unidades a las que se refiere cada magnitud. En principio podrías pensar que las escribes con el dato y listo. Y es cierto, puedes hacerlo, pero si tuvieras la necesidad de realizar cualquier operación con los datos te encontrarías con serios problemas, pues los datos serían caracterizados como texto.
Para solucionarlo, puedes hacer uso de los formatos personalizados. En este artículo te mostraré cómo personalizar datos numéricos o fechas a tu antojo.
Videotutorial
Si eres de los que te gustan las cosas rápidas te dejo a continuación un videotutorial con las principales aspectos que podrás encontrar con más detalle en este artículo. Espero que lo disfrutes.
Navegación
Acabo de presentarte un videotutorial en el que te explico detalladamente todo lo que necesitarás saber para poder crear tus propios formatos personalizados.
En todos los apartados del artículo irás encontrando una referencia al minuto y segundo del videotutorial donde se explica visualmente esa parte.
Además, a continuación, podrás hacer uso de la tabla de contenido para acceder directamente al apartado que quieras leer. No tienes más que hacer clic en el enlace.
Formatos personalizados: Dónde crearlos
Vídeo: Formatos personalizados en Excel
Inicio: 2'38''
Es lo primero que debes conocer. ¿Dónde crear un formato personalizado en Excel?
Los formatos personalizados se introducen en el mismo cuadro de diálogo de Formato de celda que cualquier otro formato de celda.
Para abrirlo debes utilizar la solapa de Inicio y pulsar en el menú emergente Formato de número y en la pestaña de la izquierda que indica General, seleccionar Personalizado.
TRUCO
Una forma rápida de abrir el cuadro de diálogo Formato de celdas es utilizar la combinación de teclas Ctrl + 1.
En la parte derecha de este cuadro de diálogo observarás muchas opciones de formatos donde además se irán agregando los formatos personalizados que vayas creando. Podrás seleccionar cualquiera de ellos y modificarlo para conseguir el aspecto que pretendes alcanzar.
Si lo que deseas es crear un nuevo formato personalizado deberás introducirlo en el cuadro de texto etiquetado como Tipo.
A medida que vayas escribiendo, en la parte superior, el área marcada como Muestra te irá enseñando el resultado de aplicar tu formato personalizado al contenido de la celda que tuvieras seleccionada al iniciar este proceso.
Formatos personalizados para números
Vídeo: Formatos personalizados en Excel
Inicio: 6'35''
Código con secciones de los formatos personalizados de números
Los formatos de números en Excel están formados por un código que contiene cuatro secciones perfectamente diferenciadas.
Su estructura es la siguiente:
<POSITIVO>;<NEGATIVO>;<CERO>;<TEXTO>
Cada sección se encuentra separada de la siguiente por un punto y coma (;). En el caso del formato de número existen cuatro secciones que definen su formato en el orden mostrado.
En la imagen del comienzo del artículo te puse un ejemplo de definición de formato personalizado para un número. Te la muestro de nuevo.
No te preocupes ahora si no acabas de comprender su significado. Ese es el objeto de este artículo. Cuando acabes de leerlo dominarás la creación de formatos personalizados.
Otra cuestión interesante que debes conocer sobre esté código de cuatro secciones es que no es obligatorio introducir todas las secciones.
Si solo indicas las dos primeras secciones, la primera se utilizará para los números positivos y para el valor cero y la segunda se utilizará para los valores negativos.
[AZUL]#.0;[ROJO]"Faltan "#.0
Como ya podrás imaginar, si sólo especificas una sección, se utilizará para todos los números (positivos, negativos y cero).
[AZUL]#.0
Si deseas indicar la primera y tercera sección, deberás insertar el punto y coma de la segunda sección.
[AZUL]#.0;;[NEGRO]"Cero"
Formato personalizado de números con texto
¿Qué supones que ocurrirá si intentamos sumar dos celdas como las de la siguiente imagen?
Como puedes observar en la celda B8 se hace la suma de las dos celdas que están encima y el resultado es 0. No suma nada porque no encuentra datos numéricos.
Para poder introducir ese formato personalizado que incluye la partícula "uds." habrá que definir la parte numérica y la parte de texto en la sección que desees cambiar.
Veamos ahora los caracteres modificadores que intervienen en la creación de formatos personalizados:
- La # muestra un dígito o nada en la parte no significativa del número (a la derecha de la coma decimal).
- El cero (0) pondrá tantas posiciones decimales como ceros pongas en la parte menos significativa.
En el siguiente cuadro podrás observar la diferencia significativa que supone poner # ó 0 en tus formatos personalizados de números.
NOTA
En la imagen anterior, en las cifras con tres decimales, se produce un redondeo a 5,37 pero el valor de la celda sigue siendo 5,365 a efectos de cualquier cálculo.
- Para utilizar el separador de millar deberás introducir el punto en el formato personalizado. Basta con que muestres un punto y ya aparecerá cada tres cifras significativas. Por ejemplo #.### Por cierto, que este formato es equivalente a #.0
- El separador decimal, como ya habrás observado en la imagen anterior, es la coma (,)
- El interrogante (?) te servirá para alinear por la coma decimal varios valores con un formato personalizado en una misma columna, como se muestra en la siguiente imagen:
- Si deseas introducir Texto, lo deberás entrecomillar. En el ejemplo que puse al comienzo de este apartado podría quedar el formato personalizado de la siguiente manera: #.###" Uds." Debes fijarte que hay un espacio al comienzo del texto para evitar que aparezca unido a la cifra introducida en la celda.
- El [color] es un modificador que te permitirá designar de qué color quieres que aparezcan los datos de cada sección del código. Por defecto, deberás introducir el nombre de un color como negro, azul, cian verde, magenta, rojo, amarillo o blanco.
TRUCO
Si no quieres restringirte a los colores indicados, siempre puedes utilizar este truco. En la etiqueta con el color introduces la palabra [COLORXX] y sustituye las XX por un código numérico entre 0 y 56.
Ya estás en disposición de comprender lo que pasaría con el ejemplo que puse en la imagen del comienzo del artículo y que te vuelvo a mostrar a continuación
[AZUL]#.0;[ROJO]"Faltan "#.0;[NEGRO]"Cero";[Verde]
Tenemos las cuatro secciones:
- Números positivos: Aparecerán en azul, con separadores de miles y sin decimales.
- Números negativos: Aparecerán en rojo, sin decimales, con separadores de miles y precedidos por el texto "Faltan "
- Valores cero: Los valores ceros serán representados por el texto "Cero"
- Textos: Aparecerán en verde.
Ahora te propongo un ejercicio. Te pondré la solución al final del artículo.
El objetivo de este ejercicio es imitar el resultado que se observa en la columna de la izquierda. Para ello dale el formato personalizado a las celdas de la columna de la derecha.
Formatos personalizados para fechas
Vídeo: Formatos personalizados en Excel
Inicio: 24'48''
Al igual que has observado para el caso de los datos numéricos, la fechas también pueden ser objeto de cualquier formato personalizado que se te ocurra.
De igual manera, podrías introducir todo el texto junto con las fechas para mostrar estos datos como te apetezca, pero al igual que con los datos numéricos, si luego deseas aplicarle cualquier cálculo pues tendrás problemas.
por ejemplo, para determinar el número de días que hay entre dos fechas basta con restar la más antigua de la más actual. Obtendrás un entero con el número de días que las separa.
Sin embargo, si decides escribir tus fechas con algo de texto, no podrás hacerlo. No podrás restarlas. Obtendrás un error del tipo #¡VALOR! pues Excel entenderá que pretendes restar dos cadenas de texto.
Los modificadores utilizados para crear formatos personalizados de fechas son los siguientes:
El primer grupo de modificadores denotados por utilizar la letra "d" se utilizan para designar el formato que deseas darle al día. Puede adoptar los siguientes estados:
- d: Muestra el día del mes entre 1 y 31. Para los días del 1 al 9 no le pone delante un 0.
- dd: Igual que el anterior pero antepone un cero cuando el día va del 1 al 9.
- ddd: Fórmula corta para el día de la seman de lunes (lun) a domingo (dom)
- dddd: Fórmula larga para los días de la semana. El nombre del día completo.
El segundo grupo de modificadores indicados con la letra "m" son para indicar cómo quieres que aparezca el mes. Tienes estas opciones.
- m: Muestra el número de mes entre 1 y 2. Para los mes del 1 al 9 no le pone delante un 0.
- mm: Igual que el anterior pero antepone un cero cuando el mes va del 1 al 9.
- mmm: Fórmula corta para el nombre del mes de enero (ene) a diciembre (dic)
- mmmm: Fórmula larga para los nombres del mes. El nombre del mes completo.
En el tercer grupo, como ya puedes imaginar, se trata el aspecto que quieres que tenga el año. La opciones son algo más escasas:
- a: Muestra el número del año a dos cifras.
- aa: Muestra el número del año a cuatro cifras.
Si quieres introducir texto en el formato personalizado de fechas, el tratamiento es el mismo que se le dio en el formato personalizado de números.
Para ejemplificar el formato personalizado de fechas vamos a realizar en una celda el siguiente juego. Al introducir la fecha en la que naciste, Excel te va a devolver la frase "Yo nací un " seguido del día de la semana en que naciste.
En el ejemplo de la imagen anterior en la ceda B20 he introducido la fecha en la que yo nací. Siiiiii, hace nada que fue mi cumpleaños. Ainsssss que mayor me estoy haciendo 😉
En el cuadro de diálogo Formato de celdas puedes ver qué sencillo es el formato personalizado para conseguir nuestro objetivo. Basta con poner entre comillas el texto "Yo nací un " seguido de cuatro "d" para que me indique el día de la semana completo.
Como no podía ser de otra forma aquí te traigo un nuevo ejercicio para que trabajes con el formato personalizado para fechas. Se trata de restar dos fechas a las que previamente les habrás puesto el formato personalizado indicado en la siguiente imagen.
Las celdas que muestran las fechas 12/04/2015 y 07/04/2015 deberán mostrar el aspecto que tienen las que se encuentran justo encima respectivamente.
A la derecha, la celda que muestra "5 días" es en realidad una operación de resta de ambas. A su vez también tiene su propio formato personalizado.
Formatos personalizados para textos
Vídeo: Formatos personalizados en Excel
Inicio: 32'23''
Puedes pensar que esto no tiene mucho sentido. ¿Para qué crear formatos personalizados en celdas que van a contener texto si se puede escribir en la celda lo que se quiera y ya está?
Pues por eso mismo. Para no tener que escribirlo todo. Para ahorrar tiempo a la hora de introducir los datos.
Un ejemplo claro de esto es imaginar la siguiente situación. Quiero realizar un listado de cursos y para cada tipo de asignatura debo introducir la cadena previa de "Asignatura de" seguida del nombre de la propia asignatura.
Con un formato personalizado haces que Excel introduzca la parte común y ya sólo tienes que escribir en la celda el nombre de la asignatura para obtener el resultado completo.
Para los formatos personalizados de textos hay un único modificador a tener presente. Se trata de la @ (arroba). Donde la introduzca en tu composición del formato personalizado, tomará el dato introducido en la celda y sustituirá la arroba por él.
En el ejemplo de la imagen anterior el formato personalizado a introducir en las celdas que están en la derecha será el siguiente:
"Asignatura de "@
Soluciones a los ejercicios propuestos
En el apartado de formatos personalizados para números te propuse un ejercicio. Aquí tienes su solución:
#.0" uds.";"Me deben "#.0" uds."
En el apartado de formatos personalizados para fechas te propuse otro ejercicio. Aquí tienes su solución:
Para la fecha 12/04/2015
"Llegará el día "dd" de "mmmm" de "aaaa
Para la fecha 07/04/2015
"Hoy es "d" de "mmmm" de "aaaa
Para el resultado de la diferencia de días:
#.0" días"
Si tienes cualquier consulta sobre estos resultados utiliza los comentarios.
¿Qué te han parecido los formatos personalizados?
¿Conocías estas posibilidades de formatos personalizados para Excel?
¿Las has usado antes? ¿Cómo?
¿Conocías las cuatro secciones de código para el formato personalizado de números?
Ya sabes lo importante que son tus comentarios para mi. No dudes en comentar, aunque sea para criticar el artículo. Me ayudará a mejorar los artículos actuales y futuros. Espero tu feedback.
Utiliza la sección de comentarios al final para apuntar todo aquello que consideres conveniente.
Autor José R. Ansó
Ingeniero Téc. Telecomunicaciones de profesión y formador de vocación. Fascinado por las hojas de cálculo. Puedes conectar conmigo en:
Hola
Buenos Días
Tengo una consulta, cómo puedo personalizar un formato de la siguiente manera:
###-######-####
Es decir:
Tres números – seis números – cuatro número y una letra
Los tres componentes separados por un guion.
Por ejemplo:
281-310885-0002W
Lo que pasa que al momento de ponerle la letra no aplica el formato, dice que no admite el formato escrito.
En esperas de sus comentarios.
Agradeciéndoles muchisimo por su apoyo.
SALUDOS
Hola Bayron,
Efectivamente, no podemos mezclar los dos tipos de formato.
Puedes introducir los datos numéricos y la letra en dos celdas anexas y utilizar una tercera para concatenar ambas cadenas
A1=281-310885-0002
A2=W
A3=A1&A2
Saludos.
Hola buscaba realizar un formato personalizado ya que en la columna tengo las variables:
%MW1
..
%MW100
pero me ordena de la siguiente forma:
%MW1
%MW10
%MW11
..
%MW19
%MW2
%MW20
%MW21
Como si no ordenara por numero, sin discriminar el “%MW” del principio.
Sabes como se podria solucionar esto? Muchas gracias
Hola Miguel, gracias por participar.
Si los número varían entre una y tres cifras, es decir, entre 1 y 999, bastaría con que complementaras las unidades y decenas con ceros a la izquierda. Es decir para el primer ejemplo %MW1 deberías tener %MW001. Haciendo esto te ordenará de forma adecuada.
hola tengo un problema ME podrias ayudar lo he intentado todo eres mi ultima esperanza el problema es el siguiente
FECHA INICIAL FFECHA FINAL DIFERENCIA
1/1/12013 2/1/2013 31 dias ,6 horas ,31 minutos
Hola Brayan.
Disculpa pero con los datos que ofreces yo no soy capaz de saber qué es lo que te pasa…
¿Podrías ser un poco más explícito?
Saludos y gracias por participar.
Hola, tengo una función de buscarv para una celda que personalice para ingresar este numero 1856514 y me muestre 185/65r14 lo personalice asi “”#”/”##”R”##” , la celda funciona bien, pero la función buscarv no me reconoce los datos. sinenbargo si los datos los ingreso sin formato de celda, directamente 185/65r14 si me funciona buscarv . Cual sera mi problema? gracias…
Hola Enrique.
El formato personalizado lo que hace es ‘enmascarar’ el dato que hay en la celda para que aparezca de otra forma, pero el dato que hay en la celda no cambia aunque tenga un aspecto distinto. Así, si utilizas BUSCARV la cadena que tienes que buscar es la que realmente está en la celda y no la que se muestra.
Espero haberme explicado bien.
Un saludo y gracias por participar.
Hola
Tengo una situación, quiero personalizar formatos de número en tres secciones
Negro cuando el valor sea igual a cero
Azul cuando el valor sea ,mayor que cero pero meno o igual a 5000
Rojo para valores mayores a 5000.
Lo planteo de la siguiente manera pero [Rojo][>5000]C$#,### .00;[Azul][5000]C$#,### .00;[Azul][<=5000]C$#,###.00
Como puedo solucionar éste problema?
Mil gracias por su apoyo
Hola
Tengo una situación, quiero personalizar formatos de número en tres secciones
Negro cuando el valor sea igual a cero
Azul cuando el valor sea ,mayor que cero pero meno o igual a 5000
Rojo para valores mayores a 5000.
Lo planteo de la siguiente manera [Rojo][>5000]C$#,### .00;[Azul][5000]C$#,### .00;[Azul][5000]C$#,### .00;[Azul][5000]C$#,### .00
Como puedo solucionar éste problema?
Mil gracias por su apoyo
El punto es que no puedo subir todo lo que quiero decir:
Solo me acepta las dos primeras condiciones: [Rojo][>5000]C$#,### .00;[Azul][5000]C$#,### .00
Como puedo resolver éste problema
Hola Muchas Gracias.
Ya encontré la solución.
Saludos
Disculpa Bayron que no te haya respondido antes.
Me alegra saber que ya lo has podido solventar.
Saludos.
Hola, tengo un cuadrante-calendario, las celdas de los días me muestran: ln (para lunes), ma (para martes), etc.
Lo que pretendo es que salga una sola letra y en mayúscula: L (lunes), M (martes), X (miércoles), etc.
Como puedo hacerlo.
Gracias
Se me ocurren un par de soluciones a bote pronto.
La primera es algo rudimentaria y consiste en chequear el contenido de la celda con sentencias IF y en función del contenido mostrar lo que te interesa.
La segunda es un poco más elegante. Utilizar una tabla auxiliar con las equivalencias y con la sentencia BUSCARV asignar el valor que corresponda.
Necesito un formato personalizado para una celda
Valor Formato Que Muestre
926 ### 926
926.11 ###.## 926.11
926.1 ###.#0 926.10
926.03 ###.#0 926.03
me podrían ayudar?
Gracias y Saludos
Hola. Quiero aplicar un formato personalizado a una celda para valores de porcentaje [_-* #.##0,000 %_-;-* #.##0,000 %_-;_-* “-“?? %_-;_-@_-] controlando que si da valor 0 me sustituya por un guión, sin embargo no puedo hacer que se centren estos valores, se quedan a la derecha de la celda. ¿qué más necesitaría en la fórmula para conseguirlo?
Nt1: el valor me queda así [ 0,856 % ] y lo que busco es [ 0,856 % ]
Nt2: Ya intenté centrarlo con el mismo formato de celda.
Hola Joseb24. Gracias por participar.
No me había planteado esta cuestión hasta ahora pues no la había necesitado.
Conocía que las alineaciones verticales y horizontales no funcionaban con los formatos condicionales y por lo que se ve tampoco con los formatos personalizados.
Saludos.
Hola
tengo el siguiente problema…
En una hoja de cálculo que tengo formulada, el resultado me da en datos normalmente menores a 0
El problema es que debo presentar siempre los resultados con dos cifras significativas, a veces me da con una cifra significativa el resultados otras veces con dos…. o con tres… pero siempre necesito sea con dos cifras significativas y no se puede estar modificando la hoja cada vez que se emiten resultados.
Me puedes ayudar?
Hola Claudia, bienvenida.
Cuando dices dos cifras significativas entiendo que te refieres a dos posiciones decimales. Si las quieres fijar, basta con que vayas al formato de esa(s) celda(s) y establezca el tipo de dato y fijes el número de posiciones decimales.
Saludos.
Estimado José:
Tus explicaciones me parecen muy claras y explicativas. Veamos, tengo el siguiente problema. Quiero operar con un código que tiene una parte numérica de dos dígitos, luego una parte de texto de 5 caracteres y finaliza con otra parte numérica de 4 dígitos.
Me gustaría operar con los dos primeros dígitos y con los cuatro últimos, según las circunstancias, pero no veo como dar a la celda el formato correspondiente.
Es decir, el formato debería ser ##TTTTT####, para que los dos primeros caracteres formen un número de dos cifras, luego tengamos una cadena de texto de cinco caracteres (las he representado por TTTTT pero pueden ir variando) y por último se añada otro número de cuatro cifras con el que también pueda operar en un momento dado.
No me importan si se necesita aplicar fórmulas para todo esto, pero si es posible prefiero evitar macros, a no ser que no haya otra solución.
Muchas gracias, quedo a la espera de tu respuesta.
Alfonso
Hola Alfonso,
No consigo ver qué quieres hacer con el formato que describes. Lo puedes componer con cadenas de textos utilizando para unirlos la concatenación (función CONCATENAR u operador &). Una vez compuesta la cadena definitiva puedes acceder a cada grupo de números o caracteres utilizando la función EXTRAE, aplicar la operación que necesites con ellos y volver a concatenarlo.
Saludos.
amigo tengo una consulta. quiero crear un formato en donde automaticamente excel me separe texto de numeros, es decir, escribire en la celda J123456789 y quiero que el me separe el texto de los numeros de esta forma J-12345678-9, sin usar formulas solo formato, podria hacerlo colocando “J-“00000000”-“0 este formato pero, no puedo ya que la “J” Puede cambiar, por “V” o por “E”, necesito ayuda, llevo dias en esto.
Hola Juan, gracias por participar.
Como sabes, los formatos personalizados, permiten diferenciar el formato para números positivos, negativos, cuando es cero o cuando es texto, pero no es capaz de distinguir por el contenido de la celda. Por tanto, lo que me indicas no es posible hacerlo con formatos personalizados.
Me indicas que no quieres hacer fórmulas. Otra posibilidad sería haciendo una macro, pero si no quieres fórmulas imagino que menos una macro.
Lo único que se me ocurre es que introduzcas la letra y el campo numérico en celdas diferentes y en una tercera compongas el dato a partir de las dos primeras. Puedes introducir la letra en una celda que tenga validación de datos (Validación de datos), así puedes limitar la letras al juego que tú decidas.
Saludos.
Y cómo sería con una fórmula?
Hola José Domingo, gracias por participar.
Para dar ese formato podrías combinar las funciones CONCATENAR o & con la función EXTRAE que es capaz de coger cualquier carácter (letra o número) de la posición que le indiques. Con ello sólo te quedaría concatenar los guiones y listo.
Saludos.
Hola, te tengo un reto. Sucede que quiero colocarle texto a numeros para identificarlos sin perder las funciones numericas, pero el texto no es fijo; pueden ser bultos, paletas, sacos, etc. colocando #”texto” me limita a una sola posibilidad. Qué podría hacer en este caso?
Hola Edar, gracias por participar.
Verás, como bien dices, con los formatos personalizados añadiendo texto a datos numéricos no puedes hacer lo que quieres. Puedes poner diferentes etiquetas a los datos para diferentes tipos de datos (positivos, negativos, cero y texto). Este no es tu caso. Con formatos personalizados no puedes hacerlo en una misma celda.
Deberás separar los datos por columnas para cada etiqueta de texto. Es decir, en una columna para los bultos, otra para las paletas, etc.
Saludos.
COMO PUEDO DAR FORMATO PERSONALIZADO SUBRAYADO A UNA CELDA PERO DESDE UNA FORMULA NO CON FORMATO DE CELDA.
Hola Will, te he respondido en el mensaje inmediatamente inferior.
Saludos.
Amigo, como podría darle formato personalizado subrayado al texto de una celda, pero no el formato con CNTRL + 1, sino algo asi como cuando le damos color a los numeros negativos
Hola Will, bienvenido. Gracias por tu comentario.
En cuanto a la respuesta, decirte que con formatos personalizados no se puede hacer lo que pretendes.
Tendrás que irte a macros para elaborar lo que deseas.
Saludos.
Hola!! Me gustaría saber cómo poner como formato de texto, por ejemplo tengo “ene-2015” (las comillas las puse yo para resaltar el texto, nada más) y yo quiero que empiece con mayúscula, es decir, que se lea: “Ene-2015”. ¿Qué hay que hacer???
Muchas gracias por contestar.
Rubén
Hola Rubén.
Mucha gracias por tu comentario.
Verás, el problema que tienes, muy probablemente, se debe a que Excel reconoce lo que introduces como una fecha.
Si quieres introducir una cadena de texto tienes dos posibilidades:
1.- Cambiar el formato de la celda de “General” a “Texto” con lo que ya no te reconocerá la cadena introducida como una fecha.
2.- Si no quieres cambiar el formato, puedes introducir el texto directamente como una cadena de la siguiente forma: =”Ene-2015″. No te olvides del signo igual (=), ni de las dobles comillas al principio y al final.
Saludos.