Best 8 Tips to Filter Data in Excel

Using a filter in Excel is not a #rocketscience. Everyone 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 knows how to use a filter in a smart way.

So, today in this post, I’ll show you eight amazing things which you can do with 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.

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 a 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.
3 filter cells with red color

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

Quick Tip: 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.

2. Type in the Search Bar

Simple and 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.

 Quick Tip: 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.

Want to learn some time saver tips? Then you should download this e-book.

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 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.
  • In the end, click OK.

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

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.

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.
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 a name list you want to search for the names which are starting from alphabet “J”.

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 a 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.

8. Filter Values in Pivot Tables

The best way to filter values in a pivot table is by using a slicer. You can link multiple pivot tables to one filter with a slicer.

  • Once you create 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.

Sample File

Download this sample file from here to learn more.

Conclusion

As I said, there are a lot of options you have in filters 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 5X.

And, the options which I have mentioned above are a super time saver for you.

Now, it’s your time to speak your mind. Do you have any other filter option which you use frequently? Please share with me in the comment section, I'd love to hear from you.

And, please don't forget to share this tip with your friends. 

Must Read Next

  1. Count Unique Values in Excel using Advanced Filter
  2. A Simple Form to Enter Data in a Table in Excel
  3. Advanced Find and Replace Tips
  4. Combine Multiple Worksheets into a Single Worksheet
  5. How to Multiply Using Paste Special Option 

  • mma173

    Slicers are also available in regular Excel tables.

    • Puneet Gogia

      Yup. 🙂