Slicer Selections in Title
May 14, 2018 - by Bill Jelen
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.
But as soon as you select 2 or more items from the slicer, then the title becomes (Multiple Items). That is not helpful.
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.
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.
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.
In the figure above, I applied the Title style to cell A1. This is found in the Cell Styles gallery.
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