📊 Lesson 4.4: Dynamic Arrays — FILTER, SORT & UNIQUE (Excel's Modern Power)

What Dynamic Arrays Are — Spilling

For its whole history, one Excel formula lived in one cell and produced one result. If you wanted a list of answers, you copied the formula down a column, once per row. Dynamic arrays change that completely. A dynamic array formula can return many values at once, and Excel automatically places them into the neighboring cells — a behavior called spilling. You write the formula in a single cell, press Enter, and a whole range of results appears, flowing down (and sometimes across) from where you typed.

Here's the simplest possible demonstration. If A2:A6 contains five numbers and you type, in a single empty cell, =A2:A6*2, older Excel would complain — but modern Excel returns all five doubled values, spilling them down five cells from where you stood. You didn't copy anything down; one formula produced the whole column. The cell where you typed it is the anchor; the cells it fills are the spill range.

Why is this such a big deal? Because it means a single formula can produce a live, self-sizing list. If the source data grows from five rows to fifty, the spill range grows to match — no re-copying, no dragging, no stale ranges. Combined with the functions we meet below (FILTER, SORT, UNIQUE), spilling lets one cell generate an entire summary table that maintains itself. This is the modern heart of Excel, and it's where Excel and Google Sheets both went in the same direction.

The Spill Range, the # Reference & #SPILL!

Three ideas make working with spills comfortable. Learn them once and dynamic arrays feel natural.

The spill range

When a formula spills, all the result cells belong to one formula. Click any spilled cell and you'll see the formula grayed out — you can only edit it in the top-left anchor cell, and a faint blue border outlines the whole spill range. You never type into the spilled cells yourself; they're owned and refreshed by the anchor formula.

The # spilled-range reference

Because a spill range changes size as data grows, you need a way to refer to "however many cells this spill currently covers." That's the spilled-range operator: put a # after the anchor cell. If a UNIQUE formula in E2 spills a list of categories, then E2# means "the entire spilled list, whatever its current length." So =COUNTA(E2#) counts the categories and stays correct as the list grows or shrinks. The # reference is how you chain spills together and build summaries that never need re-pointing.

The #SPILL! error

A spill needs empty space to expand into. If something is already sitting in the cells where the results want to go, Excel can't spill and shows #SPILL! instead. The fix is almost always simple: clear whatever is blocking the spill range (a stray value, a label, merged cells), and the results appear. Excel usually highlights the obstructing cells for you.

FILTER — Rows That Match a Condition

FILTER is the star of the show. Give it a range and a condition and it returns — spilled — every row that meets the condition, and only those rows. It's a live version of the manual filtering you learned in Lesson 3.1, except the results are a real range you can feed into other formulas and charts. Its syntax:

=FILTER(array, include, [if_empty])

• array — the range to return rows from (can be one or many columns)
• include — a condition the same height as the array; rows where it's TRUE are kept
• [if_empty] — what to show when nothing matches (optional but recommended)

Say your data sits in A2:D50 with the category in column B. To spill every row whose category is "Lighting":

=FILTER(A2:D50, B2:B50="Lighting", "No matches")

All matching rows appear below your formula, resizing automatically as the data changes. The condition is a comparison across the whole column — exactly the TRUE/FALSE thinking from Lesson 4.3, now applied to a range. You can combine conditions with arithmetic: multiply two conditions for and ((B2:B50="Lighting")*(D2:D50>10)), add them for or. To pull just one column of the matches, point array at that single column, e.g. =FILTER(A2:A50, B2:B50="Lighting") returns only the codes.

This alone replaces a surprising amount of manual work. Instead of applying a filter, copying the visible rows, and pasting them elsewhere — then redoing it all when the data changes — you write one FILTER and the extract stays live forever.

SORT, SORTBY, UNIQUE & SEQUENCE

FILTER has a family of companion functions, each doing one clean job on a spilled list:

SORT takes the range, an optional column index to sort by, and a direction (1 for ascending, -1 for descending). SORTBY is handier when you want to sort one list by a different list — say, product names ordered by their sales — because you name the ranges directly rather than counting columns. UNIQUE is the one that unlocks summaries: hand it a messy column of repeated categories and it spills a clean list of each distinct value exactly once — the perfect row labels for a summary table. And SEQUENCE conjures number series (great for numbering rows, building date ladders, or feeding other formulas).

Combining Them & the Old Way Compared

The real magic appears when you nest these functions — the output of one becomes the input of the next. Because each returns a spilled range, they compose beautifully. A classic, genuinely useful pattern is a sorted, unique list of categories:

=SORT(UNIQUE(B2:B50))

Read it inside-out: UNIQUE distills the category column down to each distinct value, then SORT puts that list in order — one formula, one living, alphabetized list of categories. Take it a step further and filter before distilling, to get the sorted, unique list of categories among only the paid orders:

=SORT(UNIQUE(FILTER(B2:B50, E2:E50="Paid")))

That single cell now maintains a clean, ordered, de-duplicated summary that updates the instant any order's status changes. Pair it with the # reference and the SUMIFS from Lesson 4.3 — =SUMIFS($D$2:$D$50, $B$2:$B$50, E2#) next to a spilled category list — and you have a complete, self-building summary table with almost no manual upkeep.

The old way, honestly

How did people do this before dynamic arrays? A few ways, all clunkier. To get a unique list you'd use Remove Duplicates (a one-time command that goes stale the moment data changes) or an Advanced Filter, or a helper column with a COUNTIF trick, then sort manually. To summarize by category you'd build a PivotTable — still an excellent tool (Lesson 5.1 is devoted to it) — but a PivotTable must be refreshed to reflect new data, whereas a dynamic-array summary is always live. Neither old approach was wrong; they were just more steps and more re-doing.

🎯 Project: A Live Spilled Summary

Let's build something that would have taken a dozen manual steps before — and here takes one or two formulas. You'll create a self-maintaining summary from your tracker: a unique, sorted list of categories (with totals), or a live filtered view of your table. Either way, editing the data will update it automatically, with no dragging or refreshing.

Data assumed in A1:D50 — Code (A), Category (B), Date (C), Amount (D).

Sorted, unique list of categories (put in E2):
=SORT(UNIQUE(B2:B50))

Total per category, lined up beside the spilled list (in F2):
=SUMIFS($D$2:$D$50, $B$2:$B$50, E2#)

Live filtered view of one category typed into cell H1:
=FILTER(A2:D50, B2:B50=H1, "No matching rows")

Only high-value rows:
=FILTER(A2:D50, D2:D50>100, "None over 100")

Sorted, unique categories among only the paid orders — on a data
sheet that also has a Status column in E (so spill this one at G2,
clear of the data, not in E2):
=SORT(UNIQUE(FILTER(B2:B50, E2:E50="Paid")))

Best Practices for Dynamic Arrays

✅ Do's

• Give spills room to grow. Leave the cells below and beside an anchor formula empty so results can expand.
• Use the # reference to point at a whole spilled range, so dependent formulas resize with it.
• Feed dynamic arrays from Excel Tables (Lesson 3.1) so the source range grows automatically with new data.
• Always give FILTER an if_empty value so a no-match result reads clearly instead of erroring.

❌ Don'ts

• Don't type into spilled cells. Only the anchor cell is editable; the rest are owned by the formula.
• Don't assume everyone has them. Older Excel shows #NAME? — offer a PivotTable or helper columns for those users.
• Don't crowd the spill area with stray labels or merged cells, or you'll trigger #SPILL!.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You've reached the modern frontier of Excel formulas. You understand spilling, the # reference, and the #SPILL! error, and you can build live extracts and self-updating summaries with FILTER, SORT, and UNIQUE. Your tracker now carries a summary that maintains itself — no dragging, no refreshing. That completes Module 4's Formulas layer and sets you up perfectly for the Analysis stage.

❓ Common Questions at This Stage

🔭 Looking Ahead

In the next lesson — Lesson 5.1: PivotTables — Summarize Anything in Seconds — we open Module 5, the Analysis stage. You'll meet the tool that summarizes thousands of rows with a few drags: the PivotTable. Having just built summaries by formula, you'll deeply appreciate what a PivotTable automates — and when each approach is the right call.

✅ Before the Next Lesson

• Confirm your spilled summary and filtered view update when you add or change data
• Try nesting: build one SORT(UNIQUE(FILTER(...))) formula and see it work
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Microsoft Excel Help & Learning (Microsoft Support)
• microsoft365.com — open Excel for the web
• Microsoft Excel — product overview