Create a filtered report that spills into empty cells

Use FILTER, UNIQUE, and SORT to create a changing result area while preserving repeated source records.

Before you start

Excel for Microsoft 365, Excel 2021, and Excel 2024 for Windows desktop with FILTER, UNIQUE, and SORT. These functions and this spill workflow are not available in Excel 2016 or 2019. English formulas use commas; some locales require semicolons. Time is an estimate.

Useful first: Turn an order range into a growing Excel table · Answer the same business question with a sum and a count

By the end, you can…

Create a multi-row result from one formula cell.
Distinguish all matching records from a distinct-value list.
Handle a blocked spill area and a no-match result deliberately.

YOUR PRACTICE FILE

Small data. A result you can check.

Synthetic data only. Paste at A1 and leave the result areas empty before entering formulas.

Download practice data

View the raw practice data

Region	Item	Amount
North	Mug	12
South	Tray	8
North	Cup	5
North	Mug	12
South	Plate	10

01Reserve separate areas for data and results

Paste the sample into A1:C6. Enter TargetRegion in E1 and North in E2. Put Region, Item, and Amount in G1:I1, and DistinctItems in K1. Reserve empty G2:I6 and K2:K6 outside any Excel table.

The source contains North/Mug/12 twice. Those are two source records, not automatically an error. A filtered report should retain both unless the task specifically calls for deduplication. The distinct-items list answers a different question: which product names occur?

Area	Purpose
A1:C6	Original five-record dataset
E2	Region criterion
G2:I6	Reserved matching-record output
K2:K6	Reserved distinct-name output

02Enter one formula and inspect its full result

Enter the formula below in G2 and press Enter. It returns three rows in G2:I4: North/Mug/12, North/Cup/5, and North/Mug/12, in source order. Do not copy the formula down; G2 owns the entire spilled result.

The condition A2:A6=E2 tests five source rows, matching the height of A2:C6. If E2 is West, the supplied fallback displays No matching rows in G2. That is a message, not a zero-row data table. Do not count it as a sale or assume a blank-string fallback would be a truly empty array.

=FILTER(A2:C6,A2:A6=E2,"No matching rows")

Enter only in G2. Excel places the returned records in neighboring cells when the output area is clear.

03Build a distinct list without changing the source

In K2 enter the formula below. FILTER selects the region's item names, UNIQUE removes repeated names from that result, and SORT arranges the remaining names. For North, K2:K3 contains Cup then Mug. The original five records remain untouched.

A blocked result area produces #SPILL! instead of overwriting nearby content. Inspect the blockage and move valuable content elsewhere; clear only disposable cells you created for practice. Edit a spill formula in its top-left cell, not an individual output cell. A growing table can supply input through structured references, but the spilling output itself must remain outside that table.

=SORT(UNIQUE(FILTER(B2:B6,A2:A6=E2,"No matching rows")))

Enter only in K2. This nested formula returns distinct item names, not deduplicated complete source rows.

PUT IT INTO PRACTICE

Change the criterion and verify both the record report and the distinct list.

Change E2 to South. Check the full records in G2:I3 and names in K2:K3.
Change E2 to West, inspect the two fallback messages, then restore North.
On a disposable copy, remove the G2 formula, enter Keep in G3, and re-enter the FILTER formula in G2. Observe the blockage; remove only your Keep text to allow the spill.

Show the worked answer

South returns South/Tray/8 and South/Plate/10; the distinct names are Plate and Tray.

West shows No matching rows at G2 and K2. It does not represent a matching record.

The temporary G3 obstacle causes #SPILL!. Removing it restores North's three-record report.

Check your work

The North report retains both Mug records; its amounts total 29.
South's two amounts total 18.
Only G2 and K2 hold the report formulas; no formulas are filled down into their spill areas.

CHECK YOUR UNDERSTANDING

One question before you move on.

Why does North's FILTER report contain two Mug rows while its UNIQUE list contains one Mug?FILTER erased the duplicate source row permanently.FILTER returns matching records; UNIQUE returns distinct values from the selected item column.SORT multiplies every amount by two.

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.

NEXT LESSONBuild a PivotTable and prove that its totals are right

Keep the skill close.

FIELD GUIDECompare Two Lists in Excel: Find Missing IDs and Duplicate Counts FIELD GUIDERemove Duplicates in Excel and Keep the Latest Record WORKSPACE TOOLCompare two lists WORKSPACE TOOLRemove duplicates

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.