FORMATOS CONDICIONALES EN EXCEL

Introducción a formatos condicionales

El formato condicional es un formato el cual vamos aplicar a las celdas que cumplen con determinada condición.

También le podemos aplicar formatos condicionales a celdas dependiendo del valor de otra celda.

El conjunto de herramientas que nos van a permitir realizar formatos condicionales se encuentran ubicada en la cinta de opciones, en el apartado estilos:

Vamos a tener la posibilidad de lo que nos brinda esta herramienta son innumerables, vamos a poder hacer que los datos se nos muestren de una manera más específica en la medida de cumplan con determinada condición, y como ya dijimos, este formato se actualiza dinámicamente.

Por ejemplo, en la figura se nos van a mostrar los salarios de varias personas, podemos notar que los salarios más altos tienen un color de relleno rojo y un color de fuente rojo más oscuro:

Si el valor $1,900.00 le damos cambiar por $2,500.00, vamos a ver como se actualiza dinámicamente el formato de esa celda, ahora tiene un color de relleno rojo, con color de fuente rojo más oscuro:

Aplicando formatos condicionales II

En Excel vamos a poder aplicar formatos condicionales a celdas con cualquier tipo de datos, ya sea de tipo numérico, de tipo texto, fecha, etc.

Dentro de Resaltar reglas de celdas veremos la opción de Texto que contiene…, en la cual podemos escribir algunas reglas comparando textos para aplicar el formato condicional.

En Excel vamos a poder también escribir nuestras propias reglas de formato condicional, para esto vamos a tener la opción de Más reglas…

Esta herramienta nos va trasladar a una ventana donde vamos a poder definir la regla a aplicar, escribir la prueba lógica y además, definir el formato que van a tener las celdas que cumplan esa condición.

Dentro de Resaltar reglas de celdas vamos a encontrar también una herramienta que nos va a permitir trabajar con fechas:

Cuando ejecutamos esta opcion, Excel nos va a mostrar una ventana donde debemos de manera predeterminada algunas pruebas lógicas para trabaja con fechas:

Formato condicional. Reglas superiores e inferiores

En Excel tendremos un conjunto de herramientas que nos va a permitir resaltar con formato condicional un determinado número de celdas teniendo en cuenta un valor absoluto o porcentual de celdas que van a cumplir con determinadas condiciones. Éstas van a poder ser:

• Determinado número de valores inferiores o superiores.

• Determinado por ciento de valores inferiores o superiores.

• O que se encuentren por encima o por debajo del promedio.

Estas herramientas se van a encontrar ubicadas dentro de la opción Reglas superiores o inferiores del botón Formato condicional:

En estas cuatro primeras opciones se nos mostraran indistintamente una ventana donde vamos a poder fijar la cantidad de celdas que necesitamos resaltar, ya sea atendiendo a un número absoluto o a un valor porcentual, también vamos a poder definir el formato a aplicar:

En el caso de estos valores que hacen comparativas con el promedio de un rango seleccionado, la ventana que se nos va a mostrar solo nos va permitir elegir el formato, ya que al seleccionar alguna de ellas, la prueba lógica que estamos definiendo es precisamente esa, los valores que estén o bien por encima, o bien por debajo del promedio.

Por último, vamos a tener la opcion Más reglas, se nos va a mostrar la ventana donde están contenidas todas las reglas de formato condicional, pero de una forma predeterminada estará seleccionada la opción que nos permitirá escribir reglas relativas a valores son rango inferior y superior.

Desde esta ventana vamos a poder definir si necesitaremos comparar valores superiores o inferiores, también si el número de celdas a resaltar va a ser a partir de un valor absoluto o un valor porcentual, así como personalizar el formato.

Formato condicional. Barras de datos

En Excel, vamos a tener un grupo de herramientas que nos va permitir pintar las celdas de manera que contengan barras que ilustren atendiendo a determinados criterios, los valores contenidos en determinado rango de celdas.

Este conjunto de herramientas se va a encontrar ubicado dentro de la opción Barras de datos:

Aquí tenemos que elegir entre barras de diferentes colores predeterminados por Excel, pero también para que se nos muestren las barras con un color degradado o un color sólido.

Vamos a tener la opcion de personalizar estas barras, atendiendo a su aspecto y también vamos a poder definir a partir de qué valores comparativos se van a mostrar las barras. Si ejecutamos esta opción se nos mostrara una ventana en la cual tenemos un sinnúmero de opciones:

