How a Filter Works in Excel Spreadsheets

Screenshot of Excel data filter
Ted French

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.

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.

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

Copying Filtered Records

In addition to temporarily hiding records, Excel gives you options to copy the desired data to a separate area of the worksheet. Often this procedure is done when a permanent copy of the filtered list meets some sort of business requirement.

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 shave 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 give you everyone who's over 60 in California. Such a filter will not show you the 60-year-olds or the Californians.
  • 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 who live in "Ilinois" or "Illenois."
  • 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.