La tabla dinámica de Excel muestra elementos eliminados en la lista de filtros: solución

Cuando eliminas datos de tu tabla de origen y actualizas una tabla dinámica, la lista desplegable de filtros puede seguir mostrando elementos antiguos que ya no existen. Esto ocurre porque Excel almacena en caché los valores únicos del rango de datos original dentro de la caché de la tabla dinámica. Los elementos obsoletos pueden saturar tu lista de filtros, dificultando la búsqueda de entradas actuales. Este artículo explica por qué la caché retiene elementos eliminados y proporciona dos métodos fiables para eliminarlos por completo.

Por qué los elementos antiguos permanecen en el filtro de tabla dinámica después de eliminar datos de origen

Excel almacena una instantánea de todos los valores únicos de los datos de origen dentro de la caché de la tabla dinámica cuando creas o actualizas el informe. Esta caché es independiente de la tabla de origen. Cuando eliminas filas o cambias valores en el origen, la caché no actualiza automáticamente su lista de elementos almacenados. La siguiente actualización solo actualiza los números agregados, no la lista de elementos que se muestran en la lista desplegable de filtros.

La causa raíz es la forma en que Excel gestiona la caché de la tabla dinámica. La caché conserva cada valor distinto que ha visto para un campo a menos que fuerces una reconstrucción completa de la caché. Excel hace esto para mejorar el rendimiento en conjuntos de datos grandes, evitando un escaneo completo del origen cada vez que filtras. Sin embargo, este diseño hace que los elementos eliminados permanezcan visibles en la lista de filtros indefinidamente.

Otro factor que contribuye es el uso de rangos con nombre o referencias de tabla que se reducen después de la eliminación de datos. Cuando eliminas filas de un rango con nombre, la definición del rango no se contrae automáticamente. La tabla dinámica sigue haciendo referencia al rango original, que ahora puede incluir celdas vacías. Excel almacena en caché cadenas vacías como elementos válidos, añadiendo más desorden a la lista de filtros.

Pasos para eliminar elementos eliminados de la lista de filtros de tabla dinámica

Dos métodos pueden eliminar los elementos de filtro obsoletos. El primer método utiliza una opción integrada de Excel que fuerza una actualización de diseño diferida. El segundo método utiliza una macro VBA simple para restablecer completamente la caché. Elige el método que se adapte a tu flujo de trabajo y nivel de comodidad técnica.

Método 1: Forzar una reconstrucción de caché mediante la actualización de diseño diferida

1. Selecciona cualquier celda dentro de la tabla dinámica
   Haz clic en una celda de la tabla dinámica para activar las pestañas Analizar y Diseño de tabla dinámica en la cinta de opciones.
2. Abre el cuadro de diálogo Opciones de tabla dinámica
   Haz clic derecho en la tabla dinámica y elige Opciones de tabla dinámica. Alternativamente, ve a Analizar tabla dinámica > Opciones en el extremo izquierdo de la cinta de opciones.
3. Habilita Actualización de diseño diferida
   En el cuadro de diálogo Opciones de tabla dinámica, ve a la pestaña Datos. En Datos de tabla dinámica, marca la casilla etiquetada Actualización de diseño diferida. Esto le dice a Excel que espere antes de aplicar cualquier cambio de diseño.
4. Cambia ligeramente el origen de datos
   En el mismo cuadro de diálogo, cambia a la sección Datos de origen. Haz clic en el botón Cambiar origen de datos. En el cuadro de diálogo Cambiar origen de datos de tabla dinámica, no cambies realmente el rango. En su lugar, haz clic dentro del cuadro Tabla/Rango y presiona Enter. Esta acción obliga a Excel a reevaluar el rango de origen y reconstruir la caché.
5. Desmarca Actualización de diseño diferida
   Vuelve a la pestaña Analizar tabla dinámica. En el grupo Tabla dinámica, desmarca la casilla Actualización de diseño diferida. Excel ahora actualiza la tabla dinámica con una caché limpia que contiene solo los elementos actuales de los datos de origen.

Método 2: Limpiar la caché de tabla dinámica con una macro VBA

1. Abre el Editor de Visual Basic
   Presiona Alt + F11 para abrir el editor de VBA. En el menú, ve a Insertar > Módulo para crear un nuevo módulo de código.
2. Pega el código de la macro
   Copia y pega el siguiente código en la ventana del módulo:

   Sub ClearPivotCache()
       Dim pt As PivotTable
       For Each pt In ActiveSheet.PivotTables
           pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
           pt.RefreshTable
       Next pt
   End Sub