En esta ventana se mostraran todas las opciones posibles para configurar las barras de datos. Excel de manera predeterminada nos muestra las barras prestando atención a los valores de las celdas del rango seleccionado, o sea, la barra más larga corresponderá al valor más alto, y de manera contraria, la barra más corta corresponderá al valor más bajo.

También se nos mostrara un eje en forma de línea discontinua para diferenciar los valores negativos, haciendo que la barra en estos aparezca en dirección contraria.

Mediante estas opciones en esta ventana vamos a poder cambiar todo eso, podemos definir atendiendo a qué tipo de valor atienda a la hora de pintar las barras, puede ser de forma automático, número, porcentual, etc. Tendremos el poder de definir además el color de las barras, el tipo de relleno, también todo lo relacionado a los bordes de las barras.

Cuando ejecutamos el botón Valor negativo y eje…, se nos va a mostrar una ventana donde configuramos cómo se mostrarán las barras para los valores negativos:

En definitiva, mediante las opciones que nos brinda la herramienta Más reglas, contenida dentro de la opción Barras de fórmulas, tenemos el poder de configurar todo lo relacionado con la manera de pintar las barras dentro de las celdas, así como atendiendo al criterio que queremos se utilice para ilustrar los datos.

Formato condicional. Escalas de color

Las opciones de formato condicional agrupadas dentro de Escalas de color, nos permitirán pintar las celdas con diferentes tonos de colores atendiendo al valor de los datos que contengan, lo que representan en el contexto y también, aplicando diversos criterios.

Entre estas herramientas, tenemos varias formas de aplicar este formato, que ya vienen predeterminadas por Excel, donde se pueden observar, combinaciones de colores, de tonos, etc.

Como ya tenemos información, los formatos condicionales los podemos cambiar de manera dinámica dependiendo de los valores que vayan tomando las celdas en cuestión.

La opción más interesante y completa la vamos a tener al ejecutar la herramienta Más reglas, que nos llevara a una ventana donde podremos personalizar todo lo relacionado con las escalas de color.

Vamos a poder definir en esta ventana, el tipo de formato, ya sea de dos colores o tres, también si en vez de escalas de colores necesitamos que se nos muestren barra de datos o conjuntos de íconos. También se nos mostraran opciones para el tipo de criterio a aplicar a la hora de pintar las celdas, que podrá ser atendiendo a valores más bajos o más altos, o a partir de un número definido manualmente, teniendo en cuenta los valores porcentuales o percentiles, o respecto a una fórmula. Se definen además, los colores para los rangos más bajos y más altos, etc.

Formato condicional. Conjuntos de iconos

Estas opciones de formato condicional agrupadas dentro de Conjuntos de iconos, nos van a permitir insertar en las celdas varios iconos atendiendo al valor de los datos que contengan.

El conjunto de herramientas que nos va a permitir aplicar este tipo de formatos se encuentran ubicada dentro del Formato condicional, en la opción Conjunto de iconos.

Como se nos mostraran en la figura, tendremos una gran variedad de iconos, divididos en cuatro apartados: Direccional, Formas, Indicadores y valoración.

De forma predeterminada Excel usa criterios propios a la hora de aplicar este formato, por lo cual también podemos personalizarlos de manera que se ajuste a nuestras necesidades. Para ello tendremos la opción, Más celdas, que nos mostrara una ventana donde vamos a poder definir desde el tipo de icono y su formato, hasta los criterios para su representación en cuanto a los valores de las celdas.

En la figura anterior, vamos a poder ver que tenemos una amplia gama de opciones, mediante las cuales podemos definir el tipo de icono, el criterio de comparación, así como la prueba lógica comparativa para insertar indistintamente y atendiendo al valor de las celdas, un tipo determinado de iconos dentro de las celdas de un rango seleccionado.

Trabajando con criterios de tipo Número y de tipo Porcentual

En Excel vamos a poder aplicar diferentes criterios a la hora de definir las comparativas para formato condicional:

• Número

• Porcentual

• Fórmula

• Percentil.

Estos diferentes criterios los podemos usar de manera indistinta dependiendo del resultado que queremos obtener. Por ejemplo, si solamente necesitamos comparar en base a valores absolutos, pues utilizaremos la opción de Tipo Número, si necesitamos hacer una comparativa a partir de valores porcentuales respectivos al rango de celdas seleccionada, pues usamos la opción Porcentual.

