How to Filter in Excel? A Step-by-Step Guide
Note: This guide on how to filter in Excel is suitable for all Excel versions including Office 365.
The data filter is probably the most underrated feature in Excel. It is very robust and gets more things done easily than its counterparts. I feel that It doesn’t get the attention it deserves, especially in beginner’s Excel guides.
That is why we have made this essential guide on how to use the data filter in Excel, covering all its lesser-known but important aspects.
You’ll learn:
- How to Filter in Excel ? – A Simple Example
- How to Use the Excel Filter for Text, Numbers and Dates?
- How to Filter in Excel Based on Multiple criteria?
- How to Use the Sheet View Feature in Excel Filter?
- How to Clear the Filter in Excel?
How to Filter in Excel ? — A Simple Example
The Filter option in Excel selects a part of the table and displays it only if it meets certain criteria. Use the filter option to search for a record or a particular row(s) when the table is large.
To use the Excel filter, follow these five steps:
- Select the criteria or the column header based on which you need to filter the rows. Then select the Data tab in the Excel ribbon and locate the Filter option.
- When you click on Filter, small arrows will appear in the columns. Clicking on it will take you to a drop-down menu with options to filter the data.
- All the column headers get an arrow like this
. If you click on any one of them, it will display all the values based on which you can filter the data. - From this list, select all the data you want to view and click OK. This will display the required information and temporarily hide other information.
. If you click on any one of them, it will display all the values based on which you can filter the data.
- You will see the Filter iconon the column header to indicate that a filter is active and applied on the corresponding column.
on the column header to indicate that a filter is active and applied on the corresponding column. Related:
Dynamic Array Functions in Excel
How to Custom Sort Excel Data? 2 Easy Steps
How to Use the Excel Filter for Text, Numbers and Dates?
In Excel, you can create four kinds of filters based on text, numbers, date, and based on criteria. These filters are mutually exclusive, which means when one filter is used another cannot be applied. Let us see how to use them one by one.
Filters Based On Text Values
Text-based filters are very common. In this example we want the data to be filtered only for the customers who purchased a monitor.
To do this, click on the drop-down menu, to view all the items listed in the table.
Now, select only the items you are interested in. Excel will filter and display the relevant data.
You can also filter data using the following predefined logical criteria: Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain.
Filter Based on Numeric Values
Use this option when you need to filter based on numeric values. If you have already clicked on the Filter option, the drop-down menu appears at the column header. Otherwise, click on the Data tab and then click on the Filter option to get the arrow icon, next to the column header.
When you click on the drop-down menu, the values in that column are displayed. Now you can select the ones you are interested in. Here too, there are some logical filter options like Equals, Does Not Equal, Greater Than, Less Than, Between, Top 10, Above Average, Below Average.
These will come in handy when you need to quickly filter based on such criteria.
Below these options, you can find a Customized Filter that can be used to still more manipulate the numbers to filter data.
Also Read:
How to Record a Macro in Excel? In 6 Easy Steps (For Dummies)
How to Delete a Named Range in Excel? 3 Easy Methods
3 Best Methods to Find Duplicates in Excel
Filter Based On Date Values
You can filter based on the date values in the column. When you click on the drop-down menu in the date column, the list of values in the table appears.
You can select any of the options to get the details based on the particular date.
You have date filters as well such as Equals, Before, After, Between, Tomorrow, etc.
How to Filter in Excel Based on Multiple criteria?
You can filter the data based on different criteria across two columns. Let’s take the example below. We have a view of the sheet which has the data of who purchased the laptops.
You can again search the list based on the date of purchase and filter out the details of laptops that were purchased in the year 2020.
How to Use the Sheet View Feature in Excel Filter?
The sheet where the filtered results appear is the temporary sheet. You can save many such temporary sheets.
You can find this option in the View tab→Sheet View. Click on Keep and the view will be saved as View1 for the first sheet. If you don’t want to save the view, click on exit.
How to Clear the Filter in Excel?
You can remove the filters applied to the columns simply by clicking on the Filter button again or the Clear button next to Filter.
Or you can clear the filter by clicking on the Clear Filter option that you get after applying the filter.
Suggested Reads:
How to Add a Watermark in Excel? 2 Easy Methods
How to Remove Hyperlinks in Excel? 3 Easy Methods
How to Use the Format Painter Excel Feature? — 3 Bonus Tips
Closing Thoughts
This article explained everything about how to filter in Excel. Filters are widely used to quickly view and extract relevant information from a database. It is one of the must-know Excel skills for everyone.
We hope that you found this article useful. Please visit our free resources centre for more high-quality Excel guides.
Ready to take the next step and hone your skills in Excel?
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!