Formatos Condicionales

El formato condicional en Excel es una funcionalidad de gran utilidad al momento de realizar el análisis de datos ya que podemos dar un formato especial a un grupo de celdas en base a su contenido o al valor de otra celda. Esto nos permitirá aplicar un tipo de formato de celda (número, fuente, bordes, relleno) diferente para aquellas celdas que cumplan con ciertas reglas y así poder identificarlas fácilmente en pantalla.

23-May-2016

Porque utilizar el formato condicional

En cualquier momento podemos aplicar manualmente un formato a cualquier grupo de celdas que cumplan una serie de criterios, pero esta forma de actuar es costosa, repetitiva y poco productiva, sobre todo si los valores cambian continuamente. Por estos motivos el formato condicional nos puede ayudar a aplicar estos formatos automáticamente sin tener que preocuparnos del cambio de los contenidos de las celdas.

Algunos ejemplos:

Ejemplos de Formatos Condicionales

Tipos de formatos

Podemos hacer varias clasificaciones de formatos condicionales en Excel:

Según reglas

  • Reglas basadas en valores de celda. Estas reglas se basan en el mismo valor de la celda (Mayor que, Menor que, Igual a, Entre, etc.).
  • Reglas basadas en fórmulas. Estas reglas ofrecen mayor flexibilidad porque podemos aplicar un formato especial utilizando una fórmula donde podremos aplicar una lógica más compleja. Por lo mismo es un poco más complicado de aprender.

Según como se aplican las reglas

  • Aplicables en función de criterios del usuario. Serían aquellos en los que nosotros decidimos las condiciones que se tienen que cumplir para aplicar el formato: valores superiores, inferiores, comprendidos entre, distintos a, basados en fórmulas, etc.
  • Aplicables de forma automática. Serían aquellos en los que nosotros no establecemos la condición y es Excel el que elige un criterio preestablecido: suelen ser por escalas de valores en función del contenido de las celdas que tenemos seleccionadas.

Según formatos

  • Formatos de celda estándar. Son los típicos formatos de relleno, color de texto, fuente y bordes combinándolos como mejor nos parezca  para conseguir un efecto visual.
  • Formatos gráficos. Son más vistosos que los anteriores y están formados por elementos gráficos (direccionales, formas, indicadores y de valoración) y por rellenos degradados de colores. Estos formatos los aplica Excel de forma automática según escalas de valores.

Aplicar formatos condicionales

Seleccionaremos las celdas a las que deseamos aplicar el formato condicional y a continuación pulsaremos sobre el desplegable ‘Formato condicional’ del grupo ‘Estilos’ en la ficha ‘Inicio’ y seleccionaremos alguna de las muchas opciones que nos ofrece.

Lista Formato Condicional del Grupo Estilos

Resaltar reglas de celdas

Condiciones para resaltar reglas celdas

Esta opción de menú nos dará la oportunidad de resaltar las celdas que cumplan con la regla de formato condicional especificada. Cada opción muestra su propio cuadro de diálogo solicitando los argumentos necesarios para crear la regla.

Es mayor que: deberemos de indicar un valor a partir del cual se aplicará el formato especificado.

Es mayor que

Para las opciones Es menor que, Es igual a, Texto que contiene, los cuadros de dialogo que muestran son similares al del caso anterior, donde se espera que indiquemos un valor con el que comparar el contenido de las celdas a las que se les aplicará el formato.

Entre: deberemos de indicar dos valores que delimitarán el rango de valores sobre los que se tiene que aplicar el formato.

Entre dos valores

Una fecha: específica para fechas  como su nombre indica, nos muestra una lista de condiciones cronológicas, de donde podemos seleccionar la que mejor se adapte a nuestras necesidades.

Formato Condicional para Fechas

Duplicar valores: nos permite aplicar formato a los valores duplicados que se encuentren en las celdas seleccionadas, o resaltar los valores que sean únicos.

Marcar valores duplicados o únicos

Resaltar superiores o inferiores

Condiciones para resaltar superiores e inferiores

Son un grupo de reglas especiales que se basan en aplicar el formato a una cantidad determinada de elementos que son superior o inferior.

10 superiores: aplica el formato a los 10 valores superiores dentro del grupo de celdas seleccionadas. Aunque se denomina 10 superiores, nosotros podemos variar esa cantidad aumentando o disminuyendo la cantidad de elementos que deseamos resaltar.

10 Superiores / Inferiores

En este filtro hay que tener en cuenta que Excel aplicará el formato mientras no se hayan completado los X elementos superiores o sí al aplicar el formato a los valores se haya superado la cantidad de X elementos. Puede darse el caso que cuando se esté aplicando el formato a un valor, este tenga una cantidad de repeticiones superior al valor de X superiores y obtengamos al final más elementos de los deseados.

Ejemplos formato resaltar 10 superiores

La opción 10 inferiores funciona de la misma manera, pero aplicándose a los valores inferiores.

10% de valores superiores: funciona de manera similar a las anteriores, pero se indica un porcentaje de celdas que deseamos seleccionar. Por ejemplo, dentro de un rango que contiene 20 celdas, si se especifica un 10% entonces el formato condicional se aplicará a las 2 celdas con mayor valor porque el 10% de 20 celdas son 2 celdas.

Al igual que ocurre con la opción 10 superiores, este valor lo podremos modificar para variar la cantidad de celdas seleccionadas. Si indicamos un 20% en un grupo de 20 celdas, le estaremos indicando que deseamos seleccionar 4.

10% superiores

