Usando el Formato Condicional en Excel (La Guía Definitiva + Ejemplos)

El Formato Condicional es una de las características más simples pero poderosas en las Hojas de Cálculo de Excel.

Como su nombre lo indica, usted puede usar el formato condicional en Excel cuando quiere resaltar las celdas que cumplen con una condición especificada.

Le da la capacidad de agregar rápidamente una capa de análisis visual sobre su conjunto de datos. Puede crear mapas de calor, mostrar iconos que aumentan/disminuyen, burbujas de Harvey y mucho más utilizando el formato condicional en Excel.

Usando el formato condicional en Excel (Ejemplos)

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

• Identificar rápidamente los duplicados utilizando el formato condicional en Excel.
• Resaltar las celdas con valor mayor/menor que un número en un conjunto de datos.
• Resaltar los 10 valores superiores/inferiores (o el 10%) en un conjunto de datos.
• Resaltar los errores/espacios en blanco utilizando el formato condicional en Excel.
• Crear Mapas de Calor utilizando Formato Condicional en Excel.
• Resaltar cada enésima fila/columna utilizando Formato Condicional.
• Buscar y resaltar utilizando Formato Condicional en Excel.

1. Identificar rápidamente los duplicados

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

Así es como puede hacerlo:

• Seleccione el conjunto de datos en el que desea resaltar los duplicados.
• Ir a Inicio ->Formato condicional ->Reglas de resaltado de celdas -> Valores duplicados.
• En el cuadro de diálogo Duplicar valores, asegúrese de que Duplicar está seleccionado en el desplegable de la izquierda. Puede especificar el formato a aplicar utilizando el desplegable de la derecha. Hay algunos formatos existentes que puede utilizar, o especificar su propio formato utilizando 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, en varias columnas o en un rango no contiguo de celdas.

See Also: The Ultimate Guide to Find and Remove Duplicates in Excel.

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

Puede utilizar el 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 ventas inferior a 100 millones, o resaltar las celdas con marcas inferiores al umbral de paso.

Aquí tiene los pasos para hacerlo:

• Seleccione todo el conjunto de datos.
• Ir a Inicio ->Formato condicional ->Reglas de resaltado de celdas -> Mayor que.. / Menor que..
• En función de la opción que selecciones (mayor que o menor que), se abrirá un cuadro de diálogo. Digamos que seleccionas la opción ‘Mayor que’. En el cuadro de diálogo, introduce 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.
• Especifica el formato que se aplicará a las celdas que cumplan la condición utilizando el desplegable de la derecha. Hay algunos formatos existentes que puede utilizar, o especificar su propio formato utilizando la opción Formato personalizado.
• Haga clic en Aceptar.

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

Se puede seguir el mismo proceso para resaltar las celdas con un valor inferior a unos valores especificados.

3. Resaltar el 10 (o 10%) superior/inferior

El formato condicional en Excel puede identificar rápidamente los 10 elementos superiores o el 10% superior de un conjunto de datos. Esto podría ser útil en situaciones en las que desee ver rápidamente los principales candidatos por puntuaciones o los valores más altos de los tratos en los datos de ventas.

De forma similar, también puede identificar rápidamente los 10 elementos inferiores o el 10% inferior de un conjunto de datos.

Aquí tiene los pasos para hacerlo:

• Seleccione todo el conjunto de datos.
• Ir a Inicio ->Formato condicional -> Reglas superior/inferior -> 10 elementos superiores (o %) / 10 elementos inferiores (o %).
• En función de lo que selecciones, se abrirá el cuadro de diálogo. Digamos que seleccionaste los 10 primeros elementos, entonces se abriría un cuadro de diálogo como el que se muestra a continuación:
• Especifica el formato que se aplicará a las celdas que cumplan la condición utilizando el desplegable de la derecha. Hay algunos formatos existentes que puede utilizar, o especificar su propio formato utilizando la opción Formato personalizado.
• Haga clic en Aceptar.

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

También, si tiene menos de 10 celdas en el conjunto de datos, y selecciona las opciones para resaltar los 10 primeros elementos/los 10 últimos elementos, entonces todas las celdas se resaltarían.

Aquí hay algunos ejemplos de cómo funcionaría el formato condicional:



4. Resaltar 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 posteriores, podría conducir a resultados erróneos.

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

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



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

Aquí están los pasos para resaltar las celdas que están vacías o tienen errores en ella:

• 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’:
  =OR(ISBLANK(A1),ISERROR(A1))
  • La fórmula anterior comprueba todas las celdas para dos condiciones – si está en blanco o no y si tiene un error o no. Si alguna de las condiciones es TRUE, devuelve TRUE.
• Establezca el formato que desea aplicar a las celdas que están en blanco o tienen errores. Para ello, haz clic en el botón Formato. Se abrirá el cuadro de diálogo ‘Formato de celdas’, donde podrá especificar el formato.
• Haga clic en Ok.

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 el formato condicional. La fórmula mencionada sólo utiliza A1. Cuando aplicas esta fórmula a todo el rango, excel comprueba una celda cada vez y ajusta la referencia. Por ejemplo, cuando comprueba A1, utiliza la fórmula =OR(ISBLANK(A1),ISERROR(A1)). Cuando comprueba la celda A2, utiliza la fórmula =OR(ISBLANK(A2),ISERROR(A2)). Ajusta automáticamente la referencia (ya que son referencias relativas) dependiendo de la celda que se esté analizando. Así que no es necesario escribir una fórmula distinta para cada celda. Excel es lo suficientemente inteligente como para cambiar la referencia de la celda por sí mismo 🙂

