One of the most common things that I witness people (pros or amateurs / seniors or juniors) doing is applying filters to data. I mean it is so common that everyone knows it around. Agreed !
What I am going to share with you is 3 uncommon, incredibly smart ways of applying filters and not just filtering data but will also show you uncanny ways of automating filters
Method 1 – Auto Filter
This is a shortcut and will probably blow you away once you see how simple is it to use it.
Method 2 – Advanced Filter + Automation
Using advanced filter you can filter data for more complex conditions and even automate that using a tiny VB Code
Code for Pasting Filtered Data to another location
Range(“SalesData[#All]“).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(“Condition[#All]“), CopyToRange:=Range(“Extract“), _
Code for Filtering data (without pasting it to another location)
Range(“SalesData[#All]“).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
To paste this code
- Open the VBA Window (Alt F11)
- In the menu click on Insert and then click on Module
- In the blank space, paste the code (make changes to the code if needed)
Method 3 – Filter Data using Slicers
You’ll feel like a King if you are working with Excel 2013 and above. Because you can use these nifty slicers to filter your data. Take a look
DOWNLOAD THE EXCEL FILE – with all methods + make sure to enable macros before you start toying with it
Create Dashboards using Slicers & Advanced Filter
- Create a Dashboard in 15 Mins – A small case where I’ll share simple tricks and hacks to make a quick dashboard using Filters Slicers and Formulas
- Advanced Filter Dashboard – Now since slicers work in Excel 2013 and above, you don’t have to feel disheartened. You can use advanced filter to create similar dashboard!