Para modelos financieros con múltiples pestañas, esto no es solo una mejora de ergonomía. Cambia lo que es posible construir sin columnas auxiliares, sin Ctrl+Shift+Enter y sin necesidad de saber de antemano cuántas filas va a necesitar tu resultado.
Según el anuncio oficial del equipo de Excel en Microsoft Tech Community, "Announcing Dynamic Array Formulas in Excel" (22 de septiembre de 2018), las matrices dinámicas llegaron primero como versión preliminar para suscriptores de Excel 365, con disponibilidad general en 2020. Excel 2021 también las incluye. A junio de 2026, son estándar en cualquier instalación moderna de Excel, pero las versiones anteriores a 2021 no las soportan: un detalle que importa más de lo que la mayoría de las guías reconoce.
Cómo se comportan realmente los spill ranges
Escribe =UNIQUE('GL'!C:C) en una celda. Excel evalúa la fórmula, cuenta los valores distintos y escribe todos los resultados hacia abajo a partir de esa celda. La celda superior muestra la fórmula. Cada celda inferior muestra un valor en gris, parte del mismo arreglo y de solo lectura.
El spill range es dinámico. Agrega 3 nuevos centros de costo a tu pestaña de GL y el resultado de UNIQUE se expande automáticamente en el siguiente cálculo. Elimina 2 y se contrae. No hay rangos con nombre que mantener ni conteos de filas codificados de forma rígida.
Según el artículo de soporte de Microsoft "Comportamiento de las fórmulas de matriz dinámica y de la matriz desbordada": "Cuando una fórmula puede devolver varios valores a celdas vecinas, se denomina desbordamiento (spilling). Las fórmulas que pueden devolver varios resultados se denominan fórmulas de matriz dinámica."
La implicación para los modelos es significativa. Una pestaña independiente que extrae una lista de centros de costo, líneas de producto o entidades desde una pestaña de origen se mantiene sincronizada sin ningún mantenimiento manual.
El operador #: lo que la mayoría de las guías omite
El operador # (operador de spill range) referencia la salida completa actual de una fórmula de matriz dinámica, incluso a medida que cambia de tamaño.
Supongamos que los nombres de los centros de costo se están extrayendo desde Resumen!B2 mediante =UNIQUE('GL'!C2:C). Hoy son 14 centros de costo. Después de la reestructura organizacional del próximo trimestre, podrían ser 17. Referencia la lista completa desde cualquier lugar con Resumen!B2#: el # le indica a Excel que use el spill range completo, no solo la celda B2.
Un SUMIFS que mapea los valores reales contra esa lista dinámica:
=SUMIFS(
'GL'!E:E,
'GL'!C:C, Resumen!B2#,
'GL'!A:A, ">=" & Supuestos!$B$3,
'GL'!A:A, "<=" & Supuestos!$B$4
)
Esto devuelve un arreglo de valores reales por centro de costo: 14 valores hoy, 17 después de la reestructura, con tamaño ajustado automáticamente para coincidir con lo que UNIQUE extrajo. No hay un rango de criterios fijo, ni necesidad de actualizar la fórmula cuando cambia la lista de centros de costo.
Es una diferencia real respecto al patrón anterior de SUMIFS con un $B$2:$B$15 codificado que queda desactualizado en el momento en que alguien agrega una línea.
Las 6 funciones de matriz dinámica (y qué reemplazan)
| Función | Qué hace | Qué reemplaza |
|---|---|---|
| FILTER | Devuelve filas/columnas que cumplen condiciones | Advanced Filter, arreglos manuales con IFERROR(INDEX(MATCH)) |
| SORT | Ordena un rango por una o más columnas | Columnas auxiliares de ordenamiento, workarounds con tablas dinámicas |
| SORTBY | Ordena por un arreglo auxiliar separado | Columnas auxiliares basadas en rangos |
| UNIQUE | Lista deduplicada a partir de un rango | Eliminar duplicados + actualización manual |
| SEQUENCE | Genera una secuencia numérica | Trucos con ROW(INDIRECT("1:"&n)) |
| RANDARRAY | Arreglo de números aleatorios | Celdas con RAND() dispersas |
XLOOKUP también devuelve arreglos cuando el valor de búsqueda es un arreglo, lo que lo convierte en un reemplazo más limpio de INDEX/MATCH anidados con múltiples criterios.
Casos de uso en FP&A que vale la pena construir
Reportes de variación dinámicos
FILTER es la función más útil aquí. Extrae cada línea del P&L donde la variación supera tu umbral de materialidad:
=FILTER(
CHOOSE({1,2,3,4}, 'P&L'!B:B, 'P&L'!C:C, 'P&L'!D:D, 'P&L'!E:E),
ABS('P&L'!E:E) >= Supuestos!$C$2
)
Donde la columna E es la variación presupuesto vs. real y Supuestos!$C$2 es tu umbral (por ejemplo, $75,000). El resultado es una tabla en la pestaña de análisis: tantas filas como líneas que superen el umbral, con las columnas de descripción, presupuesto, real y variación ya incluidas. Cuando llegan los datos reales del mes, la tabla se actualiza sola sin copiar ni pegar nada. Con un margen bruto del 38.5% sobre $4,200,000 de ingresos, las líneas que se mueven más de $75,000 son las que generan preguntas en el directorio: esta fórmula las detecta automáticamente y las coloca en una tabla lista para presentar.
Encabezados de proyección sin valores fijos
SEQUENCE reemplaza el proceso tedioso de escribir manualmente las etiquetas de cada mes o mantener una fila de encabezados a mano:
=TEXT(SEQUENCE(1, 12, DATE(Supuestos!$B$1, 1, 1), 30), "mmm-aa")
12 encabezados a partir del año definido en tus supuestos. Cambia B1 y las 12 etiquetas se actualizan. Para un informe trimestral del directorio que se reutiliza cada trimestre, esto solo ahorra 5 minutos de limpieza por ciclo.
Para los años de proyección en un LBO o DCF:
=SUMIFS(
'Ingresos'!C:C,
'Ingresos'!A:A, SEQUENCE(1, 5, Supuestos!$B$5, 1),
'Ingresos'!B:B, "Recurrente"
)
Donde B5 es el primer año de proyección y SEQUENCE genera el arreglo {2025, 2026, 2027, 2028, 2029}. Devuelve 5 totales de ingresos con una sola fórmula. Con un múltiplo de entrada de 14.2x EBITDA, que el valor terminal del año 5 sea correcto depende de que esas proyecciones sean precisas y de que la fórmula esté tomando los años correctos.
Listas dinámicas de entidades o centros de costo
UNIQUE + FILTER juntos reemplazan el paso manual de mantener una lista maestra actualizada:
=SORT(UNIQUE(FILTER('GL'!C:C, 'GL'!B:B = Dashboard!$B$1)))
Centros de costo únicos para la entidad seleccionada, ordenados alfabéticamente. Referencia el resultado más adelante con C2# en lugar de un rango fijo. La lista se mantiene actualizada a medida que crece el GL.
Para un DCF de sindicato bancario donde estás consolidando datos desde una exportación compartida del GL, esto elimina toda una categoría de errores del tipo "la lista no coincide con la fuente".
Compatibilidad: el problema que nadie advierte
Las matrices dinámicas no funcionan en Excel 2019 ni en versiones anteriores. Cuando Excel 365 abre un archivo creado antes de las matrices dinámicas, agrega @ antes de las fórmulas que anteriormente usaban intersección implícita, así que verás =@VLOOKUP(...) en modelos antiguos. Eso es Excel preservando el comportamiento compatible con versiones anteriores, no un error.
La dirección problemática es la contraria. Un modelo que construyas con FILTER o UNIQUE devuelve #NAME? en Excel 2019. Sin degradación gradual, sin advertencia: simplemente fórmulas rotas.
Si tu modelo va a llegar a un sindicato bancario, a un LP o al equipo de due diligence de un comprador, pruébalo primero en la versión que el receptor usa. Un workaround razonable para celdas críticas:
=IFERROR(FILTER('P&L'!B:D, 'P&L'!D:D<>0), "Matrices dinámicas no compatibles")
Eso al menos falla de forma controlada. Para modelos que genuinamente necesiten correr en Excel más antiguo, usa fórmulas de arreglo tradicionales ingresadas con Ctrl+Shift+Enter o SUMIFS con rangos fijos.
¿Y Google Sheets? FILTER, UNIQUE, SORT, SORTBY y SEQUENCE también están disponibles en Google Sheets, y el comportamiento de spill es nativo: cualquier fórmula que devuelva múltiples valores se expande automáticamente sin configuración adicional. Las diferencias de sintaxis son menores pero existen. Por ejemplo, UNIQUE en Sheets acepta parámetros adicionales para controlar si la deduplicación es por filas o por columnas. Si tu flujo de trabajo combina ambas plataformas, los conceptos de este artículo aplican en Sheets con ajustes mínimos, pero conviene verificar la documentación específica de cada función antes de migrar las fórmulas directamente.
Los spill ranges no funcionan dentro de Tablas
Una restricción específica que importa para analistas que construyen tablas de entrada con Ctrl+T: no puedes usar la mayoría de las funciones de matriz dinámica cuando su rango de salida intersecta el límite de una Tabla de Excel. FILTER, UNIQUE y SEQUENCE devuelven #SPILL! en este escenario.
La separación práctica: usa Tablas para datos de entrada (la sintaxis de referencia estructurada como Tabla1[Ingresos] vale la pena conservarla), pero coloca las fórmulas de matriz dinámica en celdas regulares fuera de la Tabla o adyacentes a ella. Una zona de salida separada que no esté formateada como Tabla evita el problema por completo.
Este es el tipo de cosa que te roba 20 minutos la primera vez que lo encuentras, porque la fórmula parece correcta y el mensaje de error no ayuda en nada.
Cuándo las cadenas de matrices dinámicas se vuelven un problema de auditoría
Construir un modelo donde los spill ranges alimentan a otros spill ranges crea una arquitectura poderosa pero más difícil de auditar. Considera este flujo típico en un consolidado multi-entidad: UNIQUE extrae los centros de costo en una pestaña de catálogos, esa lista alimenta un SUMIFS con el operador # en una pestaña de consolidación, y el resultado de ese SUMIFS alimenta a su vez un FILTER en una pestaña de varianza. Tres capas de dependencias dinámicas entre pestañas.
El problema no es que falle, sino que cuando algo falla en la pestaña de varianza, rastrear la causa requiere desandar cada capa. ¿El FILTER devuelve cero filas porque no hay varianzas materiales, o porque el SUMIFS está tomando el rango de fechas incorrecto, o porque UNIQUE no está extrayendo los centros de costo de la entidad correcta? Cada capa agrega un paso de verificación que en modelos tradicionales no existía.
Algunas prácticas que reducen el riesgo sin sacrificar la potencia del diseño:
- Documenta el punto de entrada de cada cadena. Un comentario en la celda raíz del spill range, o una columna de metadatos en la pestaña de catálogos, es suficiente para orientarte seis meses después.
- Separa las capas por pestaña con nombres descriptivos. Catálogos en una pestaña, consolidación en otra, análisis en una tercera. Cuando algo falla, el scope de búsqueda se reduce a una sola pestaña.
- Usa rangos nombrados para los puntos de referencia críticos.
CentroCostos_Listaes más rastreable en una auditoría queCatálogos!B2#enterrado en la quinta fórmula de una cadena. - Prueba con datos mínimos antes de conectar las capas. Un GL de 10 filas con casos borde, entidades sin transacciones y centros de costo recién creados, detecta errores de lógica antes de que el modelo tenga 40 pestañas.
Para un LBO con tres entidades operativas y proyecciones a 5 años, estas cadenas pueden ser la diferencia entre un modelo que se mantiene solo entre cierres y uno que requiere intervención manual cada vez que alguien toca el GL.
Una nota sobre XLOOKUP como función de matriz dinámica
XLOOKUP devuelve un arreglo cuando el valor de búsqueda es un arreglo. Pásale un rango como valor de búsqueda y devuelve un rango como resultado:
=XLOOKUP(
'Análisis de Retornos'!B3:B10,
'Supuestos'!$C$2:$C$50,
'Supuestos'!$D$2:$D$50,
"N/A"
)
Esto reemplaza 8 fórmulas individuales de VLOOKUP o INDEX/MATCH con una sola. El spill range se adapta automáticamente cuando las filas 3:10 cambian a 3:15.
XLOOKUP está disponible en Excel 365, Excel 2021 y Excel para la web. No está disponible en Excel 2019 ni en versiones anteriores, lo que lo pone en el mismo grupo de restricciones de compatibilidad que el resto de las funciones de matriz dinámica.
Preguntas frecuentes sobre fórmulas de matriz dinámica en Excel
¿Qué es un spilled array en Excel?
Un spilled array (matriz desbordada) es el resultado de una fórmula de matriz dinámica que ocupa más de una celda. Excel escribe los valores automáticamente en las celdas adyacentes a partir de la celda donde ingresaste la fórmula. Esas celdas secundarias son de solo lectura: no puedes editarlas directamente. El conjunto completo de celdas que ocupa el resultado se llama spill range. Si el resultado cambia de tamaño porque cambió el origen de datos, el spill range se expande o contrae automáticamente en el siguiente cálculo sin que tengas que modificar la fórmula.
¿Qué significa el operador # en Excel?
El operador # (operador de spill range) le indica a Excel que tome toda la salida actual de una fórmula de matriz dinámica, no solo la celda donde está ingresada. Si tienes =UNIQUE(...) en la celda B2 y el resultado ocupa B2:B15, la referencia B2# devuelve las 14 celdas del spill range completo. Esto es especialmente útil cuando usas el resultado como criterio en SUMIFS o COUNTIFS: si el tamaño del spill range cambia, la referencia con # se adapta automáticamente sin que tengas que actualizar nada.
¿Las fórmulas de matriz dinámica funcionan en Excel 2019?
No. FILTER, UNIQUE, SORT, SORTBY, SEQUENCE y RANDARRAY no están disponibles en Excel 2019 ni en versiones anteriores. Un archivo creado con estas funciones en Excel 365 devuelve #NAME? cuando lo abre alguien en Excel 2019, sin ninguna advertencia previa. Si tu modelo va a circular entre personas que no controlan su versión de Excel, como puede ocurrir en procesos de due diligence o al compartir reportes con bancos o fondos, es importante verificar la compatibilidad antes de entregar el archivo.
¿Por qué aparece el error #SPILL! en mis fórmulas?
El error #SPILL! aparece cuando Excel no puede escribir el resultado de una fórmula de matriz dinámica porque las celdas destino están bloqueadas. Las causas más comunes son: que haya datos en las celdas donde Excel necesita escribir el resultado, que la fórmula esté dentro de una Tabla de Excel (Ctrl+T) cuyo límite intersecta el rango de salida, o que la fórmula esté en una celda combinada. Para resolverlo, despeja las celdas del rango de salida, mueve la fórmula fuera de la Tabla, o desactiva la combinación de celdas en el área afectada.
Si trabajas principalmente en Google Sheets y estas cadenas de dependencias entre pestañas se vuelven difíciles de rastrear, ModelMonkey puede trazar las dependencias de fórmulas entre hojas e identificar qué está roto y por qué, sin que tengas que desandar cada capa manualmente. Elige un plan de ModelMonkey.