> For the complete documentation index, see [llms.txt](https://enable-advisory-1.gitbook.io/the-way-we-work/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://enable-advisory-1.gitbook.io/the-way-we-work/microsoft-excel/dynamic-array-overview.md).

# Dynamic Array Overview

Dynamic Array formulas can create an automatically expandable result that changes in value as well as range, based on the source data.

This behavior is called "spill". The "spill range" is circled in blue lines as per screenshot below.

The "spill" behavior also applies to most **existing Excel formulas**, such as sumif, index, if etc.

![](https://1421118769-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MdtjvomCsvuXz1wFFAA%2Fuploads%2FoN7sdthNzl9UFHU9OtGP%2Fimage.png?alt=media\&token=3af0c221-5d21-4f1f-bd81-6954e4cccc13)![](https://1421118769-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MdtjvomCsvuXz1wFFAA%2Fuploads%2FUSkBqoTJ9Y4mm9s6V4VR%2Fimage.png?alt=media\&token=e379dc86-0744-4e6a-b93c-87469f5d7814)

The formula of a dynamic array can only be edited from the **first cell** of the range.

To **reference the whole range**, enter "**=E5#**" in the formula bar (E5 being the first cell of the dynamic range).

**Common error -** **#SPILL!** means that there's not enough blank cells to load the array.

**New Dynamic Array Formulas:**

[XLOOKUP](/the-way-we-work/microsoft-excel/xlookup.md)

[UNIQUE](/the-way-we-work/microsoft-excel/unique.md)

[SORT](/the-way-we-work/microsoft-excel/sort.md)

[FILTER](/the-way-we-work/microsoft-excel/filter.md)

\*RANDARRAY, SEQUENCE, SORTBY, XMATCH

Go to [Examples Download](/the-way-we-work/microsoft-excel/examples-download.md) to view nested dynamic array formula examples.