MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Slicer Selections in Title


May 14, 2018 - by Bill Jelen

Slicer Selections in Title

Joy attended my Houston Power Excel seminar and asked if there was a way to show the items selected in a slicer in a cell above the pivot table.

I love slicers, because they can show you all of the items selected.

Say that you only select one item from the slicer or Report Filter. If you put the Sector field in the Report Filter, you have a great title in B1 showing what is selected.

Report filter works as a title with only one item selected.
Report filter works as a title with only one item selected.

But as soon as you select 2 or more items from the slicer, then the title becomes (Multiple Items). That is not helpful.

Which items?
Which items?

So - here is the solution that I offered to Joy in Houston. Remove Sector from the Filter area. Select the entire pivot table. Copy with Ctrl + C.


Copy the original pivot table.
Copy the original pivot table.

Move far to the right and paste a copy of your pivot table. You want this pivot table to be outside of the print area and outside of one screen of data. I am pasting to Z3.,

Remove all fields from the new pivot table. Take whatever is in your slicer and move that field to the Rows area.

Your pivot table will consist of a heading, a list of the selected Sectors, and a cell that says Grand Total. Right-click the Grand Total and Remove Grand Total.

Remove the grand total
Remove the grand total

Go back to cell A1. Use the formula =TEXTJOIN(", ",True,Z4:Z20). This new function was introduced in February 2017 for Office 365. The second argument is called Ignore Empty. Putting True there will prevent a bunch of extra commas appearing at the end of your list.

Use TEXTJOIN to concatenate all of the row field items from the second pivot table.
Use TEXTJOIN to concatenate all of the row field items from the second pivot table.

In the figure above, I applied the Title style to cell A1. This is found in the Cell Styles gallery.

Only 2 or 3 cell styles are useful, in my opinion. Title is a good one.
Only 2 or 3 cell styles are useful, in my opinion. Title is a good one.

Watch Video

Download Excel File

To download the excel file: slicer-selections-in-title.xlsx

I love discovering new techniques in my Power Excel seminars. For a list of upcoming events, see Upcoming Power Excel Seminars.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Garbage in, insight out."

Title Photo: Matthew Cabret on Unsplash


Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.