See Also: Using IFERROR and ISERROR to handle errors in excel.

5. Crear mapas de calor

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

Algo como lo que se muestra a continuación:

El conjunto de datos anterior tiene valores entre 1 y 100. Las celdas se resaltan en función del valor que contienen. El 100 obtiene el color verde, el 1 obtiene el color rojo.

Aquí están los pasos para crear mapas de calor utilizando el 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, se aplicaría el formato al conjunto de datos. Hay múltiples gradientes de color 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 forma similar, también puede aplicar conjuntos de datos e iconos.

6. Resaltar cada dos filas/columnas

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

Estas se llaman las líneas de cebra y podrían ser especialmente útiles si va a imprimir los datos.

Ahora hay dos maneras de crear estas líneas de cebra. La forma más rápida es convertir tus datos tabulares en una tabla de Excel. Automáticamente aplica un color a las filas alternas. Puedes leer más sobre ello aquí.

Otra forma es utilizando el formato condicional.

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



Aquí tiene los pasos para resaltar las filas alternativas utilizando el formato condicional en Excel.

• Seleccione el conjunto de datos. En el ejemplo anterior, seleccione A2:C13 (que excluye el encabezado). Si desea incluir también el encabezado, seleccione todo el conjunto de datos.
• Abra el cuadro de diálogo Formato condicional (Inicio-> Formato condicional-> Nueva regla) .
• En el cuadro de diálogo, seleccione Utilizar 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(ROW())
• La fórmula anterior comprueba todas las celdas y si el número de ROW de una celda es impar, entonces devuelve TRUE. El formato condicional especificado se aplicaría a todas las celdas que devuelvan TRUE.
• Establezca el formato que desea aplicar a las celdas que están en blanco o tienen errores. Para ello, haz clic en el botón Formato. Se abrirá el cuadro de diálogo ‘Formato de celdas’, donde podrás especificar el formato.
• Haz clic en Aceptar.

¡Ya está! Las filas alternativas del conjunto de datos quedarán resaltadas.



Puedes utilizar la misma técnica en muchos casos. Lo único que tienes que hacer es utilizar la fórmula correspondiente en el formato condicional. Aquí tienes algunos ejemplos:

• Resalta las filas pares alternas: =ISEVEN(ROW())
• Resaltar filas alternas de suma: =ISODD(ROW())
• Resaltar cada 3ª fila: =MOD(ROW(),3)=0

7. Buscar y resaltar datos usando formato condicional

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

Supongamos que tienes un conjunto de datos como el que se muestra a continuación, con Nombre de productos, Representante de ventas y Geografía. La idea es escribir una cadena en la celda C2, y si coincide con los datos de alguna(s) celda(s), entonces eso debería quedar resaltado. Algo como lo que se muestra a continuación:



Aquí están los pasos para crear esta funcionalidad de Búsqueda y Resaltado:

• Seleccione el conjunto de datos.
• Ir 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 las celdas a formatear’.
• Ingrese la siguiente fórmula en el campo de la sección ‘Editar la descripción de la regla’:
  =AND($C$2<>»»,$C$2=B5)
• Establezca el formato que desea aplicar a las celdas que están en blanco o tienen errores. Para ello, haz clic en el botón Formato. Se abrirá el cuadro de diálogo ‘Formato de celdas’, donde podrás especificar el formato.
• Haz clic en Aceptar.

¡Ya está! Ahora cuando introduzcas cualquier cosa en la celda C2 y pulses intro, se resaltarán todas las celdas que coincidan.

¿Cómo funciona esto?

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

La fórmula devolvería TRUE 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 del conjunto de datos.

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

Descarga el archivo de ejemplo


Puedes utilizar la misma lógica, para crear variaciones como:

• Resaltar toda la fila en lugar de una celda.
• Resaltar incluso cuando hay una coincidencia parcial.
• Resaltar las celdas/filas a medida que se escribe (dinámico) .

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 mejor práctica, mantenga el formato condicional aplicado sólo en aquellas celdas en las que lo necesite.

Dado que es volátil, puede dar lugar a un libro de Excel lento.

Para eliminar el formato condicional:

• Seleccione las celdas de las que desea eliminar el formato condicional.
• Ir 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 hay que saber sobre el formato condicional en Excel

  • El formato condicional en volátil. Puede conducir a un libro de trabajo lento. Utilícelo sólo cuando sea necesario.
  • Cuando copie y pegue celdas que contengan formato condicional, el formato condicional también se copiará.
  • Si aplica varias reglas en el mismo conjunto de celdas, todas las reglas permanecerán activas. En caso de solapamiento, se da preferencia a la regla aplicada en último lugar. Sin embargo, puede cambiar el orden desde el cuadro de diálogo Administrar reglas.

  También le pueden gustar los siguientes tutoriales de Excel:

  • La forma correcta de aplicar el formato condicional en la tabla dinámica.
  • Encontrar y eliminar duplicados en Excel.
  • La guía definitiva para usar el formato condicional en Google Sheets.