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

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

[@AdrianoAlvesApolináriodaSilva]

Thank you!!! Only this solutions worked for me!

[@ExcelOffTheGrid]

Great! Glad it was useful.

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

[@graemegourlay2850]

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

[@ExcelOffTheGrid]

Glad it was helpful! 👍

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

[@dchan6017]

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

[@ExcelOffTheGrid]

Happy to help!

[@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! 😁

[@waitplanwp4129]

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

[@ExcelOffTheGrid]

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

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

[@mohammedelsakally540]

Awesome methodology and presentation, Thank you very much.

[@ExcelOffTheGrid]

Glad it was helpful!

[@jerrymiles7804]

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

[@ExcelOffTheGrid]

Thank you very much!

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

[@stefankirst3234]

Brilliant! Exactly what I needed. Thanks a lot 😊

[@ExcelOffTheGrid]

Glad it helped!

[@maheshlowe907]

Great. I was looking for this function for hours.

[@ExcelOffTheGrid]

Glad I could help!

[@wayneedmondson1065]

Awesome Mark! Thanks for demonstrating. Thumbs up!!

[@ExcelOffTheGrid]

Thanks Wayne, I'm glad you enjoyed it.

[@Henrik.Vestergaard]

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

[@ExcelOffTheGrid]

Glad it was helpful!

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

[@daodao3746]

Hi i followed your steps in creating the sumifs for all items. But there three items namely Charlie, Golf and Hotel has no value turned out in the sumifs values. Can you kindly advise what are the possible errors that could lead to this?

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

[@sel4sie]

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

[@sergegagne874]

Great stuff, thank you!

[@ExcelOffTheGrid]

My pleasure!

[@shyjumathew07]

Excellent!!!!

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

[@martyc5674]

Really cool trick 👍👍👍

[@ExcelOffTheGrid]

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

[@jorgebotao9700]

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

[@ExcelOffTheGrid]

Thanks 👍

[@RathinagiriSubbiah]

Wow man! A life saver!

[@nishantkumar9570]

Awesome 👌👏

[@paulmartinlife]

Thanks, that's excellent.

[@ExcelOffTheGrid]

Thank Paul, I’m glad you liked it.

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

[@s.burghardt]

very helpfull. thank you!

[@ExcelOffTheGrid]

You're welcome!

[@slayermm1122]

amazing trick.

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

[@bdiffoful]

Masterclass

[@khayourabdellah8727]

Good tks

[@ExcelOffTheGrid]

You’re welcome 👍

[@danielaifrim75]

Super tare

[@Rice0987]

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

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

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

[@mattmattelig]

VERY clever!!

[@ExcelOffTheGrid]

Thanks 👍