En la siguiente figura vamos a tener dos columnas con los mismos valores numéricos, con un formato condicional a las celdas de manera que en la primera columna tendremos con un círculo verde a los valores mayores o igual a 50, con círculo amarillo los valores entre 25 y menor a 50, y con círculo rojo, los valores menores a 25. En la segunda columna, tenemos un formato condicional con la siguiente comparativa, con círculo verde los valores por encima del 50%, dependiendo del rango de valores, con círculo amarillo las celdas con valores entre el 25% y menores al 50%, y con círculo rojo los valores que representan el valor porcentual de menor al 25%.

Vamos a poder observar que no obligatoriamente coinciden los formatos condicionales para los mismos valores en una y otra celda. Esto pasara de forma precisa porque, aunque los valores son los iguales y las reglas comparativas tienen los mismos números, al no tener el mismo tipo de criterio, ya que uno se basa en valores absolutos y otro con valores porcentual basados en el rango de celdas, van a poder existir diferencias en algunas celdas con el mismo valor.

Trabajando con criterios de tipo Fórmula y de tipo Percentil

Vamos a poder en Excel escribir comparativas en el horario de aplicar formatos condicionales mediante una fórmula.

Por ejemplo, vamos a poder hacer que se nos muestren determinado tipo de icono en las celdas que cumplan la condición de estar por encima del promedio.

Para ello, vamos a poder seleccionar la opción de tipo percentil y en el cuadro de diálogo en el cual escribiremos los valores en el caso de Número y Porcentual, pues escribimos una fórmula.

Vamos a poder también aplicar el tipo de criterio percentil, que no es más que a partir de un porcentaje, nos muestra la cantidad de valores que van a cumplir con la condición dada. El percentil es un criterio de posición, no de valor en sí, aunque dependa de valores.

Por ejemplo, si aplicáramos un criterio Percentil, en el cual queremos que la mitad de las celdas que contengan los valores más altos se nos muestren con un círculo verde, solo vamos a elegir el criterio percentil y en el cuadro de diálogo en el cual escribiremos los valores, ponemos 50, o sea, la mitad de celdas con valores superiores.

Utilizando fórmulas para aplicar formatos condicionales

Vamos a poder mediante fórmulas en Excel, aplicar formatos condicionales a celdas dependiendo del valor de otras celdas.

Por ejemplo, en la siguiente tabla, podemos aplicar un color de relleno amarillo a las celdas correspondientes a la columna nombre que les corresponda una edad menos a 30 años.

Para ello, luego de que seleccionemos el rango de celdas al cual queremos aplicar este formato condicional, vamos a ejecutar la opción Nueva Regla, dentro de Formato condicional.

Esta opción nos trasladara a la ventana Nueva regla de formato, donde tenemos que seleccionar el tipo de regla Utilice una fórmula que determine las celdas para aplicar formato. Entonces en esta ventana vamos a poder editar una descripción de regla mediante una fórmula y definiendo también el formato a aplicar.

En nuestro ejemplo, en el cual necesitamos comparar los valores de la edad, en la fórmula tenemos que poner solamente las coordenadas de la primera celda que contendrá los valores de la edad. Excel entenderá entonces que debe ir trasladando esa fórmula hacia abajo, hasta el último registro de la tabla.

Para el supuesto que deseemos que se nos aplique este formato a todo el registro de datos a los que corresponden edades menores a 30 años, vamos a tener que seleccionar todo el rango de datos, entonces cuando escribimos la fórmula vamos hacer la salvedad de que la referencia a la columna sea absoluta, para evitar que se hagan comparativas con celdas a la derecha.

Utilizando fórmulas para aplicar formatos condicionales II

Vamos a ver un ejemplo donde aplicaremos formato condicional haciendo una comparativa a un valor externo al rango de celdas seleccionado.

Por ejemplo, en la tabla próxima, aplicaremos un color de relleno amarillo a los registros de la tabla que contengan en la columna Nombre el valor que tenga la celda C1.

Luego de que seleccionamos todo el rango de datos de la tabla, vamos a llegar a la ventana que nos permitirá escribir una nueva regla de formato condicional, seleccionaremos la misma opción que Utilice una fórmula que determine las celdas para aplicar formato.

