What filtering does and when to turn it on

A filter in Excel hides rows that don't match what you're looking for, so you see only the data you want to work with. If you have a spreadsheet with 500 rows of sales records and you need to see only the ones from March, a filter lets you do that in seconds instead of scrolling through the whole sheet.

Filtering doesn't delete anything — it just hides rows temporarily. When you remove the filter, all your data comes back. This makes it safe to use on any spreadsheet, even one you're sharing with others.

You'll use filtering most when you have a table with headers (the first row that names each column) and you want to focus on a subset of that data. It works on any size table, from 10 rows to 10,000.

Key Takeaways

  • Turn on filtering by selecting any cell in your data table, then clicking the Filter button in the Data tab on the ribbon.
  • Once filtering is on, dropdown arrows appear in the header row, and you click those arrows to choose which rows to show.
  • You can filter by exact match (show only "March"), by text pattern (show anything containing "North"), or by number range (show sales over $1,000).
  • Filtering hides rows but doesn't delete them, so you can turn it off anytime to see your full data again.
  • You can filter on multiple columns at once — for example, show only March sales in the North region.

How to turn filtering on

Click any cell inside your data table — it doesn't matter which one. Then look at the top of the screen for the ribbon menu. Click the Data tab.

In the Data tab, look for the button labeled Filter (it usually has a funnel icon). Click it once. Excel will add dropdown arrows to every cell in your header row — the first row of your table.

If your data doesn't have a header row yet, add one before you turn on filtering. The header row is what tells Excel which column is which, and the filter buttons appear there.

How to filter by a single column

Once filtering is on, click the dropdown arrow in the column you want to filter. A menu appears with a list of every unique value in that column.

If you have a column called "Month" with values like January, February, March, and so on, the dropdown will show all of those. Uncheck the box next to any month you want to hide. For example, uncheck January and February, and leave March checked. Then click OK. Now Excel shows only the rows where the Month column says March.

To show all months again, click the dropdown arrow again and click "Select All" at the top of the menu, then click OK.

How to filter by text patterns or number ranges

Sometimes you don't want an exact match — you want anything that starts with a certain letter, or any number above a certain amount. Click the dropdown arrow in the column you want to filter, then look for an option called Text Filters (if the column has text) or Number Filters (if it has numbers).

A submenu appears with options like "Contains", "Begins With", "Ends With", or "Greater Than". Click the one you need. A dialog box opens where you type what you're looking for. For example, if you choose "Contains" and type "North", Excel will show only rows where that column contains the word North anywhere in the text.

For numbers, you can use "Greater Than" to show only sales over $5,000, or "Between" to show sales from $5,000 to $10,000. Type the numbers you want and click OK.

How to filter on multiple columns at once

You can narrow down your data even more by filtering on two or more columns. For example, show only March sales in the North region.

Filter the first column the way you normally would — click its dropdown, choose what you want to see, and click OK. Then click the dropdown arrow in the second column and filter that one too. Excel keeps both filters active, so now you see only rows that match both conditions.

You can add as many filters as you need. Each column you filter shows a small blue arrow in its header to remind you that a filter is active on that column.

How to remove or reset a filter

To turn off all filters at once and see your full data again, click the Data tab and click the Filter button again. The dropdown arrows disappear from the header row, and all hidden rows come back.

If you want to keep filtering on but reset just one column to show all its values again, click that column's dropdown arrow and click "Select All", then click OK. That column will show everything, but your other filters stay active.

To clear a specific filter and start over on just that column, click its dropdown arrow, then look for "Clear Filter" or a similar option near the top of the menu.

What to watch out for when filtering

If you copy data from a filtered spreadsheet, Excel copies only the visible rows — the hidden ones don't come along. This is usually what you want, but it can surprise you if you're not expecting it. If you need to copy everything, turn off the filter first.

Formulas that reference your data (like SUM or AVERAGE) will still count the hidden rows unless you use special functions like SUBTOTAL. If you're doing calculations on filtered data and the numbers seem wrong, check whether you need to switch to a function that ignores hidden rows.

If you sort a filtered spreadsheet, Excel sorts only the visible rows. The hidden rows stay hidden but don't move. This usually doesn't cause problems, but it's worth knowing.

Frequently Asked Questions

Can I filter by color or formatting?

Yes. Click the dropdown arrow in the column, then look for "Filter by Color" or similar. You can then choose to show only cells with a certain background color or text color. This is useful if you've color-coded your data by status or category.

What if I filter and see no rows at all?

This means no rows match your filter criteria. Check your filter settings — you may have been too strict. For example, if you filtered for "March" but your data says "March 2024", the exact-match filter won't find it. Use "Contains" instead, or adjust your filter to match what's actually in the cells.

Does filtering work on shared spreadsheets?

Yes, but each person sees their own filters. If you filter a shared spreadsheet and send it to someone else, they see the unfiltered version unless you save it with the filter already applied. When they open it, they can add their own filters without affecting what you see.

Can I save a spreadsheet with filters on?

Yes. When you save a file with filters active, Excel remembers those filters. The next time you open the file, the filters are still there and the data is still hidden. You can turn them off anytime by clicking the Filter button again.