Filtering data in a spreadsheet means setting conditions so that only certain data is displayed; this is to make it easier to focus on specific information in a large dataset or data table. Filtering does not remove or change any data; it simply changes which rows or columns appear in the active Excel worksheet.
Remark † These instructions apply to versions of Excel 2019, 2016, 2013, 2010, and Excel for Office 365.
- Filter data records
- Filter data in Excel
- Practical tips for filtering
Filter data records
Filters work on records or rows of data in a worksheet. The conditions set are compared with one or more fields in the record. If the conditions are met, the entry is displayed.
If the conditions are not met, the record is filtered so that it does not appear with the rest of the data records.
Data filtering is performed in accordance with two different approaches, depending on the type of data being filtered: numeric or text data.
Filter Numeric Data
Numeric data can be filtered based on:
Does the data match a certain number?
Whether the data is greater or less than a certain number
Data above or below the mean of the data as a whole
Filter text data
Text data can be filtered based on:
Does the data match a specific word?
Whether the data is a word containing one or more letters
Whether the data is a word that starts or ends with a specific letter of the alphabet
Filter data in Excel
Fortunately, if you want to filter the data in your Excel spreadsheet, the process is simple and only takes a few clicks.
Open Spreadsheet It contains the data you want to filter – the data table in our example uses three columns for each category and one header row. Click any of the cells in the title bar to enable filtering.
Open the drop-down list Sort and filter tab House on the tape panel †
Choose an option Filter †
Your headings should now display a small drop-down arrow. Click on the arrow to open filter dialog †
Check data types that you want to filter for your table.
Practical tips for filtering
Save yourself the trouble by following these best practices for working with filtered data:
Don’t keep a shared table with active filters unless there’s a good reason for it. Other users may not notice that the file has been filtered.
While you can filter multiple columns at the same time, these filters are additive, not exclusive. In other words, by filtering the contact list to show everyone in California and over 60, we get everyone over 60 in California. Such a filter will not let you see all 60-year-olds or all Californians in your table.
Complex text filters work in the same way as the original data. Dirty data results in filtered results that can be misleading or inaccurate. For example, if you filter for people who live in Illinois, people who live in “IL” or “Illinois” will not be spelled correctly.
Be careful when sorting filtered data. Sorting partially filtered data will result in a restructuring of the data file – it is usually best to avoid this situation. If you need to sort a filtered dataset, copy the filtered data to a new worksheet and sort it.