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 observ