Formato condicional que sigue a una tabla dinámica (y a cualquier desbordamiento)

Formato condicional que sigue a una tabla dinámica, un FILTRAR o un PIVOTARPOR cuando cambian de tamaño: por qué falla a mano y cómo lo resuelve el complemento.

Revisado el 03/09/2026

Pones un formato condicional sobre una tabla dinámica, cambias el filtro, y el formato se queda donde estaba: pinta media tabla, o pinta filas vacías, o deja sin pintar la columna nueva. Con un FILTRAR o un PIVOTARPOR pasa lo mismo. Esta página explica por qué ocurre, cómo se ha resuelto siempre a mano (mal) y cómo lo hace la pestaña Formato del complemento de Excel de InflueXcel, que deja en el fichero una regla de Excel normal y corriente que funciona aunque quien lo abra no tenga el complemento.

El problema: el formato condicional se ancla a un rango fijo

Excel guarda cada regla con un "se aplica a" que es un rango de coordenadas fijas, por ejemplo $C$9:$H$40. Da igual lo que escribas en esa caja: si pones $C$9# para que siga al desbordamiento, o un nombre definido dinámico, o una referencia estructurada de tabla, Excel lo congela a coordenadas en cuanto pulsas Aceptar.

Una tabla dinámica, un FILTRAR, un ORDENAR, un PIVOTARPOR o un AGRUPARPOR cambian de alto cada vez que cambian los datos o el filtro. Y las dinámicas y PIVOTARPOR cambian también de ancho. El rango de la regla no se entera. Ese es todo el problema, y no tiene arreglo dentro del cuadro de diálogo de Excel.

Cómo se resuelve a mano, y por qué acaba mal

La solución habitual es sobredimensionar: aplicar la regla a $C$9:$H$1000 y confiar en que nunca haya más de mil filas. Funciona hasta que aparecen tres problemas.

• Las filas vacías se pintan. Una celda vacía compara como cero, así que una regla <=12 pinta las novecientas filas vacías que hay debajo del bloque. Toca envolver la condición en un SI(C9="";FALSO;...).
• La regla se vuelve lenta. Muchos envuelven con Y($C9<>"";condición), pero Y() evalúa todos sus argumentos siempre, no hace cortocircuito. La condición cara se paga en cada una de las mil filas, estén vacías o no. Solo el SI() anidado es perezoso.
• Las reglas por posición mienten. "Pinta la tercera columna en rojo" deja de ser "pinta el importe" en cuanto el filtro horizontal de la dinámica quita una columna. La regla no queda mal encuadrada, queda diciendo algo falso, que es peor porque nadie lo nota.

Y cada vez que el bloque se ensancha más de lo previsto, se vuelve a empezar.

Lo que hace el complemento

La pestaña Formato del complemento genera la regla que escribirías a mano si tuvieras media hora y supieras exactamente qué guardas poner. El gesto es corto.

1. Marca una celda cualquiera del bloque (la esquina de la dinámica, la celda con el FILTRAR, o un rango normal).
2. Abre la pestaña Formato y pulsa Leer la selección. El panel detecta si es un desbordamiento, dónde están las cabeceras y cuál es la primera fila de datos, y lo muestra en una línea: Hoja1 · bloque en C9 · 32×6 (desbordamiento).
3. Elige un formato rápido (por ejemplo, Formato de tabla) o pulsa Crear formato personalizado para montar la regla condición a condición.
4. Aplica. La regla queda en el administrador de reglas de Excel como una regla más.

Cambia el filtro de la dinámica, añade filas al origen, quita una columna: el formato sigue a los datos.

Qué queda escrito en el fichero

Esto es lo importante, y lo que diferencia una herramienta de una dependencia. El complemento no está evaluando nada en segundo plano. Es un compilador: escribe una regla nativa y se retira. La regla funciona en Excel para la web, en Mac, en el móvil, en Excel 2019 y en el ordenador de quien no tiene el complemento.

La regla se construye con tres piezas.

La envolvente. El "se aplica a" arranca en la primera fila de datos y llega hasta el final de la hoja hacia abajo, más un margen de columnas a la derecha (cuántas más puede llegar a mostrar el bloque). Empezar en la fila de datos y no en la 1 deja la cabecera fuera por construcción, sin necesidad de un FILA()>8 en la fórmula.

El guard. Una condición previa que apaga la regla donde no hay datos, con SI() anidado y no con Y(). Para pintar la celda que cumple, basta con mirar la propia celda:

=SI(C9="";FALSO;C9>1000)

Para pintar la fila entera (donde la celda pintada puede estar vacía legítimamente, como la esquina de una dinámica), el guard mira si hay algo en la fila y algo en la columna, con anclaje mixto:

=SI(CONTARA($C9:$Q9)=0;FALSO;SI(CONTARA(C$8:C$9)=0;FALSO;INDICE($C9:$Q9;COINCIDIR("Importe";$C$8:$Q$8;0))>1000))

