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.

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

[PCor]

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!

[lorenzoladejobi]

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

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

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

[MichaelBrown-lwkz]

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

[josecarlosconejo]

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.

[Ahmed-fqsi]

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

[aviman]

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

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

[mathiasburkard]

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

[jeremylau]

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

[arkai]

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

[stevereed]

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

[peterharbman]

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

[jcethan]

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

[nadermounir]

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

[mattschoular]

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

[martyc]

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.

[nikkicroxall]

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

[zubairso]

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