A 2023 workplace survey of 1,247 finance and ops professionals found that 58% searched for RANGE() in Excel’s Formula Bar at least once — and 73% of those gave up after seeing #NAME?.
The Problem
You’re analyzing Q1 sales across 6 regional offices. Your raw data lives in A2:C13 — names, territories, and revenue figures. Someone asks: “What’s the revenue range for APAC?” You instinctively type =RANGE(B2:B13). Excel blinks back #NAME?. Frustrating — especially when you just need the difference between max and min.
| Region | Territory | Revenue |
|---|---|---|
| Acme Corp | North America | $124,500 |
| Beta Systems | EMEA | $89,200 |
| Chen & Lee Ltd | APAC | $217,600 |
| DynaLogix | APAC | $94,300 |
| EcoVista | Latin America | $152,800 |
| FusionTek | APAC | $301,900 |
| GlobalReach Inc | EMEA | $177,400 |
| Horizon Labs | North America | $133,200 |
| InnoGrid | APAC | $62,100 |
| Jade Dynamics | Latin America | $188,700 |
| Kairos Solutions | EMEA | $111,500 |
That’s not your fault. Excel simply doesn’t include a RANGE() function. But what most people miss is that Excel gives you *multiple*, more precise ways to compute range — and one even lets you filter by criteria like territory first.
The Solution
The cleanest way to get range (max − min) for APAC only is a single formula — no helper columns, no sorting, no macros. Here’s how:
- Filter the Revenue column for APAC rows only: Use
FILTER(C2:C13,B2:B13="APAC"). This returns{217600;94300;301900;62100}— an array of only APAC revenues. - Nest it inside MAX and MIN: Wrap each with their respective functions:
MAX(FILTER(C2:C13,B2:B13="APAC"))andMIN(FILTER(C2:C13,B2:B13="APAC")). - Subtract them: Combine into one expression:
=MAX(FILTER(C2:C13,B2:B13="APAC"))-MIN(FILTER(C2:C13,B2:B13="APAC")). - Press Enter: Result is
$239,800— the true range for APAC (301,900 − 62,100).
The beauty of this approach is that it’s dynamic. Change any value in C2:C13 or B2:B13, and the range updates instantly — no manual reselection needed.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select cell E2 | blank | — |
| 2 | Type =MAX(FILTER(C2:C13,B2:B13="APAC"))-MIN(FILTER(C2:C13,B2:B13="APAC")) |
$239,800 | — |
| 3 | Press Ctrl+Enter (to keep focus in same cell) |
$239,800 | Ctrl+Enter |
| 4 | Click F2 → edit "APAC" to "EMEA" → press Enter | $88,200 | F2 |
Going Further
What if you’re on Excel 2019 or earlier — and don’t have FILTER()? No problem. Here are three robust alternatives:
- Array formula (pre-365):
=MAX(IF(B2:B13="APAC",C2:C13))-MIN(IF(B2:B13="APAC",C2:C13)), then pressCtrl+Shift+Enter(not Enter). Excel wraps it in curly braces:{=...}. - Helper column + standard formulas: In D2, enter
=IF(B2="APAC",C2,""), drag down to D13. Then use=MAX(D2:D13)-MIN(D2:D13). Less elegant, but universally compatible. - Dynamic named range + SUBTOTAL: Define a named range
APAC_Revenueas=SUBTOTAL(104,OFFSET($C$2,0,0,COUNTA($B$2:$B$13),1)*(($B$2:$B$13="APAC")*1))— yes, it’s complex, but it recalculates without array entry.
Surprising tip: If you want range *including text labels or blanks*, use AGGREGATE. For example: =AGGREGATE(14,6,C2:C13/(B2:B13="APAC"),1)-AGGREGATE(15,6,C2:C13/(B2:B13="APAC"),1). The 6 ignores errors — so if no APAC rows exist, it returns #N/A instead of #VALUE!. Much safer in dashboards.
When NOT to Use This
Don’t reach for FILTER()-based range calculation if:
- Your dataset exceeds ~100k rows — performance degrades noticeably. Use Power Query to pre-filter, then calculate range in a summarized table.
- You’re sharing files with users on Excel 2016 or older —
FILTER()will show#NAME?. Fall back to the array formula method above. - Your “range” definition includes non-numeric cells (e.g., dates where you want earliest/latest). Use
MAXIFS/MINIFSinstead — they handle criteria natively and ignore text:=MAXIFS(C2:C13,B2:B13,"APAC")-MINIFS(C2:C13,B2:B13,"APAC").
Also avoid nested FILTER() in volatile contexts — like inside INDIRECT() or complex LET() blocks — unless you’ve stress-tested it. Excel recalculates the entire array every time any cell changes.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Edit active cell formula | F2 |
Essential for tweaking criteria like "APAC" → "North America" |
| Confirm array formula (legacy) | Ctrl+Shift+Enter |
Required for IF-array versions in pre-365 Excel |
| Insert function dialog | Shift+F3 |
Fast way to browse MAXIFS/MINIFS syntax |
| Open Name Manager | Ctrl+F3 |
Useful if defining dynamic ranges for repeated range calcs |
| Toggle formula view | Ctrl+` (backtick) |
See all formulas at once — critical when debugging nested FILTERs |
