What filtering does and why you'd use it

A filter in Excel hides rows that don't match what you're looking for, so you can focus on the data that matters. If you have a spreadsheet with 500 customer records and you only want to see orders from March, a filter shows you just those rows. The hidden rows are still there — filtering doesn't delete anything — but they're out of your way.

Filtering is faster than sorting when you want to look at one subset of data and then switch to another. It's also safer than deleting rows by accident. Once you turn off the filter, all your data comes back exactly as it was.

Key Takeaways

  • Select your data range, click the Data menu, and choose AutoFilter to add filter buttons to your column headers.
  • Click the dropdown arrow in any column header to choose which values to show or hide.
  • You can filter by text, numbers, dates, or custom rules — Excel shows different options depending on what's in that column.
  • Multiple filters work together: if you filter by month and by region, you see only rows that match both conditions.
  • Turn off the filter by clicking Data and then AutoFilter again — all hidden rows reappear.

How to turn on AutoFilter

Start by selecting the data you want to filter. Click the first cell in your data (usually a header row with column names), then hold Shift and click the last cell that contains data. This selects the entire range.

Go to the Data menu at the top of the screen. Click AutoFilter. Excel adds a small dropdown arrow to each column header. Those arrows are your filter controls.

If your data doesn't have headers in the first row, Excel still adds the filter buttons, but they'll be in row 1. You can still filter normally — just know that row 1 will be treated as data, not as a label.

Filtering by text or category

Click the dropdown arrow in the column you want to filter. A menu appears with a list of every unique value in that column. By default, all values are checked.

Uncheck the box next to any value you want to hide. For example, if the column shows regions (North, South, East, West) and you only want to see North, uncheck South, East, and West. Click OK. Now only rows with "North" in that column show up.

To show those hidden rows again, click the dropdown arrow in that column and check the boxes you unchecked. You can also click "All" to check everything at once.

Filtering by numbers or dates

When you click the dropdown arrow in a column with numbers or dates, the menu looks different. Instead of a straightforward list of checkboxes, you see options like "Greater Than", "Less Than", "Between", and "Equals".

Click the option that matches what you're looking for. A dialog box opens where you type the number or date you want to compare against. For example, to see only sales over $1,000, click "Greater Than", type 1000, and click OK.

For dates, you can also click "Filter by Date" to see options like "This Month", "Last Quarter", or "Custom Date Range". These are shortcuts for common date filters.

Using multiple filters at once

You can filter on more than one column. If you filter the Region column to show only "North" and then filter the Month column to show only "March", Excel shows only rows where Region is North AND Month is March. Every filter you add narrows the results further.

This is useful when you have a lot of data and need to zoom in on a specific slice. For instance, a sales spreadsheet might have filters for Region, Product, and Date Range all active at the same time, showing you exactly the subset you need to analyze.

To remove one filter without removing the others, click the dropdown arrow in that column and click "All" to show all values again. The other filters stay in place.

Turning off the filter

When you're done filtering and want to see all your data again, go to the Data menu and click AutoFilter. The dropdown arrows disappear from the headers, and all hidden rows reappear. Your data is unchanged — nothing was deleted, just hidden.

If you turn AutoFilter back on later, the filter settings you used before are gone. Excel starts fresh with all rows visible and no filters active.

What happens when you sort filtered data

If you sort a column while a filter is active, Excel sorts only the visible (filtered) rows. The hidden rows stay hidden and don't move. This is usually what you want — you've narrowed down to the data you care about, and now you're organizing just that subset.

If you turn off the filter later, the hidden rows reappear in their original order, not in the sorted order. The sort only applied to the rows you could see.

Frequently Asked Questions

Can I filter by more than one value in the same column?

Yes. Click the dropdown arrow, uncheck "All", then check only the values you want to see. For example, to show North and South but hide East and West, check only those two boxes. Click OK. Excel shows rows matching any of the checked values.

What's the difference between filtering and sorting?

Sorting rearranges all your rows in a new order (A to Z, smallest to largest, earliest to latest). Filtering hides rows that don't match your criteria and shows only the ones you want. Sorting changes the order; filtering changes what's visible.

If I filter data and then copy it, do I copy the hidden rows too?

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

Can I save a spreadsheet with filters turned on?

Yes. When you save the file, the filter settings are saved with it. The next time you open the file, the filters are still there and still active. If you want to remove them before saving, turn off AutoFilter first.

What if I filter by mistake and can't remember what I hid?

Go to the Data menu and click AutoFilter to turn it off completely. All rows reappear and all filter settings are cleared. Then you can turn AutoFilter back on and start fresh if you need to filter again.