Filter data in Excel by selecting your table and using the AutoFilter button

The fastest way to filter data in Excel is to highlight your table and turn on AutoFilter. Click any cell inside your data, then go to the Data tab at the top and click AutoFilter. Excel adds dropdown arrows to the header row of your table. Click any dropdown arrow to show only the rows that match what you're looking for — hide everything else with one click.

This works whether your table has 50 rows or 5,000. The filtered view shows only the data you selected; the hidden rows are still there, just not visible. When you're done filtering, click AutoFilter again to remove the dropdown arrows and see all rows.

Filtering is different from sorting. Sorting rearranges your entire table by one column — A to Z, smallest to largest, oldest to newest. Filtering hides rows that don't match your criteria, so you can focus on a subset without moving anything around.

Key Takeaways

  • AutoFilter adds dropdown arrows to your header row and lets you hide rows that don't match your selection with a single click.
  • You can filter by text (showing only "New York"), by number (showing only values above 500), or by date (showing only entries from this year).
  • Multiple filters stack — you can filter by region and then by sales amount in the same table to narrow down further.
  • Filtered data still exists; it's just hidden, so you can remove the filter anytime to see all rows again.

Filter by text, number, or date using the dropdown menu

Click the dropdown arrow in any column header to see your filtering options. For a text column like "City" or "Product Name", you'll see a list of every unique value in that column with checkboxes. Uncheck the items you want to hide. For example, if your spreadsheet lists sales by region and you uncheck "West" and "South", only "North" and "East" rows will show.

For number columns, click Number Filters to set a range. You can show only values greater than 1000, less than 50, or between two numbers. For date columns, click Date Filters to show only entries from a specific month, year, or date range — useful when you're looking at invoices or transaction records by when they occurred.

The dropdown also shows a search box at the top. Type part of a word to find it quickly instead of scrolling through a long list. This saves time when you're filtering a column with hundreds of unique entries.

Stack multiple filters to narrow down your view

You can filter by more than one column at the same time. After you filter the first column, click the dropdown in a second column and filter that one too. The table now shows only rows that match both criteria. For example, filter "Region" to show only "North", then filter "Product" to show only "Laptops". Now you see only North region laptop sales.

Add a third or fourth filter the same way. Each new filter works on the rows already visible, so the results get narrower with each one. This is how you find specific subsets of data — like all invoices from California in the last 30 days over $5,000 — without writing a formula.

The dropdown arrow in a filtered column turns blue to remind you that column is active. If you forget which columns you've filtered, look for the blue arrows.

Remove or reset filters to see all your data again

To turn off a single filter and show all values in one column again, click its dropdown arrow and select Clear Filter From [Column Name]. The other filters stay active. To remove all filters at once and see your entire table, go to the Data tab and click Clear (or Reset Filter depending on your Excel version).

Clearing a filter does not delete any data. It just makes the hidden rows visible again. Your original spreadsheet is unchanged.

Use Standard Filter for complex filtering rules

AutoFilter's dropdown menu works for most filtering tasks, but sometimes you need more control. The Standard Filter option lets you build custom rules. Go to Data > More Filters > Standard Filter (the exact menu path varies by Excel version).

Standard Filter shows a dialog box where you can set up rules like "Region equals North AND Sales is greater than 5000" or "Product contains Laptop OR Product contains Desktop". You can combine multiple conditions with AND (all must be true) or OR (any can be true). This is useful when your filtering needs don't fit the straightforward dropdown options.

Most everyday filtering happens in the dropdown menu. Standard Filter is there when you need it, but you don't need to learn it first.

Filter data without changing the original spreadsheet

When you filter a table, you're only hiding rows — the original data stays exactly as it is. If you copy filtered data and paste it into a new sheet, Excel copies only the visible rows, not the hidden ones. This is useful when you want to send a colleague only the rows that match certain criteria.

To copy filtered data, select the visible cells (including headers), copy them, and paste into a new location. Excel automatically skips the hidden rows. If you want to keep the original spreadsheet and work with a filtered copy, paste into a new sheet instead of overwriting your source data.

Common filtering mistakes and how to avoid them

The most common mistake is forgetting you have a filter active. You look at your spreadsheet and think you're missing data, but it's just hidden. Check the Data tab — if you see a Clear or Reset Filter button, a filter is on. Click it to see everything again.

Another mistake is filtering before your headers are in place. AutoFilter needs a header row to work properly. If your first row is data instead of column names, select your actual headers before turning on AutoFilter, or add headers first.

If you filter and then sort, the sort applies only to visible rows. This can rearrange your data in unexpected ways. If you need to sort, do it before filtering, or clear the filter, sort, then filter again.

Frequently Asked Questions

Can I filter by color or formatting?

Yes. Click the dropdown arrow in any column, then look for Filter by Color (the exact name varies by version). You can show only cells with a specific background color or font color. This works if you've manually colored cells or used conditional formatting to color them based on values.

What's the difference between filter and sort?

Sort rearranges all rows by one column — A to Z, smallest to largest, oldest to newest. Filter hides rows that don't match your criteria, so you see only what you selected. You can use both together: filter to show only North region sales, then sort those by date.

If I filter and then save the file, will the filter stay on?

Yes. Excel saves the filter settings with your file. When you open it again, the same rows will be hidden. If you want to send someone the unfiltered version, clear the filter before saving, or save a copy with the filter removed.

Can I filter by a formula result?

Yes. If a column contains formulas, you can filter by the results those formulas show, just like any other data. The filter looks at what's displayed in the cell, not the formula itself.

What if my table has no header row?

AutoFilter assumes the first row is headers. If it's not, add one before turning on AutoFilter, or manually select your data range including headers before clicking AutoFilter. Excel will then use your actual headers instead of guessing.