Using slicers with formulas in Excel | Allow users to select parameters | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Download the example file:★
exceloffthegrid.com/wp-conten...
★ About this video ★
Slicers are a great tool for adding user interactivity. Natively they currently only work with Pivot Tables, Pivot Charts, tables and cube functions. But, how can we bring the power of Slicers to standard formulas? In this video, I'll show you how.
★ 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 #ExcelOffTheGrid

[@oohzulu]

This is great, thank you for taking the time to share your brilliance

[@ExcelOffTheGrid]

My pleasure! :-)

[@chimo2dax]

Thank you, your explanations are easy and to the point. Can't wait to try it with my projects👍👍

[@ExcelOffTheGrid]

Great, pleased I could help 😊

[@ziggle314]

Fantastic presentation! I have an immediate use for this approach. Thanks.

[@ExcelOffTheGrid]

Thank you. Hopefully you can put it to good use. 😀

[@meia_noite]

Thank you so much! Just what I needed from min 15! Multiple criteria with multiple slicers!

[@ExcelOffTheGrid]

Hopefully the first 15 minutes were not a waste. 😂

[@ketandparanjape]

Absolutely Amazing & Helpful - This can be used in multiple places

[@ExcelOffTheGrid]

Thanks - it can get a little complicated with multiple slicers and multiple selections. But hope you find it a useful technique. 😀

[@daleanderson5258]

That was very good. Thank you.

[@ExcelOffTheGrid]

Thanks Dale. I'm glad you enjoyed it :-)

[@modernaone]

You got another Subscriber. This is really brilliant. Also, nice pace. Thanks so much!!

[@ExcelOffTheGrid]

Thank you 😀

[@mariamelaniereyes2691]

Subscribed. Thank you!

[@ExcelOffTheGrid]

You are awesome, thank you!

[@bryanc65]

super helpful and creative use of formula logic. Thank you.

[@ExcelOffTheGrid]

Thanks, I’m glad you found it useful.

[@arakelasson832]

Thank you Sir ! very useful itself and also a generator of ideas for other uses of Boolean logic

[@ExcelOffTheGrid]

Yes, you’ve got it. This is just one application of using arrays to create Boolean logic. I think the reverse COUNTIF is a key part of achieving this in many contexts. 😀

[@alansidman]

Mark--Very slick presentation!

[@ExcelOffTheGrid]

Thanks Alan 😀

[@RishabGoel1994]

Too Good! Nice.

[@ExcelOffTheGrid]

Thanks Rishab 😀

[@PaulaWeb3andCrypto]

Brilliant!

[@ExcelOffTheGrid]

Thanks Paula 😊

[@perfectvisit]

I think this is exactly what I needed! I will play with it and see if it works Thank you 🙏

[@ExcelOffTheGrid]

Check out this link too, which is an alternative approach. kzbin.info/www/bejne/noCwmJ1_lLmfqpo

[@sumardjo]

thanks for your knowledge simple file for easy to practice thanks you very much

[@ExcelOffTheGrid]

Thanks - I’m glad you could follow along and understand the principles.

[@BIGorilla]

Cool use of slicers! It really makes it easy for people to combine regular formulas with slicers while keepings things relatively simple. Learned something about the TEXTJOIN function too. Are you planning on posting about Cube Formulas too?

[@ExcelOffTheGrid]

Thanks Rick - Cube formulas have been in my content list for a long time. They are a pretty cool feature. Maybe later in the year.

[@Up4Excel]

Excellent techniques in this video Mark. Getting the chosen criteria info from multiple selections on multiple slicers is a problem I've never seen solved on video and you explain it very clearly. My particular use case is to add titles to pivot charts which requires an extra step of linking the slicers to additional pivot tables to drive the lists...I shall be using this technique very often. Very glad I've found your channel as so much useful content on here. Keep it up 👍

[@ExcelOffTheGrid]

It’s a bit of a hack, but works for most people. I now wonder if a slicer connected to a Table would actually be a better option.

[@jsbaker1972]

@@ExcelOffTheGrid I tried it but the table just filters so you never know which row numbers your results will be on. Pivot tables are the way to go.

[@FRANKWHITE1996]

Thanks for sharing❤

[@ExcelOffTheGrid]

You're welcome Frank.

[@shubhabratadey]

Great Video...I am going to save it for my future references... 😄

[@ExcelOffTheGrid]

Great stuff. It's a useful little trick from time to time.

[@fliptop157]

Awesome! 🚀

[@ExcelOffTheGrid]

Thanks. I've got a newer method which might interest you too. kzbin.info/www/bejne/noCwmJ1_lLmfqpo

[@fliptop157]

@@ExcelOffTheGrid thanks!

[@hant89]

That was pure genius..

[@ExcelOffTheGrid]

Thanks… I appreciate that 😀

[@johnsonbrown5735]

Marvelous

[@ExcelOffTheGrid]

Thanks :-)

[@jimfitch]

Great trick! I use dynamic arrays extensively & have wanted to incorporate slicers for users’ ease of use, but couldn’t figure out how to do that. Now I know. Looking forward to adding this functionality. Thanks!

[@ExcelOffTheGrid]

