Dynamic arrays in Excel: FILTER, SORT and UNIQUE explained

For years, pulling only one region’s sales out of a table, sorting them or getting a list without repeats meant manual filters, PivotTables or very long formulas copied row by row. With dynamic arrays, a single formula typed in one cell returns a whole range of results that updates when the data changes.

Here you will see how FILTER, SORT and UNIQUE work, how to combine them and what to do when a spill error appears. Every example uses the same sample table, so you can reproduce them in a couple of minutes.

What dynamic arrays are and which Excel version you need

A dynamic array is the result of a formula that takes up several cells. You type the formula in the first cell and Excel “spills” the results down and to the right, across as many rows and columns as needed. If the source data changes, the result range adjusts automatically.

These functions are available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. They do not exist in Excel 2019 or earlier: if you open a file that uses them there, you will see the #NAME? error or fixed values that no longer recalculate.

Before you start, keep three rules in mind:

• You only type the formula in the first cell. The rest of the result is outlined in blue when you select it.
• You cannot edit or delete a single cell inside the spilled range. To change something, edit the source formula.
• To refer to the whole result from another formula, add the # operator after the first cell, for example F2#.

The sample table

Copy this data into a new sheet, starting at cell A1. The formulas in the following sections work on the range A2:D9.

FILTER: extract only the rows that meet a condition

The syntax is FILTER(array, include, [if_empty]). The first argument is the range you want to return, the second is a condition evaluated row by row, and the optional third one sets what to show if no row meets the condition.

To get all sales from the North region:

=FILTER(A2:D9,C2:C9="North","No results")

The result is four complete rows (Customer A, Customer C, Customer D and Customer C again) with all four columns.

Several conditions at once

You cannot use AND or OR inside the include argument, because those functions return a single value rather than one per row. Instead, write each condition in brackets and combine them with arithmetic:

• For all conditions to be met (AND logic), multiply: (C2:C9="North")*(D2:D9>500).
• For at least one to be met (OR logic), add: (C2:C9="North")+(C2:C9="South").

North region sales above 500:

=FILTER(A2:D9,(C2:C9="North")*(D2:D9>500),"No results")

It returns two rows: Customer D with 1250 and Customer C with 870.

SORT: sort results without touching the original data

The syntax is SORT(array, [sort_index], [sort_order], [by_col]). The index tells Excel which column of the array to sort by (1 is the first), and the order is 1 for ascending, the default, or -1 for descending.

=SORT(A2:D9,4,-1)

It returns the whole table sorted by amount from highest to lowest: first Customer D (1250), then Customer A (910), Customer B (890) and so on down to Customer C with 45. The original table keeps its order.

Sorting the result of a filter

Since FILTER returns an array, you can pass it straight to SORT. This gives you the North sales from highest to lowest amount:

=SORT(FILTER(A2:D9,C2:C9="North"),4,-1)

UNIQUE: lists without repeats in a single formula

The syntax is UNIQUE(array, [by_col], [exactly_once]). In its simplest form it returns each distinct value once, in the order it first appears:

=UNIQUE(B2:B9)

The result is Keyboard, Monitor, Mouse and Laptop.

Values that appear exactly once

The third argument changes the behaviour: with TRUE it only returns values that are never repeated. Notice the double comma, which leaves the second argument empty:

=UNIQUE(A2:A9,,TRUE)

It returns Customer D and Customer E, that is, the customers with a single purchase.

Counting distinct values

If you wrap UNIQUE in COUNTA, you get how many different items there are. This formula returns 3, because there are three different regions:

=COUNTA(UNIQUE(C2:C9))

Combining the three functions: a summary by region

The real power shows when you nest them. For example, to see which products were sold in the South region, without repeats and in alphabetical order:

=SORT(UNIQUE(FILTER(B2:B9,C2:C9="South")))

The result is Keyboard and Monitor. With the same logic you can build a small sales summary by region that grows on its own:

1. In F1 type the heading Region and in G1 the heading Total.
2. In F2 type =SORT(UNIQUE(C2:C9)). East, North and South will appear.
3. In G2 type =SUMIF(C2:C9,F2#,D2:D9). The # operator makes the sum calculate for every region in the spilled list.
4. Check the totals: East 970, North 2485 and South 1200.

If a new region appears in the data, the list in F2 grows and column G follows it without copying any formula.

Common errors and how to fix them

• #SPILL!: something is in the range where the results should go (a value, a formula or even a space), or there are merged cells. When you select the formula, a dashed border marks the area it needs. Clear it or move the formula.
• #CALC!: FILTER found no rows and you did not set the third argument. Add a text such as “No results” or an empty pair of quotes.
• #VALUE!: the array and the condition have a different number of rows, for example A2:D9 versus C2:C10.
• Formula inside an Excel table: spilling formulas do not work inside a table created with Ctrl+T. Place them outside, although they can still read the table’s data.

When you need to clean the file, not just view it

UNIQUE is perfect for looking at distinct values, but it does not remove repeated rows from the original file: the source table stays the same. If you need to hand over a clean file, or you use an Excel version without dynamic arrays, you can use our free tool to remove duplicates: upload the xlsx, xls, csv or ods file and download a copy without repeated rows. And if you want to check which rows were removed, you can compare the original file with the clean one.

Conclusion

FILTER, SORT and UNIQUE replace many tasks that used to require manual filters or formulas copied row by row. Type one formula, leave free space for the result and use the # operator to chain calculations. Start with the examples in this guide on your own data and, within a few days, you will be building reports that update themselves.

Frequently asked questions