It's 3:12 PM. You just pasted 87 rows of supplier invoices into Sheet1. Your CFO needs a live list of all line items over $2,500 — sorted by date, with vendor name and item description — by 4:00. You type =FILTER(A2:C88,C2:C88>2500). Nothing appears. Then you get #SPILL! in E2. You hit F9. Still nothing. You try Ctrl+Shift+Enter. Now it says #VALUE!. You glance at the clock. 3:14.
The Setup
You’re working with raw procurement data from Acme Corp’s Q2 vendor portal. No headers were imported. The first entry starts at A1. Here’s what’s in A1:C10:
| A (Vendor) | B (Item) | C (Amount) |
|---|---|---|
| GlobalTech Inc. | SSD Drive - 2TB | $3,240.00 |
| Nexus Logistics | Freight Surcharge | $1,890.00 |
| Veridian Labs | Calibration Kit v4.2 | $4,115.50 |
| Alpha Components | Copper Busbar Set | $2,670.00 |
| TerraSys Engineering | Site Survey Report | $1,420.00 |
| Orion Data Group | Cloud Storage License | $3,850.00 |
| Stellar Fabrication | Custom Chassis Assembly | $5,900.00 |
| Quantum Metrics | Thermal Imaging Sensor | $2,210.00 |
| LumenCore Systems | Fiber Patch Panel | $3,025.00 |
| VistaPoint Consulting | Project Kickoff Workshop | $1,995.00 |
The Challenge
You need to extract every row where C2:C10 > $2,500. Not just filter visually. Not just copy-paste. You need a live, expanding list that updates when new rows land in column C — and spills cleanly into adjacent columns without manual dragging.
Here’s what makes it tricky: Excel doesn’t let you ‘create an array’ like Python or R. There’s no array() function. What people call ‘creating an array’ is really about triggering Excel’s dynamic array engine — which only activates under strict conditions.
Condition one: Your formula must return multiple values. Condition two: It must be entered in a single cell — not selected across a range first. Condition three: That cell must have empty space below and to the right. If there’s even one merged cell in E2:E100, #SPILL! appears. And yes — merged cells break arrays. Every time.
Walking Through It
Start in cell E1. Type this exactly:
=FILTER(A2:C10,C2:C10>2500)
Press Enter. Not Ctrl+Shift+Enter. Not Ctrl+Enter. Just Enter.
You’ll see the first result appear in E1. Then — if E2:E100 and F1:G100 are blank — Excel automatically spills results down and right. That’s the array. Not a selection. Not a range you highlight. It’s Excel auto-filling E1:G5 with matching rows.
Before (E1 empty):
| E (Vendor) | F (Item) | G (Amount) |
|---|---|---|
| — | — | — |
After pressing Enter (E1 populated, spill active):
| E (Vendor) | F (Item) | G (Amount) |
|---|---|---|
| GlobalTech Inc. | SSD Drive - 2TB | $3,240.00 |
| Veridian Labs | Calibration Kit v4.2 | $4,115.50 |
| Alpha Components | Copper Busbar Set | $2,670.00 |
| Orion Data Group | Cloud Storage License | $3,850.00 |
| Stellar Fabrication | Custom Chassis Assembly | $5,900.00 |
| LumenCore Systems | Fiber Patch Panel | $3,025.00 |
Notice how Excel spilled 6 rows — not 5, not 7 — because exactly six entries met the condition. That’s the array in action.
Now try sorting them by amount, descending. In H1, type:
=SORT(E1#,3,-1)
That # after E1 tells Excel: “grab the entire spilled range starting at E1”. No need to guess how many rows. No need for CSE. Just E1#.
This is the counterintuitive tip: You don’t create arrays by selecting cells. You create them by writing formulas that *return* arrays — and letting Excel handle the sizing.
The Result
Final output — live, sorted, auto-expanding — lands in H1:J6:
| H (Vendor) | I (Item) | J (Amount) |
|---|---|---|
| Stellar Fabrication | Custom Chassis Assembly | $5,900.00 |
| Veridian Labs | Calibration Kit v4.2 | $4,115.50 |
| Orion Data Group | Cloud Storage License | $3,850.00 |
| LumenCore Systems | Fiber Patch Panel | $3,025.00 |
| GlobalTech Inc. | SSD Drive - 2TB | $3,240.00 |
| Alpha Components | Copper Busbar Set | $2,670.00 |
Insert a new row at A11 with VentureScale, AI Audit Suite, $3,400.00. Watch H1:J6 instantly expand to H1:J7 — no editing required.
What Could Go Wrong
Here are the three mistakes we see in nearly every live session:
- Mistake #1: Trying to enter an array formula in a non-empty spill range. If cell F3 contains text,
=FILTER(A2:C10,C2:C10>2500)in E1 will show#SPILL!. Clear F3:F100 first — or useAlt+E+S+V(Paste Values) to unmerge and wipe formatting in bulk. - Mistake #2: Using Ctrl+Shift+Enter on a dynamic array function. This wraps it in curly braces
{=FILTER(...)}— which breaks it in Excel 365/2021. Delete the braces. Press Enter only. - Mistake #3: Referencing a spilled range without the # symbol. Typing
=SORT(E1,3,-1)instead of=SORT(E1#,3,-1)returns only the top value — not the whole array. Excel treats E1 alone as a scalar, not a range.
Next step: Open your workbook. Go to any blank column. Type =SEQUENCE(5) in a single cell. Press Enter. Watch Excel fill five rows. That’s the simplest array — and your foundation for everything else.
