MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Report Slicer Selections in a Title

June 10, 2019 - by Bill Jelen

Selected slicer in title: Photo by Daria Nepriakhina on Unsplash

Slicers are great, but they can take up a lot of space in a print out.

A slicer has 26 products and five are selected. Rather than print out the slicer, the goal is to list all slicer selections in one cell. Select a pivot table that is using the slicer and Ctrl+C to copy.

Here is an awesome way to get the selected slicers in a single cell. First, select your entire pivot table and copy with Ctrl+C.

Then, paste a new pivot table somewhere outside of your print range. Copying and pasting makes sure that both pivot tables react to the slicer. Change the pivot table so you have the slicer field in the Row area. Right-click the Grand Total and choose Remove Grand Total. You should end up with a pivot table that looks like this:

Paste the pivot table far to the right of your print range. Change the pivot table to show only Product. Remove the Grand Total. In the current example, the products are in I4:I8, but they could stretch down to cell I29.

The list of products starts in I4 and might potentially extend to I26. Use the new TEXTJOIN function to join all of the selected products in a single cell. The first argument of TEXTJOIN is the delimiter. I use a comma followed by a space. The second argument tells Excel to ignore empty cells. This makes sure that Excel does not add a bunch of commas to the end of your formula result.

For the title in A1, use ="Report for "&TEXTJOIN(", ",True,I4:I26). The result of the formula will be Report for Apple, Cherry, Guava, Lime, Orange.

Title Photo: Daria Nepriakhina on Unsplash

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.