keepcalculator

06 Numbers and Powers

Dynamic Array Formulas: How They Work

Card No. 06·8242 · Updated · Standard formula, shown below

Dynamic array formulas let a single formula in Excel or Google Sheets return multiple values that spill into neighboring cells automatically. You enter one formula in one cell, and the results fill as many cells as needed, updating when the source data changes. This replaces the older Ctrl+Shift+Enter (CSE) array formulas and manual dragging of formulas across columns or rows.

Background: From CSE to Spill

Before dynamic arrays, array formulas were powerful but awkward. You had to select the entire output range, type the formula, and press Ctrl+Shift+Enter to tell Excel it was an array formula. If you needed more rows later, you had to extend the selection and re-enter. Google Sheets had a similar limitation until it introduced dynamic arrays with functions like FILTER and SEQUENCE.

In 2018, Microsoft announced dynamic arrays for Excel 365. The key change: formulas now return a range of values that spills from the formula cell into adjacent cells. This spill behavior is automatic and resizes as the result changes. Excel 2021 and later versions include dynamic arrays, as does Excel for the web. Google Sheets has supported dynamic arrays since 2018 with functions like FILTER, UNIQUE, SORT, and SEQUENCE.

How Dynamic Arrays Work

When you enter a formula that returns multiple values, Excel or Sheets calculates the entire result and places it in a contiguous block. The cell with the formula is the anchor; the filled range is the spill range. The anchor shows the formula, and the spill cells show values but are not editable individually.

Example: =A1:A5*2 in cell C1 multiplies each value in A1:A5 by 2 and spills the five results into C1:C5. If you change a value in A1:A5, the spill updates. If you try to type something in a spill cell, you get a #SPILL! error because the range is blocked.

Key functions that work with dynamic arrays include:

  • SEQUENCE – generates a list of numbers
  • UNIQUE – returns distinct values from a range
  • SORT and SORTBY – sort a range dynamically
  • FILTER – returns matching rows based on conditions
  • RANDARRAY – generates random numbers
  • XLOOKUP – can return multiple values

Using the # Spill Operator

To reference the entire spill range from another formula, use the # operator after the anchor cell. For example, if =UNIQUE(A1:A100) is in C1, then =COUNTA(C1#) counts the number of unique values. This is useful for dynamic ranges that change size.

You can also use C1# in functions like SUM, AVERAGE, or in charts. The spill range is treated as a single array, so you don’t need to know its size in advance.

Common Problems and Errors

The most frequent error is #SPILL!. This happens when something blocks the spill range. Common causes:

  • Merge cells in the spill area
  • Data already present in the spill cells
  • A table (structured reference) that doesn’t allow spills
  • Formulas in the spill area that aren’t empty

To fix, clear the blocking cells or move the formula. Another error is #CALC!, which appears when a function like FILTER returns no results. You can handle that with the IFERROR function or the IFNA function in some cases.

Also note: dynamic arrays are not supported in older Excel versions (2016 and earlier). If you share a file with someone using an older version, they’ll see the formula but not the spill results—they’ll need to press Ctrl+Shift+Enter manually.

Dynamic Arrays vs. Traditional Array Formulas

Traditional CSE array formulas require you to pre-select the output range and press Ctrl+Shift+Enter. They are static in size—if you need more rows, you must edit the formula and re-enter. Dynamic arrays are self-sizing and recalculate automatically.

Google Sheets has similar behavior but uses ARRAYFORMULA for some legacy compatibility. However, modern Sheets functions like FILTER and UNIQUE spill natively without needing ARRAYFORMULA.

When to use which? If you’re on Excel 365 or 2021, use dynamic arrays. If you’re on an older version, you’ll need CSE. In Google Sheets, prefer the native dynamic functions.

Performance and Limits

Dynamic arrays can be heavy on calculation. Each spill range recalculates whenever any of its inputs change. For large datasets, this can slow down your workbook. To improve performance, limit the use of volatile functions like RANDARRAY and OFFSET, and avoid whole-column references (e.g., A:A) in dynamic formulas.

Excel has a limit on spill range size: it can’t exceed the worksheet dimensions (1,048,576 rows by 16,384 columns). In practice, huge spills are rare and slow. Google Sheets has a similar cell limit.

Also, dynamic arrays don’t work inside Excel tables (ListObjects). If you try to use a dynamic formula in a table, you’ll get an error. You can convert the table to a range, or use a regular range instead.

Frequently Asked Questions

How do I turn off dynamic arrays?

You can’t turn off dynamic arrays in Excel 365 or 2021—they’re always enabled. In Google Sheets, you can use ARRAYFORMULA to force a non-spilling behavior in some cases, but it’s not recommended. If you need compatibility with older Excel, you can use legacy CSE formulas by pressing Ctrl+Shift+Enter, but they won’t spill.

Why is my formula showing #SPILL!?

#SPILL! means the spill range is blocked. Check for merged cells, existing data, or a table in the spill area. Also, make sure the formula isn’t referencing the spill range itself (circular reference). Clear the blocking cells and the error should resolve.

Can I use dynamic arrays in Excel 2019?

No. Dynamic arrays are only available in Excel 365, Excel 2021, and Excel for the web. Excel 2019 and earlier require CSE array formulas. You can still use some functions like UNIQUE and FILTER if you install the Excel 365 version, but not in 2019.

How do I reference a spill range in another formula?

Use the # operator after the anchor cell. For example, if =SORT(A1:A10) is in C1, then =SUM(C1#) sums all spilled values. This works in both Excel and Google Sheets.

Can I use dynamic arrays in Google Sheets?

Yes. Google Sheets supports dynamic arrays with functions like FILTER, UNIQUE, SORT, and SEQUENCE. They spill automatically. You don’t need ARRAYFORMULA for these, though it still works for older formulas.

Why is my dynamic array not updating?

Check if calculation is set to manual. In Excel, go to Formulas > Calculation Options > Automatic. In Google Sheets, go to File > Settings > Calculation and choose ‘On change’. Also, ensure the source data isn’t a table that’s blocking spills.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →