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

[@danielaifrim75]

Super tare

[@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. 😀

[@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.

[@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. 👍

[@khayourabdellah8727]

Good tks

[@ExcelOffTheGrid]

You’re welcome 👍

[@waitplanwp4129]

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

[@ExcelOffTheGrid]

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

[@stefankirst3234]

Brilliant! Exactly what I needed. Thanks a lot 😊

[@ExcelOffTheGrid]

Glad it helped!

[@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.

[@mohammedelsakally540]

Awesome methodology and presentation, Thank you very much.

[@ExcelOffTheGrid]

Glad it was helpful!

[@dchan6017]

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

[@ExcelOffTheGrid]

Happy to help!

[@belchannels]

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?

[@graemegourlay2850]

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

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@wayneedmondson1065]

Awesome Mark! Thanks for demonstrating. Thumbs up!!

[@ExcelOffTheGrid]

Thanks Wayne, I'm glad you enjoyed it.

[@jerrymiles7804]

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

[@ExcelOffTheGrid]

Thank you very much!

[@AlisonSchubert-s8o]

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]

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.

[@AlisonSchubert-s8o]

@@ExcelOffTheGrid thank you 👍

[@Henrik.Vestergaard]

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

[@ExcelOffTheGrid]

Glad it was helpful!

[@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])

[@Rice0987]

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

[@freeworld2658]

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

[@ExcelOffTheGrid]

Hopefully you can put it to good use.

[@RobertHind]

Brilliant!

[@ExcelOffTheGrid]

Thanks Robert. I appreciate that 😁

[@nishantkumar9570]

Awesome 👌👏

[@shyjumathew07]

Excellent!!!!

[@maheshlowe907]

Great. I was looking for this function for hours.

[@ExcelOffTheGrid]

Glad I could help!

[@sel4sie]

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

[@s.burghardt]

very helpfull. thank you!

[@ExcelOffTheGrid]

You're welcome!

[@jorgebotao9700]

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

[@ExcelOffTheGrid]

Thanks 👍

[@sergegagne874]

Great stuff, thank you!

[@ExcelOffTheGrid]

My pleasure!

[@paulmartinlife]

Thanks, that's excellent.

[@ExcelOffTheGrid]

Thank Paul, I’m glad you liked it.

[@martyc5674]

Really cool trick 👍👍👍

[@ExcelOffTheGrid]

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

[@mattmattelig]

VERY clever!!

[@ExcelOffTheGrid]

Thanks 👍

[@RathinagiriSubbiah]

Wow man! A life saver!

[@operamaniak81]

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

[@ExcelOffTheGrid]

Glad to hear that! 😁

[@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. 👍

[@slayermm1122]

amazing trick.

[@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.

[@bdiffoful]

Masterclass

[@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.

[@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 😬)