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
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.