How to Use Filters in Excel

Filtering data in a spreadsheet allows only certain data to display. This function is useful when you want to focus on specific information in a large dataset or table. In this guide, we show you how to filter data in Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Microsoft 365, including how filtering works and some best practices.

How to Filter Data in Excel

If you want to filter data in an Excel spreadsheet, here's how to do it.

1. Open the spreadsheet that contains the data you want to filter.

2. If the Home tab isn't already displayed, on the ribbon select Home. In the Editing group, select Sort & Filter > Filter.

3. Each header in your spreadsheet now displays a small drop-down arrow. Select an arrow to filter by the information in that column. A filtering dialog box appears.

4. Select Number Filters or Text Filters to see options for filtering your data.

5. Continuing the example from above, as the sales manager you want to select those salespeople whose Q4 earnings were more than $19,500. From the options menu, select Greater Than.

6. In the Custom AutoFilter dialog box, in the Greater Than field, type 19,500. Select OK.

7. Excel displays only the records whose Q4 values are greater than $19,500.

   Notice in the number column to the far left there are double lines between line numbers if rows of data aren't displayed between those rows. Also, notice that the down arrow in the Q4 header now displays a filter icon to show data is being filtered based on data in that column.

8. If you want to filter data a different way, go back to step 5 and choose a different selection from the menu. Then, follow the screen prompts to filter your data the way you want.

9. To remove the filter, select the same filter arrow and choose Clear Filter.

How Filtering Works

Best Practices for Filtering

Save yourself some hassle by following best-practice guidelines for working with filtered data:

• Unless there's a good reason for it, don't save a shared spreadsheet with filters active. Other users may not notice that the file is filtered.
• Although you can filter on several columns simultaneously, these filters are additive, not exclusive. In other words, filtering a contact list to show everyone in the State of California who are older than age 60 will yield everyone who's over 60 in California. Such a filter will not show you the all of 60-year-olds or all of the Californians in your spreadsheet.
• Text filters only work as well as the underlying data allows. Inconsistent data leads to misleading or incorrect filtered results. For example, filtering for people who live in Illinois will not catch records for people who live in "IL" or in misspelled "Ilinois."