Excel FILTER Function TRICK for Non Adjacent Columns

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/filter-trick-file

[@bobgreenfield9158]

I tried Ctrl + T and nothing happened. You already have the table set.

[@ChaplainRMCSJ]

I already have the file, thank you.

[@rogerhendriks999]

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]

Thanks for sharing, Roger!

[@meaakv]

Thanks, Roger! Had the same issue.

[@PS-gn4xg]

Thanks Roger!

[@alexpedrosantos]

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.

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

[@lucmorineau6741]

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]

Thanks Luc!

[@McGoerk]

Thanks for that great Video! Unfortunately it doesn't seem to work on the German version of Excel. We use ; instead, is there a trick here? When I use a , or ; or \ inside the curly brackets I get an error. Could someone help me and give me a hint?

[@denisovan]

I use en excel with de settings. My separator is ; (semi colon). In curly braces \ worked for me. i.e. {1\0} >> 2 columns. Number of arguments in curly braces must match with number of columns filtered.

[@sharon4578]

Till now I use compicated formulas or pivot to do all of it , now the life changed with this amazing function Many thanks Lili

[@LeilaGharani]

Great to hear!

[@favreje]

What a great solution! I tweaked the formula to replace the implicit constant array with a simple if() statement so the user can flag with a "y" (yes) above each column that they want to keep: =FILTER(FILTER(Tsal[[Name]:[Position]],Tsal[Salary]>J2),IF(B1:E1="y",1,0)). This way non-power users can dynamically choose the columns in their report. Thanks again for the simple solution to this issue - I looked everywhere for one!

[@LeilaGharani]

Thanks for sharing your version, Jeff!

[@MDNaHas_Mahmoud]

thank you very much

[@pradhanbalter3796]

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.

[@bday9629]

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]

You're so welcome!

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

[@LeilaGharani]

Great to hear it was helpful, Andy!

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

[@JaskellK]

Our IT department finally updated our 365 add-ons. Sad to say i work for a Tech company, but been waiting to use FILTER function. This video helped me trickle down to the columns I needed which is about 5 from about 30 columns. Thank you!

[@LeilaGharani]

Great! Glad it was helpful.

[@Galileo2pi]

Leila, she's the better in this land, this is my opinion. She opened my brain, and my life changed; she's a good professional. Do not forget her team, excellent as well.

[@LeilaGharani]

Thank you so much for the kind words!

[@krishnamurthy7733]

It's nice and easy step to filter the Non-adjacent columns. Before watching this video, I had been using the HSTACK function to create an array from Non-adjacent columns.

[@marktheirl1842]

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]

I'm so glad! Thank you for your support, Mark.

[@backpack_family]

Thanks Leila for an informative and helpful video. I tried to use the filter formula for copying the data from a database so that I do not manipulate any data accidently in the original file. However, when I close the original file, I get a #Ref! error. Could you let me know how I could ensure the data is not lost without opening the original file? Thanks

[@a.achirou6547]

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.

[@TheZofriax]

You the real MVP!

[@sahilsinghal9472]

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

[@seanzibomb]

Hi Leila, I haven't been able to find anything about using the FILTER function to reorder columns. Do you know if this is possible? Thanks!

[@samruben5750]

That was awesome! For anyone getting a #VALUE! error - 1. Check that you're using Excel 2021 or 365 2. Make sure that the array constant of boolean values you use for the include argument has as many values as columns present in the array argument! So if I'm filtering an array from Department to Salary, the curly bracket array constant must have five values (Department, Name, Start Date, Birth Date, Position).

[@SteveSwanson999]

THANKS SAM! The second part of your comments (having as many values as in the array) saved me TONS of debugging...GOOD YOU

[@samruben5750]

@@SteveSwanson999 Glad I could help! I wasted a good chunk of time trying to figure it out myself 😂

[@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]

That's definitely a more sound solution, @davidabuang The video is 100% great anyways!

[@ashishsharma6916]

Hi, We can use CHOOSECOLS function too, instead of 2nd filter function.

[@sbfredster]

Boom, solved the annoying problem of having to hide columns! Thanks Leila! I ran into another annoying result where the filter function returns zeros for blank cells in the source data. I found an elegant solution on Mr. Excel that may help others...made sense to me as I just watched Leila's video on the LET function ;) ... to remove zeros in the array returned by filter try a version of the following example: =LET(f,FILTER($A$1:$D$21,$B$1:$B$21="your_filter_criteria"),IF(f=0,"",f))

