Filter-Data-in-Excel

Well, I guess every Excel user know how important filter option is in Excel file. Using the filter in Excel help the users to perform a lot of things in easily.

But this is something that is underestimated and not utilized by every Excel user. Many of the Excel users won’t know the best of using it and carrying out various functions easily in Excel.

But, the good news is that today in this article I am going to describe the amazing thing that you can do with Excel. Here learn about the Excel filters and save your valuable time.

Not happy with your MS Excel performance?
Don't Miss The BEST TOOL To FIX .xls/.xlsx Errors & Issues

 Check them out…

 

Easy Ways to Filter Data in Excel:

 

1. Enter in the Search Bar

 

This first way is simple and highly effective. Well if you are utilizing Excel 2010 and later version than in your filter drop down menu search bar is available.

And, then check the below data table where you need to filter results. You can filter it by names for example “Sam”.

Here follow the steps:

  • First, add filters to your data.
  • Then, open the filter drop down > click on the search bar.
  • In the search bar, type the name that you want to filter Eg. “Sam”

  • As you do that, this will filter the entire values where word “Sam” is presented.
  • Lastly, click OK.

The best thing about the result is it is highly active and gives the results right at the time when you type your search.

#Bonus Tip: if you need to search for date make use of the little drop down from the search bar right side to choose if you need to search for a year, month or date.

 

2. Filter By Colour

 

Filter by colour is the highly creative technique to filter values. When some cells in the worksheet are highlighted with colour, it can be easily filtered out.

The highlighted colour is extremely easy to filter out names, data or some errors that you want to filter all those cells.

Follow the steps to do so:

  • First, to your data apply a filter.
  • Then open the filter drop-down.
  • And go to Filter by colour > choose the colour for which you want to filter.

  • Since as you click on the colour, this will filter the entire cells with the red colour

The best thing about the filter option is user can filter the cells where there is no colour.

 

3. Add a Condition with OR

 

This filter option works best with more than one situation. You can utilize this when require filtering values if one of two given circumstances is met or both the conditions are met, then you can do this by utilizing OR in Excel filters.

For example: In your entire company data, you need to filter only for the people whose name is “Sam” and “Sammy”. Then this will help you to do so easily.

Know here follow the steps to do so:

  • Very firstly, add filters to your data.
  • Then for the column where you need to filter data > open filter drop down.
  • And go to > Text Filters > Custom Filters.

  • A window appears to select filters options.
  • From this window > choose “contains” from both the drop downs.
  • Next, in the input bar type “Sam” and in second input bar enter “Sammy”.
  • From the options button > choose “Or”.

  • And lastly, click OK.

Now, from the entire cell where you are having “Sam” and “Sammy” are filtered easily.

#Bonus Tip: Well, if you want to filter values based on two conditions for instance: if you want to filter cells where you are having both “Sam” & “Sammy” in a single cell, make use of AND, instead of using OR.

 

4. Pre-Defined Date Parameters

This is the smartest tip to filter dates easily. There are a lot of options that you can utilize to filter dates and when it comes to date Excel is very good.

At the below list,  you can utilize them, they are highly useful.

  • Year to Date: This is highly useful if you want to filter the entire data from the year starting to till date.
  • All Dates in a Period: This helps you to filter entire dates within a particular month or quarter.

There are total more than 21 custom filter options for dates. And, if you need to filters custom data, you can utilize custom filter.

5. Filter Top 10 Values

 

Here in this tip, you can filter top 10 values easily. If you are having a large dataset and from the data, you need to check top 10 values, then do this easily in few clicks.

Follow the steps to do so:

  • First, apply filters.
  • Then from the column where you want to filter > open filter drop down.
  • Go to > Number Filters > Top 10

  • This will open a pop-up window. And from that window, choose the following things.
    • Top or Bottom Values: To choose top or bottom values to filter.
    • Number of Values: To identify numbers of values you need to filter.
    • Type of Filter: This is an elegant option, where you can select the way you need to filter values.

  • As the preference is selected by you, click OK.

Since as you click OK, the entire values in top 10 will be filtered

You May Also Read:

 

6. Filter Above/Below Average Values

 

This is a useful tip for checking the data insight. You can use this if you want to filter values that are above average. Then here know the easy trick to do it easily by applying Excel filters.

Follow the steps to do so:

  • First, on the column apply filters > where you are having values.
  • Then, open filter drop-down > go to “Number Filters” > click on “Above Average”.

  • As you do this it will filter the entire values which are above average.

Well, you can make use of the same method to filter values that are below average. Simply click on “Below Average” in place of above average.

 

7. Utilize a Wildcard Character

Wildcard characters are about partly matching and finding the text. And this can now be used for filtering values.

For example: if you want to select the names that start with letter “H”, then you can do this easily in the name list.

Here know how:

  • To your data apply filters.
  • Open filter drops down.
  • Then, in the search bar type “H*”.
  • As you type the text, this will quickly filter the entire names that start with the “H”

  • And click OK

This method is utilized with the custom filter option also.

8. Filter Values in Pivot Tables

 

The best way to filter values in Pivot Tables is by utilizing slicer. With the help of this, you can link multiple pivot tables to one filter with a slicer.

Follow the steps:

  • Create a pivot table > click on any of the cells in it.
  • Then, go to Analyze Tab > Filter > “Slicer”.

  • This will quickly give you a pop-up window to choose the field you want to sue in the slicer.

  • Then, click OK.

Now, utilize the slicer to filter entire values in the pivot table.

The best thing about slicer is, it can check anytime, how many values you have to use as a filter.

Well, these are some best tips that will help you to use filter amazingly and make the work easy. This will help you to save your precious time as well.

 

Conclusion

 

Hope after reading this article, you might come to know about plenty of options which have in Excel filters.

So, start making use of them when needed and increase your work productivity.

This will not only increase productivity but save tons of your precious time.

Also If, in case you have any additional questions concerning the ones presented, do tell us in the comments section below or you can also visit our Repair MS Excel Ask Question 

Good Luck….



Summary
8 Easy Excel Filters To Save Time, Money and Get Accurate Data
Article Name
8 Easy Excel Filters To Save Time, Money and Get Accurate Data
Description
Learn about the Excel filters to perform various tasks easily and save your precious time as well get the accurate result.
Author
Publisher Name
Repair MS Excel Blog
Publisher Logo

8 Easy Excel Filters To Save Time, Money and Get Accurate Data