How to Use Filters in Excel

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

Planning, risk and strategy in business, businessman and engineer gambling placing wooden block on a tower.
Sort out the data you want from what you don't want. krisanapong detraphiphat / Getty Images

Filtering data in a spreadsheet means to allow only certain data to be displayed. This function is useful when you want to focus only on specific information in a large dataset or table. Filtering doesn't remove or modify data; it just changes which records appear on your screen.

This article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Office 365.

How Filtering Works

Filters work with records or rows of data in the worksheet. The conditions you set are compared with one or more fields in the record. If the conditions are met, the record is displayed.

For example, say you're a sales manager and you want to create a program to pair your lower-performing salespeople with higher-performing ones in mentoring relationships. You could use a filter to find the highest- and lowest-performing salespeople based on their sales totals.

In this example, you would be filtering numeric data, which can be based on:

  • Whether the data equal a certain number
  • Whether the data are greater or less than a specific number
  • The data are above or below the average value of the data as a whole

You can also filter text data, which can be based on:

  • Whether the data match a certain word
  • Whether the data are a word containing one or more letters
  • Whether the data are a word that begins or ends with a specific letter of the alphabet

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

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