Excel FILTER Function TRICK for Non Adjacent Columns
Рет қаралды 308,859
Күн бұрынJoin 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/filter-tric...
🔍 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!
To learn about new Excel functions enroll in my comprehensive course 👉 www.xelplus.com/course/excel-...
LINK to FILTER video: • Excel Lookup to Return...
★ My Online Excel Courses ► www.xelplus.com/courses/
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
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
@LeilaGharani 3 ай бұрын
Grab the file I used in the video from here 👉 pages.xelplus.com/filter-trick-file
@rogerhendriks999 3 жыл бұрын
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: =SORT(FILTER(FILTER(TSal[[Name]:[Position]];TSal[Salary]>J2);{1\0\0\1});2) And then it works miracles. Had been looking for this trick for so long, glad I know it now!
@LeilaGharani 3 жыл бұрын
Thanks for sharing, Roger!
@meaakv 3 жыл бұрын
Thanks, Roger! Had the same issue.
@PS-gn4xg 2 жыл бұрын
Thanks Roger!
@alexpedrosantos 2 жыл бұрын
Thanks a lot Roger. I was trying everything as a separator and none worked until seeing yours! Portuguese excel.
@shorthey Жыл бұрын
Not typically one for commenting youtube videos, but I've been looking for this for a couple of hours now and it worked perfectly. Thank you Roger! And big ups to Leila as always. Confirming forward slash separators work instead of comma for Norwegian excel users.
@user-sn8nb7zw8i 12 күн бұрын
Have a teacher like you,its a big chance.You are amazing .Yours youtube videos give me more than my teachers .THANK YOU ❤
@johnborg5419 3 жыл бұрын
Amazing Leila. Never thought of that in a million years.
@mdtechpk739 3 жыл бұрын
You have made it super easy. Thumps up for teaching us extraordinary tricks. Thank you
@pradhanbalter3796 3 жыл бұрын
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.
@gintomino4136 3 жыл бұрын
She always make it easy for us to understand. I wish all teachers are like you.
@1nannapaneni1 3 жыл бұрын
Thank you Leila. Wonderful presentation. Very clear understandable easily every having basic knowledge. Keep it up.
@SaravSood 3 жыл бұрын
I simply love your teaching method & style, learner lot of things in simplest way.. you rock.. thank you so much
@michaeltedford2177 3 жыл бұрын
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.
@applezhe19 2 жыл бұрын
Thanks for teaching me on filter function as well as for Non Adjacent columns, this is very helpful for me. Keep it up!
@mnsoyan 3 жыл бұрын
Amazing trick! This will transform the way I do my reports! Thank you!
@ShaikPashaImran 3 жыл бұрын
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
@LeilaGharani 3 жыл бұрын
Glad you like them! Thank you so much for your support.
@ugabrew 2 ай бұрын
This was awesome, and such a simple and elegant solution. Thank you for another great lesson.
@garys2187 3 жыл бұрын
That is incredible!!Thank you for sharing. I am in the process of taking your dynamic array course, which I am enjoying very much - a very worthwhile investment. I am well past the Filter section of the course, so I am making a note to myself that this trick exists.
@mattschoular8844 3 жыл бұрын
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.
@LeilaGharani 3 жыл бұрын
Our pleasure, Matt :)
@walkwithmedownunder8089 2 жыл бұрын
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!
@ronaldarvin414 2 жыл бұрын
This is what I exactly need right now. Thank you! Subscribed.
@bravucod 3 жыл бұрын
I was looking for this solution the other day. Thanks for the TIP!
@dokudowiec01 Жыл бұрын
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.
@cgrablew 3 жыл бұрын
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!
@danielmpinga4102 27 күн бұрын
3 years later and here I am using this solution. Thanks for all these helpful videos over the years.
@LeilaGharani 23 күн бұрын
It's our pleasure. Thanks for your ongoing support!
@tammystucki3152 2 жыл бұрын
Thank you for sharing and thank your student for thinking outside of the box!
@aguerojg 3 жыл бұрын
What an amazing tutorial, you explain so well and step by step, thank you very much Leila! The subtitles are so useful to fully understand, as English is not my native language. Thanks a lot for all your effort :)
@LeilaGharani 3 жыл бұрын
You're very welcome, Juan 😊
@washing753 3 жыл бұрын
You never fail to fascinate us , take care Queen 👸🏼
@BitesizeExcel 3 жыл бұрын
This is a great video - love the tip and the way you've broken down the elements! I've been having a play around with the new functions myself and love the useful functionality they bring! :)
@LeilaGharani 3 жыл бұрын
Awesome! Thank you!
@davidabuang Жыл бұрын
The second FILTER trick is cool, but I think some people are struggling to understand how it actually works, because filtering is typically only applied to rows. However, the FILTER function is capable of filtering data both vertically and horizontally, which is the case in this example. To better illustrate how a horizontal filter works on columns, write some OR criteria for the header row like this: =FILTER(TSal,(TSal[#Headers]=I4)+(TSal[#Headers]=J4)) So, to achieve the same results as demonstrated in this video, the final nested formula would be: =FILTER(FILTER(TSal ,TSal[Salary]>J2), (TSal[#Headers]=I4)+(TSal[#Headers]=J4)) Sure, the array constant method is shorter in this example, but the horizontal criteria method has other advantages: 1) it will work regardless of the column delimiter used in your region 2) it will continue to work if new columns are added/inserted 3) it’s easier to manage with larger tables (20+ columns) Cheers!
@theawebster1505 8 ай бұрын
That's definitely a more sound solution, @davidabuang The video is 100% great anyways!
@lucmorineau6741 3 жыл бұрын
excellent, thank you. Just a note for those who will use "European configuration"The formula is becoming : "=FILTER(SORT(FILTER(TSal[[Name]:[Salary]];TSal[Salary]>J2);5;-1);{1\0\0\1\0}) "
@LeilaGharani 3 жыл бұрын
Thanks Luc!
@mikedodds1321 2 жыл бұрын
I really need to just come to your videos before I start any task. You always save me so much time!
@a.achirou6547 5 ай бұрын
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.
@paulsheffield3042 3 жыл бұрын
HI Leila, absolutely loving your courses and tutorials...thank you. Can UNIQUE and FILTER be used in a table by any chance? I get a SPILL# error. Also, can you use SUM function with UNIQUE and FILTER to aggregate column values of all of the lines related to the unique record. Fingers crossed...thanks again.
@mcpett5742 6 ай бұрын
Can't believe there was this solution, I remember brain storming for half hour and I finally went with the choose function
@gemma5677 2 жыл бұрын
That was amazing and super clear, thank you! Are you able to use the filter function to add a non-adjacent column to the same column? For example; I have a guest, their plus one and the room they will be located in on separate rows (along with additional info). I'm able to use your shown function to omit the data I want, but I am unable to put their names beneath each other. A pivot table doesn't seem to help either. What do you suggest? I hope I've made myself clear, and it's not too daft a question!
@saptharishi1974 3 жыл бұрын
I have become your ardent fan - absolutely brilliant explanation!!! Most importantly the pace that you eloquently navigate thru, thanks a lot!
@LeilaGharani 3 жыл бұрын
Awesome, thank you!
@sahilsinghal9472 3 жыл бұрын
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
@TimEllisWW 3 жыл бұрын
Great tip, I had been trying to find a way to do this. Thanks.
@RZA12 2 ай бұрын
You're a great teacher and I'm glad to find your channel. You teach in a brilliant way and I completely understand. Thank you very much 🙏🏼
@LeilaGharani 2 ай бұрын
Thank you very much!
@jimfitch 3 жыл бұрын
Fabulous tip! This is a great solution to several situations that I solved with far more complex DA formulas. This is sooo much simpler. Thank you!
@LeilaGharani 3 жыл бұрын
My pleasure, Jim! Glad it's helpful for you.
@barttitulaerexcelbart9400 3 жыл бұрын
Thank you Leila, I think you were the first who makes a video about his. Finally a good alternative to advanced filter...
@LeilaGharani 3 жыл бұрын
Without Adam's idea I wouldn't have come up with it either. It's a great alternative!
@derrickadang9816 3 жыл бұрын
Bless you...this is such a great breakthrough in my line of work.
@LeilaGharani 3 жыл бұрын
Wonderful!
@niteshmech1 Жыл бұрын
Thanks Leila 👍Your trick helped me lots in my desgn calculations.
@McKaySavage 3 жыл бұрын
Really awesome. I’ve needed this trick for a while and thought it not possible. Thanks so much for sharing this! Your videos are always great, but I’m super grateful for this one!
@LeilaGharani 3 жыл бұрын
You're very welcome!
@bday9629 3 жыл бұрын
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!
@LeilaGharani 3 жыл бұрын
You're so welcome!
@keithdutch5295 2 жыл бұрын
Exactly what I was looking for. Thanks Leila!
@The_Excel_Enthusiast6782 2 жыл бұрын
This video is so awesome! I have been looking for a way to extract specific columns from within a filtered data set and now thanks to this video I know how to do that!
@LeilaGharani 2 жыл бұрын
Glad it helped, Eric!
@Sarunsherief 3 жыл бұрын
As usual another cool trick 😎👆 Thank You Leila Looking forward to the next session.
@hoserbob 2 жыл бұрын
Wow. Thank you. I was wondering if this could be done. This is an elegant solution.
@sethrish 3 жыл бұрын
Absolutely awesome each time... Everytime..
@marktheirl1842 3 жыл бұрын
Leila, I have been a subscriber for a long time now and I am still amazed on how much I can learn from you!!!
@LeilaGharani 3 жыл бұрын
I'm so glad! Thank you for your support, Mark.
@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....
@pupycron4302 2 жыл бұрын
Fabulous and very simply explained. This is great.
@markpodesta4605 3 жыл бұрын
Thank you Leila. I will try the formulas for myself!
@anthonyverdin6743 3 жыл бұрын
OMG.....Thank you so very much. You save my day Leila!!!
@tomchrysostomou4895 3 жыл бұрын
Excellent video, explained fantastically!
@jerryvelluto1885 2 жыл бұрын
Thanks Leila, I love your videos. They are easy to follow and this one was very useful on a recent task. Pardon me if this was already covered, but I had an issue when using the double filter technique for non-adjacent columns. I was getting the #VALUE! error when there were no matching rows. I found a solution by replacing the inner “Not Found” parameter with an array of equal size to the original array that I though you could share with others that might be facing the same issue. =FILTER(FILTER(Table2,Table2[Compare]=$K$7,{"Not Found","",""}),{1,0,1},"")
@ekhp91 3 жыл бұрын
I've been combining it with the indirect formula and making separate formulas for each column that I need. This really helps!
@LeilaGharani 3 жыл бұрын
Excellent!
@prashant333kumar 3 жыл бұрын
Thanks a lot leila one more skill you taught me Very well I love it.
@arkadiuszstojek9713 3 жыл бұрын
Awesome function, awesome video :) Also it will well pair up with choose function. FILTER(CHOOSE({1,2},TSal[[Name],[Position]]),TSal[Salary]>J2)
@levi822 2 жыл бұрын
very nice
@dirkstaszak4838 2 жыл бұрын
in my Excel this is not working. The formular should be like this: FILTER(CHOOSE({1,2},TSal[Name];TSal[Position]),TSal[Salary]>J2) In addition I noticed that in build 2108 14326.20784 in the German version WAHL({1.2}...) the choose part must be spearate by "." to achieve the same. In the beta release channel it changed to "\" for the same result. Apparently Choose acts differently in country versions as well as in build version.
@RRexxxxxxx 3 жыл бұрын
Dear Leila, I'm not sure I'm gonna use this tips (I'm a very basic excel user), but I just can't stop watching your videos. You are great!!! Love how you explain things!
@peets8323 3 жыл бұрын
Hello, trust me, you keep watching, you won't be a basic user for long 😉
@nonoobott8602 3 жыл бұрын
This is so amazing and very useful. You explanation makes it so easy to grasp. Thanks for sharing
@LeilaGharani 3 жыл бұрын
Glad it was helpful!
@johnvodopija1743 Жыл бұрын
This is so powerful. Thank you for sharing and explaining it so clearly. Cheers
@LeilaGharani Жыл бұрын
You are so welcome.
@garyallan69 2 жыл бұрын
Love it. I've wanting to know how to use this for a long time. Had to resort to using Xlookup before now for the second column
@michaellortz6365 2 жыл бұрын
Leila, thanks much for all of the info you provide for the Excel community! I love the dynamic array functions and since seeing your videos here am now putting them to good use. In your example here, you us the formula =FILTER(TSal[[Department]:[Position]],{1,0,0,0,1}) to retrieve non-adjacent columns from the TSal table. Is there anyway to more dynamically refer to the list of columns to be included (the {1,0,0,0,1} portion)? How can this be a reference to a cell with the text string "1,0,0,0,1" in it or a formula, such as concat(), that resolves to that text string?
@dennisd5776 3 жыл бұрын
What a simple solution to a complex problem using only a single formula. Thank you for the tip.
@LeilaGharani 3 жыл бұрын
You're welcome, Dennis!
@rambhandari7078 Жыл бұрын
Really love the way you explain... Thak you Leila 🙏
@kevochanner8859 3 жыл бұрын
Fantastic tricks and explained very easily to follow
@ClaudeBalleux 3 жыл бұрын
Thank you. Very useful. I wonder if we can extract a unique list of data from 2 different data set ?
@thefotoshooter 3 жыл бұрын
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.
@LeilaGharani 3 жыл бұрын
Great to hear it was helpful, Andy!
@derekpross4914 3 жыл бұрын
I'm late to this video. I needed to build unique lists from large data sets for sorting and SUMPRODUCT and SUMIFS analyses. This worked like a charm. Thank you!
@LeilaGharani 3 жыл бұрын
Glad it was helpful!
@ManafKAMIL 3 жыл бұрын
Elegant, simple, and effective... awsome trick :)
@bobperkins409 3 жыл бұрын
Like this, very helpful. Is there a way to use this function to count duplicate rows/entries in a table?
@iantrugman4422 3 жыл бұрын
All of your videos are excellent! My favorite for learning excel!
@SaravSood 3 жыл бұрын
mine too...
@DougHExcel 3 жыл бұрын
Simple and elegant tip!
@sushantjoshi5865 3 жыл бұрын
Amazing tip this. Have one question on SORT formula though. Can we use dynamic array sort function and sort the data as per our custom list? I mean normal sort function has this feature. I am not sure whether dynamic array sort function also have it or not.
@divyamalik3534 Жыл бұрын
Hi Leila, love your videos, can we cover multiple criteria's for Filter function or how to apply nested Filters? Thanks!
@naqashjanjua4033 3 жыл бұрын
You had always provided great tips, please make a video on making a summary sheets for civil engineers. Thanks in advance
@mcd22630 3 жыл бұрын
What a great approach on such a common task. Also congratulations on breaking through the 500k subscriber milestone.
@LeilaGharani 3 жыл бұрын
Thank you so much 😀
@HusseinKorish 3 жыл бұрын
That's Amazing Liela ... i used to go around this problem for months ....now you solve it ...Many Thanks
@LeilaGharani 3 жыл бұрын
Happy to help!
@rubencorderotorres2915 3 жыл бұрын
Oh! I really needed that trick, thanks a lot Leila
@a101525 3 жыл бұрын
Wow! Great tip Leila! Another day I had really bad times trying something that would give me this same result. Thank you.
@LeilaGharani 3 жыл бұрын
Glad it was helpful, Angelo!
@srvithal38 2 жыл бұрын
You are just fabulous. Thanks for sharing such valuable knowledge in such a simple way. Amazing
@LeilaGharani 2 жыл бұрын
My pleasure 😊
@adaliciojunior9792 3 жыл бұрын
VOCÊ NÃO EXISTE!!!!! PERFEITAAAAAAA
@charlesnazare7358 3 жыл бұрын
Awesome info Leila! You rock!
@user-kf7im8me9k 2 ай бұрын
AMAZING. THANK YOU!
@xyclos 3 жыл бұрын
Happy New Year Lelia!!! Your're fantastic!!! Thanks!!!
@amy19355 3 жыл бұрын
That is a fabulous little trick, and I'm already putting it to work. Thank you!
@LeilaGharani 3 жыл бұрын
Wonderful!
@patrickdolisie7037 Жыл бұрын
I really think you are a genius Leila. And the opportunity to download your file is great. It allows to understand bugs between English version of Excel et French one for example. In that case {1,0,0,1} becomes {1.0.0.1} in the French version. Once again, I, like millions of people, really appreciate what you do. Thanks !
@bilalyenimol3502 Жыл бұрын
Really thanks for this comments i was trying to figure out where i am making mistake thanks bro :D
@TheMrDoch Жыл бұрын
AWESOME! as always. Thank you for this knowledge.🎓
@alexpedrosantos 2 жыл бұрын
Absolutely fantastic. Thank you for teaching.
@LeilaGharani 2 жыл бұрын
You're very welcome!
@paser2 3 жыл бұрын
Very impressive! Thanks for sharing these awesome new functions!
@LeilaGharani 3 жыл бұрын
My pleasure Joe!
@izkamarkizka 2 жыл бұрын
Thank you, Leila. It is always such a clear explanation.
@LeilaGharani 2 жыл бұрын
Glad you think so!
@Fahad-AlGhamdi 3 жыл бұрын
Greetings from🇸🇦 Saudi Arabia 🇸🇦 . Your channel is wonderful in explaining Excel. I wish you more excellence
@LeilaGharani 3 жыл бұрын
Thanks a lot!
@ronalddcunha6136 28 күн бұрын
Thank you Laila... very useful video
@sharadpunita Жыл бұрын
So nice of you to teach such a great IDEA. Thanks
@LeilaGharani Жыл бұрын
You are so welcome!
@mgnielsen707 3 жыл бұрын
Thank you for this tutorial! I was trying to sort this out today. Many thanks
@LeilaGharani 3 жыл бұрын
Glad it helped!
@GhostVale4698 9 ай бұрын
I can't thank you enough for this video. Thank you so much and keep up the good work
@LeilaGharani 9 ай бұрын
You're very welcome!
@mmjjagon5419 2 жыл бұрын
Really excellent. Again a more than useful video. Thank you Just to help : for French version of Excel, we have to write {1.0.0.1} instead of {1;0;0;1}
@erwanlecocq381 2 жыл бұрын
MERCI!!!!! Une heure que je me debat avec excel sans rien n'y comprendre! Merci!
@jamalkarimi8860 9 ай бұрын
For German version dots are also the delimeter
@monte4955 3 жыл бұрын
Great as always Leila. Will definately be using this. Good to see your channel growing, over 500k subscribers 👌
@LeilaGharani 3 жыл бұрын
Thanks a million!
@arindamdutta9589 3 жыл бұрын
Thank you so much Leila, this is so useful and makes life so much easier while preparing dynamic reports. Indeed, Filter is an excellent feature added by microsoft.
@LeilaGharani 3 жыл бұрын
Great to hear!
@osamasaid8476 3 жыл бұрын
this is wonderful , god bless you
Diver-tudo
Рет қаралды 18 МЛН
Excel Off The Grid
Рет қаралды 72 М.