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!