Excel FILTER Function + Trick to Rearrange Column Order

Excel’s FILTER function is incredible. It enables you to extract a subset of your data based on criteria you specify. You can think of it like a lookup that returns multiple matches. It can return a single column of data or multiple columns, and with a clever trick, which I’ll show you at the end, it can even return non-adjacent columns.
Download the Excel file here: www.myonlinetraininghub.com/e...
0:00 Introduction to FILTER
0:31 FILTER function Syntax
1:03 FILTER with one criteria
2:03 FILTER with multiple AND criteria
4:31 FILTER with multiple OR criteria
6:26 Handing errors
7:56 FILTER and return non-contiguous columns
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy

[@PCor18]

Disclaimer: If any European people have trouble getting this to work use {1\2} instead of {1;2} if the semicolon is your default separator.
After I spent nearly an hour figuring out this wasn't working because differences in regional formatting I'm so grateful for this very, very elegant solution!

[@MyOnlineTrainingHub]

Thanks for sharing 😊

[@GSAcharya-uv8cc]

Wow, I was dying to filter only 3 column out of 15 columns, with table having hundreds of rows, and you have just given me the torch for my dark road. Thank you very much.

[@MyOnlineTrainingHub]

So pleased I could help!

[@lorenzoladejobi8701]

The expo on use of "FILTER and return non-contiguous columns" is a game changer for me ! Thank you Mynda

[@MyOnlineTrainingHub]

Glad you liked it, Lorenzo! 😊

[@aviman2010]

Thanks Mynda. You've turned my world upside down (in a positive way) yet again. Love your teaching style

[@MyOnlineTrainingHub]

Awesome! Thank you!

[@indzara]

Really liked the OR logic and the fact that everything >0 is treated the same. Nice design. Enabling multiple error values is another good tip. Thanks for sharing. 👍

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@AnthonyEnglish]

Phenomal, clear explanation, taking us step by step. This filter function is great, and I love the bit at the end where you show non-adjacent columns.

[@MyOnlineTrainingHub]

Thanks so much, Anthony!

[@MichaelBrown-lw9kz]

The way you used the CHOOSE function was brilliant. I would like to think I would have come up with this, but I don't know. Absolutely fantastic

[@MyOnlineTrainingHub]

Thank you very much! 🤩

[@jeremylau93]

The last part is amazing!! Filter and choose function work together. That’s cool~

[@MyOnlineTrainingHub]

Glad you liked it, Jeremy! 😊

[@JeffreyWigington]

I did not know about the Choose function before! That is a really nice way to rearrange columns on the filter! I add seen another method using a double filter where the inner filter was on the whole table, but the second filter was a set of logicals [1,0,0,1] to "turn on" certain columns. It works well for removing unwanted columns, but doesn't let you rearrange columns. The choose function makes this so much cleaner. Thanks for your example!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@mathiasburkard2403]

Thanks Mynda. Thats exactly what I was looking for. I love the way you teach Excel!

[@MyOnlineTrainingHub]

Awesome 😊

[@brandonnesset1571]

You made my day today. You are always someone I can rely on to learn something new. Thank you Mynda!

[@MyOnlineTrainingHub]

So pleased you find my videos helpful 😊🙏

[@Ahmed-fq6si]

This is so useful and amazingly well explained as usual.
Wish I could give 1000 likes to each of your videos.

[@MyOnlineTrainingHub]

Thanks so much, Ahmed 😊

[@peterharbman]

Always fun and exciting new formulas/functions and presented in an easy to understand fashion... thank you!

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@mattschoular8844]

That's was great. Thanks Mynda. I too like the Choose option for the non-contiguous results.

[@MyOnlineTrainingHub]

Cheers, Matt!

[@nikkicroxall6113]

Genuinely spent the last two days straight trying to figure out how to rearrange the columns.... thank you!!!

[@MyOnlineTrainingHub]

So pleased I could help, Nikki!

[@arkai59]

Thank you so much for your amazing work as always, very comprehensive and understandable

