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

★ 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

[@stefankirst3234]

Brilliant! Exactly what I needed. Thanks a lot 😊

[@ExcelOffTheGrid]

Glad it helped!

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

[@wayneedmondson1065]

Awesome Mark! Thanks for demonstrating. Thumbs up!!

[@ExcelOffTheGrid]

Thanks Wayne, I'm glad you enjoyed it.

[@graemegourlay2850]

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

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@dchan6017]

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

[@ExcelOffTheGrid]

Happy to help!

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

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

[@ExcelOffTheGrid]

Glad it was helpful!

[@mohammedelsakally540]

Awesome methodology and presentation, Thank you very much.

[@ExcelOffTheGrid]

Glad it was helpful!

[@waitplanwp4129]

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

[@ExcelOffTheGrid]

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

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

[@shyjumathew07]

Excellent!!!!

[@jerrymiles7804]

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

[@ExcelOffTheGrid]

Thank you very much!

[@maheshlowe907]

Great. I was looking for this function for hours.

[@ExcelOffTheGrid]

Glad I could help!

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

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

Wow man! A life saver!

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

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

VERY clever!!

[@ExcelOffTheGrid]

Thanks 👍

[@bdiffoful]

Masterclass

[@sel4sie]

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

[@khayourabdellah8727]

Good tks

[@ExcelOffTheGrid]

You’re welcome 👍

[@danielaifrim75]

Super tare

[@jorgebotao9700]

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

[@ExcelOffTheGrid]

Thanks 👍

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

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

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.

[@user-pr8ew9ku6b]

@@ExcelOffTheGrid thank you 👍

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

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

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