Cómo funciona.
ARRAYFORMULA es una función fundamental de Excel. Enables array calculations across entire columns in Google Sheets. Permite automatizar hojas de cálculo, simplificar fórmulas complejas y analizar datos con total precisión en Microsoft Excel y Google Sheets.
Sintaxis.
Notación Estándar de Excel=ARRAYFORMULA(array_formula)Argumentos.
A continuación se detallan todos los parámetros aceptados por esta función.
A range or mathematical expression using ranges.
Ejemplo:A2:A5 * B2:B5Ejemplo práctico.
Caso de Uso Paso a PasoAutomated Line Totals
Calculate Total = Units * Price across multiple rows at once.
| Units | Unit Price | Line Total |
|---|---|---|
| 10 | 20 | — |
| 5 | 50 | — |
| 8 | 15 | — |
=ARRAYFORMULA(A2:A4 * B2:B4)Evaluates ARRAYFORMULA over the sample table cells, placing the verified output in cell C2.
Generador de fórmula.
Abrir Asistente CompletoPersonaliza los campos de argumento a continuación para generar una fórmula lista para copiar y pegar en tu hoja de cálculo.
=ARRAYFORMULA()Comportamiento del resultado.
Returns number 200 based on sample evaluation criteria and active cell references.
Buenas Prácticas
- Combine with IF(A2:A="", "", ...) to automatically calculate incoming Google Form rows.
Errores Frecuentes
- Specific to Google Sheets. Excel 365 natively spills calculations without requiring an ARRAYFORMULA tag.
Compatibilidad de plataforma y versiones.
Matriz verificada con base en los motores de cálculo oficiales de Microsoft Excel y Google Sheets.
In modern Microsoft Excel (365 & 2021), simply enter your formula normally — dynamic arrays spill automatically across the grid.
Not supported in Microsoft Excel. In Excel 365 and 2021, array calculations spill dynamically without requiring an ARRAYFORMULA wrapper. Legacy Excel uses Ctrl+Shift+Enter.
Native Google Sheets wrapper function that enables non-array formulas to output array results across multiple rows/columns.
Preguntas frecuentes.
Dudas TécnicasCan I use ARRAYFORMULA across different worksheets in Excel?↓
Yes. You can reference cells located on other sheets by including the sheet name followed by an exclamation mark, for example: =ARRAYFORMULA(Sheet2!A2:A10). If the sheet name contains spaces, enclose it in single quotes: ='Q1 Report'!A2:A10.
What is the most common reason for errors in ARRAYFORMULA?↓
Specific to Google Sheets. Excel 365 natively spills calculations without requiring an ARRAYFORMULA tag.
Does ARRAYFORMULA work in Microsoft Excel?↓
No. ARRAYFORMULA is a proprietary Google Sheets function and is not supported in Microsoft Excel. Recommended Excel alternative: In modern Microsoft Excel (365 & 2021), simply enter your formula normally — dynamic arrays spill automatically across the grid.