[@MyOnlineTrainingHub]

Thanks for your kind words 😊

[@stevereed5776]

Had to watch this twice to get my head around it, mainly because I've never used the CHOOSE function. This works great so will definitely be using it. Thanks for the video

[@MyOnlineTrainingHub]

Great to hear, Steve!

[@IvanCortinas_ES]

Thanks Mynda. Excellent review of the FILTER function. Everything is very practical and used.

[@MyOnlineTrainingHub]

Great to hear, Ivan 😊

[@ismaelkourouma5558]

Wow ! Amazing 🤩 ! I often use Filter function but I didn’t know some tips and tricks shared here. Thanks Mynda 🙏

[@MyOnlineTrainingHub]

Glad I could show you something new 😊

[@jcethan2859]

FILTER CHOOSE is my new favorite thing about O365, until the next thing inevitably takes its place. Great video! 👍

[@MyOnlineTrainingHub]

Thanks so much! 😊

[@robertoschenone9648]

The truck for filtering not contiguous columns Is really great! The best I have seen so far

[@MyOnlineTrainingHub]

Wow, awesome to hear, Roberto!

[@rajivghildiyal8265]

Thank you Mynda, very clear and well presented. Hoping to make the time and watch more of your videos and work. God speed and God bless!

[@MyOnlineTrainingHub]

Awesome to hear!

[@nadermounir8228]

Thank you Mynda for this great video and tip. Nice to use the choose function. Filter function saved me from using Index, small and row functions

[@MyOnlineTrainingHub]

Great to hear 😊

[@mickaell3451]

Hi Mynda! Thankx for the filter+choose combo. Will use it. Cheers

[@MyOnlineTrainingHub]

Great to hear 😊

[@stevenlagoe7808]

Such a clear explanation. Even I got it first time! Thanks.

[@MyOnlineTrainingHub]

Great to hear, Steven!

[@zubairso]

This is truly amazing! Thanks for the detailed video, will definitely give it a try.

[@MyOnlineTrainingHub]

Great to hear 😊

[@tomfontanella6585]

Very clear and useful video (as always!). Thank you!

[@MyOnlineTrainingHub]

Cheers, Tom!

[@ravichandrankannappan6552]

Very good way of illustrating how 'Filter' function works. Thanks 'MyOnlineTrainingHub' for teaching me nicely the Filter function. Please keep it up!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@helenasvensson6595]

Wonderful tutorial! So well explained and helpful! Thanks!

[@MyOnlineTrainingHub]

Thanks so much, Helena!

[@brianwied3702]

The last segment on discontiguous columns saved me a lot of time, excellent video!

[@MyOnlineTrainingHub]

Great to hear!

[@lww8132]

This solves my immediate problem at work! Thank you so much for sharing.

[@MyOnlineTrainingHub]

Great to hear!

[@utubeAgape]

So EXCELLENT! Thank you for another useful video!

[@MyOnlineTrainingHub]

My pleasure 😊

[@marksant5488]

You just helped me out big time with rearranging the columns! Thank you from Prague. :)

[@MyOnlineTrainingHub]

Great to hear, Mark!

[@bogdanp6092]

Filter and Choose a powerful combination! Thanks!

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@wayneedmondson1065]

Hi Mynda. Awesome examples! Love the {} trick for filling out the results table when no records are found and the CHOOSE trick to control the output format. Brilliant! Thanks for sharing :)) Thumbs up!!

[@MyOnlineTrainingHub]

Thanks so much, Wayne!

[@karimsamnani3660]

Thanks, Mynda! This is a wonderful formula, and you have a very nice explanation for your viewers. We all viewer like it your tutorial. God Bless you

[@MyOnlineTrainingHub]

Thanks for your kind words, Karim!

[@FaysalEasyExcel]

Mynda Your simplification is awesome

[@MyOnlineTrainingHub]

Thank you! 😃

[@alinaqvi6203]

Thanks Mynda , learnt a lot about FILTR function, your videos are full of knowledge.

[@MyOnlineTrainingHub]

