Using slicers with formulas (2022 update) | Excel Off The Grid

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

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

50% 1

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post ★
exceloffthegrid.com/using-sli...
★ About this video ★
Slicers are an excellent tool for adding interactivity. When a user clicks on a slicer button, the results change to include only those selected items. Slicers are compatible with PivotTables, PivotCharts, Cube formulas, and Tables…but not standard formulas. So, let’s see how we can use a Table in a new way to get around this limitation.
0:00 Introduction
0:37 Creating the slicer
1:53 Count visible rows with SUBTOTAL
3:52 Connecting the Slicer to the formula
6:06 Using a LAMBDA function
9:16 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #PowerQuery

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

30 июн 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@wayneedmondson1065 Год назад

Awesome Mark! Thanks for demonstrating. Thumbs up!!

@ExcelOffTheGrid Год назад

Thanks Wayne, I'm glad you enjoyed it.

@IvanCortinas_ES Год назад

Extraordinary explanation to put slicers, functions and LAMBDA in context. Great video!!! Thank you Mark.

@ExcelOffTheGrid Год назад

Thanks Ivan. Hopefully you can put it to good use.

@graemegourlay2850 Год назад

Nice technique with the Lambda function. Really helpful to bring forward these alternative and relatively new methods.

@ExcelOffTheGrid Год назад

Glad it was helpful! 👍

@dchan6017 9 месяцев назад

Exactly what i need (slicer affect filter function). Thank you!!!

@ExcelOffTheGrid 9 месяцев назад

Happy to help!

@operamaniak81 7 месяцев назад

You made my day! That's the best thing I've seen since an intro of vstack... BIG thank you!

@ExcelOffTheGrid 7 месяцев назад

Glad to hear that! 😁

@Picla_Peremohy Год назад

Mark, you always impress me with your knowledge and ability to convey this knowledge in understandable modules. Thank you

@ExcelOffTheGrid Год назад

Thanks Jim - I appreciate that feedback. 😀

@Henrik.Vestergaard 11 месяцев назад

Wonderfull, just what I needed. Great demo, thanks!

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

Glad it was helpful!

@jerrymiles7804 Год назад

Outstanding! Best regards from Limón, Costa Rica!

@ExcelOffTheGrid Год назад

Thank you very much!

@SJV992 Год назад

This is quite simply a brilliant explanation and overcomes a big challenge I was facing. Many thanks for posting this.

@ExcelOffTheGrid Год назад

Thanks. The more I explore this technique myself the more useful I find it. 👍

@waitplanwp4129 Год назад

mark you are genius, love everting you do, you so smart!!!

@ExcelOffTheGrid Год назад

Thank you. 😀 I’m just sharing things that I’ve learned.

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

Great. I was looking for this function for hours.

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

Glad I could help!

@mohammedelsakally540 9 месяцев назад

Awesome methodology and presentation, Thank you very much.

@ExcelOffTheGrid 9 месяцев назад

Glad it was helpful!

@shyjumathew07 Год назад

Excellent!!!!

@listenifymusic Год назад

thank you so much man, your formula solved a separate problem for me, I was trying to reference the value of a slicer to another cell, and I have tried a lot but had no luck unlit I came around your video. so thank you and keep going.

@ExcelOffTheGrid Год назад

Excellent! Glad I could help. 👍

@freeworld2658 Год назад

Thank you a lot. This is just AWSOME !!!

@ExcelOffTheGrid Год назад

Hopefully you can put it to good use.

@sergegagne874 Год назад

Great stuff, thank you!

@ExcelOffTheGrid Год назад

My pleasure!

@RobertHind Год назад

Brilliant!

@ExcelOffTheGrid Год назад

Thanks Robert. I appreciate that 😁

@nishantkumar9570 Год назад

Awesome 👌👏

@user-ip7tr4mh6n 4 месяца назад

Wow man! A life saver!

@slayermm1122 3 месяца назад

amazing trick.

@s.burghardt Год назад

very helpfull. thank you!

@ExcelOffTheGrid Год назад

You're welcome!

@martyc5674 Год назад

Really cool trick 👍👍👍

@ExcelOffTheGrid Год назад

Thanks Martin, I think it's pretty cool too 😁

@paulmartinlife Год назад

Thanks, that's excellent.

@ExcelOffTheGrid Год назад

Thank Paul, I’m glad you liked it.

@mattmattelig 7 месяцев назад

VERY clever!!

@ExcelOffTheGrid 7 месяцев назад

Thanks 👍

@sel4sie Год назад

Mark, thank for this awesome video. Can you please provide a link to the data used? Thank you

@danielaifrim75 14 дней назад

Super tare

@bdiffoful 7 месяцев назад

Masterclass

@jorgebotao9700 Год назад

eu sou brasileiro , olhei o teu canal hoje , tu es muito bom cara

@ExcelOffTheGrid Год назад

Thanks 👍

@belchannels 5 месяцев назад

Thank you for providing a solution for slicer. What if there is more than one list? E.g. A few products under a product category and also different years? Can something similar be built?

@khayourabdellah8727 5 месяцев назад

Good tks

@ExcelOffTheGrid 5 месяцев назад

You’re welcome 👍

@jimfitch Год назад

Mark, super trick! The data in List are hard-coded. How would you make that dynamic?

@ExcelOffTheGrid Год назад

Power Query returns values from the source into a Table. Then the Table can be used as the source for the List. This would make it completely dynamic based on the source data.

@user-pr8ew9ku6b 10 месяцев назад

Hi - this is great. Would you know with a slicer linked to a pivot chart is there a way of highlighting the column but keeping all the data?

@ExcelOffTheGrid 10 месяцев назад

A slicer linked to a Pivot Chart, no not possible. But creating a slicer with this method and a standard chart it should be possible.

@user-pr8ew9ku6b 10 месяцев назад

@@ExcelOffTheGrid thank you 👍

@Bhavik_Khatri Год назад

I like the first version as it doesn't rely on the Lambda function

@ExcelOffTheGrid Год назад

The first version is simple to understands and works on Excel 2013 and onwards. So that’s my preferred method too.

@everwelwisher Год назад

sir how to get number of rows count based on slicer selection what formula can we use

@ExcelOffTheGrid Год назад

You can use the SUBTOTAL function: =SUBTOTAL(103,tableName[columnName])

@kapzforu5347 Год назад

In my day to day job, I need to analyse RAG (Red, Amber and Green Status) and update word and ppt manually with comments, is there a way to make my life easy? Plz respond. Thank you

@ExcelOffTheGrid Год назад

There is... have a look at these posts: exceloffthegrid.com/linking-excel-files-to-powerpoint/ exceloffthegrid.com/edit-links-in-powerpoint-using-vba/ exceloffthegrid.com/linking-excel-files-word/ exceloffthegrid.com/edit-linked-objects-in-word-using-excel-vba/

@kapzforu5347 Год назад

@@ExcelOffTheGrid Thank you so much.

@Rice0987 Год назад

It's all ok, but what if i want use slicers from dynamic ranges...🤔

@larryleblanc2867 Год назад

Excellent ideas! One minor suggestion is that you not use the term “data table” for your table containing data. A data table is a different concept in Excel. .

@ExcelOffTheGrid Год назад

Good point… Though it’s probably no worse than using the words formula and function interchangeably (which I also do 😬)