How a Filter Works in Excel Spreadsheets

Excel Data Filter
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 database or table of data.

Filtering Data Records

Filters works with records or rows of data in the database.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.

Filtering does not permanently remove records, it just temporarily hides them from view.

Data filtering is broken down into two groups depending on the type of data in the database - numeric or text data.

Filtering Numeric Data

Numerical data can be filtered based on:

  • whether or not the data equals a certain number
  • whether or not the data is greater than, less than a specific number
  • the data is above or below the average value of the data as a whole

Filtering Text Data

Text data can be filtered based on:

  • whether or not the data matches a certain word
  • whether the data is a word containing one or more letters
  • whether the data is 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 is done when a permanent copy of the filtered list is desired.


  • A phone book is an example of a database. Each row or listing on a page is a record containing all of the information about a person/business - such as their name, address, and phone number.
  • Phone books are organized by location - usually by cities and towns. This is a simple type of filtering. Only those people/businesses with addresses in a town or city are to be found in that section of the phone book.