Happy to hear that!

[@nonoobott8602]

Wow. This is very useful and so explicit. Thanks so much for sharing

[@MyOnlineTrainingHub]

My pleasure 😊

[@pupycron4302]

Literally one day ago I was trying to figure out how to rearrange columns but couldn't. Excellent timing on this video ;) What an amazing method. Thank you so much!

[@MyOnlineTrainingHub]

So pleased to hear that 😊

[@josecarlosconejo5724]

Amazing!! I knew how to get only the columns that I wanted by wrapping the FILTER function with another FILTER and a set of 1s and 0s as criteria, but I had no idea about how to rearrange the column order. Very smart use of the CHOOSE function. I use FILTER a lot at work, but with your tutorial I will take it to a new dimension. Many thanks for sharing.

[@MyOnlineTrainingHub]

Great to hear 😊

[@marlonmadrona1309]

@@MyOnlineTrainingHub Which version of Excel did you use on this video?

[@rusektor]

You can also use HSTACK instead of CHOOSE

[@darrylmorgan]

Hi Mynda!Great Tutorial On The Awesome FILTER Function...Thank You :)

[@MyOnlineTrainingHub]

My pleasure, Darryl! Glad you liked it 😊

[@vgb311]

Great! Thank you for the video step by step.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@fr-reisid5405]

Love all of your content, extremely well explained.
Have used CHOOSE() concept with FILTER() function from day 1.
But to make things even more easier have used tables for source data and with INDIRECT() function refer to FILTER() function header row.
When only you change FILTER() header value to existing source table valid header value job is done.
INDIRECT() is volatile function but for not too big data sets works like a charm.

[@MyOnlineTrainingHub]

Interesting use of INDIRECT 🤔 thanks for sharing.

[@Fabi_terra]

Thank you, Mynda! Great tutorial I learned a lot. 🤗

[@MyOnlineTrainingHub]

I'm so glad!

[@SamPandiangan]

This is another great tutorial, as usual.

[@MyOnlineTrainingHub]

Cheers, Sam!

[@user-ns5cq7bb2c]

Thank you so much!!!! I have a project that I can try this on and I'm sure it will save me so much time. You're always a fantastic help.

[@MyOnlineTrainingHub]

Awesome to hear!

[@shoaibrehman9988]

Super uses of filter function, thanks gr8 video.

[@MyOnlineTrainingHub]

Thanks, Shoaib!

[@Luciano_mp]

Great, i liked Filter. Thank you Mynda!

[@MyOnlineTrainingHub]

Cheers, Luciano! 😊

[@Luciano_mp]

@@MyOnlineTrainingHub For you too!

[@xinliu7379]

So powerful ! Thank you!

[@MyOnlineTrainingHub]

My pleasure 😊

[@miiihaaas]

Thanks for CHOOS(E)ing to show us how to FILTER and return non-contiguous columns :)

[@MyOnlineTrainingHub]

My pleasure 😊

[@vitruvian_man]

Good stuff. Thanks Mynda!

[@MyOnlineTrainingHub]

Thanks so much 😊

[@benign39]

So useful and helpful. Thank you.

[@MyOnlineTrainingHub]

Glad you’ll be able to make use of it, Jose!

[@zaighamuddinfarooqui1705]

Excellent. Thanks for such a great lesson👍

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@truewiking]

Great explained, solved my problem, Thank you very much

[@MyOnlineTrainingHub]

Glad I could help 😊

[@chrism9037]

Awesome video Mynda!

[@MyOnlineTrainingHub]

Thanks so much, Chris 😊

[@aivie1900]

omg. this is what I am looking for. thank you so much.

[@MyOnlineTrainingHub]

Wonderful to hear 😊

[@zainululum]

thanks for clear and shaep tutorial as always

[@MyOnlineTrainingHub]

You are welcome!

[@martyc5674]

Really good- I have been using filter and index to do the same, your method is cleaner if it’s just say 2-3 columns you want returned from a large data set. But index I think is cleaner if you want to Omit only a few columns from a large data set. Thanks a lot- this is great.