En el cuadro donde definimos las fórmulas vamos a usar la función Hallar, a la cual se le ingresan dos argumentos, el valor a comparar que lo tenemos en la celda C1 y el rango de celdas donde realizará la búsqueda.

En la próxima figura se observara que las coordenadas de la celda C1 tendrán una referencia absoluta , esto es así, porque vamos a necesitar que siempre compare teniendo en cuenta dicho valor, sin importar a dónde desplacemos la fórmula. Si vamos a observar el segundo argumento de la fórmula, vamos a ver que es la coordenada del primer registro de la columna Nombre, con una referencia absoluta en cuanto a la columna, lo cual nos va a indicar que siempre va a usar los valores de esta columna para realizar la búsqueda; y en cuanto a la fila, la referencia es relativa, o sea, va a ir trasladándose hacia las filas de abajo para hacer la comparativa.

Con esto vamos a lograr que, si escribimos un texto en la celda C1, y este texto esta ubicado la columna Nombre, se va aplicar un color de relleno amarillo en las celdas correspondientes al registro que contenga ese Nombre. En la figura vemos un ejemplo cuando escribimos en la celda C1 “Laura”.

Utilizando fórmulas para aplicar formatos condicionales III

Vamos a ver un ejemplo más donde vamos aplicar un formato condicional a los registros de la tabla cuyas ventas netas se van a encontrar por encima del promedio de las ventas netas de todos los vendedores. Tenemos que hacer que los registros que cumplan con esta condición, tengan un color de relleno azul claro.

Después vamos a seleccionar todo los rangos de datos, ejecutaremos la opción Nueva regla dentro del botón Formato condicional entonces vamos a poder escribir la fórmula que se nos muestra en la figura:

Podremos observar que a continuación del signo igual nos aparecerán las coordenadas de la primera celda de la columna Ventas netas, de manera que la coordenada perteneciente a la columna es absoluta y la coordenada perteneciente a la fila es relativa. Esto nos va a garantizar que compare siempre teniendo en cuenta los valores de la comuna Ventas netas, una vez que desplacemos hacia abajo comparando cada una de las celdas de dicha columna con el promedio de las ventas netas. Como usamos el operador de comparación Mayor que (>), entonces se le aplicar el formato de relleno azul claro a los registros con valores por encima del promedio.

Una vez que apliquemos este formato, vamos a poder modificarlo sin tener que escribir todo de nuevo. Para eso vamos a tener la opción Administrar reglas, dentro de Formato condicional. Una vez que lo ejecutemos, vamos a tener una ventana en la cual van aparecen los formatos aplicados a determinado rango de celdas, esto es de mucha importancia, porque para modificar algún formato, vamos a tener que seleccionar el rango de celdas donde el mismo se aplicó. Solo bastaría hacer doble clic encima de la regla para que nos muestre la ventana Editar nueva regla de formato, donde podemos modificar lo que consideremos en esa regla.

Modificando formatos condicionales

Cuando queremos modificar formatos condicionales en Excel vamos a tener la opción Administra reglas dentro de Formato condicional:

A la hora que ejecutamos esta opción, se nos va a mostrar una ventana donde vamos a poder escribir una nueva regla, modificar alguna regla existente, eliminar alguna regla, ordenarlas, definir si deseamos que una vez que se cumpla determinada regla entonces no se superpongan otras para ese conjunto de celdas, etc.

También vamos a poder definir si deseamos que se nos muestren en el panel donde se describen las reglas, las aplicadas a un rango que hayamos seleccionado o bien que se muestren todas las reglas de formato condicional de la hoja de cálculo.

Cuando modificamos algunas de estas reglas, tenemos que seleccionar la regla y hacer clic en el botón Editar regla…, o bien hacer doble clic encima de la regla. Esto nos va a trasladar a la ventana donde definimos la regla y una vez ahí vamos a poder hacer los cambios que deseemos sin tener que escribir toda la regla nuevamente.

Copiar y pegar formatos condicionales

En Excel, existen variedades de formas de copiar y pegar los formatos condicionales. Vamos a poder hacerlos extensivos a las filas de abajo o las celdas a la derecha, también copiar el formato que tenga un rango de datos hacia otro, etc.

Por ejemplo, en esta columna vemos aplicado un formato condicional a las primeras 5 celdas, para copiarlas hacia las filas de abajo podemos hacerlo de varias formas:

Vamos a poder seleccionar el rango de estas cinco primeras celdas, y deslizar su contenido hacia abajo, como mismo trasladábamos las fórmulas, pero vamos a tener que hacer una salvedad, y es que debemos indicarle a Excel que solo vamos a copiar el formato, no el contenido de las celdas. Entonces tenemos que usar las opciones de pegado especial.

Vamos a poder también través del administrador de reglas, definir un nuevo rango para que se nos aplique la regla que deseamos en las primeras 5 columnas:

En este tipo de caso solamente bastara con modificar este rango y que abarque todas las columnas de la tabla.

También vamos a poder copiar formatos condicionales hacia otras columnas, vamos a usar las combinaciones de teclas Ctrl + C y Ctrl + V, siempre indicando que necesitamos copiar solo el formato, y no el contenido de las celdas. Esto lo indicamos o bien en el botón que aparece cuando pegamos o desde el botón pegar de la cinta de opciones, donde si lo desplegamos, vemos algunas opciones de pegado, entre ellas la de Formato.

Otra forma es usar la herramienta Copiar formato.

Reglas simultáneas

Vamos a poder aplicar en Excel múltiples formatos condicionales sobre un mismo rango de celdas. Estos formatos se nos aplicarán de manera simultánea, aunque vamos a poder controlar el orden de prioridad de algunos sobre otros, y además determinar si deseamos que en algunas celdas se apliquen sólo algunos formatos mientras que otros no.

En la próxima imagen se nos mostrara en la columna en la cual tenemos las ventas netas que se han aplicado tres formatos condicionales, un conjunto de iconos de flechas, un color de relleno rojo claro con texto rojo oscuro para las celdas con los tres valores más altos y un color de relleno amarillo con texto amarillo oscuro para las tres celdas con valores más bajos. En estos tres formatos van a poder coexistir sin anteponerse unos a otros porque son excluyentes, ya sea en el tipo de formato como en las celdas que cumplen la condición.

También apliquemos un formato más, por ejemplo, de escalas de colores con color blanco y diferentes tonos de verde:

Ahora vamos a ver, que sin importar el formato que teníamos de antes, el último formato aplicado tiene prioridad sobre ellos. Veremos que las celdas de la columna de ventas netas tienen un relleno con diferentes escalas de colores a base de blanco y verde. De estas tres celdas con valores superiores, sólo quedara del formato aplicado antes el color de la letra, así mismo con las celdas con los tres valores inferiores sólo las diferencia el color amarillo de la fuente. El ultimo formato que hemos aplicado, que es el de escalas de colores, se ha antepuesto a los anteriores. Y es que el orden de prioridad es precisamente ese, siempre se sobrepone el último de los formatos aplicados.

Pero Excel también nos va proporcionar herramientas dónde vamos a poder controlar estos órdenes de prioridades, y están contenidas en la ventana donde administramos las reglas de celdas:

Vamos a poder cambiar en esta ventana las órdenes de las reglas, también si marcamos la casilla de selección al extremo derecho, entonces las celdas que cumplan esa condición no se les va a aplicar ningún otro formato hacia abajo, en la siguiente imagen, hemos subido la regla de los 3 valores superiores, y hemos marcado esa casilla de selección, también vamos a ver que las celdas con los tres valores superiores, vuelven a tener el color de relleno rojo y no se les aplica más ninguna regla, incluso la del conjunto de iconos.

Por último, es de mucha importancia saber que los formatos condicionales van a tener prioridad sobre los formatos clásicos. Es decir, si en la columna ventas netas se aplicara un formato clásico de relleno azul, no se pintaría ninguna celda, porque ellas tiene un formato condicional que involucra su color de relleno.

Borrar formatos condicionales

En Excel vamos a poder eliminar formatos condicionales de múltiples maneras, la más rápida e inmediata podrá ser a través de las opciones contenidas dentro de Borrar reglas, donde vamos a poder elegir si deseamos eliminar el formato a un rango seleccionado, borrar todas las reglas de la hoja de cálculo, etc.

También otra forma de eliminar formatos condicionales en Excel es a través de la ventana donde administramos los formatos condicionales. Para ello vamos a seleccionar la regla que deseamos eliminar, y presionar el botón Eliminar regla:

Eventualmente puede usarse además la herramienta Borrar formatos, que se ubica dentro de la opción Borrar de la cinta de opciones, pero de esta forma, si bien elimina los formatos condicionales, también quedarían eliminados cualquier otro formato que tengan las celdas: