When you filter a pivot table to many items, the title of (Multiple Items) is nearly useless. Which items? Printing the whole slicer can take up a lot of space. Today, I combine a Mike Alexander trick with TEXTJOIN to create a title that shows which items are selected in the slicer.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2263.
Report the Excel slicer selections in the title.
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
Oh you know what, ever since Excel 2007, we’vee had the ability in a pivot table to select multiple items.
But I hate when I select multiple items because no one can tell what I selected.
What the heck does this mean?
Multiple items.
Then in Excel 2010, they gave us the ability to insert a slicer and when we insert a slicer, that’s great because now we can see which items are selected but now we have to print out the whole freakin slicer.
Alright, so, here’s what we’re going to do.
We’re going to get rid of this filter.
The filter goes away, keep the slicer there, slicer in view, out view, I don’t care.
We’re going to take the pivot table, this is the Mike Alexander trick.
Hey Mike, how you doing?
Copy the pivot table far out here to the right-hand side, that makes it share a cache in the pivot table, we don’t need revenue, we don’t need customer, all we need is sector.
Oh by the way, no grand total.
Right click, remove grand total.
Alright, now here’s the awesome thing.
So this is going to be equal, text join, text join, brand new function, February of Excel 2017 so hey, it’s been two years, get Office 365, the delimiter is going to be comma space ignore empty, definitely ignore empty because you don’t know how many sectors are going to be there and then the list of sectors is this big list here, how many possible sectors there can be like that.
And then once we have that working, we can actually just update it here.
Report for sectors, ampersand, control X, over here, paste.
Cells styles.
I hardly ever use sell styles but I’ll use it for the titles just because they look cool.
Alright, check out how this works now.
So if choose communications, if I choose communications through financials, I love it.
That’s so much better than multiple items.
Well hey, I want to thank you for stopping by.
We’ll see you next time, maybe in 15 minutes for the next one from MrExcel
Report the Excel slicer selections in the title.
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
Oh you know what, ever since Excel 2007, we’vee had the ability in a pivot table to select multiple items.
But I hate when I select multiple items because no one can tell what I selected.
What the heck does this mean?
Multiple items.
Then in Excel 2010, they gave us the ability to insert a slicer and when we insert a slicer, that’s great because now we can see which items are selected but now we have to print out the whole freakin slicer.
Alright, so, here’s what we’re going to do.
We’re going to get rid of this filter.
The filter goes away, keep the slicer there, slicer in view, out view, I don’t care.
We’re going to take the pivot table, this is the Mike Alexander trick.
Hey Mike, how you doing?
Copy the pivot table far out here to the right-hand side, that makes it share a cache in the pivot table, we don’t need revenue, we don’t need customer, all we need is sector.
Oh by the way, no grand total.
Right click, remove grand total.
Alright, now here’s the awesome thing.
So this is going to be equal, text join, text join, brand new function, February of Excel 2017 so hey, it’s been two years, get Office 365, the delimiter is going to be comma space ignore empty, definitely ignore empty because you don’t know how many sectors are going to be there and then the list of sectors is this big list here, how many possible sectors there can be like that.
And then once we have that working, we can actually just update it here.
Report for sectors, ampersand, control X, over here, paste.
Cells styles.
I hardly ever use sell styles but I’ll use it for the titles just because they look cool.
Alright, check out how this works now.
So if choose communications, if I choose communications through financials, I love it.
That’s so much better than multiple items.
Well hey, I want to thank you for stopping by.
We’ll see you next time, maybe in 15 minutes for the next one from MrExcel
