Cuando creas una fórmula de matriz dinámica en Excel, los resultados se derraman en las celdas adyacentes. Si esas celdas están ocultas por un filtro, un grupo contraído o la ocultación manual de filas o columnas, la fórmula devuelve un error #SPILL!. Esto ocurre porque Excel no puede escribir los valores derramados en celdas que no son visibles en la pantalla. Este artículo explica por qué las celdas ocultas bloquean el derrame y proporciona métodos paso a paso para corregir el error.
Puntos clave: Desbloquear matrices derramadas en celdas ocultas
- Mostrar filas o columnas: Revelar las celdas bloqueadas permite que la matriz se derrame correctamente y elimina el error
#SPILL!. - Seleccionar un rango de derrame que evite áreas ocultas: Mueve la fórmula a una ubicación donde todo el rango de derrame sea visible.
- Usar el operador @ para forzar el comportamiento de resultado único: Convierte la matriz dinámica en una fórmula de una sola celda que no requiere derrame.
Por qué las celdas ocultas bloquean los derrames de matrices dinámicas
Las fórmulas de matriz dinámica en Excel, introducidas en 2020, devuelven automáticamente múltiples resultados que se “derraman” en las celdas adyacentes. La fórmula se escribe en una celda y Excel llena las celdas vecinas con la matriz de salida. Para que esto funcione, cada celda en el rango de derrame debe estar vacía y visible.
Cuando una celda en el rango de derrame está oculta debido a filtros, agrupación u ocultación manual, Excel no puede escribir el resultado allí. El motor de fórmulas trata las celdas ocultas como bloqueadas y devuelve el error #SPILL! en su lugar. El mensaje de error dice “El rango de derrame no está vacío” o “El rango de derrame está oculto”, dependiendo de la versión de Excel. La causa raíz es siempre la misma: Excel no puede colocar datos en una celda que no se muestra actualmente.
Este comportamiento es intencional. Excel evita escribir en celdas ocultas para evitar que los datos se sobrescriban sin que el usuario lo note. Una vez que las filas o columnas ocultas se muestran, la matriz se derrama normalmente. La solución es hacer visibles esas celdas o modificar la fórmula para que no necesite derramarse en el área oculta.
Métodos para corregir errores de derrame causados por celdas ocultas
Elige uno de los siguientes métodos según si deseas mantener los datos ocultos o mover la fórmula. Cada método resuelve el error #SPILL!.
Método 1: Mostrar las filas o columnas en el rango de derrame
La solución más simple es hacer visibles todas las celdas en el rango de derrame. Sigue estos pasos:
- Identificar el rango de derrame
Haz clic en la celda con la fórmula de matriz dinámica. Excel dibuja un borde azul alrededor del rango de derrame previsto. Anota las referencias de fila y columna de ese rango. - Mostrar filas
Selecciona las filas por encima y por debajo de las filas ocultas en el rango de derrame. Haz clic derecho en cualquier número de fila seleccionado y elige Mostrar en el menú contextual. Repite para todos los bloques de filas ocultas dentro del rango de derrame. - Mostrar columnas
Selecciona las columnas a la izquierda y derecha de las columnas ocultas en el rango de derrame. Haz clic derecho en una letra de columna seleccionada y elige Mostrar. - Quitar filtros
Si el rango de derrame está dentro de una tabla o rango filtrado, borra el filtro. Ve a la pestaña Datos y haz clic en Borrar en el grupo Ordenar y filtrar. Alternativamente, presiona Ctrl+Mayús+L para desactivar el filtro. - Expandir grupos contraídos
Si las filas o columnas están agrupadas, haz clic en el botón + sobre el grupo para expandirlo. También puedes ir a la pestaña Datos y hacer clic en Agrupar en el grupo Esquema para eliminar la agrupación por completo. - Verificar que el error #SPILL! desaparezca
Después de mostrar todas las celdas, la fórmula se recalcula y muestra la matriz completa.
Método 2: Mover la fórmula a una ubicación sin celdas ocultas
Si necesitas mantener ciertas filas o columnas ocultas, mueve la fórmula a una parte de la hoja de cálculo donde todo el rango de derrame sea visible.
- Seleccionar una nueva celda para la fórmula
Elige una celda donde el rango de derrame no se superponga con filas o columnas ocultas. Para verificar, muestra temporalmente todas las filas y columnas, anota el rango de derrame previsto y luego vuelve a ocultar solo las áreas que queden fuera del nuevo rango de derrame. - Copiar la fórmula
Presiona Ctrl+C en la celda con el error. Luego presiona Escape para salir del modo de copia. - Pegar en la nueva celda
Selecciona la nueva celda y presiona Ctrl+V. La fórmula se recalcula. Si el nuevo rango de derrame no tiene celdas ocultas, el error desaparece. - Eliminar la fórmula original
Selecciona la celda original y presiona Supr para eliminar la fórmula que aún muestra el error.
Método 3: Usar el operador @ para devolver un solo valor
Si solo necesitas un resultado de la fórmula de matriz, el operador de intersección implícita (@) obliga a la fórmula a devolver un solo valor en lugar de derramarse. Este método cambia el comportamiento de la fórmula, así que úsalo solo cuando no necesites la matriz completa de resultados.
- Editar la fórmula
Haz clic en la celda con el error#SPILL!y presiona F2 para entrar en modo de edición. - Agregar el operador @
Coloca el cursor inmediatamente después del signo igual y escribe@. Por ejemplo, cambia=SORT(A1:A10)a=@SORT(A1:A10). - Presionar Enter
La fórmula ahora devuelve solo el primer valor de la matriz. El rango de derrame ya no es necesario, por lo que el error desaparece. - Verificar el resultado
Comprueba que el valor devuelto coincida con el resultado único esperado. Si necesitas la matriz completa, no uses este método.
Si el error de derrame persiste después de mostrar las celdas
El rango de derrame aún muestra #SPILL! después de mostrar todas las celdas
Si has mostrado todas las filas, columnas, borrado filtros y expandido grupos pero el error persiste, una de las celdas en el rango de derrame puede contener datos o una celda combinada. Selecciona todo el rango de derrame haciendo clic en la celda con la fórmula y luego presionando Ctrl+Mayús+Flecha derecha y Ctrl+Mayús+Flecha abajo. Busca cualquier celda que no esté vacía. Borra el contenido de esas celdas seleccionándolas y presionando Supr. También verifica si hay celdas combinadas en el rango de derrame. Selecciona el rango de derrame, ve a la pestaña Inicio, haz clic en Combinar y centrar y elige Separar celdas.
El error regresa después de volver a aplicar un filtro
Si borras un filtro para corregir el error y luego vuelves a aplicar el filtro, el rango de derrame puede quedar oculto nuevamente. Para evitar esto, mueve la fórmula a una fila fuera del rango filtrado. Por ejemplo, si tus datos están en las filas 1 a 100 y filtras por una columna, coloca la fórmula de matriz dinámica en la fila 102 o inferior. El rango de derrame ocupará filas que no se ven afectadas por el filtro.
El error ocurre solo en una versión específica de Excel
Las matrices dinámicas están disponibles en Excel para Microsoft 365, Excel 2021 y Excel 2024. Las versiones anteriores como Excel 2019 o anteriores no admiten matrices dinámicas. Si abres un libro que contiene fórmulas de matriz dinámica en una versión anterior, Excel puede convertirlas en matrices estáticas o mostrar errores. Para verificar tu versión, ve a Archivo > Cuenta > Acerca de Excel. Si estás usando una versión no compatible, actualiza a Microsoft 365 o Excel 2024 para usar matrices dinámicas sin problemas.
Mostrar vs Mover fórmula vs Operador @: Diferencias clave
| Elemento | Mostrar celdas | Mover fórmula | Operador @ |
|---|---|---|---|
| Efecto sobre los datos | Revela filas o columnas ocultas | Mantiene las filas o columnas ocultas sin cambios | Devuelve un solo valor en lugar de una matriz |
| Rango de derrame requerido | Sí, la matriz completa se derrama | Sí, pero en una nueva ubicación | No, no se necesita rango de derrame |
| Mejor para | Puedes mostrar las celdas | Debes mantener algunos datos ocultos | Solo necesitas un resultado de la fórmula |
| Cambio en la fórmula | Ninguno | Ninguno, solo cambia la ubicación | Agrega @ antes del nombre de la función |
Ahora puedes resolver los errores #SPILL! causados por celdas ocultas en tus fórmulas de matriz dinámica. Comienza verificando si el rango de derrame contiene filas, columnas o datos filtrados ocultos. Si mostrar no es una opción, mueve la fórmula a un área despejada. Para salidas de un solo valor, usa el operador @. Para prevenir errores futuros, coloca las fórmulas de matriz dinámica en áreas de la hoja de cálculo que nunca estén ocultas o filtradas.