Formato condicional en Excel: la guía definitiva con ejemplos

El formato condicional es una de las funciones más simples pero poderosas de las hojas de cálculo de Excel.

Como sugiere el nombre, puede usar formato condicional en Excel cuando desee resaltar celdas que cumplan con una condición específica.

Le brinda la capacidad de agregar rápidamente una capa de análisis visual sobre su conjunto de datos. Puede crear mapas de calor, mostrar iconos crecientes / decrecientes, burbujas de Harvey y mucho más usando formato condicional en Excel.

Uso de formato condicional en Excel (ejemplos)

En este tutorial, le mostraré siete ejemplos asombrosos del uso de formato condicional en Excel:

• Identifique rápidamente los duplicados mediante el formato condicional en Excel.
• Resalte las celdas con un valor mayor / menor que un número en un conjunto de datos.
• Resaltar los valores 10 (o 10%) superior / inferior en un conjunto de datos.
• Resaltado de errores / espacios en blanco usando formato condicional en Excel.
• Creación de mapas de calor usando formato condicional en Excel.
• Resalte cada enésima fila / columna usando formato condicional.
• Busque y resalte usando formato condicional en Excel.

1. Identifique duplicados rápidamente

El formato condicional en Excel se puede utilizar para identificar duplicados en un conjunto de datos.

Así es como puede hacer esto:

• Seleccione el conjunto de datos en el que desea resaltar los duplicados.
• Vaya a Inicio -> Formato condicional -> Resaltado de reglas de celda -> Valores duplicados.
• En el cuadro de diálogo Valores duplicados, asegúrese de que esté seleccionado Duplicar en el menú desplegable de la izquierda. Puede especificar el formato que se aplicará utilizando el menú desplegable de la derecha. Hay algunos formatos existentes que puede usar o especificar su propio formato usando la opción Formato personalizado.
• Haga clic en Aceptar.

Esto resaltaría instantáneamente todas las celdas que tienen un duplicado en el conjunto de datos seleccionado. Su conjunto de datos puede estar en una sola columna, varias columnas o en un rango de celdas no contiguas.

Ver también: La guía definitiva para buscar y eliminar duplicados en Excel.

2. Resalte las celdas con un valor mayor / menor que un número

Puede usar formato condicional en Excel para resaltar rápidamente las celdas que contienen valores mayores / menores que un valor especificado. Por ejemplo, resaltar todas las celdas con un valor de venta inferior a 100 millones o resaltar las celdas con marcas inferiores al umbral de superación.

Estos son los pasos para hacer esto:

• Seleccione el conjunto de datos completo.
• Vaya a Inicio -> Formato condicional -> Resaltado de reglas de celda -> Mayor que… / Menor que…
• Según la opción que seleccione (mayor o menor que), se abrirá un cuadro de diálogo. Supongamos que selecciona la opción "Mayor que". En el cuadro de diálogo, ingrese el número en el campo de la izquierda. La intención es resaltar las celdas que tienen un número mayor que este número especificado.

• Especifique el formato que se aplicará a las celdas que cumplen la condición mediante el menú desplegable de la derecha. Hay algunos formatos existentes que puede usar o especificar su propio formato usando la opción Formato personalizado.
• Haga clic en Aceptar.

Esto resaltaría instantáneamente todas las celdas con valores superiores a 5 en un conjunto de datos.Nota: Si desea resaltar valores mayores que igual a 5, debe aplicar el formato condicional nuevamente con el criterio "Igual a".

Se puede seguir el mismo proceso para resaltar celdas con un valor menor que un valor especificado.

3. Resaltando Top / Bottom 10 (o 10%)

El formato condicional en Excel puede identificar rápidamente los 10 elementos principales o el 10% superior de un conjunto de datos. Esto podría resultar útil en situaciones en las que desee ver rápidamente a los mejores candidatos por puntajes o valores de oferta principales en los datos de ventas.

Del mismo modo, también puede identificar rápidamente los 10 elementos inferiores o el 10% inferior en un conjunto de datos.

Estos son los pasos para hacer esto:

• Seleccione el conjunto de datos completo.
• Vaya a Inicio -> Formato condicional -> Reglas superiores / inferiores -> 10 elementos principales (o%) / 10 elementos inferiores (o%).
• Según lo que seleccione, se abrirá el cuadro de diálogo. Supongamos que seleccionó los 10 elementos principales, luego se abriría un cuadro de diálogo como se muestra a continuación:
• Especifique el formato que se aplicará a las celdas que cumplen la condición mediante el menú desplegable de la derecha. Hay algunos formatos existentes que puede usar o especificar su propio formato usando la opción Formato personalizado.
• Haga clic en Aceptar.

