The Best 8 Tips to Filter Data in Excel Like a PRO

Using a filter in Excel is not a #ROCKETSCIENCE.

Everyone knows that they can do a lot of things with filter option.

The bad news is they are not actually using it up to the best…

…or maybe they forget to make a habit to use it.

But the good news is you can be one of those users who knows how to use a filter in a smart way.

And today in this post, I’ll show you eight amazing things which you can do with filters and save a ton of time.

So without any further ado, let's get started and make sure to download this sample file from here to follow along.

Table of Content

  • Using a Wildcard Character
  • Pre-Defined Date Parameters
  • Filter Above/Below Average Values
  • Filter Values in Pivot Tables

Get Better at Excel in 2018

A bundle of E-Books you need to thrive in Excel this year.

1. Filter By Color

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

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.

3. Add a Condition with OR

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
  • 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

If you have a large data set 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 one of the following things.
    • Top or Bottom Values: You can select top or bottom values to filter.
    • Number of Values: You can also specify numbers of values you want to filter.
    • 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

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

6. Pre-Defined Date Parameters

You know, when it come to dates, Excel is super smart. Just have a look at the 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

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

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

Conclusion

Content Protection by DMCA.com
2018-11-16T06:13:21+00:00

3 Comments

  1. Cody G. 18 Sep, 18 at 9:03 pm - Reply

    If I have a table with 2000 unique text fields (let’s say a list of 2000 different names but each name could have multiple different line items for other criteria), is there a way to filter to 100 of those names more quickly than pointing and clicking to them in a drop down filter or on a slicer?

  2. mma173 17 Mar, 17 at 12:27 am - Reply

    Slicers are also available in regular Excel tables.

    • Puneet Gogia 17 Mar, 17 at 4:38 am - Reply

      Yup. 🙂

Leave A Comment