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. Worse — she says 'Just pull the Q2 sales for clients in Tier 2 or higher, excluding returns.' You highlight rows manually, miss two entries, and realize too late that your filter didn’t actually remove rows — it just hid them. You’re not alone.
Quick Answer
To subset data in Excel, you don’t copy-paste filtered results — you extract *only the visible cells* using Alt+; (semicolon) after filtering, or use FILTER(), INDEX/MATCH with Boolean logic, or Power Query for repeatable workflows. The fastest reliable method depends on whether your data changes daily (use FILTER), is static (use Advanced Filter), or comes from multiple sources (use Power Query).
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| AutoFilter + Copy Visible Cells | Apply filter → select visible rows → Alt+; → Ctrl+C → paste elsewhere | One-time clean extracts; no formulas needed | Fails if source data has merged cells or blank headers |
| FILTER function (Excel 365/2021) | =FILTER(A2:E100,(C2:C100="Tier 2")+(C2:C100="Tier 3"),"No matches") | Dynamic, auto-updating subsets; handles AND/OR logic cleanly | Not available in Excel 2019 or earlier |
| Advanced Filter | Set up criteria range (e.g., F1:F2) → Data tab → Advanced → check 'Copy to another location' | Complex conditions (e.g., >10K AND <50K AND NOT "Returned") | Criteria range must match column headers exactly; no wildcards in numeric fields |
| Power Query | Data → Get Data → From Table/Range → Filter columns → Close & Load | Repeated subsetting across weekly reports or multiple files | Steeper learning curve; outputs to new sheet or connection only |
| INDEX/AGGREGATE combo | =INDEX($A$2:$A$100,AGGREGATE(15,6,ROW($A$2:$A$100)/($C$2:$C$100="Tier 2"),ROW(A1))) | Legacy Excel users who need dynamic arrays without FILTER() | Hard to debug; breaks if row numbers shift mid-formula |
| VBA macro (custom button) | Record macro while filtering → edit code to unhide all → copy visible → paste to Sheet2 | Teams with identical monthly tasks and IT-approved macros | Security warnings; fails if workbook isn’t saved as .xlsm |
Method 1 Deep Dive
Let’s say you’ve got this table in A1:E11:
| Client | Region | Tier | Revenue | Status |
|---|---|---|---|---|
| Sarah Chen | APAC | Tier 2 | $45,200 | Active |
| Acme Corp | EMEA | Tier 3 | $89,600 | Active |
| Zephyr Labs | Americas | Tier 1 | $12,400 | Pending |
| Nexus Inc | APAC | Tier 2 | $67,100 | Active |
| Orion Group | EMEA | Tier 3 | $31,800 | Returned |
| Veridian Ltd | Americas | Tier 2 | $55,300 | Active |
You need only Tier 2 and Tier 3 clients who are Active — not Pending or Returned. Click any cell in row 1, press Ctrl+Shift+L to apply AutoFilter. Click the dropdown in column C (Tier), uncheck '(Select All)', then check only 'Tier 2' and 'Tier 3'. Now click the Status dropdown, uncheck 'Pending' and 'Returned'. Only 4 rows remain visible. Here’s the trap: if you now select A2:E6 and copy, you’ll grab hidden rows too — unless you use Alt+; first. Try it: with A2:E6 selected, hit Alt+; — Excel selects only visible cells. Then Ctrl+C, paste into G1. Done. (Trust me, I learned this the hard way when my ‘filtered’ list included 3 returned orders.)
Method 2 Deep Dive
The FILTER function does this dynamically — and it’s shockingly readable. In G1, type:
=FILTER(A2:E11,(C2:C11="Tier 2")+(C2:C11="Tier 3")*(E2:E11="Active"),"No qualifying records")
That’s it. The + means OR, the * means AND. It spills results automatically down column G. If someone adds a new Tier 2 Active client in row 12 tomorrow, FILTER updates instantly. Bonus: if you change C2 to "Tier 1", the result shrinks — no re-filtering needed. One counterintuitive tip? Don’t wrap FILTER inside IFERROR to hide #CALC! errors. Instead, use the third argument — like above — because IFERROR breaks the spill range. Also, FILTER ignores entire rows where any condition references an error (#N/A, #VALUE!), so clean your source data first.
Cheat Sheet
| Task | Shortcut / Formula | Notes |
|---|---|---|
| Select only visible cells after filtering | Alt+; | Works on any selected range — even non-contiguous ones |
| Filter for values > $50,000 AND in APAC | =FILTER(A2:E11,(D2:D11>50000)*(B2:B11="APAC")) |
Use parentheses around each condition — order matters |
| Open Advanced Filter dialog | Alt+A+Q | Then choose 'Copy to another location' and fill in ranges |
| Refresh all Power Query subsets | Alt+F5 | Or right-click any query output → 'Refresh' |
