Almost everyone knows how to use filters in excel but very few know that there is visual filter called ‘slicer in excel’.
Without any doubts filters are most commonly used functionality in excel to slice and dice data. Its very useful and easy to use when you have multiple columns.
But given that filters work based on dropdowns the selections are hidden and only visible when you click on the dropdown.
I bet most of us have faced issues like wrong selection, forgot to remove selected items and when you have multiple filters things will get even more difficult.
To overcome all of these issues we will learn today how to use slicers in excel to filter data visually.
How to add a slicer in excel
Before we start there are two prerequisite you should be aware of:
Slicer option is available from Excel 2010 and above.
Unlike regular filters you cannot add a slicer to your raw data, you need to convert your data into a table and then start using a slicer.
Confused? no worries lets go step by step.
Step 1: Select entire data in your sheet and then press CTL + T shortcut and hit Enter to convert your data into a table format
Step 2: Then place your curser anywhere on your data ==> Insert ==>Click on Slicer
Step 3: Then Insert Slicers window will pop up, from this window and you need to select all your filters using checkboxes and then click Ok
Step 4: Now all visual filters will appear in your sheet so just arrange as per your wish
Step 5: Now to apply your filters you can just click on the values and immediately your data will be filtered
Why should you use a slicer for filtering?
Well, by now I think you should have understood why slicer option is useful.
Literally, you can see all the options from the above slicer boxes, and whenever you select something the other boxes will gray out to make sure you will not select them accidentally.
Let me show you how that works:
As you can see from the above image, I have selected a date range, that’s the only filter I have applied but if you notice the other slicer boxes some options are disabled.
That means, if you want to select multiple options from the next slicers you could only select the highlighted options.
The better way of using slicer in excel
This is all fine, but you can’t use slicer boxes when you have 50+ columns in your file. it will quickly become useless and difficult to maintain.
I think the best way to use the slicer filter technique is to combine it with Pivot tables to summarize and analyze your data.
The process for this technique is more or less same.
First, you need to insert a regular pivot table and then just place your cursor anywhere on the pivot table.
And then go to insert and click on slicer (step 2 to step 5 from the above).
Now you will have all your slicers and you can use them to filter your data. The good thing is you have both pivot table and slicer working together which is a deadly combination.
Using multiple filters with slicer
By default, when you click on slicer values it will select a single option but in case if you want to select multiple options as I have used above:
You just need to hold CTL key and start selecting with your mouse.
In my case I have selected multiple dates from my first filter.
How to Unfilter your data
First of all, when you select something from a slicer you will see a filter symbol with a cross mark on the top right side. It will be grayed out when you don’t have any filters applied like below.
So to clear filters you just need to click on that top right filter symbol in each slicer box. This will clear all your previously selected filters.
Alternatively, you could also use a shortcut which is Alt+C but this will only work when your slicer box is selected. So if you want to use this shortcut then you must first select all your slicer boxes.
Also read: 51 Best Excel Tips & Tricks
Also read: Complete VLOOKUP tutorial
Conclusion
As you can see the slicer filter option in excel is underrated, and not many people are aware how to use it. I have personally used this option and trust me it helped me a lot.
So I wrote this article to explain how powerful this option is and why you should start using it from today.
Do let me know your experience or any slicer tips and tricks.