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.
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:
- 7 Underrated Excel Functions That I Wish Knew Before
- 10 Best Excel Formulas Tips Every Excel User Should Know
- 11 Vital Tips To Optimize Excel File and Speed-up your Excel
- 11 Simple Yet Powerful Excel Troubleshooting Tricks for Analyzing Data
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….
Priyanka Sahu
Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.