Next level FILTER Function tricks

Next level FILTER Function tricks | Excel Off The Grid

Рет қаралды 14,581

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the example file ★
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-...
File Reference for this video: 0214 FILTER function tricks.zip
★ About this video ★
This video includes amazing FILTER function tricks that you can use to take your spreadsheets beyond the next level.
INCLUDES:
0:00 Introduction
0:17 FILTER Recap
1:42 Multiple Conditions
3:01 If_empty Array
3:52 Partial Match
5:06 Based on List
6:10 Using Slicers
8:15 Automate Excel
8:40 Show All Items
10:03 Specific Columns
11:04 User Column Selection
12:00 Dependent Drop Down
13:09 Wrap-up
★ 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

Пікірлер: 106

@ExcelOffTheGrid Ай бұрын

Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders File Reference for this video: 0214 FILTER function tricks

@excelgazialimuhiddinhacibekir Ай бұрын

HATS OFF to Excel OFF the Grid! Much, but very very much appreciated, Mark! You are absolutely a genius! It is so hard to thank you enough!

@ExcelOffTheGrid Ай бұрын

Thank You - that is so kind of you to say. I really appreciate it. 😁

@IvanCortinas_ES Ай бұрын

Excellent video Mark. As always, going a little deeper, where others don't go. Very professional. Thank you so much.

@ExcelOffTheGrid Ай бұрын

It took a little while to prepare and edit, but hopefully it was worth it.

@90hsilva 2 күн бұрын

Thank you for this very useful video, the way you explain is very easy to understand it.

@daleanderson5258 11 сағат бұрын

That was great, thanks. What a powerful function!

@kndeepak Ай бұрын

Ridiculously easy to understand!! Outstanding as usual

@ExcelOffTheGrid Ай бұрын

Thank You 😁

@sherryfox227 Ай бұрын

@MMark, outstanding! My favorite is Ex8 Filter/Choose specific columns. Thanks for sharing!!!!

@ExcelOffTheGrid Ай бұрын

Yes, that is very useful. Gives you a lot of control. But… the formatting can be tricky.

@martyc5674 Ай бұрын

Brilliant Mark- I’ve seen all of these before but not all in the one place- great Video

@ExcelOffTheGrid Ай бұрын

Glad you enjoyed it 👍

@colinekszczecin Ай бұрын

Filtering based on a list will be a big game changer for me, thank you very much for this video

@ExcelOffTheGrid Ай бұрын

Great news - I’m glad you’ll be able to put it into action.

@cherianiype Ай бұрын

one of the best videos on FILTER... thank you very much Mark! Cherian in Abu Dhabi!

@ExcelOffTheGrid Ай бұрын

Wow, thanks! 😁

@noelgreen6198 Ай бұрын

Brillant explanation as always. Have been using this function since its release, but learn a lot from your logical process steps. Thanks a lot.

@ExcelOffTheGrid Ай бұрын

Great stuff. It’s always useful to pick up new tricks.

@PhilipDrown Ай бұрын

Excellent step-by-step, from the ground up, logical, walk through explanation! I have been playing with developing a new streamlined method of searching and filtering data and this is giving me some ideas. Much thanks! (Just subscribed as well.)

@ExcelOffTheGrid Ай бұрын

Great stuff - lots of new things to try here. So you’ve got some fun days ahead. 😁

@liquidapathy82 28 күн бұрын

Absolutely amazing and in depth. I learned many things, I am happy to say!

@UtuDudas Ай бұрын

For filtering by a list, I recommend ISNUMBER with XMATCH. It works better because you can use it inside of LET. XMATCH can use arrays only whereas COUNTIF/S needs at least a range to work.

@ExcelOffTheGrid Ай бұрын

Yes, that’s very true about COUNTIFS not taking arrays. Is there a reason to use XMATCH over just MATCH? Does it provide any advantages?

@UtuDudas Ай бұрын

No, in this particular instance you could just use the old MATCH

@oxnardmontalvo7749 3 күн бұрын

I feel I got a lot smarter, althoug I only understood about 40% of it on the first watch. excellent video!

@ExcelOffTheGrid 3 күн бұрын

Watch it again... then you'll understand to 60%. Then watch it again.... After a while you'll understand 100% and they will become boring old FILTER techniques which you can't grasp why everybody doesn't understand them. 😂

@rajrawat287 26 күн бұрын

Watched Best video On FILTER functions ever.Thank you for Your Contribution to our Success

@ExcelOffTheGrid 20 күн бұрын

Thanks - that’s so nice to hear.

@eduardomunoz2764 Ай бұрын

Magistral video!.. Saludos y gracias por compartir.