[@MyOnlineTrainingHub]

Cheers, Martin!

[@Giovanni-vx8xl]

you are amazing and a super teacher and beautifully of course :) Thank you for sharing

[@MyOnlineTrainingHub]

Glad you liked it, Giovanni!

[@daliladuf]

Amazing, as always!

[@MyOnlineTrainingHub]

Thanks, Dalila!

[@hamidsh4789]

Thanks... keep on excellent tutorials...

[@MyOnlineTrainingHub]

Thanks, will do!

[@anv.4614]

Thank you, tutor. great lesson.

[@MyOnlineTrainingHub]

Glad you liked it!

[@henryhache5509]

thank you! They removed my choosecols() function in an update, so i needed this!

[@MyOnlineTrainingHub]

I'd update again, because CHOOSECOLS is still available. Sounds like you may have had your version rolled back somehow.

[@jobygeorgev06]

Thanks for making our life more easier

[@MyOnlineTrainingHub]

It's my pleasure 😊🙏

[@redhaakhund1271]

Amazing... Thank you soooo much!

[@MyOnlineTrainingHub]

You're very welcome!

[@jiajiaphotography]

Wow, the last trick is really unexpected. Cool

[@MyOnlineTrainingHub]

Glad you liked it!

[@laStar972chuck]

The use of the CHOOSE function in combination with the matrix notation is sheer genius ! (didn't even know one could put table in the CHOOSE "values" argument...definitely redeem the usefulness of this function in my mind now, lmao)

[@MyOnlineTrainingHub]

So pleased you liked it 😁

[@spilledgraphics]

woah! great tip on minute 7:22 Mynda! 😁👌👌 thanks Mr. Excel !!! 🙏

[@MyOnlineTrainingHub]

Cheers! Glad you liked it 😊

[@davidferrick]

good stuff, never thought of using the CHOOSE option for non-contiguous column returns.

[@JoelShafron]

I use CHOOSE inside of UNIQUE to get a unique list from non-contiguous columns. Didn't think of using it here either.

[@MyOnlineTrainingHub]

Cheers, Dayve!

[@MyOnlineTrainingHub]

CHOOSE is such a versatile function. Underrated IMO 😊

[@JoelShafron]

@@MyOnlineTrainingHub Do you have a training on CHOOSE?

[@MyOnlineTrainingHub]

Please see here: www.myonlinetraininghub.com/excel-choose-function

[@pvrameshbabu]

Thanks. It's a very useful function.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@garyallan69]

Love the Non - Contiguous columns. I’m now starting to use Choose for the first time in my life LOL

[@MyOnlineTrainingHub]

Great to hear, Gary!

[@ChristosLefkimiotis]

It cried for choose and offset with match (for the headers) makes it more variable, Nice video

[@MyOnlineTrainingHub]

Glad I could help 😊

[@stephenmascarenhas1987]

Well presented with some intricacies in the formulae's

[@MyOnlineTrainingHub]

Thank you 😊

[@sukunair8918]

Thanks, literally very useful

[@MyOnlineTrainingHub]

Awesome to hear! 🙏

[@NiteLynr]

I think it says something about me when you said 'I hope you're excited to give it a try" and I thought "Yes, yes I am". Also, half-way through was thinking about a list of people who'd find great uses for it to share this with. Excellently presented as always, missed this one when it was released because we hadn't made the switch to 365 at the time.

[@MyOnlineTrainingHub]

So pleased you can make use of it :-)

[@amospan14]

Very helpful Mynda, thank you very much! =)

[@MyOnlineTrainingHub]

Glad it was helpful! 😊🙏

[@manissery1956]

Really fantastic tips

[@MyOnlineTrainingHub]

Thanks so much!

[@sachinrv1]

Excel in Office 365 has much more to offer than one can imagine. Wonderfully explained :)

[@MyOnlineTrainingHub]

Glad you think so!

[@garyevans7620]

