Why should you use the Filter Tool?
The Filter tool lets you quickly filter on the relevant data in your table. It can help you filter by text values, numbers, dates, colors, and more!
How to use the Filter Tool?
To apply filtering on a range, just select one of the cells in that range, and click Sort & Filter in the Home tab:
That’s it, now you can filter this range!
Tip – You can also use the CTRL + SHIFT + L shortcut to apply Filtering. Read here more about useful Excel shortcuts!
Basic Filtering examples
Filtering on a single value
Okay, let’s see what we can do with this tool.
First, let’s ask Excel to show us only data about the Dish Washers. We can do this by first deselecting all values, and then selecting only the “Dish Washer” option:
Of course, you can select more than one value to filter in your column.
How do I go back to the original table after applying a filter?
After applying a filter, we can quickly revert by one of the following methods:
First method: Clear the specific filter applied:
Second method: Clear ALL filters from the entire worksheet:
This method is useful when we have multiple filters applied and we want to quickly get rid of all filters in the worksheet. In the Home tab, click the Sort & Filter button, then select Clear:
Using Text/Value Filters
Excel understands whether the data in your column contains text, numbers, or even dates, and can offer customized filtering options.
What if we want to see results only for customers whose names start with the letter “J”? No problem!
Click the filter on the “Customer Name” field – Text Filters – Begins With… Then type J and click OK:
You can use additional Text filters, such as “Contains”, “Does Not Contain” etc.
Now, let’s see how we can use the Filter tool to show us only items sold before October 15, 2020, and with a price lower than 300:
Notice how we applied two filters on different columns? The results left are only the rows where the date is before October 15, 2020, and the price is less than 300!
Explore the various filtering options for number and date cells – You can basically filter by any criteria you can imagine!
Filter based on cell color
Another great functionality in Excel’s Filter tool is the option to filter by color.
Let’s say we want to filter the following table to show only Yellow rows – Just Filter one of the columns, Select Filter by color, then select the desired color (Yellow):
Advanced Filtering options with Excel
So we have basically covered the most important filtering techniques for Excel beginners.
If you want to learn the master filtering like the Excel Pros – Please continue reading 🙂
Filtering using Excel Wildcards
We can use Excel Wildcards in our filter!
To make a long story short, we can use two types of Wildcards in Excel:
? (Question mark) – Match one character
* (Asterisk) – Match zero or more characters
Let’s say we want to filter all the rows containing “New Delhi”. Did you notice that there are also certain rows with a dash between the “New” and “Delhi”? If we want to catch both types in one shot, we can use wildcards, and in this example, type: new?delhi. The ? question mark tells Excel to look for any single character, and it matches both the space character and the dash character!
Advanced Filtering in Excel
In some cases, you want to filter based on multiple values at once. For such cases, read our Advanced Filtering Tool tutorial!
Practice Filter tool in Excel
Now it’s time to put theory into practice! Click here to download our Filter exercise for Excel!