You need to perform calculations on multiple values at once in Excel. The traditional method requires a special keystroke and can be difficult to edit. Modern Excel introduces a new, simpler way to work with arrays that spill results automatically. This article explains both the legacy and new methods for creating powerful array formulas.
Key Takeaways: Array Formulas in Excel
- Ctrl+Shift+Enter (CSE): This legacy method locks an array formula to a specific range and requires manual confirmation.
- Dynamic Array Functions: New functions like FILTER, SORT, and UNIQUE automatically spill results into adjacent cells.
- Spill Range (#): A dynamic array’s results are referenced using a spill operator, making formulas easier to manage.
Understanding Array Formulas and Their Evolution
An array formula performs multiple calculations on one or more sets of values. It can return either a single result or multiple results. For years, the only way to use them was with the Ctrl+Shift+Enter method, known as CSE or legacy array formulas. These formulas are enclosed in curly braces {} in the formula bar and must be entered into a pre-selected range of cells matching the output size.
Microsoft introduced dynamic arrays as a fundamental update to Excel’s calculation engine. This change allows formulas that return multiple values to spill those results into neighboring cells automatically. A blue border appears around the spilled results, defining the spill range. This eliminates the need to select the output range first or use the CSE keystroke. Dynamic arrays are available in Excel for Microsoft 365 and Excel 2021.
When to Use Each Type of Array
Use legacy CSE array formulas if you are working in an older version of Excel that does not support dynamic arrays, or if you are sharing files with users who have those older versions. The formulas will still calculate correctly. Use the new dynamic array functions for all new work in supported versions. They are simpler, more flexible, and reduce errors from mismatched ranges.
Steps to Create Legacy CSE Array Formulas
This method works in all versions of Excel but is more complex to manage.
- Select the output range
First, select the exact range of cells where you want the results to appear. For example, to multiply two columns of three numbers, select a range of three cells in a column. - Enter the formula
Type your formula into the first cell of the selected range. A common example is{=A2:A4*B2:B4}. Do not type the curly braces yourself. - Press Ctrl+Shift+Enter
Instead of pressing just Enter, hold down Ctrl and Shift, then press Enter. Excel will place curly braces{}around the formula in the formula bar. The result will fill the entire range you selected. - Edit with care
You cannot edit a single cell within a CSE array range. You must select the entire array range, edit the formula in the formula bar, and press Ctrl+Shift+Enter again to confirm.
Steps to Use New Dynamic Array Formulas
This modern approach is available in Excel for Microsoft 365 and Excel 2021.
- Enter the formula in a single cell
Click on one cell where you want the results to begin. Type a dynamic array formula, such as=SORT(A2:A100)or=A2:A4*B2:B4. - Press Enter
Press the Enter key normally. The formula will calculate and the results will spill down or across into adjacent empty cells automatically. - Reference the spill range
To reference the entire set of results from a dynamic array, use the spill operator (#). If your dynamic array is in cell C2, reference all its results withC2#. This reference will grow or shrink with the spilled results. - Manage the spill range
If a cell within the spill range is blocked by existing data, Excel shows a #SPILL! error. Clear the blocking data to resolve it. The entire spill range acts as a single entity; you can only edit or delete the formula in the top-left cell.
Common Mistakes and Limitations to Avoid
Getting a #SPILL! Error
This error means something is blocking the spill range. Check for any data, merged cells, or tables in the cells where Excel is trying to output results. Delete or move the obstructing content. Also, ensure the worksheet is not protected in a way that prevents writing to those cells.
Accidentally Editing Part of an Array
For dynamic arrays, you cannot change a cell inside the spill range. If you try, Excel will display a warning that you are trying to change part of an array. You must edit the source formula in the top-left cell of the spill. For legacy CSE arrays, you must select the entire array range before editing.
Sharing Files With Users on Older Excel Versions
If a user opens a workbook with dynamic array formulas in an older version of Excel, the formulas will show as #NAME? errors. To maintain compatibility, use the older CSE method or convert dynamic array results to static values before sharing. Use Copy and then Paste Special > Values to paste the results as numbers.
Legacy CSE Arrays vs Dynamic Arrays: Key Differences
| Item | Legacy CSE Array | Dynamic Array |
|---|---|---|
| Entry Method | Ctrl+Shift+Enter | Press Enter |
| Output Range | Must be pre-selected by user | Spills automatically |
| Formula Display | Surrounded by curly braces {} | No special symbols |
| Editing | Must edit entire array range | Edit only the source cell |
| Reference to Results | Reference the entire fixed range | Use the spill operator (#) |
| New Functions | Cannot use SORT, FILTER, UNIQUE, etc | Designed for these functions |
You can now use both legacy and modern array formulas in Excel. For new projects, adopt dynamic array functions like SORT and FILTER to save time. Try using the spill operator # to reference an entire dynamic result set in another formula. Remember that pressing just Enter is all that’s needed to confirm a modern array formula.