How a Filter Works in Excel Spreadsheets

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 set conditions so that only certain data is displayed; it is done to make it easier to focus on specific information in a large dataset or table of data. Filtering does not remove or modify data; it merely changes which rows or columns appear in the active Excel worksheet.

Note: These instructions apply to Excel versions 2019, 2016, 2013, 2010, and Excel for Office 365.

Filtering Data Records

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

If the conditions are not met, the record is filtered out so that it isn't displayed with the rest of the data records.

Screenshot of Excel showing filtering on a spreadsheet

Data filtering follows two different approaches depending on the type of data being filtered—numeric or text data.

Filtering Numeric Data

Numerical data can be filtered 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

Filtering Text Data

Text data can be filtered 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 find yourself wanting to filter data in your Excel spreadsheet, the process is thankfully straightforward, requiring only a few mouse clicks.

Screenshot of Excel showing different filter options
  1. Open a spreadsheet that contains data that you wish to filter — the data table in our example uses three columns for each category, and a single header row. Click any of the cells in the header row to enable filtering.

  2. Select the Sort & Filter drop-down in the Home tab of the ribbon bar.

  3. Select the Filter option.

  4. Your headers should now all display a small drop-down arrow. Click an arrow to open the Filter dialog box.

  5. Check the types of data you would like to filter out for your spreadsheet.

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.
  • Complex text filters work as well as the underlying data will allow. Messy data will lead to filtered results that may be misleading or incorrect. For example, filtering for people who live in Illinois will not catch the people who live in 'IL' or the mistyped 'Ilinois.'

Exercise caution when sorting filtered data. Sorting partially filtered data will lead to a restructuring of the data file — a situation usually best avoided. If you must sort a filtered dataset, copy the filtered data to a new worksheet and then sort it.