[@LeilaGharani]

Thanks for sharing your solution!

[@xjustinx1993]

Or just go into file-options-advanced and deselect the option that says “show 0 for blank cells. “

[@tammystucki3152]

Thank you for sharing and thank your student for thinking outside of the box!

[@RZA12]

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]

Thank you very much!

[@danielmpinga4102]

3 years later and here I am using this solution. Thanks for all these helpful videos over the years.

[@LeilaGharani]

It's our pleasure. Thanks for your ongoing support!

[@dennisd5776]

What a simple solution to a complex problem using only a single formula. Thank you for the tip.

[@LeilaGharani]

You're welcome, Dennis!

[@mikedodds1321]

I really need to just come to your videos before I start any task. You always save me so much time!

[@mdtechpk739]

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

[@johnborg5419]

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

[@arkadiuszstojek9713]

Awesome function, awesome video :) Also it will well pair up with choose function. FILTER(CHOOSE({1,2},TSal[[Name],[Position]]),TSal[Salary]>J2)

[@levi822]

very nice

[@dirkstaszak4838]

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.

[@mmjjagon5419]

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]

MERCI!!!!! Une heure que je me debat avec excel sans rien n'y comprendre! Merci!

[@jamalkarimi8860]

For German version dots are also the delimeter

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

[@gunaybabacanova]

Have a teacher like you,its a big chance.You are amazing .Yours youtube videos give me more than my teachers .THANK YOU ❤

[@Dubai711]

Thanks for the great Video Leila. Can I add more than two criteria? I tried adding another one using *( ) but it didnt work.

[@ivanamarkovic9131]

Hi Leila, do you have an idea how to prevent losing data in a depending workbook after closing a source workbook? Filtering is not done in the same workbook, it's done in a different workbook. Also there can be multiple sourcing workbooks that fill the depending one. Thank you in advance

[@67duiker]

I would use =SORT(FILTER(CHOOSE({1\2};TSal[Name];TSal[Position]);TSal[Salary]>J2)) as this gives more visibility in the formula which columns are chosen. But Your trick has the advantage that it is easier to change between columns when you need to. I changed the {1,0,0,1} to a range and could then choose which columns i wanted to see. Very useful trick

[@67duiker]

I just tried the following formula CHOOSE(+TRANSPOSE(SORTBY(FILTER(T6:T10;S6:S10>0);FILTER(S6:S10;S6:S10>0)));TSal[Name];TSal[Start Date];TSal[Birth Date];TSal[Position];TSal[Salary]) where I put in r6 to r10 the names of the columns and in 26 to s10 the columns i wanted with 0 to 5 to indicate where i wanted them(0 not shown). as help-column in t6 to t10 (1,2,3,4,5) and now I can choose which columns i want in which order. Adding a filter is easy at that point.. and with a formula for the headers +TRANSPOSE(SORTBY(FILTER(R6:R10;S6:S10>0);FILTER(S6:S10;S6:S10>0))) it's a complete table. I like playing with your examples :-)

[@hoserbob]

Wow. Thank you. I was wondering if this could be done. This is an elegant solution.

[@ronaldarvin414]

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

[@paulsheffield3042]

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.

[@jeevarajagopal1707]

Thanks Leila for this wonderful trick. Is there any way that we can have the headers being showed in the answer (Dynamically). Please let us know.

[@jmclean981]

Leila this has been very helpful! Question - if the output column you selected is a DATE - how can you get the format in DATE format. I tried using, ,{0,1,1,0,1,1,1,text(date(1,"dd-mmm-yyyy"),0,0}) ==> but to no success

[@ugabrew]

This was awesome, and such a simple and elegant solution. Thank you for another great lesson.

[@kulbhushangaur6298]

You are awesome, I got stuck to add two columns in filter function…thank you very much

[@pascalperrotey6465]

Hi Leila,Thanks for this video.. is there a trick to format the results of this function, i think particularly the date column, when the list evolves, i notice that the format does not evolve accordingly... thanks for your answer and sorry for my poor english .. (From France)

[@DougHExcel]

Simple and elegant tip!

[@saptharishi1974]

I have become your ardent fan - absolutely brilliant explanation!!! Most importantly the pace that you eloquently navigate thru, thanks a lot!

[@LeilaGharani]

Awesome, thank you!

[@srvithal38]

You are just fabulous. Thanks for sharing such valuable knowledge in such a simple way. Amazing

[@LeilaGharani]

My pleasure 😊

[@rinakaur7245]

Can you sort by 2 columns, eg 1st sort by location, then sort by employee? Or can you only sort on 1?

[@b438]

Useful trick, but what about if I want the returned columns to be in a different order? Say I want to show Position before Name? Thanks a lot!

[@barttitulaerexcelbart9400]

Thank you Leila, I think you were the first who makes a video about his. Finally a good alternative to advanced filter...

[@LeilaGharani]

Without Adam's idea I wouldn't have come up with it either. It's a great alternative!

[@niteshmech1]

Thanks Leila 👍Your trick helped me lots in my desgn calculations.

[@VKIRA2802]

Hello, Is it posible to format as a table the section where you put the filter formulas?

[@patriciapek4444]

Hi want to learn from you. can it be create 2 filters in the same tab? which it has 2 different type of data table in the same tab. Thanks

[@mikeszczesny4117]

You can just use one filter function to select name column and position column, then add your filter criterion. =filter(choose({1,2}),name range, position range),salary range>=criteria). With the choose function, you can select any column and how many columns.

[@sideshowbobby71]

Can will filter the columns dynamically instead of using the Boolean trick?

[@Fahad-AlGhamdi]

Greetings from🇸🇦 Saudi Arabia 🇸🇦 . Your channel is wonderful in explaining Excel. I wish you more excellence

[@LeilaGharani]

Thanks a lot!

[@johnvodopija]

This is so powerful. Thank you for sharing and explaining it so clearly. Cheers

[@LeilaGharani]

You are so welcome.

[@carlrigefsky3380]

Is it possible to filter a table to show rows when column C is equal column D (both are dates)? Thanks

[@santroramki]

Hi.. It's a good tricky one indeed. But if we wish to focus on columns more than 20 + how do we proceed? Please guide. Thanks

[@bfilgate]

Hello Leila - this is great except that it requires one to reference the column by column number instead of by the name of the column. So if a new column is inserted in the table between two columns selected it would mess things up downstream. Is there a way to do this by referencing the a set of non consecutive columns by their column names?

[@sudhasomasundaram1509]

Does the data have to be in a table to use this function? I use the pivot table data I get most of the time.

[@wayneedmondson1065]

Hi Leila.. great trick.. FILTER FILTER with a list of array constants to extract the desired columns.. nice! This solution works great if your data table is static in term of columns. But, if you insert columns in the data table in the future, the array constants within the curly brackets cannot be made to be dynamic and so the formulas would then break until or unless modified. To avoid this problem and maintain maximum flexibility for future design changes, I like to use CHOOSE to assemble the desired non-contiguous columns, as follows: Your example cell I5: =SORT(FILTER(CHOOSE({1,2},TSal2[Name],TSal2[Position]),TSal2[Salary]>J2),2) Your example cell O5: =SORT(UNIQUE(CHOOSE({1,2},TSal2[Department],TSal2[Position]))) Your example cell I13: =CHOOSE({1,2},INDEX(SORT(FILTER(CHOOSE({1,2,3},TSal2[Name],TSal2[Position],TSal2[Salary]),TSal2[Salary]>J2),3,-1),,1),INDEX(SORT(FILTER(CHOOSE({1,2,3},TSal2[Name],TSal2[Position],TSal2[Salary]),TSal2[Salary]>J2),3,-1),,2)) Granted, the formula in cell I13 may be a bit more complex than FILTER FILTER, instead using CHOOSE to assemble the 3 columns to sort and then INDEX to extract the 2 columns to present, but I always thank myself for building in the added flexibility for future design changes. My formulas above will all withstand column insertions in the data table without the need of modification. The FILTER FILTER with {} list formulas would require rebuilding. Again.. not better or worse.. just different potential, depending on whether or not there is a chance the underlying data table might change in the future. A good parallel is VLOOKUP vs. either XLOOKUP or INDEX/MATCH. VLOOKUP is less flexible to future changes, but perfectly functional if you know the data table design will remain static. As always, thanks for the video and the inspiration to create and share. Always good learning and fun at your channel :)) Thumbs up!!

[@LeilaGharani]

Thanks Wayne for sharing. Yes that’s correct. Choose function is great - it’s the one I teach in the DA course for these cases. The good thing about choose aside from being flexible with new columns is that you can get columns in the opposite order as well. Another alternative is to use Index and sequence. Many thanks for your feedback 😊

[@wayneedmondson1065]

@@LeilaGharani Thanks Leila.. I intended to mention that CHOOSE also gives flexibility of column order.. glad we are on the same page. I really appreciate all your videos and courses. Though my work and schedule get in the way of bearing down like a full time student, I consume new content every day and it adds up over time. Keep up the great work :)) Cheers!!

[@PabitraJanajana]

Hi! Leila, you doing very great and I am very thankful for what you are doing. I am very helpful with your videos. Now I am requesting you to help me on that my data has multiple line of a product with different quantity for a day. How I use filter function with some other formula to get filter data with sum of data. It's very challenging to me. Please help.

[@sharadpunita]

So nice of you to teach such a great IDEA. Thanks

[@LeilaGharani]

You are so welcome!

[@SimonLangridge-no5gu]

Found this tutorial, was exactly what i was looking for. Was lost now found. Thank you so much.

[@LeilaGharani]

Glad it helped!

[@walkwithmedownunder8089]

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!

[@keithdutch5295]

Exactly what I was looking for. Thanks Leila!

[@jessicalopez2162]

I super super enjoyed it. Do you know how could i use a sumif so that i can summarize the results?

[@derekpross4914]

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]

Glad it was helpful!

[@Rain2bird]

Nice video, I want the "filter table" on a new tab but it gives an error with me. Is it possible?

[@screaminbrod]

thanks leila... appreciate your work here... i learn a lot... i just have a question, how can i insert the aggregate function from the unique filter function, especially if i wanted to return as well the amount of each records/employees right next to it? is it possible?

[@simongray6292]

Thanks Leila, I discovered the new array functions in 365 can include formula as a return on the filtered list. For example =SORT(FILTER(CHOOSE({1,2,3,4,5,6},Backorder[Item number],XLOOKUP(Backorder[Item number],ReleasedData[Item number],ReleasedData[Item name],0),Backorder[Number],Backorder[Quantity],Backorder[Prod schedule start date],Backorder[Revised Date]),((Backorder[Prod schedule start date]0)),"??"),3,1) In this formula I was able to bring in data from a seperate table that matched the filtered results in column 1 in this example. The use of the Choose function for the array allows any order of columns to be chosen as a bonus. I have not found an example of formula used in a dynamic array return on the net so thought you might be interested to research it and maybe add into a tutorial along the way. I would also be interested in the effects of the new array functions on excel performance and calculation speeds. I'm loving your tutorials and I am sure I will sign up for one of your courses soon.

[@patrickkinbonso1809]

Rather than using another Filter function, you can also use the choosecols function.. since both needs hard-coding the columns needed?

[@poyraztahan]

Hey Leila, when I use this tricks it applies to row instead of columns. Is there a way to specify the filter to column?

[@pontuslindholm7379]

I had the same problem due to non-english Excel and used the add-on Excel Functions Translator to solve the issue. Change the curly brackets to {1\0\0\1} and it should work

[@michaelmarburger6005]

Loving your video --- My need is a bit different. I have table columns A B C and D, and want my filter to return C A D B, where E="blah". I estimate this will not work in one filter command, and that I will need to have 4 filter commands in each of the 4 columns in my target area. Perhaps I am missing something. What are your thoughts?

[@gloystar]

Nowadays I just use the CHOOSECOLS function instead, but both solutions are fine. Great video!

[@LeilaGharani]

👍

[@deesquare08]

Can I use the COUNTA formula the same way you used the UNIQUE and SORT functions with the filter formula e.g. =COUNTA(FILTER(Tsail[[Department]:[Position]],{1,0,0,0,1}) to return the count of "DEPARTMENTS" and "POSITIONS" or =COUNTA(Unique(FILTER(Tsail[[Department]:[Position]],{1,0,0,0,1})) to return the count of "Unique" "DEPARTMENTS and "POSITIONS"? Also will it be possible for EXCEL to allow a table to be created from the result of a FILTER Formula?

[@jeeteshsingh9452]

Good video. Ques: Is it possible to filter data from two different tables into one result table using this formula? Let say I have table 1 and table 2, and I wish to filter data from these two table based on a specific criteria, and show it in a result table?

[@julianizdebski5035]

Can you do this on rows instead of gollum's? Im stuck trying to make a "dynamic" range for an 4 x (dynamic) range / table"

[@AP2922]

@leila I'm happy you liked that trick! But you took it and taught me new things you could do with it that I hadn't considered. When I saw the choose function method you teach for this type of problem I was sure that was the better way but your Bonus Tip example shows why we can't rely on just one formula. Both formulas have their place.

[@LeilaGharani]

Thanks Adam for sharing this great idea! I'm sure it will be helpful for many of us.

[@Prostatafocal]

Is it possible to use this trick to select de rows returned instead of columns?

[@AdamKaraszewski-b4x]

Thanks a lot for the mask {1;0;1;1;...} tip! Adapted it to "Chose arbitrary columns with arbitrary order, by their names". More readable. I avoid 'abstract' numerical masks with tables, so there is an example with named column mask, and you set cols order, too :) Example - I needed to select ordered unique records with my own column order and selection, after filtering them : =SORT(UNIQUE(""&CHOOSECOLS( FILTER( myTab; (myTab[myColX]=myTab[myColY]+myTab[myColZ])*(myColA=0) ); MATCH({"myColX"; "myColZ"; "myColY"; "myColA"}; myTab[#Headers]; 0));FALSE;FALSE) ;4)

[@lmajiedmary]

Just what the doctor ordered. Thank you!

[@rambhandari7078]

Really love the way you explain... Thak you Leila 🙏

[@michaellortz6365]

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?

[@lisadeniney405]

Thanks...can filter function be used to filter data with master data in a seperate workbook?

[@izkamarkizka]

Thank you, Leila. It is always such a clear explanation.

[@LeilaGharani]

Glad you think so!

[@vishugupta250]

Hi Liela....thanks for this video... Can't we insert any formula/function within curley bracket in order to get true or false(i.e. 0 or 1)??

[@MiguelCastro-ze2rs]

Hello Leila, great video :D! thanks for sharing.... I have a questions, what about if I have a table with a huge amount of columns (for e.g. 100 Cols) and I'd like just to use the columns number 3 and number 99. Do I need to create an array like {0,0,1...1,0} (100x1 array) or may I call just the columns that I need using an index or the columns name (e.g. {"Col 3" , "Col 99"} ) Thanks for the help :)

[@maciekwsp]

Firstly, great video big up to Adam and Leila :) I had the same problem with large tables 100+ columns, my solution is =FILTER(Table1,(Table1[#Headers]="ColumX")+(Table1[#Headers]="ColumnY")) you can of course make it dynamic by replacing the "ColumnX/Y" with a cell reference and you can add more columns by adding +(Table1[#Headers]="ColumnXYZ") to the "Include" argument of FILTER function Alternatively if you're working on cell ranges instead of tables then you can use the below =FILTER(A2:BZ100,(A1:BZ1="ColumnX")+(A1:BZ1="ColumnY"))

[@denisovan]

kzbin.info/www/bejne/fqeUpJ2mrZ2rg9k

[@hichamhadj9640]

What if we insert or remove or move columns ?. Is it possible to refer to columns by name so it remains fully dynamic?

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

[@JosephMcDaid]

Great video. Very clever use of the double FILTER!

[@LeilaGharani]

Thanks Joe for bringing these functions to us :)

[@JOSE-du7mu]

Different ways of doing the same thing; for example, she also showed us howt to do the CHOOSECOLS, to select only the column you want; I think CHOOSECOLS is better.

[@janusviborgolesen9613]

Hello Leila. thank You for all Your videos. there very inspiring. question: how can You filter cells which contain functions? e.g. cell A1 to A10 each contain vaules which are a result of the function sumifs. when i filter these to be shown as smallest to largest it doesn't work. thank You in advance

[@gemma5677]

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!

[@cgrablew]

Leila I was looking for a solution like this. This is great but I ran across a situation where the column I wanted first in the spilled array was the last in the input array. Any ideas of how to make the last column first and the first column last using FILTER? The true and false in this solution only selected the column but does not dictate the order of the columns. Thanks!

[@sjh1956]

@Chuck Grablewski Here's a link to a video from Jon Acampora at Excel Campus I believe may help you: kzbin.info/www/bejne/aJvbhJJmprZ7oqM

[@GhostVale4698]

I can't thank you enough for this video. Thank you so much and keep up the good work

[@LeilaGharani]

You're very welcome!

[@pupycron4302]

Fabulous and very simply explained. This is great.

[@amy19355]

That is a fabulous little trick, and I'm already putting it to work. Thank you!

[@LeilaGharani]

Wonderful!

[@ekhp91]

I've been combining it with the indirect formula and making separate formulas for each column that I need. This really helps!

[@LeilaGharani]

Excellent!

[@UtuDudas]

What if you want to filtre two columns, One contains names, the other values. Could you do any calculations with the values in the second column without breaking them apart with choosecols or index?