Excel - How to Display Selected Slicer Value(s) in a Cell

[@Crawf876]

I've been trying to figure this out for ages, to make dynamic chart titles. Well done!

[@MikeThomas67]

Glad it was helpful!

[@vilnis_KP]

Fantastic! Thanks a lot! Neither ChatGPT nor Gemini AI was able to give solution without VBA (which is restricted in our organization for security reasons).

[@MikeThomas67]

Glad it helped!

[@avinashsai9196]

Thanks Mike. Great video!!!

[@KrishnaKumar-zn9kg]

Lovely explanation!👍

[@pawepuchaczrolny9348]

Thank you Sir. I needed this today😊

[@RenierWessels]

Nice solution. Thanks Mike. I was thinking of a =TEXTJOIN(", ",TRUE,SORT(UNIQUE(table[field]))) Your solution is more simple and elegant though. Thanks for sharing!

[@MikeThomas67]

@Renier I initially used a formula similar to yours. Actually I used UNIQUE to generate the list of states and a second formula with TEXTJOIN. I went with the Pivot Table solution because it was simpler and not everyone has UNIQUE. But that's what I love about Excel - different approaches to the same problem

[@iziaurrahman]

Thanks MIke. Great video. It was very helpful.

[@MikeThomas67]

Glad to hear it!

[@joycelinelu]

Genius...THANK YOU!!😊

[@MikeThomas67]

You're welcome!

[@michaelhill803]

When I go to report connection on the slicer tab it's greyed out and unable to click, even though I created a pivot table with my table. Any ideas?

[@afeesakanni]

Thanks for this helpful tutorial. Please what of the default case where nothing is picked, and the slicer defaults to the first name? What if I want it to default to blank?

[@arturovaldez9102]

Thanks Mike

[@thomasmininger7405]

Thanks!

[@ayoolaolamiekan1488]

Thank you, very helpful. Quick question; is it possible to have the cell value show "All" when all values in the slicer are selected ? could we work around this with a formula?

[@MikeThomas67]

@ayoolaolamiekan1488 This is what I came up with... =IF(LEN(Pivots!N2) - LEN(SUBSTITUTE(Pivots!N2,",",""))=COUNTA(UNIQUE(Orders[State]))-1,"ALL",Pivots!N2) LEN(Pivots!N2) - LEN(SUBSTITUTE(Pivots!N2,",","")) counts how many commas there are in the string in N2 (generated by the TEXTJOIN function COUNTA(UNIQUE(Orders[State]))-1 will only work in Excel 365 - it counts the number of unique items in the State column of the Orders table and then subtracts 1 So if the number of commas is 1l fewer than the number of states, show ALL (we subtract 1 because there will be 1 less comma than states)

[@ayoolaolamiekan1488]

@@MikeThomas67 Thank you very much Mike. This has been really helpful, I appreciate it.

[@ErikaJohansson-vuxantagmalmo]

Beautiful! 👏

[@shj92922]

thanks man, ilike your brief explanation, i have a sheet that have 2 slicers at excel, first one country second is division. what i want that if no slicer is selected data showing zero (grand total) not combine all slicers, hope you got my point

[@marcw.5492]

Thanks ! great stuff... what do you do when the new pivot tables have a valid name - but dont appear in the connections list ? I made sure all Pivot tables are coming from same source...Table Name . Thanks

[@MikeThomas67]

Make sure that ALL Pivots are coming from the same source - it's easy to miss one. Also this might help... stackoverflow.com/questions/50932225/report-connections-for-pivot-table-slicer-doesnt-show-all-pivot-tables

[@MatthewCraig-d7k]

Can you display a slicer option without using pivot table. I use slicers in a table, but do I need to create the PivotTable?

[@MikeThomas67]

You can attach a Slicer to a Table or a Pivot Table. For this solution you do need a pivot table however there may be an alternative way to do it (which I havent looked at) that doesn't need the pivot table

[@walterstevens8676]

Nice solution. But what if your slicer isn't on a pivot table but on an ordinary table?

[@MikeThomas67]

@walterstevens8676 Check out this video from Mark Proctor: exceloffthegrid.com/using-slicers-with-formulas/

[@MickyLion5527]

Thank you So much