How to Use Filters in Excel

This feature enables you to view only data that meets certain criteria

What to Know

  • Select Home > Sort & Filter > Filter. Select a header drop-down menu and choose Number Filters or Text Filters to view filter options.
  • Remove a filter: Select the same header's filter arrow and choose Clear Filter.
  • Filters work with records or rows of data in a worksheet. Conditions you set are compared with one or more fields in the record.

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.

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

    MS Excel spreadsheet with Sort & Filter menu displayed
  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.

    MS Excel with filtering dialog box displayed
  4. Select Number Filters or Text Filters to see options for filtering your data.

    MS Excel with filters dialog box displayed
  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.

    MS Excel with filtering menu displayed
  6. In the Custom AutoFilter dialog box, in the Greater Than field, type 19,500. Select OK.

    MS Excel with Custom AutoFilter dialog box displayed
  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.

    MS Excel with data filtered
  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.

    MS Excel with filter dialog box displayed

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."

Use caution when sorting filtered data. Sorting partially filtered data may cause a restructuring of the data file, which can lead to additional problems. If you must sort a filtered dataset, copy the filtered data to a new worksheet and then sort it.

Was this page helpful?