What if you have an Excel Slicer with hundreds of values? Today, an awesome trick from Tine at Zebra BI that allows you to replace that huge slicer with one cell.
Table of Contents
(0:00) Welcome
(0:15) One cell to filter
(0:42) Start with a slicer
(1:07) Copy the pivot table
(1:23) Remove fields except slicing field
(1:50) Copy PT to dashboard & hide half
(2:30) Printable list of steps
Table of Contents
(0:00) Welcome
(0:15) One cell to filter
(0:42) Start with a slicer
(1:07) Copy the pivot table
(1:23) Remove fields except slicing field
(1:50) Copy PT to dashboard & hide half
(2:30) Printable list of steps
Transcript of the video:
It's not often, but every once in a while, I see something in Excel.
And I'm just blown away by it and I had to share this with you.
This is a Tine's trick to replace an incredibly long slicer with a single cell. All right, so I was watching his demo from Zebra BI and Tine, who is their tech guy, was showing me a demo of a dashboard that he created.
And I'm like, "Wait, what is that?" Now, his dashboard was beautiful.
I just have a stupid little dashboard here but he had this one cell right here in L3 that allowed you to go in and choose someone, you could search.
And I'm like, "What is this? What are you doing?" Oh, my God, this is so awesome.
All right.
So here's a pivot table, and next to this pivot table, I've inserted a slicer with sales rep and there's so many sales reps.
There's no way to get this thing.
I made it six columns, and it still has this huge scroll bar.
Tine has managed to replace this huge slicer with a single cell. Watch this.
First off, you have your pivot table, and that pivot table's tied to the slicer.
So yeah, we're going to make sure that the next pivot table is tied to the slicer so I copy the entire pivot table, come out here to the right hand side and paste.
So now that's to make sure that this second pivot table is using the same slicer as the first pivot table.
In this second pivot table, I'm going to take the slicer field and move it to the filters, and then I'm going to remove every other field from the pivot table and so what we end up with is this tiny little two cell pivot table.
I'm going to open the dropdown and select multiple items, click, okay and, oh, geez, I should have done this one first, right click, pivot table options, uncheck autofit column with on update.
Now I can make it wider and then what we can do copy the entire pivot table, all two cells of it, come back here and paste, right? And Tine hides column K right?
So now we're left with just this one single cell with a dropdown that has a search box that allows us to filter the dashboard and of course when we choose an item from this list that automatically changes the slicer, which is hidden back here.
So you see Adam Ball, right, and the pivot table, the dashboard of course is pulling, using formulas to pull from this pivot table. I thought that was very, very clever.
I went back to Tine and said, "Hey, this is so cool.
I want to shoot on my channel". He's like, "Sure. Show it on my channel".
So shout out to Tine from Zebra BI.
What a cool, cool trick this is, especially if your slicer is just out of control with too many items.
Well, I want to thank Tine for that awesome trick and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
And I'm just blown away by it and I had to share this with you.
This is a Tine's trick to replace an incredibly long slicer with a single cell. All right, so I was watching his demo from Zebra BI and Tine, who is their tech guy, was showing me a demo of a dashboard that he created.
And I'm like, "Wait, what is that?" Now, his dashboard was beautiful.
I just have a stupid little dashboard here but he had this one cell right here in L3 that allowed you to go in and choose someone, you could search.
And I'm like, "What is this? What are you doing?" Oh, my God, this is so awesome.
All right.
So here's a pivot table, and next to this pivot table, I've inserted a slicer with sales rep and there's so many sales reps.
There's no way to get this thing.
I made it six columns, and it still has this huge scroll bar.
Tine has managed to replace this huge slicer with a single cell. Watch this.
First off, you have your pivot table, and that pivot table's tied to the slicer.
So yeah, we're going to make sure that the next pivot table is tied to the slicer so I copy the entire pivot table, come out here to the right hand side and paste.
So now that's to make sure that this second pivot table is using the same slicer as the first pivot table.
In this second pivot table, I'm going to take the slicer field and move it to the filters, and then I'm going to remove every other field from the pivot table and so what we end up with is this tiny little two cell pivot table.
I'm going to open the dropdown and select multiple items, click, okay and, oh, geez, I should have done this one first, right click, pivot table options, uncheck autofit column with on update.
Now I can make it wider and then what we can do copy the entire pivot table, all two cells of it, come back here and paste, right? And Tine hides column K right?
So now we're left with just this one single cell with a dropdown that has a search box that allows us to filter the dashboard and of course when we choose an item from this list that automatically changes the slicer, which is hidden back here.
So you see Adam Ball, right, and the pivot table, the dashboard of course is pulling, using formulas to pull from this pivot table. I thought that was very, very clever.
I went back to Tine and said, "Hey, this is so cool.
I want to shoot on my channel". He's like, "Sure. Show it on my channel".
So shout out to Tine from Zebra BI.
What a cool, cool trick this is, especially if your slicer is just out of control with too many items.
Well, I want to thank Tine for that awesome trick and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
