Filters let you hide rows that don't match what you're looking for

A filter in Excel is a tool that hides rows in your spreadsheet based on rules you set. If you have a list of 500 transactions and you only want to see purchases from January, a filter shows you just those rows and hides the rest. The hidden rows are still there — you haven't deleted anything — but they're out of sight until you change or remove the filter.

Filters work on columns. You pick a column, tell Excel what values to show or hide, and it does the work. You can filter one column or several at the same time. This is different from sorting, which rearranges your rows in a new order. Filtering just makes some rows invisible.

Key Takeaways

  • Turn on filtering by selecting your data and clicking the Filter button in the Data tab, which adds dropdown arrows to each column header.
  • Click a dropdown arrow to choose which values to show — uncheck the ones you want to hide, then click OK.
  • You can filter by text (exact matches or partial matches), by number (greater than, less than, between), or by date (before, after, or within a range).
  • Multiple filters work together — if you filter two columns, Excel shows only rows that match both conditions at the same time.
  • Removing a filter shows all rows again, but your data stays in the same order it was before you filtered.

How to turn on filtering for your data

First, select the data you want to filter. Click any cell in your table, then go to the Data tab at the top of the ribbon. Click the button labeled Filter (it looks like a funnel). Excel will add a small dropdown arrow to the header of each column in your table.

If your data doesn't have a header row — a row at the top with column names — Excel will treat the first row of your data as headers. If your first row is actual data, not labels, you may want to add headers before filtering. This makes it much clearer what each column contains.

Once the dropdown arrows appear, your filter is ready to use. You haven't hidden anything yet. The arrows are just controls waiting for you to click them.

Filtering by exact values in a column

Click the dropdown arrow in the column you want to filter. A menu appears showing every unique value in that column, each with a checkbox next to it. By default, all boxes are checked, meaning all values are visible.

To hide rows with a specific value, uncheck the box next to that value. For example, if your column lists product names and you only want to see "Widget A", uncheck every other product. Then click OK at the bottom of the menu. Excel hides all rows where the product is not "Widget A".

You can uncheck as many values as you want. If you have 20 different products and you want to see only 3 of them, uncheck the other 17. The menu also has a search box at the top — type a word to find it quickly instead of scrolling through a long list.

Filtering by number ranges and comparisons

When you filter a column that contains numbers, the dropdown menu includes an option called Number Filters. Click that to see choices like "Greater Than", "Less Than", "Between", and "Equals". These let you set up a rule instead of picking individual values.

For example, if your column shows sales amounts and you want to see only sales over $1,000, click Number Filters, then Greater Than. A dialog box appears where you type 1000. Click OK, and Excel shows only rows where the sales amount is more than $1,000.

The "Between" option is useful when you want a range — say, sales between $500 and $2,000. You enter both numbers, and Excel shows only rows that fall within that range.

Filtering by dates

Date columns work similarly to number columns. Click the dropdown arrow in a date column and look for Date Filters. You'll see options like "Before", "After", "Between", and "This Year".

If you want to see transactions from the last 30 days, click Date Filters, then After, and enter a date 30 days ago. If you want a specific month, use Between and enter the first and last day of that month. Excel shows only the rows that match your date rule.

Using multiple filters at once

You can filter more than one column in the same table. Each filter works independently, but Excel shows only rows that match all of your filters together. This is called an AND filter.

For example, say you have a table with columns for Product, Region, and Date. You filter Product to show only "Widget A", Region to show only "North", and Date to show only 2024. Excel will show you rows where the product is Widget A and the region is North and the year is 2024. Any row that doesn't match all three conditions is hidden.

To add another filter, click the dropdown arrow in a different column and set up your rule the same way. To remove a filter from one column without removing the others, click its dropdown arrow again and click Clear Filter at the top of the menu.

Removing filters and showing all rows again

To remove all filters at once and show every row, go to the Data tab and click Filter again. This turns off filtering entirely and removes the dropdown arrows from your headers. All rows reappear in the order they were in before you filtered.

If you want to keep the filter buttons but just show all rows temporarily, click the dropdown arrow in any filtered column and select Clear Filter. This removes that one filter but leaves the others in place and keeps the dropdown arrows visible.

Removing a filter does not undo any changes you made to your data. If you edited cells while rows were hidden, those edits stay. Filtering only controls what you see, not what's actually in your spreadsheet.

Frequently Asked Questions

Can I filter text that's only part of a word?

Yes. Click the dropdown arrow in a text column and look for Text Filters. Choose Contains and type the partial word. For example, if you type "son" it will show rows containing "person", "reason", and "season" — anything with those letters in that order.

What if I filter a column and then sort it — does the sort explore to hidden rows too?

No. When you sort a filtered table, Excel sorts only the visible rows. The hidden rows stay hidden and don't move. This is useful when you want to see your filtered results in a specific order.

Can I save a filter so it comes back when I open the file again?

Yes. When you save your Excel file, the filters you've set up are saved with it. The next time you open the file, the same rows will be hidden. If you want to show all rows before saving, remove the filters first.

If I copy filtered data, does it copy the hidden rows too?

No. When you select and copy filtered data, Excel copies only the visible rows. The hidden rows are not included in the copy. This is helpful when you want to paste only the filtered results into another sheet or file.

Can I filter by color or formatting instead of by the actual values?

Yes, but only if you're using conditional formatting or manually colored cells. Click the dropdown arrow and look for Filter by Color. You can then choose to show only cells with a specific background color or font color. This is less common than filtering by value, but it's useful when your spreadsheet uses color to mark important data.