Dynamic Arrays: FILTER, SORT, UNIQUE & SEQUENCE

For most of Excel's history, one formula produced one value in one cell. Dynamic arrays changed that. A single formula can now return a whole list or table, and Excel spills the results into the cells below and beside it. Filtering, sorting and de-duplicating data, which used to need menus or long formulas, now take one short formula that updates by itself.

In this lesson you will learn how spilling works, and use FILTER, SORT, SORTBY, UNIQUE and SEQUENCE to build live reports. These functions need Microsoft 365, Excel 2021 or later, or Excel for the web; Google Sheets has them too.

The sample sheet

This is the October sales register of the mobile accessories shop in Lucknow from COUNTIF, SUMIF & SUMIFS:

How spilling works

Type a dynamic array formula in one cell and press Enter. Excel fills as many cells as the result needs and draws a thin blue border around them: that area is the spill range. Only the first cell holds the formula; the others show greyed-out formulas in the formula bar and cannot be edited on their own.

If something is already in the way, the formula shows #SPILL! instead. Clear the blocking cells and the results appear.

To refer to the whole spill range from another formula, write the first cell followed by #. If a list spills from G2, then G2# means "all of it, however long it is today".

FILTER: rows that meet a condition

=FILTER(array, include, [if_empty])

Show all of Isha's sales. Type this in G2:

=FILTER(A2:E11, B2:B11="Isha")

The dates in column G may show as serial numbers until you apply a date format. Add a sale for Isha in row 12 of the data (and widen the ranges to row 12) and the result grows to four rows.

For AND conditions, multiply the tests. Each test gives TRUE or FALSE, which act as 1 and 0, so a row is kept only when every test is 1:

=FILTER(A2:E11, (C2:C11="Hazratganj") * (E2:E11>1000))

This returns three rows: Priya's two chargers and Farhan's earbuds. For OR conditions, add the tests: (D2:D11="Cover") + (D2:D11="Power bank") keeps four rows.

When nothing matches, FILTER returns #CALC!. The third argument gives a friendlier result: =FILTER(A2:E11, B2:B11="Kabir", "No sales").

SORT and SORTBY

=SORT(array, [sort_index], [sort_order], [by_col])

sort_index is the column number inside the array, and sort_order is 1 for ascending (the default) or -1 for descending. =SORT(A2:E11, 5, -1) lists all sales from the largest amount down.

Three sales tie at ₹2,499. SORTBY sorts by one or more ranges, so you can add a tie-breaker. Largest amount first, then earliest date:

=SORTBY(A2:E11, E2:E11, -1, A2:A11, 1)

The sort ranges do not have to appear in the result, which makes SORTBY the more flexible of the two.

UNIQUE: a list without duplicates

=UNIQUE(C2:C11)

Hazratganj
Gomti Nagar
Aliganj

The values come out in the order they first appear. Wrap it in SORT for alphabetical order: =SORT(UNIQUE(C2:C11)) gives Aliganj, Gomti Nagar, Hazratganj. A third argument of TRUE returns only values that appear exactly once.

A summary report that builds itself

Combine UNIQUE with the spill reference #. Type the first formula in G2 and the second in H2:

=SORT(UNIQUE(B2:B11))
=SUMIF(B2:B11, G2#, E2:E11)

G2# hands all three names to SUMIF, which returns three totals, so column H spills too. When a new salesperson appears in the data, both columns grow by one row with no copying.

SEQUENCE: generate numbers

=SEQUENCE(rows, [columns], [start], [step])

=SEQUENCE(10) lists 1 to 10 down a column, ready for serial numbers. =SEQUENCE(1, 12) lists 1 to 12 across a row. Because dates are numbers, SEQUENCE can also build a week of dates starting Monday 2 November 2026:

=SEQUENCE(7, 1, DATE(2026,11,2))

46328
46329
46330
46331
46332
46333
46334

Apply a date format and they show as 02-Nov-2026 to 08-Nov-2026. For serial numbers that always match the data, use =SEQUENCE(COUNTA(B2:B11)).

Advanced: TAKE for a top-N list

In Microsoft 365 and Excel 2024, TAKE keeps the first (or, with a negative number, the last) rows of an array. Combined with SORTBY, it gives a live top-3 list:

=TAKE(SORTBY(A2:E11, E2:E11, -1, A2:A11, 1), 3)

Related functions in the same versions include DROP, CHOOSECOLS, VSTACK and HSTACK, which remove rows, pick columns and stack arrays.

Common mistakes

• #SPILL! errors. A value, even a space, sits in the spill area. Clear it, and keep empty space below and to the right of spilling formulas.
• Spilling inside an Excel table. Tables do not allow spill ranges. Put dynamic array formulas outside tables.
• Using AND or OR inside FILTER. AND() returns one value for the whole range. Multiply tests for AND and add them for OR.
• Referring to a fixed range instead of #. =SUMIF(B2:B11, G2:G4, ...) misses new names. Use G2#.
• Google Sheets syntax. Its SORT takes TRUE or FALSE for the order, as in =SORT(A2:E11, 5, FALSE), instead of 1 or -1.

Key points

• Dynamic array formulas spill their results into neighbouring cells; #SPILL! means something is in the way.
• G2# refers to the whole spill range that starts in G2.
• FILTER keeps rows that meet conditions: multiply tests for AND, add them for OR.
• SORT sorts by column number; SORTBY sorts by one or more ranges and handles ties.
• UNIQUE removes duplicates and SEQUENCE generates numbers or dates; TAKE keeps the top rows.

=SORT(FILTER(D2:E11, C2:C11="Gomti Nagar"), 2, -1)
=SORT(UNIQUE(D2:D11))
=COUNTIF(D2:D11, G2#)

Earbuds     2499
Power bank  1899
Charger     1299

Charger     3
Cover       2
Earbuds     3
Power bank  2