Excel FILTER Function TRICK for Non Adjacent Columns

Are you up for a really cool trick with Excel's new Filter Function? With a simple trick, you can use it to get multiple match results from Non-Adjacent columns in a Simple & Dynamic way. You can also use this trick to get unique values from non-adjacent columns.

🔍 What You'll Learn:
- Dynamic Data Analysis: Learn to dynamically extract and analyze data from non-adjacent columns using the FILTER function.
- Sorting and Filtering Tricks: Unveil tricks to sort and filter data based on specific criteria, making your analysis more efficient and accurate.
- Handling Unique Values: Explore techniques to extract unique values from different columns for a comprehensive data overview.
- Practical Examples: Implement these skills in real-world scenarios, such as employee salary analysis, to gain actionable insights.

So, let's say we have a dataset with 6 columns but we're only interested in the information in column 2 and 5, i.e. in non-adjacent columns. Plus, we don't want to get "all" value from these columns but instead only records that match a certain criteria. And we want it to be in a dynamic way so whenever we change the criteria, the result updates automatically.

I'll show you an Excel trick that get's this done in record time. The secret is to broadcast array constants in our formula. And not only that: You can also use this to get a UNIQUE list of combinations that are in non-adjacent columns.

As a bonus tip I'll show you how you can SORT the result based on a value that's not in the filtered end result.
Many thanks to my student Adam Payne for sharing this method with me!


00:00 Multiple Match Results from Non-Adjacent Columns
01:40 Excel FILTER Function
04:00 Filter Out the Columns You Do NOT need
06:09 Get UNIQUE list from Non-Adjacent Columns
09:10 Bonus Trick for Sorting

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[rogerhendriks]

Thanks, Leila, for yet another very helpful video. Just for those who live in a country like me, where you have to use other separators: to make this trick work, instead of the ", " you'll have to use the "\". The first formula then looks like this:
And then it works miracles.
Had been looking for this trick for so long, glad I know it now!

[mdtechpk]

You have made it super easy. Thumps up for teaching us extraordinary tricks. Thank you

[johnborg]

Amazing Leila. Never thought of that in a million years.

[pradhanbalter]

I love Leila and everything she does. Her videos are so clear, step-by-step and covers every different "what if?". When it comes to Excel, her channel is always the first place I look.

[michaeltedford]

Leila, Once again you display a fantastic option to extend the usefulness of Excel. It makes keeping data updated very easy. Thank you. One curiosity I found is, if you drag the formula, like a normal copy, the formula changes to reflect each column in the table. Example - =FILTER(FILTER(Table2, Table2[Subsegment]=D1), {0, 1}) turns to =FILTER(FILTER(Table2, Table2[LEGACY]=E1), {0, 1}). It is easily solved with a regular copy and paste. Thank you again.

[ShaikPashaImran]

Really love the way you put up examples and explain them so easily.
Thank you leila, this is the first time ever i am fan of someone who has been training online

[bday]

I want to express how much I love you Leila, I started to need to learn excel about 2 years ago, and whenever I am stuck I searched up it is always your videos that knows what I am looking for, thank you!

[a.achirou]

Waou ! I love the simplicity of the filter trick. Thank you, Leila, for sharing this. It is a good alternative to CHOOSECOLS function for filtering the output.

[sahilsinghal]

I had been breaking my head for the last few days on this exact problem. Your solution is brilliant. Makes the filter function so much more useful. Thank you so much Leila

[dokudowiec]

This is my favourite Excel function so far. I use it every day. The only thing that I hate about it, is that I had to redo all my sheets ;) I love that you can use as many 'include' arguments as you want, like an 'IF' function. Just put all 'include' arguments and '*' between them. Genius! - Leila, great work! Please keep it up.

[ronaldarvin]

This is what I exactly need right now. Thank you! Subscribed.

[walkwithmedownunder]

I have been working on a solution for this for who knows how long and you solved it in minutes! Thanks you so so so very much Leila!

[gintomino]

She always make it easy for us to understand. I wish all teachers are like you.

[bfilgate]

I googled and could not find anything on how to do what described in my comment below - but then I was messing with the FILTER function and realized I could put the HSTACK function inside the first parameter of the FILTER function and that gives me exactly what I need. i.e. to select the specific columns by column name in a FILTER function in any order without choosing all of them, etc. and without relying on the column order in the source table. It works great! Thought it might be worth a video....

[SaravSood]

I simply love your teaching method & style, learner lot of things in simplest way.. you rock.. thank you so much

[thefotoshooter]

Watched the video a half hour ago, and already used it to save about 15 minutes of manual effort. Great function and GREAT instruction on using it.

[marktheirl]

Leila, I have been a subscriber for a long time now and I am still amazed on how much I can learn from you!!!

[cgrablew]

Thanks ... I did something like this a month ago but using choose and it was a lot more cumbersome..This is great easy way to do non-adjacent columns. Thanks so much!

[applezhe]

Thanks for teaching me on filter function as well as for Non Adjacent columns, this is very helpful for me. Keep it up!

[mattschoular]

Wow! I have been dabbling with the new functions for awhile now. This video is packed with great content. Thanks Leila and Adam for sharing your knowledge.