What filters do and how to turn them on

A filter in Excel lets you hide rows that don't match what you're looking for, so you see only the data you need. Instead of scrolling through thousands of rows, you click a dropdown arrow in a column header and pick which values to show. Excel keeps the hidden rows in place — it doesn't delete them — so you can turn the filter off anytime and see everything again.

To turn on filtering, click any cell in your data table, then go to the Data tab at the top of the ribbon. Click Filter (it looks like a funnel). Excel will add a small dropdown arrow to the header of every column in your table. That's it — you're ready to filter.

If the Filter button appears grayed out, make sure you've clicked inside your data table first. Excel needs to know which data you want to filter. If you've selected cells outside a table, the button won't work.

Key Takeaways

  • Click any cell in your data, go to the Data tab, and click Filter to add dropdown arrows to your column headers.
  • Click a dropdown arrow, uncheck the values you want to hide, and click OK to show only the rows you need.
  • You can filter multiple columns at once — each filter narrows down what the others show.
  • To remove a filter from one column, click its dropdown arrow and select Reset Filter.
  • Turning off the Filter button removes all dropdown arrows but doesn't change which rows are hidden.

Filtering a single column by value

Click the dropdown arrow in the column header you want to filter. A menu appears with a checkbox next to every unique value in that column. By default, all values are checked, meaning all rows show.

To hide rows with a specific value, uncheck the box next to that value. For example, if you have a column called "Status" with values like "Pending," "Complete," and "Cancelled," unchecking "Cancelled" will hide every row where Status is Cancelled. You'll see only Pending and Complete rows.

After you've unchecked the values you want to hide, click OK at the bottom of the menu. The dropdown arrow in that column header will turn blue to remind you a filter is active on it. Scroll through your data — the hidden rows are gone from view, but the row numbers will skip (you might see rows 1, 2, 3, 5, 6, 8 instead of consecutive numbers).

Filtering by number ranges and dates

For columns with numbers or dates, you can filter by range instead of picking individual values. Click the dropdown arrow in the column header, then click Number Filters (or Date Filters if it's a date column). A submenu appears with options like "Greater Than," "Less Than," "Between," and others.

Choose the condition you need. If you click "Between," a dialog box opens asking for a start value and an end value. For example, you could show only sales between $1,000 and $5,000, or only dates in the last 30 days. Type your values and click OK.

This approach is faster than unchecking hundreds of individual values. If your data has dates from 2020 to 2024 and you only want to see 2024, using "Greater Than or Equal To" with a date is much quicker than scrolling through and unchecking each year.

Using multiple filters at once

You can filter more than one column simultaneously. Each filter narrows down what the next one shows. For example, if you filter the "Region" column to show only "West," then filter the "Status" column to show only "Complete," you'll see only rows where Region is West AND Status is Complete.

The order doesn't matter — filtering Region first then Status gives the same result as filtering Status first then Region. Both dropdown arrows will turn blue to show filters are active on both columns.

To remove a single filter without affecting the others, click its dropdown arrow and select Reset Filter. The rows that filter was hiding will reappear, but the other filters stay in place. If you want to clear all filters at once, go to the Data tab and click Filter again — this removes all dropdown arrows and shows every row.

Finding and using the search box in filters

When you click a dropdown arrow, you'll see a search box at the top of the menu. If a column has hundreds of values, typing in this box is faster than scrolling and unchecking. Type part of the value you're looking for — Excel will show only the matching items in the list below.

For example, if you have a "City" column with hundreds of entries and you want to show only rows from cities starting with "San," type "San" in the search box. The list shrinks to show only San Francisco, San Diego, San Antonio, and so on. Then uncheck the ones you want to hide.

The search box is case-insensitive, so "san" and "San" find the same results. After you've narrowed the list, click OK to apply the filter.

Sorting while filtering

You can sort the visible rows without affecting your filter. Click the dropdown arrow in any column header and choose Sort A to Z (for text), Sort Z to A, Sort Smallest to Largest (for numbers), or Sort Largest to Smallest. The filtered rows will rearrange, but the hidden rows stay hidden.

This is useful when you've filtered to show only what you need and now want to organize it. For instance, filter to show only "Pending" orders, then sort by date to see the oldest ones first.

If you sort and then remove the filter, the entire table will be sorted by that column, not just the visible rows. The sort applies to all your data, not just what you were looking at.

Turning off filters and clearing your view

To remove all dropdown arrows from your headers, go to the Data tab and click Filter again. The button will no longer be highlighted, and the arrows disappear. All hidden rows become visible again.

If you want to keep the filter buttons but show all rows, click the dropdown arrow in any filtered column and select Reset Filter. This unhides the rows that column was hiding. If multiple columns have active filters, you'll need to reset each one individually, or you can click the Data tab and choose Clear (in some Excel versions) to reset all filters at once.

Removing a filter does not delete any data. It only changes what you see on screen. Your original data is unchanged and always recoverable.

Frequently Asked Questions

Why is the Filter button grayed out?

The Filter button only works when you've selected a cell inside a data table. Click anywhere in your data range and try again. If you've selected blank cells or cells outside your table, the button won't activate.

Can I filter text that contains a specific word?

Yes. Click the dropdown arrow and select Text Filters, then choose Contains. Type the word or phrase you're looking for, and Excel will show only rows where that column contains those characters anywhere in the cell.

What happens to my filter when I add new rows?

If you add new rows to your table, they won't automatically be included in the filter range. You may need to reapply the filter or extend your data range. The safest approach is to convert your data to an Excel table (select your data and press Ctrl+T), which automatically expands the filter range when you add rows.

Can I filter by color or formatting?

Yes, but only if you're using Excel's table feature. Convert your data to a table first (Ctrl+T), then click a dropdown arrow and select Filter by Color. You can then show or hide rows based on cell color or font color.

How do I show only the top 10 values in a column?

Click the dropdown arrow in the column header and select Number Filters (or Top 10 if it appears directly), then choose Top 10. A dialog opens where you can change "10" to any number you want. Click OK to show only the highest values in that column.