La hoja de deuda vencida de Penrose Scaffolding pone en rojo una factura a los sesenta días, y cobros trabaja las filas rojas cada lunes. Durante cinco semanas tres facturas por 41.800 € estuvieron a noventa días y más, en blanco y negro.
La regla era correcta. Se equivocaba en dónde vivía: el cuadro «Se aplica a» decía =$A$2:$G$8, y esas tres facturas se habían pegado en la fila 9.
| Medida | Lo que mostraba la hoja | Lo que tenía el libro |
|---|---|---|
| Facturas abiertas de más de 60 días | 3 | 6 |
| Importe resaltado en rojo | 40.800,00 € | 82.600,00 € |
| Importe nunca reclamado | 0,00 € | 41.800,00 € |
| Filas dentro del rango de la regla | 7 | 10 |
Un formato condicional no es una propiedad de tus datos. Es una propiedad de un rango, y un rango es algo que se acaba en algún sitio.
01La Regla Era Correcta; Su Rango No
Seis Facturas de Más de Sesenta Días, Tres de Ellas en Rojo
Diez facturas de la hoja de deuda vencida de Penrose Scaffolding, en A2:H11. La columna C son los días de retraso y la columna E es el estado; la columna F indica si la fila cae dentro del rango «Se aplica a» de la regla de formato condicional, y la columna G es lo que hizo la hoja de verdad. La regla es `=Y($C2>60;$E2="Open")` con relleno rojo, y su cuadro «Se aplica a» dice `=$A$2:$G$8`, escrito cuando la hoja acababa en la fila 8. Seis filas están abiertas y pasan de sesenta días: INV-4118, INV-4133 e INV-4151 dentro del rango y en rojo, 40.800,00 € entre las tres, e INV-4172, INV-4181 e INV-4190 pegadas en las filas 9 a 11 y en blanco, 41.800,00 €. INV-4166 es el control: noventa y siete días, dentro del rango y correctamente sin rojo porque está marcada como pagada.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
La columna C son los días de retraso y la columna G es lo que hizo la hoja con cada fila. Las filas 3, 5 y 7 están en rojo y bien. Las filas 9, 10 y 11 pasan de sesenta días, están abiertas y siguen en blanco.
La diferencia entre ellas no está en los datos. INV-4172, con noventa y tres días, está más vencida que cualquier fila de arriba; se pegó por debajo de la última celda de la que la regla había oído hablar.
La fila 8 es el control. INV-4166 tiene noventa y siete días y está dentro del rango, y correctamente no sale en rojo, porque está marcada como pagada y la regla pide las dos condiciones a la vez.
Escenario: Selecciona una celda de la última fila de tu hoja y lee Inicio > Formato condicional > Administrar reglas > Se aplica a. Después pon =CONTAR.SI.CONJUNTO($C$2:$C$11;">60";$E$2:$E$11;"Open") en una celda libre y compara su respuesta con las filas rojas que puedas contar.
02«Se Aplica a»: Dónde Vive de Verdad una Regla
Cada regla lleva un rango, y Excel lo guarda con la regla y no con las celdas. Inicio > Formato condicional > Administrar reglas lo enseña en el cuadro «Se aplica a», y ese cuadro se edita ahí mismo.
Pon la lista desplegable de arriba del cuadro de diálogo en «Esta hoja de cálculo». En «Selección actual» solo salen las reglas que tocan la celda en la que estás, y esa es la razón habitual de que una regla parezca haber desaparecido.
El rango nunca se amplía solo. Escribir una fila debajo añade datos y nada más, y así es como una hoja que funcionó todo el año deja de funcionar la semana en que crece.
Editar «Se aplica a» es el arreglo de una línea. Haz clic en el cuadro, escribe =$A$2:$G$5000 y pulsa Entrar. Excel conserva la fórmula de la regla tal como está escrita y la evalúa sobre el rango nuevo.
Alcance. Esto es Excel de escritorio, de 2016 a Microsoft 365. El mismo cuadro está en Formato > Formato condicional en Mac, y «Se aplica a» se comporta igual.
Excel en la web enseña las reglas y deja editar el rango, pero no deja reordenarlas, así que un problema de «Detener si es verdad» hay que arreglarlo en la aplicación de escritorio.
Escenario: Abre Administrar reglas, pon el desplegable en «Esta hoja de cálculo» y lee todos los «Se aplica a» de la hoja. Amplía el que se quede corto a =$A$2:$G$5000 y mira cómo las filas de debajo del límite antiguo toman el formato al instante.
03El Pegado Que Parte una Regla en Cuarenta
Copiar una celda copia también sus formatos condicionales. Pégala donde la regla no llega y Excel no amplía la regla: escribe una segunda copia de ella sobre las celdas en las que has pegado.
Haz eso unos meses y Administrar reglas lista la misma condición ocho, veinte o cuarenta veces, cada una con su rango desigual: $G$4:$G$7;$G$12;$G$18:$G$21.
Los huecos entre esos fragmentos son filas sin ninguna regla. Y son también un archivo que tarda diez segundos en desplazarse, porque Excel evalúa cada fragmento por separado en cada recálculo.
Arréglalo de una pasada. Selecciona las columnas, usa Inicio > Formato condicional > Borrar reglas > Borrar reglas de las celdas seleccionadas, y después escribe la regla una sola vez sobre el rango que de verdad quieres.
Para mover datos sin arrastrar su formato, cópialos y usa Inicio > Pegar > Valores. El destino conserva sus propias reglas y no se añade nada nuevo a la lista.
Escenario: Copia una celda con formato y pégala tres filas por debajo del rango de la regla, abre Administrar reglas con «Esta hoja de cálculo» y cuenta las entradas. Bórralas con «Borrar reglas de las celdas seleccionadas» y escribe una sola regla sobre =$A$2:$G$5000.
04El Orden de las Reglas y «Detener si es Verdad»
Las reglas se evalúan de arriba abajo en Administrar reglas, y la primera que fija una propiedad se la queda. Una regla posterior todavía puede fijar otra propiedad, así que el relleno de una y la negrita de otra pueden caer en la misma celda.
«Detener si es verdad» corta la lista. Márcala y nada de lo que haya debajo se evalúa para las filas que esa regla cumple, que es exactamente lo que una regla gris para facturas pagadas le hace al rojo que escribiste más abajo.
Las flechas de arriba del cuadro de diálogo suben y bajan una regla. Pon las reglas estrechas y concretas por encima de las amplias, y no dejes nunca un «Detener si es verdad» donde la siguiente persona no vaya a mirarlo.
«Detener si es verdad» viene de los archivos en los que era la única forma de ocultar un conjunto de iconos en algunas filas. En una hoja de reglas de relleno es casi siempre el accidente de alguien.
Escenario: En Administrar reglas, marca «Detener si es verdad» en tu regla de arriba y mira cómo todas las de debajo dejan de aplicarse en las filas que cumple la primera. Desmárcala, sube tu regla roja al principio con la flecha y comprueba que gana.
Pruébalo en la cuadrícula05$C2, C2 y $C$2 Dentro de una Regla
La fórmula de una regla se escribe una vez, para la celda superior izquierda de «Se aplica a», y Excel la copia por el resto del rango igual que copia una fórmula por un bloque de celdas.
Así que con «Se aplica a» en =$A$2:$G$8, la fórmula se escribe como si estuviera en A2. =Y($C2>60;$E2="Open") fija las dos columnas y deja viajar el número de fila, que es lo que pinta la fila entera.
Quita los dólares y =Y(C2>60;E2="Open") mira D2 y F2 desde la columna B, E2 y G2 desde la columna C, y disparates a partir de ahí. Fíjalos del todo y =Y($C$2>60;$E$2="Open") pinta las siete filas del color de la primera.
| En la regla | Qué mira cada celda | Para qué sirve |
|---|---|---|
$C2 | Columna C, su propia fila | Resaltar una fila entera |
C$2 | Su propia columna, fila 2 | Una regla guiada por el encabezado |
$C$2 | Siempre C2 | Una celda de umbral para todas |
C2 | Se mueve en los dos sentidos | Casi nada |
Selecciona la celda superior izquierda del rango antes de abrir el cuadro de diálogo. Excel escribe la fórmula contra la celda activa, y una regla escrita con la fila 40 seleccionada nace desviada treinta y ocho filas.
Escenario: Selecciona A2:G8, escribe la regla =Y($C2>60;$E2="Open") y comprueba que se pintan filas enteras. Ahora edita la regla para quitar los dólares y mira cómo el relleno se dispersa en diagonal por el bloque.
06Reglas Que Nunca Devuelven VERDADERO
La fórmula de una regla tiene que dar VERDADERO o FALSO. Cualquier otra cosa y Excel la toma por FALSO o rechaza la fórmula de entrada, y ninguna de las dos salidas te dice qué filas se saltó.
El texto que parece un número es el caso habitual, y falla en la dirección ruidosa. Excel coloca cualquier texto por encima de cualquier número, así que una celda con "93" como texto hace =$C2>60 VERDADERO, y una celda con "9" también.
Cuéntalo. =CONTAR($C$2:$C$11) cuenta los números de verdad y =CONTARA($C$2:$C$11) cuenta todo lo que no está vacío. La diferencia entre ambos es el número de celdas en las que tu comparación no es fiable.
Las celdas vacías fallan en la dirección silenciosa. Una celda vacía vale cero, así que =$C2<30 es VERDADERO en todas las filas vacías de debajo de los datos, y una regla pensada para marcar facturas recientes pinta el resto de la hoja.
El remedio cabe en la propia regla: =Y($A2<>"";$C2<30) exige que la fila exista antes de mirar la cifra, y las filas vacías vuelven a quedarse como estaban.
Los errores matan una regla. Un solo #N/A en una columna que la regla lee convierte toda la fórmula en error en esa fila, un error no es VERDADERO, y la fila se queda con su relleno de siempre. =SI.ERROR($C2>60;FALSO) te devuelve la decisión.
Escenario: Escribe un apóstrofo delante de un número de tu columna de umbral, para que la celda tenga texto, y mira cómo la fila se pone roja sea cual sea la cifra. Después compara =CONTAR($C$2:$C$11) con =CONTARA($C$2:$C$11) y arregla las celdas de la diferencia.
07Haz Que el Rango Crezca: Tablas y Columnas
El rango que nunca se queda corto es el que mantiene Excel por ti. Selecciona los datos, pulsa Ctrl+T y escribe la regla sobre la columna de la tabla: cada fila que añadas a la tabla llega con la regla ya puesta.
Una tabla crece cuando escribes en la fila de debajo o pegas dentro, y el «Se aplica a» la sigue. Administrar reglas sigue enseñando direcciones de celda en vez de Tabla1[Días de retraso], y Excel reescribe esas direcciones según se mueve la tabla.
Las columnas enteras son la alternativa bruta. Un «Se aplica a» de =$A:$G no se acaba nunca, al precio de una regla evaluada sobre un millón de filas: se nota en un archivo pesado y no se nota en uno pequeño.
El término medio es un rango fijo generoso como =$A$2:$G$5000, con las filas vacías resueltas en la fórmula y no en el rango: =Y($A2<>"";$C2>60;$E2="Open").
Elijas lo que elijas, escríbelo una sola vez. Una regla sobre un rango honrado es la única versión de esto que otra persona podrá seguir leyendo dentro de un año.
Escenario: Selecciona tus datos, pulsa Ctrl+T para convertirlos en tabla y reescribe la regla sobre la columna de la tabla. Después pega tres filas nuevas al final y comprueba que llegan con formato sin que nadie abra Administrar reglas.
Pruébalo en la cuadrícula08Cuenta lo Que Debería Estar en Rojo
El color no es un dato. Nada en la hoja lo suma, SUMAR.SI no lo ve, y la única forma honrada de auditar un resaltado es escribir su condición como fórmula al lado de la cuadrícula.
=CONTAR.SI.CONJUNTO($C$2:$C$11;">60";$E$2:$E$11;"Open") devuelve 6 en esta hoja frente a tres filas rojas. =SUMAR.SI.CONJUNTO($D$2:$D$11;$C$2:$C$11;">60";$E$2:$E$11;"Open") devuelve 82.600,00 € frente a los 40.800,00 € que se estaban trabajando.
=SUMAPRODUCTO(--($C$2:$C$11>60);--($E$2:$E$11="Open")) responde lo mismo en una línea para quien tenga un Excel lo bastante antiguo como para necesitarla.
Para listar las filas en vez de contarlas, derrámalas. =FILTRAR($A$2:$E$11;($C$2:$C$11>60)*($E$2:$E$11="Open")) devuelve las seis, dentro o fuera del rango de la regla, y =ORDENAR() alrededor pone las más antiguas primero.
Después comprueba el rango en sí: =CONTARA($A$2:$A$5000) frente a la última fila que menciona tu «Se aplica a». Si los datos son más largos que la regla, ya sabes lo que los colores no te están contando.
Escenario: Pon el CONTAR.SI.CONJUNTO y el recuento de filas rojas en dos celdas de la parte de arriba de la hoja, con =SI(CONTAR.SI.CONJUNTO($C$2:$C$11;">60";$E$2:$E$11;"Open")>3;"Revisa el rango";"OK") al lado, y déjalos ahí para siempre.
09Nueve Formas en Que una Regla se Calla
- «Se aplica a» se queda corto. La regla está bien, las filas por debajo de su última celda no tienen regla ninguna, y nada en la hoja lo dice.
- Administrar reglas está en «Selección actual». La lista parece vacía porque estás en una celda que no toca ninguna regla. Cámbialo a «Esta hoja de cálculo».
- Un pegado partió la regla en fragmentos. Veinte copias de una condición, cada una sobre unas pocas filas, con huecos sin formato entre ellas.
- «Detener si es verdad» por encima. Una regla más alta de la lista cumplió en esa fila y cortó la evaluación antes de llegar a la tuya.
- El orden de las reglas. Dos rellenos en una fila: gana la regla de arriba, y la de abajo no se ve aunque se esté cumpliendo.
- Faltan los dólares.
C2en vez de$C2hace que cada columna mire otra columna, y el relleno se dispersa por el bloque. - Texto donde debería haber números. El texto supera a cualquier número en una comparación, así que
=$C2>60es VERDADERO con una celda que contiene"9". - Filas vacías debajo de los datos. Una celda vacía vale cero, así que cualquier regla de «menor que» pinta todas las filas vacías del rango.
- «Borrar formatos» se lo llevó. Inicio > Borrar > Borrar formatos quita el formato condicional de la selección junto con la negrita y los bordes, sin decir nada.
10Mini Ejercicios
- Lee el «Se aplica a» de todas las reglas de una hoja con «Esta hoja de cálculo» seleccionado. Espera al menos un rango que acabe antes que los datos.
- Copia una celda con formato, pégala fuera del rango de la regla y vuelve a abrir Administrar reglas. Espera una segunda copia de la misma condición.
- Escribe
=Y($C2>60;$E2="Open")sobre A2:G8 y después quita los dólares. Espera que el relleno se mueva en diagonal en vez de por filas. - Pon el texto
"9"en la columna de días con un apóstrofo delante. Espera que la fila se ponga roja bajo una regla que pide más de 60. - Compara
=CONTAR.SI.CONJUNTO($C$2:$C$11;">60";$E$2:$E$11;"Open")con las filas rojas. Espera 6 y 3.
Lo Que Hay Que Llevarse
Nadie en esta historia tecleó una cifra mal ni escribió una regla mal. Diez facturas estaban bien envejecidas, la condición era exactamente la que quería cobros, y las tres filas a las que llegaba se pusieron rojas la mañana que tocaba.
El cuadro «Se aplica a» de la regla acababa en la fila 8 porque ahí acababa la hoja el día que alguien la escribió. Las tres facturas que llegaron después quedaron fuera.
Y una celda fuera del rango de una regla no es una celda que la regla haya decidido no pintar: es una celda de la que la regla no ha oído hablar nunca.
Así que amplía el rango o, mejor, conviértelo en tabla y deja que lo mantenga Excel. Y escribe el recuento al lado, porque un color es la única salida de Excel que ninguna otra cosa puede comprobar.