Excellent lesson, I learnt a lot, thank you. One small point. Parentheses are both brackets ( and ). So, a bracket is ( or ) and parentheses are () 🙂

[@MyOnlineTrainingHub]

In Australia and the UK (and probably elsewhere) this ( is a bracket and these () are brackets. My understanding is in the US this ( is a parenthesis and these ( ) are parentheses. Usually the term bracket/s are reserved for square brackets [ ]. But I’m not American, so feel free to correct me if you are 😁

[@lopher70]

Holly cow!
I tough I already knew all about filter, how wrong I’m
Thanks!

[@MyOnlineTrainingHub]

So pleased you learnt something new, Ricardo!

[@sjn7220]

Still used to Advanced Filter but may have to start using the filter function more.

[@MyOnlineTrainingHub]

FILTER is way better than advanced filter because is evaluates when the source data changes, whereas advanced filter has to be run again.

[@_sootyfoot]

I had no idea this was possible in excel, you've saved me about 30mins every Monday! 😂

[@MyOnlineTrainingHub]

Great to hear!

[@TheTechPlorer]

Thank you very much, This made my day

[@MyOnlineTrainingHub]

Great to hear 😊

[@gilbertosegoviano7701]

You are amazing 😻 . Thanks 🙏

[@MyOnlineTrainingHub]

Thank you! 😃

[@Seftehandle]

I am impressed great job

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@marywise5943]

This is great! I can think of several uses where I can apply this filter method. I would also love to see how you would select the columns based on a value example of something like

[@MyOnlineTrainingHub]

Yep, that's easy to do, Mary. Just reference the value column and apply the logical test in your criteria arguments of FILTER. If you get stuck you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@marywise5943]

Perfect! Thank you!

[@Permik123]

Thank you for the great video MInda. We can use the new CHOSSECOLS function and make it even easier to Exctract Non-contiguous Columns. Like so '=FILTER(CHOOSECOLS(B11:F19;2;4;5);B11:B19=C53)

[@MyOnlineTrainingHub]

Yes, love CHOOSECOLS and CHOOSEROWS. Unfortunately, when this video was recorded CHOOSECOLS wasn't out.

[@ademkursatkaracil]

Another great video. Thank you Mynda. As far as I Know FILTER function available for Office 2021 also besides Office 365.

[@MyOnlineTrainingHub]

Yes, it should also be in the new Office release.

[@heishoi]

wow this is excellent

[@MyOnlineTrainingHub]

Thank you very much!

[@stevennye5075]

very useful!

[@MyOnlineTrainingHub]

Glad you think so!

[@dougmphilly]

FILTER UNIQUE and SORT - learn it, love it, live it

[@MyOnlineTrainingHub]

Definitely, Doug :-)

[@ImranlShaikh]

Well explained

[@MyOnlineTrainingHub]

Thanks, Imran 😊

[@GeorgeAJululian]

Many thanks helpful information

[@MyOnlineTrainingHub]

My pleasure, George!

[@GeorgeAJululian]

@@MyOnlineTrainingHub Please I need your help, the formula works sooo good but is it possible for the table header to show in the result of this formula
=FILTER(INDEX(A3:G25,SEQUENCE(ROWS(A3:G25)),{1,2,3,4,6,7}),(F3:F25>0)*(G3:G25="Credit"))

[@davewilliams355]

Jesus you are clever, love your vids .

[@MyOnlineTrainingHub]

Great to hear 😊

[@mohammadj.shamim9342]

This is amazing

[@MyOnlineTrainingHub]

So pleased you liked it 😊

[@alicemorili1330]

This is great

[@MyOnlineTrainingHub]

Thanks, Alice!

[@hasfiz]

Alas! I can give only one like!❤👍👍👍👍👍

[@MyOnlineTrainingHub]

Aw, thanks so much, Hasan!

[@andianugerah5837]

life saver!!

[@MyOnlineTrainingHub]

Glad I could help 😊

[@silvanodemetrio407]

Excellent

[@MyOnlineTrainingHub]

Thank you 😊