MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Report Slicer Selections in a Title

June 11, 2020 - by

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

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.