Filtering shows and hides rows based on what you type, without deleting anything

A filter in Excel lets you display only the rows that match what you're looking for. If you have a spreadsheet with 500 customer records and you want to see only the ones from California, a filter does that in seconds. The rows don't disappear — they just hide until you remove the filter or change it.

Filters work on columns. You pick a column header, tell Excel what values to show, and every row that doesn't match gets hidden. You can filter by text (like a state name), by number (like sales over $1,000), or by date (like orders from the last 30 days). You can also filter multiple columns at once — show me California customers who spent more than $500.

The most common type is the AutoFilter, which adds dropdown arrows to your column headers. That's what most people mean when they say "add a filter".

Key Takeaways

  • Select any cell in your data table, go to the Data tab, and click AutoFilter to add dropdown arrows to every column header.
  • Click the dropdown arrow in any column header, uncheck the values you want to hide, and click OK to filter that column.
  • You can filter multiple columns at the same time — each filter narrows down the rows further.
  • Filtered rows are hidden, not deleted, so removing the filter brings them back exactly as they were.
  • The row numbers on the left turn blue when a filter is active, so you know some rows are hidden.

How to turn on AutoFilter in three steps

Start by clicking any cell inside your data table — it doesn't matter which one. Excel will figure out where your data starts and stops.

Go to the Data tab at the top of the ribbon. Click the button labeled AutoFilter. Excel will add a small dropdown arrow to the right of every column header in your table. If you don't see the Data tab, you may be in a view that doesn't show it — try clicking a cell in the middle of your data and trying again.

That's it. The filter is now on. You haven't hidden anything yet — you've just added the controls that let you hide rows.

How to filter a single column

Click the dropdown arrow in the column header you want to filter. A menu will open showing every unique value in that column — if your column is "State", you'll see Alabama, Alaska, Arizona, and so on.

By default, all values are checked. Uncheck the ones you want to hide. If you want to see only California, uncheck everything except California. If you want to hide California, uncheck only California and leave the rest checked.

Click OK at the bottom of the menu. Excel will hide every row that doesn't match your choice. The row numbers on the left side will turn blue, which tells you a filter is active and some rows are hidden.

To remove the filter and show all rows again, click the dropdown arrow in that column header and click Clear Filter.

How to filter by a condition instead of picking values

Sometimes you don't want to pick specific values — you want to show rows where a number is greater than something, or where text starts with certain letters. Click the dropdown arrow in the column you want to filter, then click Filter by Condition (the exact wording varies by Excel version, but it's usually near the bottom of the menu).

A dialog box will open. Choose your condition from the first dropdown — options include "equals", "does not equal", "greater than", "less than", "contains", and "does not contain". Type or select the value you want to compare against in the second field. For example, to show only sales over $1,000, choose "greater than" and type 1000.

Click OK. Excel will hide all rows that don't meet your condition.

How to filter multiple columns at once

You can explore a filter to as many columns as you need. Each filter narrows down the results further. If you filter State to show only California and then filter Sales to show only amounts over $500, you'll see only California rows where sales exceeded $500.

Start with the first column: click its dropdown arrow, choose your values or condition, and click OK. Then click the dropdown arrow in the second column and repeat. The row numbers will stay blue as long as any filter is active.

To remove one filter without touching the others, click its dropdown arrow and click Clear Filter. To remove all filters at once, go to the Data tab and click AutoFilter again — this turns the filter off entirely and shows every row.

What happens to your data when you filter

Filtering only hides rows. It does not delete them, move them, or change them in any way. If you filter out 400 rows and then copy the visible rows to another sheet, only the visible ones copy — but the hidden ones are still in your original sheet, unchanged.

If you sort a filtered table (by clicking a column header and choosing sort order), Excel sorts only the visible rows. The hidden rows stay in their original position and will reappear in that same spot if you remove the filter.

If you save your file with a filter active, the filter settings save too. The next time you open the file, the same rows will be hidden. To save the file with all rows visible, remove the filter before saving.

Common reasons a filter doesn't work the way you expect

If you click AutoFilter and no dropdown arrows appear, you may have clicked a cell outside your data table. Click a cell that's clearly inside the data — in a column header or in the middle of your numbers — and try again.

If a dropdown menu shows only blank values or seems to be missing some data, your table may have blank cells in the header row, or Excel may not have recognized where your data ends. Make sure the row above your data is completely empty, and that there are no completely blank columns in the middle of your table.

If you filter a column and a value you expected to see doesn't appear in the dropdown menu, that value may be hidden in a hidden row from a different filter. Clear all filters and try again, or check whether another column has a filter active.

Frequently Asked Questions

Can I filter text that contains certain letters without picking exact matches?

Yes. Click the dropdown arrow in the column, choose "Filter by Condition", select "contains", and type the letters you're looking for. For example, "contains" and "john" will show all rows where the cell contains "john" anywhere in the text — "John Smith", "johnson", "john_doe", and so on.

What's the difference between AutoFilter and other filter types?

AutoFilter is the simplest and most common. It adds dropdown arrows to headers and lets you pick values or set conditions. Advanced Filter is a separate tool for more complex searches, but most people never need it. Standard Filter is an older name for the same thing.

If I filter a column, sort another column, and then remove the filter, will my data go back to its original order?

No. Sorting changes the order permanently. Filtering only hides rows, so removing a filter shows the sorted order, not the original order. If you need to go back to the original order, use Undo right after you sort, or sort by a column that has unique numbers in the original sequence.

Can I filter by color or by whether a cell is bold?

Yes, but only if your version of Excel is recent (2007 or later). Click the dropdown arrow, look for "Filter by Color" or similar, and choose the color or formatting you want. This is less common than filtering by value or condition.

What if I want to hide specific rows instead of filtering by column values?

That's a different tool. Right-click the row number on the left side and choose "Hide". Hidden rows stay hidden until you right-click and choose "Unhide". This is useful when you want to hide one or two specific rows rather than all rows that match a condition.