3 Ways to Filter Data in Excel

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

Sub My_Filter_Macro()

Range(“SalesData[#All]“).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(“Condition[#All]“), CopyToRange:=Range(“Extract“), _
Unique:=False

End Sub

 

Code for Filtering data (without pasting it to another location)

Sub My_Filter_Same_Data()

Range(“SalesData[#All]“).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range(“Condition[#All]“), Unique:=False

End Sub

 

To paste this code

1. Open the VBA Window (Alt F11)
2. In the menu click on Insert and then click on Module
3. In the blank space, paste the code (make changes to the code if needed)
4. Sweet..Done!

 

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

1. 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
2. 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!