Esto resaltaría instantáneamente los 10 elementos principales en el conjunto de datos seleccionado. Tenga en cuenta que esto solo funciona para las celdas que tienen un valor numérico.

Además, si tiene menos de 10 celdas en el conjunto de datos y selecciona las opciones para resaltar los 10 elementos principales / 10 elementos inferiores, todas las celdas se resaltarán.

A continuación, se muestran algunos ejemplos de cómo funcionaría el formato condicional:

4. Resaltado de errores / espacios en blanco

Si trabaja con muchos datos numéricos y cálculos en Excel, sabrá la importancia de identificar y tratar las celdas que tienen errores o están en blanco. Si estas celdas se utilizan en cálculos adicionales, podría dar lugar a resultados erróneos.

El formato condicional en Excel puede ayudarlo a identificar y resaltar rápidamente las celdas que tienen errores o están en blanco.

Supongamos que tenemos un conjunto de datos como se muestra a continuación:

Este conjunto de datos tiene una celda en blanco (A4) y errores (A5 y A6).

Estos son los pasos para resaltar las celdas que están vacías o tienen errores:

• Seleccione el conjunto de datos en el que desea resaltar las celdas en blanco y las celdas con errores.
• Vaya a Inicio -> Formato condicional -> Nueva regla.
• En el cuadro de diálogo Nueva regla de formato, seleccione Usar una fórmula para determinar qué celdas formatear.
• Ingrese la siguiente fórmula en el campo de la sección "Editar la descripción de la regla":
  = O (ESBLANCO (A1), ESERROR (A1))
  • La fórmula anterior verifica todas las celdas en busca de dos condiciones: si está en blanco o no y si tiene un error o no. Si alguna de las condiciones es VERDADERA, devuelve VERDADERO.
• Establezca el formato que desea aplicar a las celdas que están en blanco o tienen errores. Para hacer esto, haga clic en el botón Formato. Se abrirá el cuadro de diálogo "Formato de celdas", donde puede especificar el formato.
• Haga clic en Aceptar.

Esto resaltaría instantáneamente todas las celdas que están en blanco o tienen errores.

Nota: No es necesario utilizar todo el rango A1: A7 en la fórmula en formato condicional. La fórmula mencionada anteriormente solo usa A1. Cuando aplica esta fórmula a todo el rango, Excel comprueba una celda a la vez y ajusta la referencia. Por ejemplo, cuando marca A1, usa la fórmula = OR (ESBLANCO (A1), ESERROR (A1)). Cuando verifica la celda A2, usa la fórmula = O (ESBLANCO (A2), ESERROR (A2)). Ajusta automáticamente la referencia (ya que son referencias relativas) dependiendo de qué celda se está analizando. Por lo tanto, no es necesario que escriba una fórmula separada para cada celda. Excel es lo suficientemente inteligente como para cambiar la referencia de celda por sí solo 🙂

Ver también: Usando IFERROR e ISERROR para manejar errores en Excel.

5. Creación de mapas de calor

Un mapa de calor es una representación visual de datos donde el color representa el valor en una celda. Por ejemplo, puede crear un mapa de calor donde una celda con el valor más alto es de color verde y hay un cambio hacia el color rojo a medida que el valor disminuye.

Algo como se muestra a continuación:

El conjunto de datos anterior tiene valores entre 1 y 100. Las celdas se resaltan según el valor que contiene. 100 obtiene el color verde, 1 obtiene el color rojo.

Estos son los pasos para crear mapas de calor usando formato condicional en Excel.

• Seleccione el conjunto de datos.
• Vaya a Inicio -> Formato condicional -> Escalas de color y elija uno de los esquemas de color.

Tan pronto como haga clic en el icono del mapa de calor, aplicará el formato al conjunto de datos. Hay varios degradados de color entre los que puede elegir. Si no está satisfecho con las opciones de color existentes, puede seleccionar más reglas y especificar el color que desee.

Nota: De manera similar, también puede aplicar conjuntos de iconos y Data Bard.

6. Resalte todas las demás filas / columnas

Es posible que desee resaltar filas alternativas para aumentar la legibilidad de los datos.

Estos se denominan líneas de cebra y podrían ser especialmente útiles si está imprimiendo los datos.

Ahora hay dos formas de crear estas líneas de cebra. La forma más rápida es convertir sus datos tabulares en una tabla de Excel. Aplicó automáticamente un color a filas alternas. Puedes leer más sobre esto aquí.

Otra forma es usar formato condicional.

Suponga que tiene un conjunto de datos como se muestra a continuación:

Estos son los pasos para resaltar filas alternativas usando formato condicional en Excel.