Por encima del promedio: hace dos cosas. En primer lugar calcula el valor promedio de las celdas del rango seleccionado y posteriormente aplica el formato condicional a todas aquellas celdas que tienen un valor por encima del promedio recién calculado. Al no tener ningún argumento, el cuadro de diálogo de esta regla solamente solicita el formato a aplicar.

Por encima del promedio

La regla Por debajo del promedio seleccionará todas las celdas que tengan un valor inferior al promedio.

Barras de datos

Modelos de barras de datos

Son un grupo especial de formatos condicionales que aplican escalas de colores o degradados tomando el valor más alto para la barra más grande y a partir de este valor va recortando el tamaño de la barra para el resto de los valores hasta llegar a la barra más pequeña que representa el valor más pequeño.

Ejemplo barra de datos

Como se puede observar en la imagen anterior la barra más grande corresponde al número 35 que es el valor máximo y la barra más pequeña corresponde al valor 10 que es el mínimo. El resto de las barras van tomando su tamaño en función de ese máximo y ese mínimo.

Escalas de colores

Resaltar escalas de colores

Las escalas de colores funcionan de manera similar a las barras de datos, pero modificando la intensidad de un color en función del valor de la celda o con combinaciones de colores según sean valores máximos, mínimos o intermedios.

Aplicar reglas con fórmulas

Formatos basados den fórmulas

Una de las opciones más flexibles que nos ofrece Excel a la hora de aplicar formatos es basar las condiciones en fórmulas. Estas fórmulas pueden ser simples comparaciones de un valor con los valores de las celdas seleccionadas o funciones cuyos resultados determinarán si se aplica el formato. Si la expresión utilizada da un resultado verdadero, entonces el formato se aplicará.

Veamos algunos ejemplos:

Deseamos marcar todos los conceptos de una lista si coinciden con el valor que escribamos en la celda G5.

Ejemplo formato condicional con fórmula
  • Seleccionamos la lista de conceptos (B5:B13).
  • Pulsamos sobre el desplegable Formato condicional y seleccionamos la opción Nueva regla.
  • En el cuadro de diálogo que nos muestre seleccionamos la opción ‘Utilice una fórmula que determine las celdas para aplicar formato’.
  • En el cuadro de texto introducimos la expresión que compare el contenido de la celda G5 con el primer valor de la lista de conceptos (=$G$5=B5). Observar que el valor de la celda G5 lo tenemos que indicar con una referencia absoluta para siempre sea el mismo patrón de comparación y el valor de la celda B5 lo escribimos de forma relativa para que Excel vaya ajustándolo para cada una de las celdas del rango.
Ejemplo formato condicional con fórmula
  • A continuación pulsamos el botón ‘Formato’ para establecer los formatos de relleno, fuente y color deseamos.
  • Para finalizar pulsamos el botón Aceptar.

En el siguiente ejemplo un poco más complejo, deseamos resaltar los nombres de una serie de personas si el total de compras realizadas es superior a la media y pertenecen al grupo de clientes iniciales.

Ejemplo formato condicional con fórmula
  • Comenzaremos seleccionando la lista de nombres de clientes a los que deseamos aplicar el formato (B5:B25).
  • Pulsamos sobre el desplegable Formato condicional y seleccionamos la opción Nueva regla.
  • En el cuadro de diálogo que nos muestre seleccionamos la opción ‘Utilice una fórmula que determine las celdas para aplicar formato’.
  • En el cuadro de texto introducimos la expresión que compare si el total de compras del cliente (D5) es mayor que el promedio de compras de todos los clientes (PROMEDIO($D$5:$D:25)) y que además el grupo del cliente (C5) sea igual a “INICIAL”.
  • Como es obligatorio que se cumplan las dos condiciones para que se le aplique el formato, necesitaremos la ayuda de la función lógica Y que devuelve un valor VERDADERO si todas y cada una de las expresiones que se le introducen se cumplen.
  • Observar que al igual que en el ejemplo anterior los valores que hacen referencia a algún dato de la lista los indicamos con referencias relativas, mientras que los que son constantes como es el rango de todas las compras utilizado en la función promedio los indicamos con referencias absolutas.
Ejemplo formato condicional con fórmula
  • A continuación pulsamos el botón ‘Formato’ para establecer los formatos de relleno, fuente y color deseamos.
  • Para finalizar pulsamos el botón Aceptar.
Ejemplo formato condicional con fórmula

Como resultado veremos que se han marcado los nombres de los clientes que cumplen las dos condiciones.

Borrar reglas del formato condicional

Si en algún momento deseamos volver al formato inicial de las celdas de Excel, tendremos que eliminar los formatos condicionales que se hubieran aplicado.

Una de las opciones posibles para eliminar los formatos condicionales, consiste en seleccionar las celdas que tienen aplicado el formato, pulsar sobre el desplegable de Formato condicional de la ficha de inicio,  pulsar la orden Borrar reglas y seleccionar borrar reglas de las celdas seleccionadas. Automáticamente Excel eliminará cualquier formato que tuvieran aplicado.

Si lo que deseamos es eliminar sólo alguna de las reglas que se están aplicando, pulsar sobre el desplegable de Formato condicional de la ficha de inicio y a continuación seleccionar Administrar reglas. Esta orden mostrará un cuadro de diálogo desde donde podremos administrar las reglas que se están aplicando sobre la selección. Seleccionamos la regla que deseamos eliminar y pulsamos el botón ‘Eliminar regla’.

Borrar reglas del formato condicional