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

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post ★

★ 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 ★

★ Where to find Excel Off The Grid ★

#MsExcel #PowerQuery

[AdrianoAlvesApolináriodaSilva]

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

[graemegourlay]

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

[IvanCortinas_ES]

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

[waitplanwp]

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

[jerrymiles]

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

[dchan]

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

[mohammedelsakally]

Awesome methodology and presentation, Thank you very much.

[maheshlowe]

Great. I was looking for this function for hours.

[operamaniak]

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

[Picla_Peremohy]

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

[SJV]

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

[stefankirst]

Brilliant! Exactly what I needed. Thanks a lot 😊

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

[Henrik.Vestergaard]

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

[wayneedmondson]

Awesome Mark! Thanks for demonstrating. Thumbs up!!

[freeworld]

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

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

[selsie]

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

[jorgebotao]

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

[jimfitch]

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