What filtering does and when to use it

A filter in Excel hides rows that don't match what you're looking for, so you see only the data that matters. If you have a spreadsheet with 500 customer records and you want to see only those from California, a filter shows you just those rows and hides the rest. The hidden rows are still there — filtering doesn't delete anything — but they stay out of sight until you remove the filter or change it.

Filtering is different from sorting. Sorting rearranges your rows in a new order (alphabetical, by date, by size). Filtering removes rows from view based on what's in a specific column. You'll use filtering when you need to focus on a subset of your data without permanently changing the spreadsheet, or when you need to count or work with only certain rows.

Excel's basic filter is built in and takes about 30 seconds to turn on. It works best when your data has a header row — the first row that names each column — and when you want to filter by exact matches or straightforward ranges. If you need to filter by multiple conditions at once (show me California customers who spent more than $1,000), you'll use the same tool but with a few extra clicks.

Key Takeaways

  • Click any cell in your data, then click the Data menu and select AutoFilter to add filter buttons to your header row.
  • Click the dropdown arrow in any column header to choose which rows to show or hide based on that column's values.
  • You can filter by multiple columns at once — each filter narrows down what the others show.
  • Filtering hides rows but does not delete them; remove the filter to see all rows again.
  • If your data does not have a header row, Excel will treat the first row of data as headers when you turn on AutoFilter.

Turning on AutoFilter in three clicks

Start by clicking any cell that contains data in your spreadsheet. It doesn't matter which cell — Excel will recognize the entire data range. Then click the Data menu at the top of the screen.

In the Data menu, look for the option called AutoFilter and click it. Excel will when ready add a small dropdown arrow to the right side of each column header. These arrows are your filter controls. If you don't see arrows appear, it usually means your first row wasn't recognized as headers — go back and make sure the first row contains column names, not data.

To turn off the filter and remove the arrows, click Data again and click AutoFilter a second time. The arrows disappear, but your data stays exactly as it was.

Filtering a single column to show only what you want

Click the dropdown arrow in the column you want to filter. A menu will open showing every unique value in that column. For example, if you're filtering a State column, you'll see a list of all states in your data. At the top of this menu is a checkbox next to "All" — currently checked, meaning all rows are visible.

To show only rows from one state, first uncheck "All". This unchecks every value at once. Then scroll down and check only the state you want to see. Click OK. Excel now hides every row except those with that state in the column you filtered.

To add more states to the view, click the same dropdown arrow again, check the additional states you want, and click OK. The filter updates when ready. You can filter by as many values as you want in a single column — just keep checking boxes until you've selected everything you need to see.

Filtering multiple columns at the same time

Each filter you add narrows down what the other filters show. If you filter the State column to show only California, then filter the Sales column to show only amounts over $1,000, you'll see only California rows where sales exceeded $1,000. The filters work together.

Start by clicking the dropdown in the first column and selecting the values you want. Click OK. Then click the dropdown in the second column and select its values. Each new filter is applied to whatever rows the previous filter left visible. You can filter three, four, or more columns this way — Excel will keep narrowing the results.

To remove one filter and see all rows again for that column, click its dropdown arrow and check "All", then click OK. The other filters stay active. To remove all filters at once, click Data and then AutoFilter again — this turns off filtering entirely and shows every row.

Using the filter menu to find rows by number ranges

If your column contains numbers — like prices, quantities, or dates — the dropdown menu includes an option called Number Filters or Date Filters (depending on what type of data is in the column). Click this option to see choices like "Greater Than", "Less Than", "Between", and "Equals".

Click "Greater Than" to show only rows where the number is above a certain amount. A small dialog box will open asking you to type the number. Type 1000 and click OK to see only rows where that column's value is greater than 1,000. Use "Between" to show rows within a range — for example, dates between January 1 and March 31.

These number filters are faster than scrolling through a long list of values and checking boxes one by one. They're especially useful when you have hundreds of different prices or dates and you only care about a range.

Clearing filters and starting over

If you've applied several filters and want to see all your data again, click the Data menu and look for an option called Clear Filter or Reset Filter. Clicking this removes all active filters at once and shows every row. The filter arrows stay in your header row — you can explore new filters whenever you need them.

If you want to remove the filter arrows entirely and go back to a spreadsheet with no filtering capability, click Data and then AutoFilter to turn it off. Your data doesn't change; you just lose the dropdown arrows in the header row.

A common mistake is thinking that filtering has deleted rows. It hasn't. If you filter to show only California customers and then save the file, the file saves with only California rows visible — but all the other rows are still in the spreadsheet, just hidden. When you open the file later, you'll see only California rows until you clear the filter. If this surprises you, click Data and Clear Filter to see all rows again.

What filtering cannot do, and when to use other tools instead

Filtering shows and hides rows, but it doesn't change them. If you need to permanently delete rows, change values, or reorganize your data, filtering isn't the right tool — you'll need to edit the cells directly or use sorting instead. Filtering also works best on data that's organized in a straightforward table with one header row and no blank rows in the middle. If your spreadsheet has a complex layout with multiple sections or merged cells, filtering may not work as expected.

If you need to filter by very complex rules — for example, "show me rows where column A is greater than 100 AND column B contains the word 'urgent' AND column C is blank" — Excel's basic AutoFilter can handle it, but you may find it easier to use the Advanced Filter option under the Data menu. Advanced Filter lets you write out your conditions in a separate area of the spreadsheet, which is clearer when you have many rules.

Frequently Asked Questions

Can I filter text that contains a specific word, not just exact matches?

Yes. Click the dropdown arrow in the column, then look for an option like "Text Filters" or "Contains". Click it and type the word you're looking for. Excel will show only rows where that column contains that word anywhere in the text, even if there are other words around it.

If I filter my data and then copy rows, will I copy the hidden rows too?

No. When you copy filtered data, Excel copies only the visible rows. The hidden rows stay in the spreadsheet but don't get copied. This is useful when you want to move a filtered subset to another sheet or document.

Can I save a spreadsheet with filters turned on?

Yes. When you save a file with filters active, the filters save with it. The next time you open the file, you'll see only the filtered rows until you clear the filter. If you want to save the file showing all rows, clear the filter before saving.

What if my data doesn't have a header row?

When you turn on AutoFilter, Excel treats the first row as headers whether it contains column names or data. If your first row is actual data, it will get filter arrows too, and you'll lose it from view when you filter. Add a header row at the top before turning on AutoFilter, or manually select your data range before clicking AutoFilter so Excel knows where your headers are.

Can I filter by color or formatting?

Yes, if your cells are colored or formatted. Click the dropdown arrow in the column, look for "Filter by Color" or a similar option, and select the color you want to see. Excel will show only rows with cells in that color.