Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

7+1 Amazing Things You Can Do with Excel Filters

amazing things you can do with excel filters

Using a filter in excel is not a #rocketscience. Everyone out there knows that they can do a lot of things with filter option.

But, the bad news is they are not actually using it to the best or maybe they forget to make a habit to use it.

And, the good news is you can be one of those excel users who know how to use a filter in a smart way.

So, today in this post, I’ll show you 7+1 amazing things which you can do with excel filters and save a ton of time.

Click here to download the sample file which I have used here in examples. And now, let’s get started to explore these amazing options.

Table of Content

  1. Filter By Color
  2. Typing in the Search Bar
  3. Applying Condition With OR
  4. Filter Top 10 Values
  5. Using a Wild Card Character
  6. Pre-Defined Date Parameters
  7. Filter Values Above Average
  8. [Bonus Tip] Filter Values in Pivot Table

1. Filter By Color

The most creative way to filter values.

Whenever you have some cells in your worksheet which are highlighted with a color, you can easily filter them.

Let’s have look at the below sheet where you have some highlighted cell with red color in which you have some errors and you want to filter all those cells.

cell are highlight with red colorare highlight with red color

Here are the steps.

  • First of all, apply a filter to your data.
  • After that, open filter drop-down.
  • Go to Filter by color -> select the color for which you want to filter.

open filter option and go to filter with color

  • Once you click on the color, it will filter all the cells with the red color.

filter cells with red color

The best thing about this option is you can filter all those cells where you don’t have any color as well.

Quick Tip: If you have applied font color or cell color this filter will work in both ways.

2. Type in the Search Bar

Simple + Effective

If you are using excel 2010 or earlier version then you have a search bar in your filter drop down. You can use this search bar to find a value you want to filter.

Now, check out the below data table where you need to filter employees whose first name is “John”.

data to filter name john

Here are the steps.

  • Make sure to add filters to your data.
  • After that, open the filter drop down and click on the search bar.
  • Now, type the word “John” in the search bar.

enter value in filter search bar

  • As soon as you do that, it will filter all the values where word “John” is there.
  • In the end, click OK.

click ok to filter name john

The best thing about this option is it is so dynamic that it will give you results right away when you are typing your search.

Important Point: When you need to search for a date you can use the little drop down from the right side of the search bar to select whether you want to search for a year, month, or a date.

3. Add a Condition with OR

Filter with more than one criteria.

When you need to filter values if one of two given conditions is met or both of the conditions are met, you can do this by using OR in filters

Just think this way. You have data for all the employees in your company and you want to filter only for people whose name is “Jon” and “John”.

filter value with a condition

Now here, the condition is to filter cells in which have the name, Jon or John. So, follow these simple steps and make sure you have my sample file with you.

  • First of all, add filters to your data.
  • For the column where you want to filter data, open the filter drop down.
  • Now, go to -> Text Filters -> Custom Filters.

filter data with or custom filters

  • Here, you have a window to select filter options.
  • So, from this window, select “contains” from both of the drop downs.
  • And after that, in the input bar enter “Jon” and in second input bar enter “John”.
  • Select “Or” from the option buttons.

window to filter values with or condition

  • In the end, click OK.

Now, all the cell where you have “Jon” or “John” are filtered.

filter data with or condition

Quick Tip: Let’s say if you want to filter values based on two conditions, For Example: if you want to filter cells where you have both “Jon” and “John” in a single cell, you can use AND instead of OR.

4. Filter Top 10 Values

Get top 10 values in a few clicks.

If you have a large dataset and from that data, you want to check top 10 values then all you need a few clicks. No, you don’t have to create a pivot table.

Here are the steps.

  • Apply filters first.
  • And after that, open the filter drop down for the column where you want to filter values.
  • Go to -> Number Filters – > Top 10.

click on top 10 to filter top10 value from the data

  • This will give a pop-up window. And, from that window, you have to select following things.
    1. Top or Bottom Values: You can select top or bottom values to filter.
    2. Number of Values: You can also specify numbers of values you want to filter.
    3. Type of Filter: This is a smart option where you can select the way you want to filter values. Let’s say, if you want to filter values on a percentage basis then you can change this option from here.

pop up window to select top 10 values

  • Once you select your preferences, click OK.

So, once you click OK, all the values which are in top 10 will be filtered.

5. Using a Wildcard Character

Think out of the box.

Wildcard characters are all about partially matching and finding a text. And, you can also use wildcard characters for filtering values.

Let’s say from your employee’s list you want to search for the names which are starting from alphabet “J”. So, here are the steps you have to follow.

  • Again, apply filters to your data.
  • And then, open filter drop down.
  • Now, in the search bar type “J*”.
  • Once you type your text, it will instantly filter all the names which are starting from the letter “J“.

use wildcard characters to filter data in excel

  • After that, click OK.

You can also use this method with custom filter option as well. And, there are total three wildcard characters which you can use in excel.

6. Pre-Defined Date Parameters

A smart way to filter dates.

You know, when it come to dates, excel is super smart. There is already you have a lot of options to filter dates. Just have look at below list.

date options to filter data in excel

You can use any of them, they all are super useful. But, let me tell you few of them which I am sure will be a game changer for you.

  1. Year To Date: Super useful if you want to filter all the date from the starting of the year to till date.
  2. All Dates in a Period: It will help you to filter all the dates within a particular month or quarter.

You have total 21 + custom filter options for dates. And, if you want to filter a custom date you can use a custom filter.

7. Filter Above/Below Average Values

A handy option to check data insight.

Let’s say you want to filter values which are above average. What will you do? Add a formula? Well, you have a decent option for this.

Here are the steps.

  • Apply filters on the column where you have values.
  • After that, open filter drop- down, go to “Number Filters” and click on “Above Average”.

click on above average to filter data above average

  • And, once you do that it will filter all the values which are above average.

By using the same method you can also filter values which are below average. Just click on “Below Average” instead of above average.

[Bonus Tip] Filter Values in Pivot Tables

The best way to filter values in a pivot table is by using a slicer. You can insert a slicer in excel using following steps.

  • Once you insert a pivot table click on any of the cells in it.
  • Now, go to Analyze Tab -> Filter -> “Slicer”.

select slicer from analyze tab to filter data in pivot table

  • It will instantly give you a pop-up window to select the field you want to use in the slicer.

select field to use in slicer to filter data in pivot table

  • Click OK.

Now, you can use this slicer to filter all the values in your pivot table.

filter data in pivot table with a silcer

The best part about slicer is you can check anytime how many values you have there to use as a filter. You can also link your all the pivot tables with a single slicer and filter all of them in one click.

Sample File

download sample file to learn more about this tips

Conclusion

As I said, there are a lot of options you have in excel filter which probably you are not using yet. If you start using some of them which are highly relevant to your work, you can increase your productivity by 10X.

And, the options which I have mentioned above are a super time saver for you. You can use them to skyrocket your productivity in no time.

Now, it’s your time to speak your mind. Do you have any other filter option which you use frequently and you want to share with me?

Just go to the comment section and share with me.


  • mma173

    Slicers are also available in regular Excel tables.

    • Puneet Gogia

      Yup. 🙂