3. Ejecuta la macro
   Presiona F5 para ejecutar la macro mientras el cursor está dentro del código. La macro establece la propiedad MissingItemsLimit en xlMissingItemsNone, lo que le dice a Excel que descarte todos los elementos almacenados en caché que ya no existen en el origen. Luego actualiza cada tabla dinámica en la hoja activa.
4. Guarda el libro como un archivo habilitado para macros
   Presiona Ctrl + S. En el cuadro de diálogo Guardar como, establece el tipo de archivo en Libro de Excel habilitado para macros (.xlsm). Haz clic en Guardar.

Si el filtro aún muestra elementos antiguos después de la solución principal

La tabla dinámica está conectada a una fuente de datos externa

Cuando tu tabla dinámica utiliza una conexión externa como SQL Server o Power Pivot, el comportamiento de la caché difiere. La fuente externa puede retener elementos históricos en su propia caché de consulta. Abre las propiedades de la conexión en Datos > Consultas y conexiones. Haz clic derecho en la conexión y selecciona Propiedades. En la pestaña Uso, marca la casilla Actualizar datos al abrir el archivo. Luego ve a la pestaña Definición y haz clic en Editar consulta. Agrega una cláusula WHERE para excluir filas eliminadas a nivel de origen.

Tabla dinámica basada en OLAP desde un cubo

Las tablas dinámicas construidas sobre un cubo OLAP no almacenan elementos localmente. La lista de filtros refleja los miembros de dimensión actuales del cubo. Si los elementos eliminados aún aparecen, el cubo no ha sido procesado. Contacta a tu administrador de base de datos para procesar el cubo y eliminar los miembros obsoletos. No puedes solucionar esto solo desde Excel.

Varias tablas dinámicas comparten la misma caché

Si creaste varias tablas dinámicas desde el mismo rango de origen, comparten una sola caché de tabla dinámica. Eliminar elementos en una tabla dinámica no limpia la caché para las demás. Usa la macro VBA del Método 2 y aplícala a todas las hojas. Alternativamente, haz clic derecho en cada tabla dinámica y elige Opciones de tabla dinámica > pestaña Datos. Establece Número de elementos para conservar por campo en Ninguno. Esta configuración obliga a cada tabla dinámica a descartar elementos huérfanos al actualizar.

Comparación rápida: Diseño diferido vs. Borrado de caché con VBA

Elemento	Actualización de diseño diferida	Macro VBA (MissingItemsLimit)
Nivel de habilidad requerido	Principiante	Intermedio
Tiempo de ejecución	30 segundos	10 segundos después de la configuración
Afecta a todas las tablas dinámicas de la hoja	Una a la vez	Todas a la vez
Requiere archivo habilitado para macros	No	Sí (.xlsm)
Previene permanentemente elementos obsoletos	No, debe repetirse después de cada cambio de datos	No, debe ejecutarse la macro después de cada cambio de datos

Prevenir elementos de filtro obsoletos en futuras tablas dinámicas

Para evitar que los elementos antiguos reaparezcan, puedes ajustar la estructura de los datos de origen. Convierte tu rango de origen en una tabla de Excel seleccionando el rango y presionando Ctrl + T. Las tablas se expanden y contraen automáticamente a medida que agregas o eliminas filas. Cuando actualizas la tabla dinámica, Excel lee el conjunto de filas actual de la tabla y almacena en caché solo los elementos que existen. Esto elimina la necesidad de limpiar manualmente la caché después de cada cambio de datos.

Otra medida preventiva es establecer la opción de tabla dinámica Número de elementos para conservar por campo en Ninguno. Haz clic derecho en la tabla dinámica, elige Opciones de tabla dinámica, ve a la pestaña Datos y establece la lista desplegable en Ninguno. Esto le dice a Excel que no almacene en caché elementos históricos en absoluto. Combinado con una fuente basada en tabla, esta configuración mantiene la lista de filtros limpia automáticamente.

Finalmente, siempre actualiza la tabla dinámica usando el botón Actualizar todo en la pestaña Datos en lugar del atajo Ctrl + Alt + F5. Actualizar todo obliga a Excel a reconstruir la caché para todas las tablas dinámicas del libro, no solo la activa. Esto reduce la posibilidad de que elementos obsoletos permanezcan en cachés compartidas.