@ExcelOffTheGrid Ай бұрын

Thank you. I'm happy you've found it so useful.

@donniemcgee7523 Ай бұрын

Estoy completamente de acuerdo. Los videos de Mark son entre Los mejores.

@osoriomatucurane9511 Ай бұрын

Another Epic tutorial, I knew right away that it would be outstanding one from Mark, specially when it come to play arround with the filter as you have cracket advanced filter with vba. You are absolutely an Excel Legend, always going extra mile. I couldnt agree more, that your Excel skills are unmatched! Keep it up. By the way, do you have any tutorial on Excel lambda(). I cant thank you enough for your awesome tutorials. ❤

@ExcelOffTheGrid Ай бұрын

Thank you for your kind comments. Some of my videos use LAMBDA, but not a specific video about it. My next course will be about LAMBDA, so I will go deep there.

@silvanodemetrio407 Ай бұрын

Awesome. I've been looking for "filter" functions examples and that's the best one. Great video!!!!

@ExcelOffTheGrid Ай бұрын

Awesome, thank you!

@williamarthur4801 Ай бұрын

Really like the return all option, also the use of choosecols, I still head straight to index or worse offset for a lot of things where these 'newer' funcs could be used.

@ExcelOffTheGrid Ай бұрын

INDEX is still the most important Excel function. So, it’s a solid choice. I toyed with showing INDEX, but decided that it would better to show another newish function.

@muhammadsadiq2330 3 күн бұрын

Very nice video, learnt a lot from it.

@andreyserdyukov7465 Ай бұрын

Thank you!

@alexrosen8762 Ай бұрын

One of the best tutorials I have seen so far on the filter function 👌

@ExcelOffTheGrid Ай бұрын

Wow! Thank you. I appreciate that.

@Tfla803 Ай бұрын

Great video!

@ExcelOffTheGrid Ай бұрын

Thanks!

@viktorasgolubevas2386 Ай бұрын

wide, deep, quality material ! thanks a lot for sharing ! - - q: why in Ex7 SEQUENCE(ROWS) technique is used instead of more "consistent" for conditionals/booleans, say, Example7[Type]=Example7[Type]

@ExcelOffTheGrid Ай бұрын

Thank You. For Ex7 - there are probably 10 ways to do that, and I had to pick one. So that’s the one I went with. No specific reason.

@viktorasgolubevas2386 Ай бұрын

@@ExcelOffTheGrid ok :)

@Bhavik_Khatri Ай бұрын

Thank you for sharing these excellent techniques.

@ExcelOffTheGrid Ай бұрын

You are welcome!

@gabrielgordon Ай бұрын

Wow! super extra extremely useful! Thanks!

@ExcelOffTheGrid Ай бұрын

Glad it was helpful! 👍

@chrisklimantiris2520 Ай бұрын

Thank you Mark.Great explanation

@ExcelOffTheGrid Ай бұрын

Thank you. 😁

@h.esther9400 Ай бұрын

Excellent video! I often use the filter function, but still learned a lot with these tips! Thanks!

@ExcelOffTheGrid Ай бұрын

Great news - I hope you can put some of the techniques to good use.

@donniemcgee7523 Ай бұрын

This is extremely helpful. Very well done! Thank you!

@ExcelOffTheGrid Ай бұрын

Glad it was helpful!

@stevemorris4938 Ай бұрын

Good roundup and a couple of new tricks for me :) Ctrl + Enter keeps the current cell selected so you don't have to leep moving up again

@ExcelOffTheGrid Ай бұрын

Thanks. I know about Ctrl + Enter... I just don't use it. I'm not even thinking about what the next thing is until I've seen the result of the previous step. So, for me, it's an irrelevance.

@kebincui Ай бұрын

Super video as always👍❤. Thanks Mark.

@ExcelOffTheGrid Ай бұрын

Kebin - Thank You as always. I appreciate your support.

@krishnatate4765 Ай бұрын

Thank u sir ❤❤

@et.sachin Ай бұрын

Excellent content 💯

@ExcelOffTheGrid Ай бұрын

Appreciate that, thank you. 😁

@clivepetty2338 Ай бұрын

Mark, very helpfully and very well presented

@ExcelOffTheGrid Ай бұрын

Thank you, I'm glad it was helpful!

@piotrvsa317 Ай бұрын

Thx for great examples and summary of FILTER function. So clearly explained :))) Bravo!

@ExcelOffTheGrid Ай бұрын

Thank you. I hope you can put them to good use.

@MarcelFaltermeier Ай бұрын

