It’s 4:47 PM on Friday. Your manager just asked for a consolidated report by 5. You have 12 spreadsheets open and no idea how to combine them. You type =UNIQUE(A2:A100) — and get #SPILL!. You try Ctrl+Shift+Enter. Nothing happens. You’re not broken. You just haven’t defined the array properly.
Quick Answer
You define an array in Excel by either (a) entering a formula that returns multiple values into a single cell (which spills automatically in Excel 365/2021), (b) using Ctrl+Shift+Enter on older versions (legacy CSE arrays), or (c) naming a static or dynamic range as an array via Formulas > Name Manager — and yes, those named ranges behave like true arrays in formulas like SUMPRODUCT or FILTER.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Dynamic Spill Array | Type =SORT(UNIQUE(B2:B15)) in D2 → press Enter |
Modern Excel users needing live, expanding results | Fails if spill range is blocked (e.g., D2:D10 has data) |
| Legacy CSE Array | Select E2:E6 → type =TRANSPOSE(A2:A6) → press Ctrl+Shift+Enter |
Excel 2019 or earlier; fixed-size outputs | Cannot edit individual cells in the array; whole range must be selected first |
| Named Array (Static) | Formulas > Name Manager > New → Name: SalesQ1, Refers to: =Sheet1!$C$2:$C$10 |
Reusing fixed ranges across reports (e.g., quarterly sales figures) | Doesn’t auto-expand if source data grows |
| Named Array (Dynamic) | Name: ActiveClients, Refers to: =FILTER(Sheet1!A2:C100,Sheet1!C2:C100="Active") |
Teams maintaining dashboards with changing datasets | Requires Excel 365 or 2021; won’t work in Excel Online without full license |
Method 1 Deep Dive
Let’s say you’re tracking client renewals. In Sheet1, columns A–C hold:
| Client | Contract End | Status |
|---|---|---|
| Sarah Chen | 2024-09-15 | Active |
| Acme Corp | 2024-11-30 | Active |
| Nexus Labs | 2024-07-22 | Pending |
| Vista Group | 2024-12-05 | Active |
| TerraSoft Inc | 2024-08-10 | Inactive |
To define a dynamic array of only active clients’ names, go to cell F2 and type:
=FILTER(A2:A100,C2:C100="Active")
Press Enter. Excel spills the result down — say, F2:F4 — and defines that output as a live array. If you add a new row at A101 with “Lumen Dynamics” and status “Active”, the array auto-expands. That’s your array — no CSE, no selection required. (Trust me, I learned this the hard way after spending 22 minutes troubleshooting a blocked spill range.)
Surprising tip: You can use this array directly inside another function — no helper column needed. Try =COUNTA(FILTER(A2:A100,C2:C100="Active")) in H1. It works. Excel treats the entire FILTER result as one unit.
Method 2 Deep Dive
Now imagine you’re stuck on Excel 2019 and need to transpose quarterly revenue from rows to columns — and it must stay locked in place. Your raw data lives in B1:E1: $45,200, $51,800, $48,900, $53,100.
Here’s how to define a legacy array:
- Select cells G1:G4 (four empty vertical cells)
- Type
=TRANSPOSE(B1:E1) - Press Alt + M + M + Enter — that’s the ribbon shortcut for Ctrl+Shift+Enter (Formulas tab > Define Name > then… no, wait — actually, just use Ctrl+Shift+Enter. But if your keyboard’s sticky, Alt+M+M+Enter does the same thing.)
You’ll see {=TRANSPOSE(B1:E1)} appear in the formula bar — the curly braces mean Excel recognizes it as an array. Now G1:G4 holds your transposed values. Try editing G2. Excel blocks it. That’s intentional. To change anything, you must select all four cells (G1:G4), edit the formula, then re-press Ctrl+Shift+Enter.
This array isn’t dynamic — adding a fifth quarter in F1 won’t update G1:G4. But it *is* stable. And if you name this range? Go to Formulas > Name Manager > New. Name: QtrRevenue. Refers to: =Sheet1!$G$1:$G$4. Now you can use =SUM(QtrRevenue) anywhere — and it behaves like a proper array argument.
Cheat Sheet
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Type dynamic array formula (e.g., FILTER, SORT, SEQUENCE) | Spills automatically into adjacent blank cells | Enter |
| 2 | Select output range for legacy array | Range becomes bound to the array formula | None — manual selection only |
| 3 | Confirm legacy array entry | Curly braces { } appear in formula bar | Ctrl+Shift+Enter |
| 4 | Create named array (static) | Name appears in Name Box; usable in any formula | Alt + M + M |
| 5 | Define dynamic named array (e.g., with FILTER) | Updates when source data changes | Alt + M + M → enter formula in Refers to box |
