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

No video

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

Рет қаралды 77,989

Excel Off The Grid

Күн бұрын

Пікірлер: 85

@meia_noite 2 жыл бұрын

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

@ExcelOffTheGrid 2 жыл бұрын

Hopefully the first 15 minutes were not a waste. 😂

@ziggle314 3 жыл бұрын

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

@ExcelOffTheGrid 3 жыл бұрын

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

@MarissieV Жыл бұрын

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. kzfaq.info/get/bejne/nrV9ms16xLrQpGw.html

@ElTigreFIME 2 жыл бұрын

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

@ExcelOffTheGrid 2 жыл бұрын

Thank you 😀

@jimfitch 3 жыл бұрын

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 3 жыл бұрын

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

@francisbraden1309 3 жыл бұрын

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 3 жыл бұрын

@Francis Braden Instablaster =)

@francisbraden1309 3 жыл бұрын

@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 3 жыл бұрын

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

@Up4Excel 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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

@chimo2dax 3 жыл бұрын

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

@ExcelOffTheGrid 3 жыл бұрын

Great, pleased I could help 😊

@alansidman 2 жыл бұрын

Mark--Very slick presentation!

@ExcelOffTheGrid 2 жыл бұрын

Thanks Alan 😀

@BIGorilla 4 жыл бұрын

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 4 жыл бұрын

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.

@arakelasson832 3 жыл бұрын

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

@ExcelOffTheGrid 3 жыл бұрын

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

@ketandparanjape 4 жыл бұрын

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

@ExcelOffTheGrid 4 жыл бұрын

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

@bryanc65 Жыл бұрын

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

@ExcelOffTheGrid Жыл бұрын

Thanks, I’m glad you found it useful.

@shubhabratadey 2 жыл бұрын

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

@ExcelOffTheGrid 2 жыл бұрын

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

@oohzulu 3 жыл бұрын

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

@ExcelOffTheGrid 3 жыл бұрын

My pleasure! :-)

@sumardjo 2 жыл бұрын

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

@ExcelOffTheGrid 2 жыл бұрын

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

@yezdanirani 28 күн бұрын

Thanks super helpful video

@mkparker99 2 жыл бұрын

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

@ExcelOffTheGrid 2 жыл бұрын

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

@hant89 2 жыл бұрын

That was pure genius..

@ExcelOffTheGrid 2 жыл бұрын

Thanks… I appreciate that 😀

@daleanderson5258 4 жыл бұрын

That was very good. Thank you.

@ExcelOffTheGrid 4 жыл бұрын

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

@PaulaWeb3andCrypto 4 жыл бұрын

Brilliant!

@ExcelOffTheGrid 4 жыл бұрын

Thanks Paula 😊

@mariamelaniereyes2691 3 жыл бұрын

Subscribed. Thank you!

@ExcelOffTheGrid 3 жыл бұрын

You are awesome, thank you!

@naveensharma9436 Жыл бұрын

It's fantastic bro

@ExcelOffTheGrid Жыл бұрын

Thank you, I’m glad you liked it.

@asifsfm Жыл бұрын

Waoo great work.....

@ExcelOffTheGrid Жыл бұрын

Thanks a lot 😊

@FRANKWHITE1996 Жыл бұрын

Thanks for sharing❤

@ExcelOffTheGrid Жыл бұрын

You're welcome Frank.

@RishabGoel1994 4 жыл бұрын

Too Good! Nice.

@ExcelOffTheGrid 4 жыл бұрын

Thanks Rishab 😀

@fliptop157 Жыл бұрын

Awesome! 🚀

@ExcelOffTheGrid Жыл бұрын

Thanks. I've got a newer method which might interest you too. kzfaq.info/get/bejne/nrV9ms16xLrQpGw.html

@fliptop157 Жыл бұрын

@@ExcelOffTheGrid thanks!

@carolinereinbach8879 5 ай бұрын

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

@johnsonbrown5735 3 жыл бұрын

Marvelous

@ExcelOffTheGrid 3 жыл бұрын

Thanks :-)

@peltiertech1879 2 жыл бұрын

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

@ExcelOffTheGrid 2 жыл бұрын

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

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

@michalroesler 3 жыл бұрын

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 3 жыл бұрын

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

@sokratespanopoulos7143 3 жыл бұрын

Excellent

@ExcelOffTheGrid 3 жыл бұрын

Thanks 😀

@mohammadafsari4686 3 жыл бұрын

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

@ExcelOffTheGrid 3 жыл бұрын

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

@mohammadafsari4686 3 жыл бұрын

@@ExcelOffTheGridhello excel 2016

@atletimaniac 3 жыл бұрын

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 3 жыл бұрын

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("

@mikelennon1078 3 жыл бұрын

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 3 жыл бұрын

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.

@iampcal 2 жыл бұрын

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

@amyjain97 2 жыл бұрын

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

@nituradu3467 2 жыл бұрын

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 2 жыл бұрын

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

@krishnaram789 3 жыл бұрын

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

@ExcelOffTheGrid 3 жыл бұрын

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.