Thank you for sharing this amazing video. Good job! I have a question. Could you also do an example with a filter function (dropdown list or slicer) and a scrollbar? Idea is to display in a dashboard only 10 rows and by scrolling down with the scrollbar you see the rest items. One more specific thing. The database has more column I want to show in a dashboard. So I only want to certain columns.

@ExcelOffTheGrid Ай бұрын

While you can link a scroll bar value to a cell, you can’t set the length of the scroll bar based on a cell. Therefore, this requires VBA to achieve it. So there is a lot more nuance, which would need to be covered.

@user-wr3el1te1l Ай бұрын

Amazing depth Mark ❤❤❤❤

@ExcelOffTheGrid Ай бұрын

Thank you 😁

@usmaniqbal1836 Ай бұрын

Perfect 👍

@ExcelOffTheGrid Ай бұрын

Thanks 👍

@petercompton538 Ай бұрын

Another superb video Mark, I learnt a lot.

@ExcelOffTheGrid Ай бұрын

Great stuff - thanks Peter. 😁

@waitplanwp4129 Ай бұрын

so love this chanel❤

@ExcelOffTheGrid Ай бұрын

Thanks. I hope we can keep delivering quality content.

@ivanbork4175 Ай бұрын

Wow - Normally I consider myself fairly good using Excel, but this is a bit like playing in the Sunday league and watching Premia league. The part using Slicers, and the capability to let users choose columns, are new to me, thanks One question, I see that you wrap choosecols inside filter, but I guess that it works as well wrapping filter inside choosecols?, I find it easier to do it like that!.

@ExcelOffTheGrid Ай бұрын

Thanks - I'm glad I should share something new. In terms of CHOOSECOLS inside/outside the FILTER. Either will work. I went with the inside because in theory, it should be faster as it does the easier task (i.e. CHOOSECOLS) to reduce the columns, then uses the FILTER (the harder task) on a smaller array. But I have no idea if it actually makes any difference.

@hahome155 Ай бұрын

Much better tutorial than anyone else. Kudos ❤

@ExcelOffTheGrid Ай бұрын

Thank you - that is very kind of you to say. 😁

@safuwanch Ай бұрын

As always nice video 🎉❤

@ExcelOffTheGrid Ай бұрын

Thank you! 😃

@spilledgraphics Ай бұрын

Mark amazing video! one question though: is it me or it is counter-intuitive that on minute 5:41, the arguments of the COUNTIFS function are inverted? Meaning shouldn't the criteria range (property column on the table) be first and then the criteria, which is your list?

@ExcelOffTheGrid Ай бұрын

Good spot. Yes it is counter intuitive. I usually call this a “Reverse COUNTIFS”. We want the function to spill for each item (known as a scalar), therefore we need to provide multiple scalars in the criteria argument.

@Trucpq 26 күн бұрын

Can you share the solution for Example 6 for older Excel version (without Lambada function). Thanks a lot.

@ExcelOffTheGrid 20 күн бұрын

You can try this method. But it’s not as good as the LAMBDA version: kzfaq.info/get/bejne/nrV9ms16xLrQpGw.htmlsi=vsv5_c1CQ-KTJtfh

@yvlove77 Ай бұрын

Thank you for this! FILTER is pretty much my favorite function! I often use =UNIQUE(CHOOSECOLS(FILTER(....))) to pull specific filtered columns; I had not considered =FILTER(CHOOSECOLS...)) before. Do you know if both formulas work about the same? Or is one more efficient than the other? I always want to make sure I'm using the most efficient formulas! Thanks!! Great video!

@ExcelOffTheGrid Ай бұрын

I don’t think it makes much difference. In theory using CHOOSECOLS first would be more efficient as it reduces the size of the array before doing more complex calculations. But I have no idea if that is true.

@yvlove77 Ай бұрын

@@ExcelOffTheGrid Thanks for the response!

@dharmmu Ай бұрын

Making us understand that true and false logic in filter open lots of different permutations and combinations which can be used. its really insane😂

@ExcelOffTheGrid Ай бұрын

It’s all about True/False. The same techniques work on the new GROUPBY and PIVOTBY functions.

@22niloc Ай бұрын

Great content! Re. filling no match values across the entire row. The formula fills down the first column rather than across all columns. Any thoughts to fix this?

@ankursharma6157 Ай бұрын

Please ensure You are using a comma and not a semi-colon. Comma - to spill the values in columns. Semi-colon - to spill the values in rows. Example: {1, 2, 3, 4, 5} Hope, this helps.

@22niloc Ай бұрын

Thanks for your reply. I'm actually using the French version which uses semi colons rather than commas. I've tried both punctuations but still no success. I'm doing something wrong...

@ExcelOffTheGrid Ай бұрын

I think with French settings it is the \ character.