List Slicer Selections in a Cell in Excel

Подписаться 251 тыс.

Просмотров 44 тыс.

50% 1

Excel File: www.teachexcel.com/excel-tuto...
Excel Courses: www.teachexcel.com/premium-co...
Microsoft Office Doc: support.microsoft.com/en-us/o...
How to list all slicer selections for a Pivot Table in a single cell in Excel.
This allows you to make a nice interface and better integrate the usage of slicers and filters into your worksheets.
You will also be able to show slicer selections on whichever worksheet you want; the slicer itself and even the pivot table can be on separate, or even hidden, worksheets. This allows you to have a high level of flexibility.
Pivot Tables and slicers are amazing, but also can be quite complex - this tutorial combines a few simple tricks to get the desired result, and it should be easy for anyone to follow.
Helpful Additional Resources:
Password Protect & Hide Worksheets: • Password Protect & Hid...
Excel Courses:
- VBA/Macro Course: www.teachexcel.com/vba-course...
- Building Professional Forms: www.teachexcel.com/premium-co...
- Email Course: www.teachexcel.com/premium-co...
Chapters:
0:00 Intro & Topics Covered
0:40 Setup the Workbook
2:06 Slicer Setup
2:35 List Slicer Selections in a Single Cell
4:46 Improve the Worksheet Interface
5:25 Additional Features
TeachExcel.com
#msexcel #howto #tutorial

Опубликовано:

2 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 44

@acd0210 2 года назад

I've been searching for a solution like this for 2 days and I was going crazyyyy. I thought I could only do that using VBA or something. Thank you sooo much. This trick is awesome!

@makisalimhussain2334 2 года назад

Thank you Jon New Trick idea I tried another way without using helper sheet and succeeded; following steps below: 1- Go to Pivot Table 2- Go to Design tab - Report Layout - Show in Outline Form 3- Write TEXTJOIN formula and select one group of Categories column of the Pivot Table And that's it. The formula will work according the slicer selection. Thanks Maki S. Hussain Baghdad-Iraq

@plavali_znaem 2 года назад

was looking exactly for this!!

@sramandcotrivandrum423 3 года назад

That was amazing! Thanks from India!

@millanchauhan Год назад

This is fantastic! Thanks so much!

@HuIk7 2 года назад

Man you saved my life. Thank you !

@AmySwayzee 3 года назад

Great trick. Didn't know textjoin. Will use this immediately!

@TeachExcel 3 года назад

If you have a newer version of Excel, it is a must-use! I'm glad you like the trick :)

@marekzadecki470 3 года назад

It's very interesting. Thank you Don! Great trick.

@TeachExcel 3 года назад

Thanks for the comment Marek! I'm glad you like it :)

@HamzaAshur 2 года назад

Thanks a lot u helped finish my idea perfectly

@raorahul358 2 года назад

Thanks it was very useful 👍

@IvanCortinas_ES 3 года назад

Supertrick! Thank you Don!

@TeachExcel 3 года назад

Thanks a lot Ivan!! I'm glad you like it :)

@abidfarid439 Год назад

Wonderful trick. thank you.

@karimallahwala7022 2 года назад

Very helpful thank you very much

@COURSSTATSCHAMBERY Год назад

nice tip Tks

@wayneedmondson1065 3 года назад

Great trick! Thanks for sharing :)) Thumbs up!!

@TeachExcel 3 года назад

Thanks Wayne!! I'm glad you think so :))

@GeertDelmulle Год назад

If you add the table to the data model, then you can add small and simple DAX measure to CONCATENATEX those VALUES and then show that measure in a single cell pivot table, right here. No need for a helper sheet.

@Excelambda 3 года назад

Perfection!!!😊✌

@darrylmorgan 3 года назад

Really Neat Little Trick/Tip...Thank You Sir :)

@TeachExcel 3 года назад

Thanks Darryl!! I'm glad you like this trick :))

@KilianDarkwater 2 года назад

Neat trick and captivating voice! Even enchanting ;-)

@kellybrehm911 2 года назад

I love this. Now I want to take this and 'link' the cell to a text box but I'm running into a problem. Not all the text translates into the text box from the cell, only some of it does. Can you help?

@paulaparo1583 Год назад

The trick is useful. I thank you for that. Rest assured that viewers will understand what you're saying even if you speak normally.

@ajayv304 11 месяцев назад

Amazing

@jimfitch 3 года назад

Very clever!

@TeachExcel 3 года назад

Thank You :)))

@vikaasb2016 3 года назад

Super helpful

@TeachExcel 3 года назад

Thanks Vikus! I'm glad you think so :))

@rahularanger407 2 года назад

Nice, I was wondering if we could do opposite, like here we can selected Multiple Items like Bathing, Gardening to store "Bathing, Gardening". is it Possible to select Bathing from a cell that contains "Bathing, Gardening."

@garethmorrall1047 Месяц назад

Is it possible for the cell to return nothing or “ALL” if no slicer option is selected? Say if slicer is “Customer” with 100 unique customers but only want the cell to return selected customers not all 100 customers listed when the slicer isn’t selected/filtered. Iw this possible? Thanks

@MarioLopez-eu8tj 2 года назад

How can i select one element of the slicer from a cell value ?

@rajeshchawda9449 3 года назад

what if I do not have the latest version of Excel with TEXTJOIN? How can we achieve the same functionality without TEXTJOIN (I mean with some other older version functions)?

@TeachExcel 3 года назад

Here is another tutorial I made that will help you combine values in older versions of Excel: www.teachexcel.com/free-excel-macros/2201/simple-excel-function-to-combine-values-in-all-versions-of-excel-udf?nav=yt_comment

@JoRands1 2 года назад

@@TeachExcel Hi, I tried your other method as I am using Excel 2016 Pro which does not have TextJoin. The UDF works as intended, but when I use it with the slicer as in the video method. The text does not update automatically, it just lists all of the words together. IS this how it should be or did i do something wrong? Update: Tried this same method on the online version with TEXTJOIN and seeing the same problem. The slicer is moving correctly and the text is joining but its not being updated with the slicer.

@abbottkatz2535 3 года назад

Not only can you hide the Raw sheet you can delete it, as the pivot table draws its data from a hidden cached replica of Raw.

@TeachExcel 3 года назад

Pivot Cache is really great, you just won't be able to update the values easily then. But I like your idea for streamlining a workbook with static data!!!

@abbottkatz8830 3 года назад

@@TeachExcel Thanks. True - the only way I know how to update values would be to double-click a grand total in a pivot table, which restores the data source.

@Rice0987 Год назад

Today is 444th day of terrorusia's war against us... and i have put my 444th like to this nice tip. Thanks!

@Flameville Год назад

Everything about excel is needlessly confusing it’s annoying can’t wait for A.I to do this for me

@AXNJXN1 3 года назад

You're 'file' isn't sanitized, rather all the points you've created (in this vid) are actually already setup IN the downloadable file so you can't really 'follow along'... You may want to sanitize it then upload so it truly can be edited alongside your video. Additionally, your point of view(s) seem to be all over the place for this tut., politely stating this... What exactly are you trying to teach? That it's flexible or saving time with something?

@TeachExcel 3 года назад

I am only trying to teach you what is in the title of the video - if this is a trick that you would like to use, then you now know how to use it and if not, then you only lost a few minutes of time. For the file that you can download, you are right in saying that I do not provide a "Before" and "After" file and I do not plan to do so - if you want to start from scratch, then create a new file or delete the final data from the downloadable file. I feel that what I provide allows you to follow along at whatever pace you would like, and deleting existing data from the downloadable file takes mere seconds.