Thanks Jim - Dynamic arrays certainly make this technique easier to apply than traditional formulas 👍

[@francisbraden1309]

I guess Im randomly asking but does anyone know a method to get back into an instagram account..? I stupidly lost the password. I would appreciate any assistance you can give me.

[@elliotclark6770]

@Francis Braden Instablaster =)

[@francisbraden1309]

@Elliot Clark Thanks for your reply. I found the site on google and Im waiting for the hacking stuff atm. I see it takes quite some time so I will get back to you later when my account password hopefully is recovered.

[@francisbraden1309]

@Elliot Clark it worked and I actually got access to my account again. Im so happy:D Thank you so much you really help me out !

[@mkparker99]

This is brilliant! I'm hoping this will work with filter and xloopup.

[@ExcelOffTheGrid]

Thank You. Yes, it should work with both of those functions. 👍

[@naveensharma9436]

It's fantastic bro

[@ExcelOffTheGrid]

Thank you, I’m glad you liked it.

[@sokratespanopoulos7143]

Excellent

[@ExcelOffTheGrid]

Thanks 😀

[@asifsfm]

Waoo great work.....

[@ExcelOffTheGrid]

Thanks a lot 😊

[@carolinereinbach8879]

Thank you so much for the video, it is amazing! I just got stuck at one point, I have at my table a column with dates and in my formula I need to insert only dates from a specific period, but I can not make it work using countif. Is there any chance you could please help me out? I have tried countif(">="&Z10;$A$2:$A$20) and many other ways, but it did not work. Z10 is just a regular date in the same padron (short date).

[@user-rq1wj9ns9s]

Hi Mark, I have a additional requirement over and above the multiple slicers + multiple criteria solution you have given. Is there a way that I get the entire row or rows filtered out instead of getting the sum of the values.

[@peltiertech1879]

I knew how to use the slicers, but I wasn't familiar with the reverse COUNTIF technique. Thanks

[@ExcelOffTheGrid]

I suspect I may have invented the term ‘Reverse COUNTIF’, but it seems like the best way to describe it.

[@michalroesler]

I didn't understood what version of Excel do I need to have, for theses formulas to work in the way presented in the video. I mean Vlookup and Sumifs with multiple criteria in the first 5 minutes of the video. Can you please write this for me (the Excel version)? Great video by the way.

[@ExcelOffTheGrid]

I'm using Excel 365, which automatically spills results into multiple cells. Check out this video: kzbin.info/www/bejne/inTalpZuYtmmes0 This video demonstrates the techniques that will work with Excel 365, and also earlier versions.

[@amyjain97]

For multiple selection in a single slicer i applied countifs and added sum but not getting the value when I select multiple data in slicer, please help

[@nkowk]

👍👍👍

[@krishnaram789]

Thank you, I am facing performance issue when I used multiple slicer . Can u plz tell for fix this issue.

[@ExcelOffTheGrid]

Performance is a known issue when using a lot of array formulas, as the number of calculations performed by Excel in the background increases significantly. Given that most of this effect is formula driven, it comes down to your skills with picking and using the most efficient formulas for a given scenario.

[@atletimaniac]

Hello Mark, i see you are using the slicer to filter by sales rep and then do a sumif. Is it possible to do a countif instead? My issue is that i’m trying to use the slicer to filter by month (data column is as dd/mm/yyyy) and then do a countif of values. Any insight would be very much appreciated.

[@ExcelOffTheGrid]

In the video, I'm showing the principles to follow. It should work with COUNTIF. For you specific scenario, you may need to use the MONTH function to extract the month of the dd/mm/yyyy column.

[@kevinricobudiyono8863]

Hello Mark, in my case is that i'm trying to use the slicer to filter date range COUNTIF("

[@mohammadafsari4686]

Tnx 4 nice video , just in multiple values text when I wrote formula gives a #Name error! !

[@ExcelOffTheGrid]

Which version of Excel have you got? TEXTJOIN is currently only available in Excel 2019 and Excel 365.

[@mohammadafsari4686]

@@ExcelOffTheGridhello excel 2016

[@iampcal]

I've been trying to make this work but since I have multiple criteria's I kept on having errors. -__-

[@mikelennon1078]

When I try to insert any slices on any tables, a box comes up asking for "Existing Connections" asking to select a connection or this workbook's Data Model and there is nothing to select to proceed. Your thoughts please.

[@chinacetacean]

Ciao, the problem could be that you work with a very old version of office or with a Mac. Try to create a pivot table instead of a normal table and add a slicer this way.

[@nituradu3467]

Hello guys! I need a little help : when i do sumifs formula ( without the pivot with slicer criteria ) the formula it's work.But when i add the range and criteria for the pivot ( in the exemple for sales rep ) i have an #SPILL! error. Can you help me ?

[@ExcelOffTheGrid]

The #SPILL! error most likely means - There are some cells blocking the spill range (check out this video for a better understanding of Dynamic Arrays) - You are trying to using the formula inside an Excel Table

[@s.y.daniel2137]

advance use of basic functions!

[@ExcelOffTheGrid]

Exactly - that’s the skill. Knowing how to use them together is the key.