El formato condicional basado en fórmulas es una opción disponible en Excel que nos ayuda a resaltar los datos en base a si cumplen una serie de criterios. Si bien el uso de esta opción es bastante sencillo, también es cierto que hay algunas cuestiones importantes sobre formato condicional que es necesario conocer para evitar volvernos locos en el intento.
Definición del objetivo
Tenemos los siguientes datos, que representan las ventas mensuales de 2019 y nuestra previsión para 2020.
Y queremos que nos destaque los datos con los siguientes criterios:
- Si las ventas del mes de 2020 son superiores, en un porcentaje que definamos, a las del mismo mes de 2.019, queremos que nos lo indique coloreando la celda de dicho mes en verde
- Por el contrario, si las ventas son inferiores a un porcentaje que definamos de las del mes correspondiente de 2.019, nos lo debe indicar coloreando la celda en rojo
Para empezar, vamos a definir esos límites en las celdas B6 (% superior) como un 10% mayores a las del año previo y en la celda B7 (% inferior) como un 5% inferiores a las del año previo.
Definición de fórmulas
Si tienes dudas de como debes introducir las fórmulas, lee el artículo Aplicar un formato condicional basado en una fórmula y continúa aquí después.
Cuando se aplica una fórmula para definir un formato condicional, lo que hace Excel es evaluar si el resultado devuelto por dicha fórmula es Verdadero o Falso y, en base a ello aplica o no aplica el formato que hayamos definido.
De acuerdo con este razonamiento, para evaluar si las ventas de Enero de 2.019 (celda B4) son un 10% superiores a las de Enero de 2.018 (celda B3), nuestro fórmula debería ser (B4/B3)>B6. De igual forma, para evaluar si son inferiores al 95% de las del periodo del año previo, la formula sería (B4/B3)<B7.
Insertando las fórmulas en el cuadro de diálogo
Vamos a insertar la primera fórmula en el cuadro de diálogo correspondiente. Para ello, nos ponemos en la celda B4 y vamos a Formato condicional > Nueva regla >Utilice una fórmula que determine las celdas para aplicar formato. Hacemos click en el cuadro bajo la leyenda Dar formato a los valores donde esta fórmula sea verdadera y escribimos la primer fórmula, incluyendo un signo = al comienzo.
Ahora definimos el formato, para lo que haremos click en el botón formato y seleccionaremos las opciones que deseemos hacemos click en Aceptar.
Seguiremos el mismo proceso para el otro formato condicional, es decir, que las ventas sean inferiores al 95% de las del periodo del año previo.
Como Enero de 2.019 es un 96,69% de las ventas de Enero 2.018, no vemos que pase nada, así que vamos a probar si nuestro formato condicional está bien configurado. Cambiamos el valor de la celda B6 sustituyendo el 110% (ventas Ene-20 un 10% mayores a ventas Ene-19) por un 95% (las ventas de Ene-20 si son superiores al 95% de las de Ene-19, con lo que la celda, debería cambiar a verde).
Vemos que hace lo que queremos. Vamos a probar ahora el formato de ventas inferiores. De nuevo, como las ventas han sido un 96.69% de las del periodo previo a comparar, deberemos buscar un límite en el que se cumpla esta condición. Cambiaremos el valor de la celda B7 a un 97%.
Y vemos que, efectivamente, la celda cambia a rojo.
Pero es esto lo que realmente queremos en este caso? Se están cumpliendo ambas condiciones, pero la celda solo nos puede mostrar una. Y ¿por qué muestra la celda en rojo y no en verde?
Las prioridades en la evaluación de las condiciones
Vamos a ver que está pasando. Para ello, nos posicionamos en la celda B4, que es la que tiene las condiciones definidas y vamos a Formato condicional > Administrar reglas. Vemos esto:
Podemos comprobar que a la celda B4 (Se aplica a), se le evalúan las dos condiciones que hemos definido previamente. También vemos, justo encima de las fórmulas, un texto que dice «Regla (aplicada en el orden mostrado)«. Y a la derecha, la última columna dice «Detener si es verdad«.
Pues bien, si lo analizamos con detenimiento, Excel debería:
- Evaluar la primera condición. Como es cierta, aplicar el formato de relleno rojo a la celda.
- Evaluar la segunda condición, ya que no hemos seleccionado que se detenga si es cierta la primera condición. Nos debería haber coloreado el fondo verde… Algo va mal
Vamos a hacer la prueba cambiando el orden de las condiciones. Para ello, selecciona una de ellas y con las flechas que hay a la derecha del botón Eliminar regla, cambia el orden de las mismas. Debes acabar así:
¡Ahora nos muestra la celda de color verde!
Es decir, se evalúa la primera condicion, y si es cierta se aplica y punto. O bien detiene la comprobación de formatos posteriores o, una vez aplicado un formato condicional, no sigue evaluando mas.
Conclusión 1
Primera de las cuestiones importantse sobre el formato condicional: Mucho cuidado con el orden en que evaluamos las condiciones ya que afecta a lo que vamos a ver.
Extendiendo el formato condicional al resto de meses
Vamos a extender el formato condicional al resto de meses. Para ello, vamos a copiar la celda B4 y seguidamente hacemos un pegado de formatos al resto de celdas.
Pues se nos ha puesto casi todo verde… Vamos a ver si hace lo que queremos. Y lo vamos a comprobar, volviendo a nuestros límites originales. Como es un poco lioso, he añadido el % de las ventas de cada mes del 2.019 con respecto al mismo mes de 2.018.
De momento, Enero, está perfecto sin color ya que las ventas en 2.019 son el 96,9% de las de 2.018 y para ese valor, no queremos resalte (ni es menos del 95% ni mas del 100%).
Pero Febrero me parece que no está bien. La relación de ventas es el 99,1% y no debería tener color. ¿Que está pasando?. Vamos a ver las reglas que se están aplicando en Febrero. Seleccionamos la celda C4 y nos vamos a Formato condicional >Administrar reglas y nos encontramos con esto:
Y vemos que las fórmulas han cambiado a =(C4/C3)>C6 y =(C4/C3)<C7. Está aplicando bien la parte de caálculo de la relación entre ventas (Febrero, que corresponde a la columnaC), pero está comparando esa relación con lo que haya en las celdas C6 y C7, que están vacías, porque nuestros límites están en las celdas B6 y B7.
Corrigiendo lo que ha ido mal
Vamos a arreglar esto. Empezamos por cerrar esta ventana, volvemos a seleccionar la celda B4 y editamos de nuevo las reglas (recuerda: Formato condicional >Administrar reglas). Vamos a, de una en una, seleccionar cada regla y cambiarla de «=(B4/B3)>B6» a «(B4/B3)>$B6» y de «=(B4/B3)<B7» a «(B4/B3)<$B7«.
Lo que acabamos de hacer es pasar de referencias relativas a referencias absolutas para la columna de las celdas B6 y B7, es decir, hemos fijado B, de forma que si arrastramos el formato hacia la derecha al resto de meses, las columnas que no tengan $ por delante se referirán a una posición relativa desplazada las mismas columnas que tuviesen en la celda original.
Dicho de otra forma, hemos definido el formato de la celda B4 (columna B), a un cálculo realizado sobre datos de las celdas B4 y B3 (misma columna, es decir, desplazamiento en columnas 0 a la izquierda de su posición y 0 a la derecha de su posición). Si arrastramos esto a la columna C, se referirá a las celdas desplazadas 0 columnas a su derecha y 0 a su izquierda, es decir, a las de la columna C.
En cambio, si anteponemos el signo $, y esto vale tanto para columnas como para filas, estamos FIJANDO la posición de la columna. Y si copiamos eso en cualquier columna de LA MISMA FILA, siempre nos referiremos a la celda $B6. SI ademas, nos desplazamos en filas, como el número de fila NO ESTA FIJADO, al haber definido esta fórmula en la fila 4, hemos hecho referencia a una celda que está 2 filas mas abajo (la fila 6), es decir, si copiásemos el formato a la celda F23, estaría buscando un valor en la columna $B, pero el la fila 25: $B26.
Si te hace falta, vuelve a leer lo anterior. Es lioso, pero no complicado.
Ahora, si copiamos el formato hacia el resto de meses, veremos que tenemos lo que queríamos.
Conclusión 2
Segunda de las cuestiones importantes sobre el formato condicional: Hay que prestar mucha atención al tema de referencia relativa o absoluta a las celdas que definen el formato condicional.
Aquellas que queramos que vayan «moviéndose» con las celdas a que aplicamos el formato deben ser relativas. Sin Símbolo $.
Las que necesitemos que sean siempre el valor de una celda concreta (o solo la fila o la columna), deberemos fijarlas en lo que corresponda con el símbolo $ delante de la fila, la columna o ambas.
Añadiendo una nueva condición
Vamos a añadir una condición nueva, que es que si el valor está entre los límites fijados en B6 y B7, que la celda muestre el dato en negrita.
Si no tienes claro como especificar esta condición, es una operación lógica Y que se debe escribir Y((B4/B3)<=$B6;(B4/B3)>=$B7). Esta operación evalúa las condiciones, que deben ir separadas entre si por «;» y si ambas son cierta devuelve VERDADERO y si una de ellas o ambas no son ciertas, devuelve FALSO.
Como ves, he tenido en cuenta fijas las «coordenadas» necesarias y dejar como relativas el resto.
Vamos de nuevo la celda B4 (podríamos seleccionar todo el rango) y a Formato condicional > Administrar reglas y hacemos click en Nueva regla. Seleccionamos la opción de utilizar una fórmula y allí, pegamos nuestra fórmula Y((B4/B3)<=$B6;(B4/B3)>=$B7). Definimos el formato, en esta ocasión que el texto sea negrita, y hacemos click en Aceptar. Si has seleccionado el rango completo, ya has terminado, si no, copia formato de la celda B4 al resto de meses. ¡Y listo!
¿Y listo?. No ha pasado nada. Vamos a ver por qué. Editamos el administrador de reglas y vamos a fijarnos en un «pequeño detalle». Las fórmulas que introdujimos antes, no tienen nada entre el igual y ellas. Sin embargo, la última que hemos introducido, tiene está entre comillas.
Y este «pequeño detalle», hace que no sea una fórmula, sino un texto.
Como hemos cazado el error, vamos a editar la regla, quitar las comillas y aceptar la nueva regla. Ten cuidado en como te desplazas dentro de la edición de la fórmula. Verás que Microsoft ha hecho de esto todas una experiencia. intenta siempre posicionarte justo delante del carácter que quieras editar, y elimínalo con el backspace.
Ahora si tenemos lo que queríamos.
Lo que ha pasado es que hemos introducido la fórmula sin el signo igual por delante (espero que hayas hecho un copia-pega, que para eso la he escrito antes sin signo igual) y Excel ha añadido el signo igual por su cuenta porque lo ha interpretado como un texto y ha pensado que queríamos que nuestra condición fuese un texto igual al que hemos escrito.
Conclusión 3
Tercera de las cuestiones importantes sobre el formato condicional: Presta atención a que la fórmula sea una fórmula y no un texto. Un error de estos a veces cuesta verlo porque nos centramos en la fórmula, especialmente si es algo compleja y nos puede llevar a grandes quebraderos de cabeza.
Resumen
Hemos recorrido paso a paso como introducir condiciones a nuestro formato y hemos repasado algunas cuestiones importantes sobre formato condicional que generan los problemas mas frecuentes que he visto en muchas ocasiones.
Como ves, hay detalles que pueden hacer que todo vaya mal, pero que si somos cuidadosos y, sobre todo, entendemos lo que estamos haciendo nosotros y Excel, no nos tienen por que dar mas quebraderos de cabeza de aquí en adelante.
Si te ha interesado este artículo, por favor, deja tu comentario. Y si tienes aún dudas, mándalas al foro de Excel de IMF que estaré encantado de resolverlas.