$C9 fija la columna y deja correr la fila: apaga las filas de más. C$8 fija la fila de cabeceras y deja correr la columna: apaga las columnas que hoy no se muestran. Con eso cubre un bloque que cambia de alto y de ancho a la vez.

La condición por cabecera. En vez de "la columna E", la regla busca la columna por su nombre. Para pintar solo las celdas de esa columna, como la celda evaluada ya es la de esa columna, basta con comprobar su cabecera:

=SI(E9="";FALSO;Y(E$8="Importe";E9>1000))

Si mañana "Importe" está en la columna G, la regla la sigue. Y quien abra el administrador de reglas sin el complemento puede leerla entera y entender qué hace, que es la otra razón de escribirlas así: una regla ilegible se borra por miedo.

Los formatos rápidos

El panel abre con cuatro formatos de fábrica, pensados para lo que se pierde al usar un desbordamiento en vez de una tabla: las bandas, la cabecera y la fila de totales.

• Formato de tabla: cabecera en negrita con relleno, bandas alternas (empezando en la segunda fila de datos, para que la primera quede limpia pegada a la cabecera) y última fila en negrita con borde superior. Es el "dale formato de tabla" que Excel no ofrece para un FILTRAR ni para un PIVOTARPOR.
• Números con formato: separador de millares a las celdas numéricas, sin tocar cabeceras ni etiquetas.
• Resaltar totales: última fila y última columna del bloque en negrita con relleno.
• Huecos y errores: celdas vacías dentro del bloque en gris, errores en rojo, para revisar un bloque de un vistazo.

Marcas el rango, pulsas el botón y ya está. Los botones leen la selección ellos mismos, así que no hace falta pulsar antes "Leer la selección". Con la suscripción, los formatos que crees tú se guardan al lado y se aplican igual, en cualquier libro.

Crear un formato personalizado

El asistente tiene dos familias de condiciones, en un selector aparte, porque "primera fila" no es un operador y no debería vivir en el mismo desplegable que "mayor que".

Por valor

Eliges la columna por su cabecera (el desplegable ofrece los nombres, no las letras), el operador y el valor. Y decides qué se pinta: la celda que cumple, toda la columna que cumple, o la fila entera según esa columna.

En el mismo desplegable de operadores están las pruebas de contenido: es un número, es texto, está vacía, es un error. Van ahí y no en "posición" porque son contenido, y porque es donde Excel las pone también. Si el operador pide un valor y lo dejas vacío, el panel no genera nada y te dice qué falta, en vez de fabricar una fórmula válida pero absurda.

Por posición

Esta familia es la que sustituye al formato de tabla. Todas las fórmulas van ancladas a la esquina del bloque, que es lo que casi nadie hace a mano: un RESIDUO(FILA();2) a secas desfasa las bandas si el bloque no empieza en fila impar, y se descuadra al moverlo.

"Última fila" no cuenta nada: mira si la fila de debajo está vacía. Es instantáneo y no necesita saber el tamaño del desbordamiento. Tiene una limitación real, que el panel dice: falla si hay otro bloque pegado justo debajo.

Formato de número y bordes

Además de relleno, color de texto, negrita, cursiva y subrayado, una regla puede fijar el formato de número y los bordes. El formato de número es seguramente el mejor argumento de toda la pestaña: un desbordamiento no lleva formato, porque el formato vive en la celda y no en la fórmula. Si el bloque gana una columna, la nueva sale sin formato; si la pierde, queda un formato huérfano. Con una regla anclada por cabecera, la columna "Importe" sale con #.##0,00 esté donde esté.

Los bordes van a cada celda que cumple, no al perímetro del rango: Excel no permite otra cosa en un formato condicional. Para cerrar un bloque se combinan predicados: "última fila" con borde inferior, "primera fila" con borde superior.

Las reglas se apilan atributo a atributo, así que el diseño natural es una regla por intención: bandas para el relleno, columna Importe para el número, última fila para la negrita y el borde. Conviven sobre la misma celda sin pelearse.

Configurar el rango

Debajo del resumen del bloque hay un enlace Configurar rango con lo que describe el bloque y no la regla, porque se comparte entre todas las reglas que pongas sobre él.

• Fila de cabeceras y 1ª fila de datos. El panel las rellena con la suposición habitual (cabecera en la fila de encima) y tú corriges. Hace falta porque hay bloques con las cabeceras dentro del propio desbordamiento, como APILARV(cabeceras;datos) o el resultado de PIVOTARPOR. Si la fila de cabeceras está vacía, el panel lo dice y sugiere el arreglo, en vez de adivinar.
• Alcance: envolvente o rango exacto. Al leer un desbordamiento se propone la envolvente; al leer un rango fijo, el rango exacto, que es además el único que vale para escalas de color, barras e iconos.
• Margen de columnas: cuántas columnas más puede crecer el bloque. La envolvente llega al final de la hoja hacia abajo, pero en horizontal se corta en el ancho de hoy más este margen. Con margen 0, si el bloque se ensancha, las columnas nuevas quedan sin regla. No es un fallo, y el panel lo avisa en ámbar.

