Software & Apps MS Office How to Use Filters in Excel This feature enables you to view only data that meets certain criteria By Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. our editorial process Ted French Updated January 12, 2020 krisanapong detraphiphat / Getty Images MS Office Excel Word Powerpoint Outlook Tweet Share Email 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 numberWhether the data are greater or less than a specific numberThe 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 wordWhether the data are a word containing one or more lettersWhether 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. Open the spreadsheet that contains the data you want to filter. If the Home tab isn't already displayed, on the ribbon select Home. In the Editing group, select Sort & Filter > Filter. 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. Select Number Filters or Text Filters to see options for filtering your data. 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. In the Custom AutoFilter dialog box, in the Greater Than field, type 19,500. Select OK. 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. 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. To remove the filter, select the same filter arrow and choose Clear Filter. 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.