Dynamic Arrays and Modern Formulas

Introduction

Traditional formulas return one value in one cell. Modern spreadsheet programs have dynamic array functions that return a whole list or table, which spills into the cells below and beside. They replace many older tricks with short, readable formulas.

Check support first. Google Sheets has had these for years. Current versions of Microsoft 365 and Excel for the web support them, and recent LibreOffice versions have added many of them. Older program versions do not, and files may show errors for people using those. If you share a file, make sure everyone's program supports the functions you use.

The data

We use the same sales table (rows 2 to 9 in columns A to F), with Month, Region, Product, Units, Price and Revenue.

UNIQUE: a list without repeats

=UNIQUE(C2:C9)

Returns the distinct products, once each: Pen, Book, Bag. They spill down three cells. If the data changes, the list updates.

SORT: order a range

=SORT(F2:F9, 1, FALSE)

Sorts the revenue values from largest to smallest: 60, 50, 37.5, 30, 25, 20, 16, 10. The inputs are the range, the column to sort by and whether the order is ascending (TRUE) or descending (FALSE). You can sort a whole table by naming a sort column.

FILTER: rows that match a condition

=FILTER(A2:F9, B2:B9="N")

Returns every row where the region is N (four rows: Jan Pen, Feb Book, Mar Pen and Mar Bag). Unlike a filter button, the original data is unchanged, and the result is a live, separate table.

You can add a message for no results:

=FILTER(A2:F9, F2:F9>100, "None found")

No revenue exceeds 100 in our table, so this shows "None found".

The cells a formula spills into must be empty. If they are blocked by other data, you get an error such as #SPILL! in some programs. Clear the blocked cells.

Combine them

You can nest dynamic functions:

=SORT(FILTER(A2:F9, B2:B9="S"), 6, FALSE)

This takes the rows for region S and sorts them by column 6 (Revenue) from highest to lowest, giving the Apr Bag row (60) first, then Feb Book (50), then Mar Book (25) and Jan Pen (10).

XLOOKUP: a modern lookup

=XLOOKUP("Book", H2:H4, I2:I4, "Not found")

Finds "Book" in H2:H4 and returns the matching value from I2:I4, or the message if none. Advantages over VLOOKUP: it can look to the left, defaults to exact match, and needs no column numbers.

LET: name parts of a formula

In programs that support it, LET assigns names inside a formula, which makes long formulas readable and avoids repeating calculations:

=LET(total, SUM(F2:F9), n, COUNT(F2:F9), total / n)

This computes total revenue divided by the number of rows: 248.5 ÷ 8 = 31.0625. Naming total and n explains what each part means.

Other useful functions

• SEQUENCE(n) creates a list of numbers
• TEXTSPLIT or SPLIT splits text across cells (the name depends on the program)
• TRANSPOSE swaps rows and columns
• VSTACK and HSTACK combine ranges (not available everywhere)

Be careful

• Compatibility: test your file in the programs the readers use.
• Readability: a long nested formula is hard to audit. Break it into steps on a calculation sheet, or use LET.
• Spill ranges can collide, so leave room.
• Referencing a whole spill uses a special syntax that differs between programs. Check the help.
• Verify results with a simple check, such as counting rows.

Try it yourself

List the unique months, filter the table for product Book, and sort the result by revenue. Check that the Book rows total 112.5 (37.5 + 50 + 25).

Summary

• Dynamic array functions such as UNIQUE, SORT and FILTER return whole lists that spill into neighbouring cells.
• XLOOKUP and LET make lookups and long formulas simpler, but check that every user's program supports them.
• Keep formulas readable, leave room for spills and verify the results.