Ver qué reglas afectan a una celda, y por qué se pinta

Antes de crear nada, el panel lista las reglas que afectan a lo que has marcado, con una etiqueta legible de lo que hace cada una ("Filas alternas", "Columna Importe · números"). Distingue entre "la hoja no tiene reglas" y "ninguna pinta esta celda, la hoja tiene 14", que no es lo mismo cuando estás depurando un libro heredado.

Con la suscripción está además ¿Por qué se pinta esta celda?: evalúa cada regla que alcanza la celda, en orden de prioridad, y dice cuál se cumple, cuál no y cuál queda tapada por una de más prioridad con "detener si es verdad". Cada veredicto lleva un desplegable con la fórmula que realmente se evaluó y el valor que devolvió. Es la respuesta a la pregunta que más tiempo quita en un libro con reglas de tres personas distintas.

Editar, reordenar y borrar

Cada regla se edita en sitio, dentro de su tarjeta: cambiar un color, la fórmula o el rango no obliga a borrarla y crearla de nuevo (que le haría perder la prioridad). La fórmula se muestra y se guarda en el idioma de tu Excel, tal cual la escribes. Reordenar es arrastrar la tarjeta. Borrar, una ✕.

Con la suscripción, la fórmula de la regla se edita en el mismo editor de fórmulas de la pestaña Celda: autocompletado, firma de la función con el parámetro actual resaltado, tabulado automático para leer un SI(SI(SI.ERROR(INDICE(COINCIDIR(... y marcar rangos en la hoja. La caja de fórmula del formato condicional de Excel es un campo de una línea donde las flechas insertan referencias en vez de mover el cursor; escapar de ella es, por sí sola, una razón para abrir el panel. Sin suscripción se escribe la misma fórmula en una caja de texto normal.

Fusionar reglas repetidas

Este es para quien no usa desbordamientos, que es la mayoría. Heredas un libro donde cada copiar y pegar ha duplicado las reglas de formato condicional: 400 reglas idénticas sobre rangos de dos celdas, el administrador es ilegible y el libro va lento. Fusionar reglas repetidas agrupa las que tienen la misma fórmula y el mismo formato en una sola con todas las áreas, te enseña el plan ("400 reglas → 12") y lo aplica si dices que sí. Función de suscripción.

Recetas: guarda tus formatos

Una receta guarda los ajustes de una o varias reglas (condición, colores, formato de número, bordes), nunca las direcciones. El ancla, las cabeceras y la envolvente se leen del bloque al aplicarla. Por eso una receta guardada en un libro vale en otro con otras coordenadas, y por eso se sincroniza con tu cuenta y la tienes en cualquier ordenador. Función de suscripción; las recetas curadas de InflueXcel son gratis.

Lo que no puede hacer

• Tipo y tamaño de letra, y alineación. No existen en el formato condicional de Excel, ni en el diálogo nativo ni en la API. Ninguna herramienta puede.
• Bordes al perímetro del bloque. Los bordes son por celda. Se simula con "primera fila" y "última fila".
• Bloques pegados. "Última fila" y "última columna" miran si la celda de al lado está vacía, así que dos bloques sin fila en blanco entre ellos se leen como uno.
• Ensanchar más allá del margen. Es la única cosa que no se arregla sola. El panel dice "cubre hasta 20 columnas (hoy 12)" para que el margen sea un dato y no una suposición.

Gratis y con suscripción

La pestaña gratuita crea reglas correctas. No produce nada incorrecto a sabiendas: produce lo mismo, con menos comodidades.

Preguntas frecuentes

¿Si mando el fichero a alguien sin el complemento, se rompe el formato? No. Lo que queda en el fichero es una regla de formato condicional de Excel, sin macros ni conexiones. Se abre en cualquier Excel.

¿Funciona con tablas dinámicas o solo con fórmulas que desbordan? Con las dos. Una dinámica es un bloque que cambia de alto y de ancho, exactamente el caso para el que se diseñó. Se lee igual: marca una celda de la dinámica y pulsa Leer la selección.

¿Por qué la regla que veo en el administrador de Excel es tan larga? Porque lleva el guard que apaga las filas y columnas vacías, y la búsqueda por cabecera. Es la fórmula que tendrías que escribir a mano para que funcionara bien. En el panel se muestra tabulada para que se lea.

¿Mi Excel está en inglés, funciona? Sí. Las fórmulas se generan en el idioma de tu Excel, y las reglas se reconocen por su versión neutra, así que las etiquetas salen igual en cualquier idioma de Office.

¿Cómo instalo el complemento? Es gratis con una cuenta de InflueXcel. Los pasos están en la página del complemento y en tu perfil una vez registrado.