Forum Discussion
Slicer to create Dynamic Chart Titles
Hi.
I have a chart that the title is the name of a category. There are 6 categories, let's say: A,B,C,D,E,F
I created a Category slicer, so that when I select whatever category, the chart title updates with that Category label.
I know how to create a formula that shows the title as whatever categories are selected, e.g. if I select A,B,C,D, it shows "A+B+C+D", and if I select all 6, then it shows "All Categories".
But I want the formula to be such that if 4 out of 6 categories are selected, it would show "All Categories excluding..." where the "..." are the categories which are excluded, so instead of "A+B+C+D", it would show "All Categories excluding E+F".
That's where I am stuck. I want it so if the majority of categories (i.e. 4-6) are selected, it would display the "All Categories excluding...", but otherwise it would display the categories separated with the "+".
Hope my question makes sense. Can you advise me how to proceed. Thanks!
2 Replies
You're nearly there, since you already have the selected categories listed somewhere. The one extra thing you need is a fixed list of all 6, so the formula can work out which ones are missing.
Say the selected categories sit in A4:A9 (for example the row labels of a small PivotTable connected to the slicer, with Grand Totals turned off so the total row isn't counted), and the full list of 6 is typed in D2:D7. Put this in the cell your chart title links to:
=IF(COUNTA(A4:A9)=ROWS(D2:D7),"All Categories",IF(COUNTA(A4:A9)>=4,"All Categories excluding "&TEXTJOIN("+",TRUE,IF(COUNTIF(A4:A9,D2:D7)=0,D2:D7,"")),TEXTJOIN("+",TRUE,A4:A9)))
COUNTIF checks each of the 6 against the selected list, and IF keeps only the ones with no match, which gives you the excluded ones. Change the 4 if you ever want a different cut-off.
We recalculated it in another spreadsheet app, not Excel itself, on 7 selections: A,B,C,D gives All Categories excluding E+F, A,C,D,E,F gives All Categories excluding B, A,C,F gives A+C+F, and all 6 gives All Categories. TEXTJOIN needs Excel 2019 or later. In Excel 2019 confirm the formula with Ctrl+Shift+Enter; in 365 and 2021 plain Enter is enough.
(Drafted with AI assistance; formulas tested before posting.)
VARSDigital WORKED LIKE A CHARM! And I liked that I didn't have to define each channel within the formula itself, but rather reference the category names in a hidden sheet. THANKS SO MUCH! Hope this thread helps another person who is looking for a solution like this.
