Formato condicional en Excel
Utilice el formato condicional para destacar información, analizar datos y tendencias, resaltar celdas o rango de celdas, detectar problemas y variaciones específicas de los datos trabajados.
El formato condicional en Excel nos permite mediante una condición dada, resaltar una celda o un rango de celdas, aplicar colores cuando haya variaciones específicas en los datos, así como aplicar iconos según las condiciones que se especifique.
Si las condiciones dadas en el formato condicional son verdaderas este aplicará el formato a la celda o rango de celdas. Si son falsas, la celda o el rango de celdas no recibirán el formato establecido.
En este artículo veremos varias condiciones que puedes emplear en un formato condicional, también te mostraremos como crear una regla o condición propia. Y como un formato condicional en Excel puede aplicarse no solo a una celda o rango de celdas, sino también a una tabla de Excel e incluso a una tabla dinámica en Excel
Cómo aplicar formato condicional a tablas dinámicas
Para aplicar formato condicional a una tabla dinámica se hace de la misma manera que a un rango de celda o una tabla en Excel. Sin embargo, aplicar formato condicional a una tabla dinámica tiene algunas consideraciones:
- Existen algunos formatos condicionales que no funcionan en las tablas dinámicas de Excel. Por ejemplo, no se puede dar formato a campos que contienen valores únicos o duplicados.
- Si se filtra, oculta, contrae o expande los niveles de un informe de tabla dinámica, el formato condicional se mantiene siempre y cuando no se eliminen los datos subyacentes.
- El ámbito de aplicación del formato condicional para los campos del área de valores puede estar dado por jerarquías de datos y determinado por elementos secundarios visibles. Por lo cual, al dar formato condicional en una jerarquía de datos, los elementos secundarios no heredan el formato condicional del elemento primario y este no hereda el formato condicional del elemento secundario.
- Hay tres métodos que puedes aplicar para especificar el ámbito de los campos del área de valores en el formato condicional: Ámbito por selección, por campos de valor y por campo correspondiente.
A continuación, vamos a especificar cada uno de los métodos. De manera predeterminada el método para especificar el ámbito de los campos del área de valores es por selección. Para cambiar esto, puedes ir al campo correspondiente mediante el botón de opción Aplicar regla de formato a, en el cuadro de diálogo eliges Nueva regla de formato o Editar regla de formato.
Ámbito por selección
- Agrupa los valores contiguos, como por ejemplo, los totales de productos de una región específica.
- Crea un grupo de valores no contiguos, como por ejemplo, los totales de productos de diferentes regiones, pueden estar en los distintos niveles de la jerarquía de datos.
Ámbito por campo de valor
- Evitar aplicar formato condicional a muchos campos no contiguos.
- Aplicar formato condicional a un conjunto de valores para todos los niveles de la jerarquía de datos.
- Puedes incluir totales y subtotales generales.
Ámbito por campo correspondiente
- Evitar seleccionar muchos campos no contiguos.
- Aplica formato condicional a un conjunto de valores para un nivel específico de la jerarquía de datos.
- Puedes excluir subtotales.
Al aplicar formato condicional a campos en el área de valores para valores superiores, inferiores, superiores al promedio o inferiores al promedio, la regla se basa en todos los valores de forma predeterminada. Sin embargo, cuando específicas el ámbito por el método de campo correspondiente, en lugar de usar todos los valores visibles puedes aplicar formato condicional a una columna y al campo de su fila principal o a una fila y al campo de su columna principal.
Formato condicional con análisis rápido
Puedes usar el análisis rápido para aplicar formato condicional a los datos de un rango de celdas o una tabla de Excel. No obstante, debes tener en cuenta que esta opción no está disponible en algunas versiones de Excel.
- Para aplicar el formato condicional solo debes seleccionar los datos y en la esquina inferior derecha de la selección aparecerá un botón (Análisis rápido).
- Haga clic en el botón de análisis rápido o también puedes presionar las teclas CTRL + Q
- Aparecerá una ventana emergente. En la opción Formato podrá visualizar las diferentes opciones y hacer clic en el formato que desee aplicar. Las opciones que se visualicen dependen de los datos que hayas seleccionado.
- Si aparece un cuadro de diálogo “Texto que contiene” deberá escribir el formato condicional que desea aplicar y hacer clic en Aceptar
Aplicar formato condicional con escala de colores
Cuando se aplica formato condicional con la escala de colores, este nos ayuda a comprender la variación y la distribución que existen en los datos. Así como validar tendencias o comportamientos de ciertas actividades, ya que nos permiten comparar un rango de celdas usando una grabación de colores o mapa de calor, como lo veremos a continuación en un ejemplo.
Ejemplo de formato condicional con escala de colores
En el siguiente ejemplo te mostraremos cómo hacer un mapa de calor de ventas con formatos condicionales. Para lo cual, utilizaremos un rango de celdas con las ventas que se hacen por horas en un negocio X desde las 9 horas hasta las 22 horas, de lunes a sábado.
Mediante el formato condicional con escala de colores queremos identificar cómo se comportan las ventas en este rango de días y horarios.
Para aplicar el formato condicional seleccionamos nuestra tabla como se muestra en la imagen anterior, vamos a ir la pestaña Inicio – Estilos – damos clic en Formato Condicionalpara que se desplieguen las opciones y vamos a utilizar la opción que dice Escala de color.
Administrar reglas de la escala de colores
Luego de seleccionar una opción de escala de colores, vamos a ir a configurarlo, para hacer esto, volvemos a dar clic en formato condicional y desplegamos las opciones y escogemos la que dice Administrar reglas y le damos clic en Editar regla.
Como vemos en la imagen, se nos muestra varias opciones, la primera de ellas nos indica que selecciones el tipo de regla que queremos aplicar. Para este ejemplo vamos a dejar la opción “Aplicar formato a todas las celdas según sus valores”, luego puedes ir probando las demás reglas y validar cada una de sus funciones.
En la segunda opción, editar una descripción de la regla, podemos dar formato a todas las celdas según sus valores. En estilo de formato, puedes elegir opciones como: Escala de 2 colores o Escala de 3 colores.
Para nuestro ejemplo, vamos a utilizar la opción de escala de 3 colores y en tipo de valor le voy a decir que me coloque un color rojo a los valores más bajos, luego al punto medio que nos calcule el percentil 50 (dato que se encuentra en la posición central – 50%) y nos lo pinte de color amarillo. Por último, para el valor más alto, vamos a decirle que nos ponga como valor alto, lo que este a partir del percentil 90, ósea del 90% de los valores.
Luego de establecer cada una de los valores, damos clic en Aceptar, Aplicar y nuevamente en Aceptar. Ahora podemos ver gráficamente con los colores cuales son los días y horarios en que más ventas se registran.
Formato a las celdas con barras de datos
Al aplicar formato condicional con barras de datos nos ayuda a ver el valor de cada celda en relación a las demás. En este formato la barra de datos representa el valor de cada celda y se mide de acuerdo a su longitud, es decir, si la barra es más larga va a representar un valor alto o si es una barra corta nos va a representar un valor bajo.
Las barras de datos también son muy útiles para mostrarnos valores positivos y negativos, y podemos plasmarlos o representarlos mediante colores.
Ejemplo de formato condicional con barras de datos
En el siguiente ejemplo utilizaremos un rango de celdas con una serie de datos para aplicar formato condicional con barra de datos. Para aplicar el formato condicional debemos seleccionar los datos de nuestra tabla, luego, vamos a Inicio – Estilos – y hacemos clic en la opción de Formato condicional para que se nos desplieguen una serie de opciones y escogemos Barras de datos.
Al pararnos sobre esta opción vamos a visualizar distintas maneras en que podemos aplicar el formato condicional de barras de datos y podemos hacerlo mediante un relleno degradado o un relleno sólido.
Administrar reglas de la barras de datos
Una vez que hemos aplicado un relleno a nuestras barras de datos, vamos a configurar la regla de formato. Por lo tanto, volvemos a seleccionar los datos, vamos a Inicio – Estilos – Formato condicional y hacemos clic en la opción de Administrar reglas.
Al hacer clic en Administrar reglas se abrirá la ventana emergente del Administrador de reglas de formato condicionales y en el cual, vamos a hacer clic sobre la opción Editar reglas. A continuación, se nos muestran dos opciones generales: seleccionar un tipo de regla y editar una descripción de la regla para dar formato condicional a todas las celdas según sea el caso.
En la opción de seleccionar un tipo de regla, para este ejemplo, vamos a Aplicar formato a todas las celdas según sus valores, luego podrás ir probando las demás opciones que se encuentran allí.
Al editar una descripción de la regla vamos a poder darle formato a todas las celdas según sus valores, podemos elegir un estilo de formato, que para el caso hemos escogido barras de datos y en la opción tipo podemos modificar o configurar la opción tanto para la barra más corta, como para la barra más larga. Por ejemplo:
Para la barra más corta algunas de las opciones que podemos establecer o configurar son:
- Valor más bajo
- Número
- Porcentual
- Fórmula
- Percentil
- Automático
Para la barra más larga, estas son las opciones que podemos establecer o configurar:
- Valor más alto
- Número
- Porcentual
- Fórmula
- Percentil
- Automático
Dependiendo de la opción (tipo) que elijamos debemos establecer un valor. Luego, podemos modificar la apariencia de la barra (Colores), tanto en valores positivos, como en los valores negativos según creas conveniente.
Usar formato condicional con iconos
Podemos aplicar formato condicional con iconos para comentar o clasificar la información. Cada icono que establezcamos nos ayuda a representar un rango de datos, como por ejemplo, un icono o punto verde, nos indica que el valor está cumpliendo un objetivo, un circulo o icono de color naranja puede indicarnos que el indicador tiene un cumplimiento del 50% o el icono de color rojo puede indicarnos que el cumplimiento está por debajo del 50%.
Como podemos analizar, un formato condicional con iconos nos permite mostrar o ilustrar las celdas que cumplen con determinada condición.
Ejemplo de formato condicional con iconos
Para hacer un formato condicional con iconos utilizaremos una tabla donde tenemos los presupuestos, ventas y porcentajes de cumplimientos de las líneas de productos.
Entonces, lo que queremos hacer es resaltar mediante iconos de color verde, aquellas líneas donde hayamos cumplido por encima del 100% y resaltar con color naranja las líneas que estén entre el 99.9% y el 50% de cumplimiento. Por último, poner iconos de color rojo a las líneas de productos que tenga un cumplimiento de menos del 50%
Lo primero que vamos a hacer es seleccionar la fila de los porcentajes de cumplimiento, seleccionamos el rango completo. Luego, vamos a ir a Inicio – Estilos – y hacemos clic en Formato condicional para desplegar todas las opciones, elegimos la opción de Conjunto de Iconos. Al hacer clic sobre la opción de conjunto de iconos se nos mostrará todas las opciones que podemos emplear, para este ejemplo utilizamos la opción formas – 3 Semáforos (sin marco)
Administrar y editar reglas del formato condicional con iconos
Una vez que hayamos aplicado el formato condicional, vamos a ir administrar o configurar la regla del formato. Para ello, volvemos Inicio – Estilos – y hacemos clic en Formato condicional, desplegamos todas las opciones, elegimos la opción de Administrar regla.
Una vez hemos dado clic en Administrar regla, se abre una ventana con el administrador de reglas de formato condicionales. Allí, vamos a dar clic en Editar regla, para lo cual, se abrirá una nueva ventana (Editar regla de formato) en la cual vamos a poder configurar y mostrar cada icono según la regla que establezcamos
Podemos mostrar cada icono según la regla que hayamos configurado. Es decir, el tipo de valor (Porcentaje, Número, Fórmula o percentil) y a partir de qué valor se quiere mostrar un icono y se le asigna un color.
Formato a celdas que contienen datos, números o fecha y hora
También podemos aplicar formato condicional a las celdas que contienen valores de texto, números o fecha y hora para encontrar rápidamente o específicamente las celdas con algún criterio establecido. Por ejemplo, podemos resaltar de color verde las celdas que están en determinada fecha (hoy, ayer, los últimos 7 días, este mes, el mes pasado, etc.) o podemos resaltar las celdas que contienen artículos menores a 3 unidades disponibles.
Ejemplo de formato a celdas que contiene valores de texto, número o fecha y hora
Para ilustrar cómo aplicar formato condicional a celdas que contienen valores, vamos a hacer uso de una tabla de ventas, en la cual, tenemos relacionadas las referencias de ventas, el nombre del cliente, la ciudad, fecha y los valores de la venta.
Vamos a poner formato a las celdas o compras que se hayan hecho desde la ciudad de Bogotá. Para iniciar, seleccionamos el rango de ciudad, nos vamos a Inicio – Estilos y damos clic en Formato condicional para que se desplieguen todas las opciones y nos ubicamos en la opción de Resaltar reglas de celdas. Al ubicarnos en Resaltar reglas de celdas encontraremos criterios como por ejemplo, Es mayor que, Es menor que, Es igual a, Texto que contiene, etc.
Para nuestro ejemplo, vamos a hacer uso del criterio “Texto que contiene” al hacer clic sobre él, podemos configurar y aplicar formato a las celdas que contenga la ciudad de Bogotá y podemos darle un formato preestablecido o personalizado.
Editar reglas del formato condicional a celdas que contiene valores
Para editar la regla del formato dado a las celdas podemos hacerlo de la siguiente manera, nos dirigimos nuevamente a Inicio – Estilos y damos clic en Formato condicional para que se desplieguen las opciones y damos clic en la opción de Administrar reglas.
Una vez que hayamos dado clic en Administrar reglas, se abre la ventana del administrador de reglas de formato condicionales y damos clic en Editar regla, que a su vez nos va abrir la ventana de Editar regla de formato, en esta ventana tenemos dos opciones principales, seleccionar un tipo de regla y editar una descripción de la regla.
En seleccionar un tipo de regla dejamos la opción de Aplicar formato únicamente a las celdas que contengan y en Editar una descripción de la regla podemos aplicar formato personalizado al valor de la celda que queremos resaltar.
Aplicar formato condicional a valores
El formato condicional nos ayuda de manera eficiente a trabajar con valores. Como hemos visto anteriormente en los distintos tipos de formato, siempre aplicamos formato para mejorar el análisis de datos, mirar tendencias o resaltar información.
Pero además, podemos utilizar o aplicar formato condicional para buscar los valores más altos y más bajos en rango de celdas. También, para buscar valores por encima o por debajo del promedio, o buscar valores únicos o duplicados.
Veamos cómo funciona cada uno de estos tipos de formato:
Valores únicos o duplicados
Para aplicar este formato, solo debes seleccionar el rango de celdas donde quieres resaltar los valores únicos o duplicados, dirígete a la pestaña Inicio y haga clic en Formato condicional para desplegar las opciones y después debes hacer clic en Resaltar reglas de celdas y seleccionar la opción Duplicar valores.
Aplique el formato a los valores que quieres resaltar. Puedes elegir si es un valor duplicado o si es un valor único.
Encima o debajo del promedio
Aplica formato condicional para que puedas buscar valores por encima o por debajo del promedio. Selecciona el rango de celdas, la tabla o puedes trabajar también sobre tablas dinámicas. Dirígete a la pestaña de Inicio, en el grupo de Estilos, encuentras el Formato condicional, haz clic para desplegar todas las opciones y elige Reglas superiores o inferiores.
Luego, seleccione el comando que deseas usar, si quieres aplicar formato Por encima del promedio o Por debajo del promedio, seguidamente escriba los valores que desea utilizar y aplique el formato para estos valores.
Rango inferior o superior
También puedes buscar valores superiores o inferiores en un rango de celdas según el valor que se especifique. Para aplicar este formato solo debes seleccionar el rango de celdas, tablas o puedes aplicarlo a tablas dinámicas. Dirígete a la pestaña de Inicio – Estilos y haz clic en Formato condicional para desplegar todas las opciones y a continuación haga clic en Reglas superiores e inferiores.
Elija el comando que desea usar, por ejemplo, 10% de valores superiores o 10% de valores inferiores. Luego puede aplicar los valores que desea utilizar y darles formato.
Crear y administrar reglas
Para crear o administrar las reglas del formato condicional debe usar el cuadro de diálogo Administrador de reglas de formato condicionales. Ve a la pestaña de Inicio y ubica el grupo de Estilo, haz clic en Formato condicional para que se desplieguen todas las opciones y a continuación debes hacer clic en Administrar reglas.
Una vez hecho esto aparecerá el Administrador de reglas de formato condicionales
En el Administrador de reglas de formato condicionales podrás crear, modificar, eliminar y mostrar todas las reglas de formato condicionales que hayas aplicado. Debes tener en cuenta que si tienes más de dos reglas de formato condicional, el orden en el que se evalúan estas reglas es de arriba a abajo.
Ordenar las reglas de formato condicional
Una regla más alta indica que tiene mayor prioridad que una más baja, y de manera predeterminada al crear una regla nueva, esta se ubica en la parte de arriba, por lo que tendrás que estar controlando el orden o la prioridad mediante las flechas de subir y bajar del cuadro de diálogo.
Copiar y pegar formato condicional
Para copiar un formato existente a otros datos nuevos en la hoja de cálculo, puede usar la opción de Copiar formato que se ubica principalmente en la pestaña de Inicio
Lo primero que debes hacer es ubicarte en la celda que contiene el formato que deseas copiar, luego ir a Inicio y dar clic en Copiar Formato. Notaras automáticamente que el puntero cambia de forma a un pincel, ahora solo debes tocar la celda en la cual quieres pegar el formato condicional.
Si deseas dejar de usar el pincel, solo pulsa la tecla Esc
Cómo encontrar celdas con formato condicional
Para buscar las celdas que tienen formato condicional, puedes hacerlo directamente con el comando Ir a Especial, el cual te ayudará a encontrar las celdas con un formato condicional específico o todas las celdas con formato condicional que estén en nuestra hoja de cálculo.
Una vez que identifiques las celdas con formato condicional podrás copiar, modificar o eliminar los formatos condicionales.
Si buscas todas las celdas con formato condicional debes ir a la pestaña Inicio, en el grupo Modificar hacer clic en la opción Buscar y seleccionar para desplegar todas las opciones y a continuación, haz clic en Formato condicional. De esta manera identificamos todas las celdas con formato condicional.
Ahora, si deseamos buscar solo las celdas con el mismo formato condicional aplicado, debes hacer lo siguiente: Ve a la pestaña Inicio – Modificar – Buscar y seleccionar y haz clic en Ir a Especial, luego en Celdas con formatos condicionales y selecciona Todos o Iguales a celda activa.
Eliminar formato condicional
Para eliminar el formato condicional de una celda, grupo de celdas, tabla o tabla dinámica debes ir a la pestaña de Inicio y hacer clic en Formato condicional >> Borrar reglas, luego podrás escoger la opción que deseas para eliminar el formato condicional según corresponda el caso:
- Borrar reglas de las celdas seleccionadas
- Borrar reglas de toda la hoja
- Borrar reglas de esta tabla
- Borrar reglas de esta tabla dinámica
¿Necesitas ayuda?
En Excel Para Todos capacitamos a las personas que están preocupadas e interesadas en aprender Excel y que han invertido su tiempo, viendo videos de YouTube o en cursos poco efectivos, y sienten que no les ha generado resultados positivos en el aprendizaje de la herramienta de Excel.
Por esta razón, si quieres dedicar tu tiempo y todos tus esfuerzos en aprender Excel de manera práctica y aplicada en casos de la vida real, te invitamos a explorar nuestro curso de Excel
En este curso aprenderás de manera fácil todos los elementos que se expusieron en este artículo y conseguirás dominar Excel desde lo básico hasta lo más avanzado. ¡Te aseguramos que no te arrepentirás!