• Seleccione el conjunto de datos. En el ejemplo anterior, seleccione A2: C13 (que excluye el encabezado). Si también desea incluir el encabezado, seleccione el conjunto de datos completo.
• Abra el cuadro de diálogo Formato condicional (Inicio-> Formato condicional-> Nueva regla) [Atajo de teclado - Alt + O + D].
• En el cuadro de diálogo, seleccione el cuadro de diálogo "Usar una fórmula para determinar qué celdas formatear".
• Ingrese la siguiente fórmula en el campo de la sección "Editar la descripción de la regla":
  = ISODD (FILA ())

• La fórmula anterior verifica todas las celdas y si el número de FILA de una celda es impar, devuelve VERDADERO. El formato condicional especificado se aplicaría a todas las celdas que devuelvan VERDADERO.
• Establezca el formato que desea aplicar a las celdas que están en blanco o tienen errores. Para hacer esto, haga clic en el botón Formato. Se abrirá el cuadro de diálogo "Formato de celdas", donde puede especificar el formato.
• Haga clic en Aceptar.

¡Eso es! Se resaltarán las filas alternativas del conjunto de datos.

Puede utilizar la misma técnica en muchos casos. Todo lo que necesita hacer es usar la fórmula relevante en el formato condicional. Aquí hay unos ejemplos:

• Resalte filas pares alternas: = ISEVEN (ROW ())
• Resalte agregar filas alternativas: = ISODD (ROW ())
• Resalte cada tercera fila: = MOD (ROW (), 3) = 0

7. Busque y resalte datos usando formato condicional

Este es un uso un poco avanzado del formato condicional. Te haría parecer una estrella de rock de Excel.

Suponga que tiene un conjunto de datos como se muestra a continuación, con nombre de producto, representante de ventas y geografía. La idea es escribir una cadena en la celda C2, y si coincide con los datos en cualquier celda, entonces debería resaltarse. Algo como se muestra a continuación:

Estos son los pasos para crear esta función de búsqueda y resaltado:

• Seleccione el conjunto de datos.
• Vaya a Inicio -> Formato condicional -> Nueva regla (Atajo de teclado - Alt + O + D).
• En el cuadro de diálogo Nueva regla de formato, seleccione la opción "Usar una fórmula para determinar qué celdas formatear".
• Ingrese la siguiente fórmula en el campo de la sección "Editar la descripción de la regla":
  = Y ($ C $ 2 ””, $ C $ 2 = B5)
• Establezca el formato que desea aplicar a las celdas que están en blanco o tienen errores. Para hacer esto, haga clic en el botón Formato. Se abrirá el cuadro de diálogo "Formato de celdas", donde puede especificar el formato.
• Haga clic en Aceptar.

¡Eso es! Ahora, cuando ingrese algo en la celda C2 y presione enter, se resaltarán todas las celdas coincidentes.

¿Como funciona esto?

La fórmula utilizada en el formato condicional evalúa todas las celdas del conjunto de datos. Supongamos que ingresa a Japón en la celda C2. Ahora Excel evaluaría la fórmula para cada celda.

La fórmula devolvería VERDADERO para una celda cuando se cumplan dos condiciones:

• La celda C2 no está vacía.
• El contenido de la celda C2 coincide exactamente con el contenido de la celda en el conjunto de datos.

Por lo tanto, se resaltan todas las celdas que contienen el texto Japón.

Descargar el archivo de ejemplo

Puede utilizar la misma lógica para crear variaciones como:

• Resalta toda la fila en lugar de una celda.
• Resalte incluso cuando haya una coincidencia parcial.
• Resalte las celdas / filas mientras escribe (dinámico) [Te va a encantar este truco :)].

Cómo eliminar el formato condicional en Excel

Una vez aplicado, el formato condicional permanece en su lugar a menos que lo elimine manualmente. Como práctica recomendada, mantenga el formato condicional aplicado solo a las celdas donde lo necesite.

Dado que es volátil, puede resultar en un libro de Excel lento.

Para eliminar el formato condicional:

• Seleccione las celdas de las que desea eliminar el formato condicional.
• Vaya a Inicio -> Formato condicional -> Borrar reglas -> Borrar reglas de las celdas seleccionadas.
  • Si desea eliminar el formato condicional de toda la hoja de trabajo, seleccione Borrar reglas de toda la hoja.

Cosas importantes que debe saber sobre el formato condicional en Excel

• Formateo condicional en volátil. Puede llevar a un libro de trabajo lento. Úselo solo cuando sea necesario.
• Cuando copia y pega celdas que contienen formato condicional, el formato condicional también se copia.
• Si aplica varias reglas en el mismo conjunto de celdas, todas las reglas permanecen activas. En caso de superposición, se da preferencia a la regla aplicada en último lugar. Sin embargo, puede cambiar el orden cambiando el orden en el cuadro de diálogo Administrar reglas.