THE EXCEL GUIDE Mastering Data & Spreadsheets

Home
Data Entry and Formatting

Filter Excel Tables: Step-by-Step Guide (2026)

Learn how to use built-in filters within tables.
By:

Why does clicking a column header sometimes give you a drop-down filter, and other times nothing happens at all? If you've run into that, the answer is almost always the same: your data isn't formatted as an actual Excel Table. Once it is, filtering data in Excel tables is fast, reliable, and genuinely useful. By the end of this guide, you'll control exactly which rows are visible using the built-in drop-down arrows — no formulas required yet, just structured table filtering the way Microsoft Excel intends it to work.

The one hard prerequisite: your data needs to be an official Excel Table (not a plain range dressed up with borders). If you haven't done that yet, the guide to converting a range to a table in Excel will get you there in about two minutes. Once that's sorted, every step below works exactly as described.

How to filter data in an Excel table using drop-down arrows on column headers
Drop-down arrows appear automatically on every column header when your data is formatted as an Excel Table.

Step 1: Turn On Filters and Use the Drop-Down Arrow to Filter Excel Table Data by Value

Good news: if your data is already an Excel Table, the AutoFilter drop-down arrows are on by default. You don't need to turn anything on. But if the arrows are missing, here's how to check.

How to confirm your drop-down arrows are active

Click anywhere inside your table. Go to the Table Design tab (it only appears when you're inside a table) and look for the Filter Button checkbox in the Table Style Options group. If it's unchecked, check it. Your drop-down arrows will reappear immediately. You can also toggle this with Ctrl + Shift + L, which works for both plain ranges and structured tables.

Filtering by one or more specific values

  1. Click the drop-down arrow in the column you want to filter — say, the Region column in a table of Regional Office Supply Orders Q3–Q4 2024.
  2. In the filter panel, uncheck Select All to clear everything at once.
  3. Check only the values you want to see: "Northeast," "Southwest," or both.
  4. Click OK.

Excel hides the rows that don't match and turns the drop-down arrow icon into a small funnel — that's your visual confirmation the filter is active. The row numbers on the left will also skip (you'll see 1, 4, 9 instead of 1, 2, 3), which is the clearest sign the filter criteria are doing their job. The status bar at the bottom shows a count of visible rows too.

Excel only displays the first 10,000 unique entries in the filter panel. For most workbooks that's never an issue, but for large datasets it matters. More on that in the mistakes section below.

Step 2: Apply a Custom Filter to Match Text, Numbers, or Dates More Precisely

Value-based filtering gets you far, but sometimes you don't want to pick from a list — you want to define a condition. That's where custom filters come in, and every data type has its own submenu.

Custom filter options by data type

After clicking the drop-down arrow, hover over Text Filters, Number Filters, or Date Filters depending on what's in that column. Excel detects the type automatically.

Text Filters let you filter by conditions like "contains," "begins with," or "ends with" — useful for finding all order IDs that start with "NE-" without typing each one. Number Filters give you options like "greater than," "between," or "top 10," handy for isolating orders above a certain dollar value. Date Filters include dynamic options like "this month," "last quarter," or "between two specific dates," which makes them genuinely more useful than picking dates from the value list manually.

In the Custom AutoFilter dialog, you can apply up to two conditions per column with an AND or OR relationship between them. Need more than two conditions, or criteria that span multiple columns dynamically? That's when the broader world of Excel tables and ranges — including the FILTER function — becomes the right tool, not the AutoFilter submenu.

Step 3: Clear or Remove a Filter from an Excel Table (Without Losing Your Data)

Once you've filtered what you need, clearing it is something people occasionally panic about. They see half their rows gone and worry the data's been deleted. It hasn't. Filtering only hides rows — nothing is removed.

To clear a filter on one column, click that column's drop-down arrow and select Clear Filter From [Column Name]. Every row filtered by that column comes back immediately. To clear all active filters across every column at once, go to the Data tab and click the Clear button in the Sort & Filter group.

If you want to remove the filter arrows entirely (not just clear the criteria), use Ctrl + Shift + L to toggle them off, or uncheck the Filter Button option in Table Design. The data stays; the arrows go away.

Common Mistakes When You Filter Excel Tables — Including the One-AutoFilter-Per-Sheet Limit

Most filtering problems fall into three categories, and two of them catch people completely off guard.

Your data isn't actually a Table. Plain ranges with borders look like tables. They're not. AutoFilter still works on plain ranges, but it behaves differently: no structured references, no automatic expansion, and the filtering doesn't stay scoped to your dataset the same way. If something feels off, check whether your data has a proper Table name in the Name Box. If it just shows a cell address like "A1," it's a plain range. For a full breakdown of the difference, the Excel for Beginners guide covers this clearly.

You can't have two active AutoFilters on the same sheet. This surprises almost everyone. Excel allows only one AutoFilter per worksheet. If you've got two separate tables on the same sheet and you filter one, the other loses its active filter state. This is a genuine limitation of AutoFilter, not a bug. The workaround is to put separate tables on separate sheets, or switch to the FILTER function for one of them, which outputs dynamic results to a different range entirely and isn't affected by this restriction.

Missing values in the filter dropdown. If your filter panel doesn't show a value you know exists in the column, the most likely culprit is the 10,000-entry display cap. The data is there; the dropdown just won't list more than 10,000 unique items. For filtering large datasets in Excel, the FILTER function handles this better than AutoFilter because it works on the full dataset without the display limitation. Exceljet's FILTER function reference is the clearest explanation of the syntax available.

Merged cells break sorting, filtering, and structured references in ways that are annoying to diagnose after the fact. Unmerge all cells before you build your table.

Frequently Asked Questions

How do I filter an Excel table by multiple criteria at once?

Within the AutoFilter drop-down, you can check multiple values in the filter panel, or use the Custom AutoFilter dialog to stack two conditions per column with AND or OR logic. For more than two conditions — or criteria across multiple columns that need to update dynamically — the FILTER function is the right move, since AutoFilter maxes out at two conditions per column.

What's the difference between AutoFilter and the FILTER function in Excel?

AutoFilter is interactive: you click drop-down arrows to hide and show rows in place. The FILTER function is a formula that outputs matching rows to a separate location and updates automatically when your source data or criteria change. AutoFilter is faster for ad-hoc browsing; the FILTER function is better when you need filtered results to feed into dashboards or other formulas.

Why can I only have one AutoFilter active per worksheet in Excel?

It's a built-in limitation of AutoFilter — Excel only supports one AutoFilter state per worksheet, regardless of how many tables are on the sheet. If you need to filter two separate datasets independently on the same sheet, use the FILTER function for at least one of them, or move one table to its own sheet.

Why aren't all my values showing in the Excel filter dropdown?

Excel's filter dropdown only displays the first 10,000 unique entries per column. The data beyond that limit is still in your worksheet and will still be filtered correctly — it just won't appear in the list for manual selection. For large datasets, using the FILTER function or Power Query gives you full control without the display cap.