Filtering Data in Excel — Examples & How-To Guide

Most Excel tutorials treat filtering like it's just a way to hide rows you don't want to see right now. That's a fine description of AutoFilter, and it misses half the picture entirely. Filtering data in Excel actually splits into two different tools with two different jobs, and which one you reach for depends entirely on what you need your data to do after you filter it.

If you're brand new to this, don't worry. The examples below use a single realistic dataset, "Regional Logistics Orders Q3–Q4 2024," so you'll see the same data filtered multiple ways without constantly reorienting yourself. And if you're not sure whether you have Excel 365 or an older build, check File → Account → About Excel before you start. It matters here.

What You'll Be Able to Filter — and the One Distinction That Makes These Examples Click

By the end of this guide, you'll know how to hide irrelevant rows temporarily using AutoFilter and how to extract a filtered subset into a completely separate range using the FILTER function. Those aren't the same thing, and mixing them up is the source of most of the confusion I see in teams moving from manual spreadsheets to actual data workflows.

AutoFilter works in every version of Microsoft Excel. It hides rows that don't match your filter criteria — the data's still there, just out of view. The FILTER function is a dynamic array formula available only in Excel 365 (Microsoft 365 subscription) and Excel 2021. It doesn't hide anything. Instead, it writes matching rows into a new location on your sheet. If you're on an older build, stick with Step 1 and come back to Step 2 when you upgrade.

If you're still getting comfortable with Excel basics overall, the Excel for Beginners starter guide covers foundational setup before you start filtering.

---

Step 1: Apply AutoFilter to a Data Range and Filter by Value, Color, or Multiple Criteria

AutoFilter is the fastest way to answer a quick question about your data. "Show me only the Northeast region orders from October." Done in about four clicks, or two keystrokes if you know the shortcut.

Turn AutoFilter On and Pick Your First Filter Value

Click any cell inside your data range (not the whole sheet, just one cell inside the table). Then press Alt → D → F → F on Windows to toggle AutoFilter on. You'll see dropdown arrows appear on every column header in your data range.

1. Click the dropdown arrow on the column you want to filter — say, "Region."
2. Uncheck Select All to clear everything, then check only Northeast.
3. Click OK. Excel hides every row where Region isn't Northeast.

Your data is still intact. You haven't deleted anything. Row numbers will skip (you'll see 1, 4, 9, 12…) which is how you know a filter is active.

If a column has mixed data types — some cells formatted as numbers, some as text that looks like numbers — Excel only shows filter options for the most common type. You lose the other values in the dropdown silently. This can swallow entire months of order data. Keep your columns clean and consistent.

Stack Multiple Criteria and Filter by Color

To filter by multiple criteria in the same column, open the dropdown and use Text Filters → Contains, or check multiple values manually. To filter across two columns (say, Northeast region and order status "Shipped"), apply one filter, then open the second column's dropdown and apply the second. AutoFilter stacks them with AND logic automatically.

For Excel filter by color: open the dropdown, hover over Filter by Color, and pick the cell background or font color you've applied. This only works if you've already color-coded cells manually or via conditional formatting.

If you work on a Mac, the interface differs slightly. Check out sorting and filtering data in Excel on Mac for the Mac-specific steps.

---

Step 2: Use the Excel FILTER Function to Pull Filtered Data into a Separate Range (Excel 365 Only)

Once you've used AutoFilter to hide rows, you've probably noticed the limitation: the filtered view disappears the moment you clear it, and you can't really build anything on top of it. That's exactly where the FILTER function earns its place.

Write Your First FILTER Formula with a Single Condition

The FILTER function syntax is: =FILTER(array, include, [if_empty])

Using our logistics dataset with columns A through E (Order ID, Region, Date, Status, Amount), here's a formula that pulls every Northeast order into a new range starting at cell G2:

=FILTER(A2:E100, B2:B100="Northeast", "No results")

The result spills automatically into as many rows as it needs. No dragging. No copying. If a new Northeast order is added to the source data, the filtered output updates on its own.

That third argument — "No results" — is not optional in any production sheet. Most tutorials skip it. Omitting it means that if no rows match your filter criteria, Excel throws a #CALC! error instead of a blank or a message. A dashboard running without a hitch for eleven months can break in December because one region has zero qualifying orders that month and the third argument is missing. One #CALC! error can cascade into five dependent formulas going red. Always include if_empty.

For more on getting set up from scratch, the how to apply basic filters in Excel guide covers the foundational steps.

Filter with Multiple Conditions Using Boolean Logic

To filter by two conditions at once (Northeast region AND status "Shipped"), multiply the criteria arrays together:

=FILTER(A2:E100, (B2:B100="Northeast")*(D2:D100="Shipped"), "No results")

The multiplication operator acts as AND logic in criteria arrays. For OR logic, use addition (+) instead. This feature is what you'll reach for constantly once you start building real dashboards.

According to Microsoft's official FILTER function documentation, the function is exclusive to Excel 365 and Excel 2021. On Excel 2019 or earlier, it won't work at all.

---

Common Mistakes When Filtering Data in Excel — and How to Fix Them

Three mistakes account for the vast majority of "why is my Excel filter not working correctly" moments. Here they are in order of how often they come up.

---