Slicer Selections in Title

Learn Excel from MrExcel Podcast, Episode 2202: Slicer Selections in the Title.

Great question today that I picked up in my Houston Power Excel seminar from Joy that was down there for the Texas Council of the IMA. Joy has a Pivot Table-- so we'll create a Pivot Table here, Insert, PivotTable, Okay. I don't know, build some sort of a Pivot Table here, maybe Customers going down the left-hand side, Revenue, Profit, something like that. And Joy has a Slicer-- so Insert, Slicer, choose a Slicer by Cector, click OK. And Joy chooses a few things from the Slicer, and then here's the question: Joy wants to take these selected items and put them as the Title up in Cell A1.

Okay, a comment from Bart on Episode 2198, would solve this if we had just a single item selected. And Bart points out what we can do is, take the Sector field and drag it to Filters, and then if you have just one item selected, bingo! You have the title. But, going back to Excel 2007, this horrible word of multiple items, what is up with that? That's not useful at all. So it doesn't work when you have multiple items selected.

So here's my workaround that's going to get us all the Sectors: First thing I want to do, is I want to create a second Pivot Table outside of view-- just, like, over there in Column Z where no one's going to see it-- and that pivot table has to be tied to this Slicer. And so my cheap and dirty way of doing that is to copy the Pivot Table, and just come way out here to Column Z, and paste. Alright. And by copying and pasting, I don't have to worry about coming in here to Analyze and Filter Connections, and choosing this Tied to Sector-- it's already tied to sector, you know, so that's faster. And what we're going to do in this Pivot Table is we're going to put whatever is in the Slicer in the rows area. That's the only field that we're going to have here. Get rid of all the other fields-- all the other fields go away. And, most importantly, we're going to get rid of the word "Grand Total"-- so right-click and Remove Grand Total.

So, what happens right now, I have two things selected, and of course if I choose more-- so I'll come back here and hold down the Ctrl key or use the Multi Select and select multiple items-- then we'll go back and look at that Pivot Table far out there in Column Z. I now have a list of all four items. Alright? So we're going to know that starting here in Z4, for some unknown length, is going to be a list of all the items selected. So, we're going to come back here to our Title cell and use that great new function that came along in February of 2007-- =TEXTJOIN. Between each item I want to put comma, space, that's in quotes, and then the most important part here to make this work, ignore empty, yes, ignore empty (=TEXTJOIN(", ",True,). And then, where is our data? I'll go from here, let's just cruise over here, we'll start from the very first cell and just down however many we could possibly ever have, like that, press ENTER. And now, up in Cell A1-- we'll Cell Styles and choose the title-- as I choose from the Slicer, it is automatically updating. If I choose two or three, no worries, it'll put commas in between; if I choose just one, it's fine; if I choose them all, it'll put them all. Alright.

So, by using TEXTJOIN and that second Pivot Table out there on the right, great way to get the Slicer selections showing up as a Title and avoiding the whole multiple items bug-- it's a bug, they did it by design, I think it was horrible back in Excel 2007.

Now this trick and many other tricks like it are in my book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.

Wrap-up from today's Episode: Joy in Houston-- can I have the information selected in the slicer as a Title above the Pivot Table? We drag the slicer to the Report Filter, but if you choose multiple items, then you get that horrible Title of Multiple Items in parentheses. I mean, what are the multiple items? Slicers let you see the items, but if you don't want to include the Slicer in your Print Range, you can build your first Pivot Table, add a Slicer, copy that Pivot Table and paste this to the right-- far off to the right where no one will be able to see it. That'll make the Pivot Table automatically connect it to the slicer. Put only the Slicer field in rows of that second Pivot Table, delete the Grand Total row, and use TEXTJOIN, saying, Ignore the blanks, to put all the Slicer Selections up in a cell.

To download the workbook from today's video, visit the URL in the YouTube description, also there are lists of my upcoming seminars-- would love to see you at one of my Excel seminars.

I want to thank Joy for asking me that question in Houston, and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.