Excel FILTER Function TRICK for Non Adjacent Columns




About this tutorial:

Video duration: 00:12:03
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.

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,…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

48 thoughts on “Excel FILTER Function TRICK for Non Adjacent Columns

    S F

    (October 17, 2020 - 8:02 pm)

    Great and life saving sometimes, i have a similar table that has a picture and using this function the result dies not Show the photo instead it dhows zeros. How can we include the photo.

    Elena Zhu

    (October 17, 2020 - 8:02 pm)

    This video already helped me a lot in the work! Many thanks Leila!
    Now I would like to do sth more, don't know if it´s workable: this video is filter some specific columns, but I will need to change which columns to show, is there a way that can filter the columns whose names are put in some cells alongside.
    For example, in these asistant cells I put "Name", then the formula filters and show the column whose header is "Name"; when I change the contect of the cell from "Name" to "Position", the formula shows the column whose name is "Position", the column "Name" will not appear.

    mehurar

    (October 17, 2020 - 8:02 pm)

    She`s too sweet !

    Patricia Pek

    (October 17, 2020 - 8:02 pm)

    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

    Mahmoud radwan

    (October 17, 2020 - 8:02 pm)

    I like you so much

    Chuck Carr

    (October 17, 2020 - 8:02 pm)

    Filter Function-Will this function be included in the Excel 2020 desktop version?

    Robert Bartholomew

    (October 17, 2020 - 8:02 pm)

    This is absolutely beautiful! Thank you 🙂

    Zubair Sohail

    (October 17, 2020 - 8:02 pm)

    Amazing stuff as always, keep it up!

    David B

    (October 17, 2020 - 8:02 pm)

    Instead of using FILTER twice: =FILTER(FILTER(A1:D999,A1:D999=H6),{0,1,,0,1})
    I place CHOOSE within the FILTER function: =FILTER(CHOOSE({1,2},B1:B999,D1:D999),A1:A999=H6)

    Andy Wise

    (October 17, 2020 - 8:02 pm)

    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.

    Luc Morineau

    (October 17, 2020 - 8:02 pm)

    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);{11})

    "

    Chuck Grablewski

    (October 17, 2020 - 8:02 pm)

    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!

    Miguel Andres Castro Bocarejo

    (October 17, 2020 - 8:02 pm)

    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} (100×1 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 🙂

    Billy Cash

    (October 17, 2020 - 8:02 pm)

    It didnt work on my computer. The result is "value". Why?

    Shanoob Olavanna

    (October 17, 2020 - 8:02 pm)

    Weldon 🙏🙏

    LISTOWELL JUSTICE K BONYA

    (October 17, 2020 - 8:02 pm)

    Sounds Great Leila! I've really learnt a New Trip! I usually work with MS Office 2013/2016, can I still use that for the New Filter Function?

    Thanks for the great lessons!!

    Augusto De Melo

    (October 17, 2020 - 8:02 pm)

    Hello Leila,

    You are so knowledgeable and didactic.
    Your videos are the best!
    I am also doing a VBA course at XELPLUS and I really enjoy it.

    Would you please help me with the following problem?

    Why it is working perfectly:

    =FILTER(tbl_geral;(tbl_geral[Fantasia da Empresa1]=parâmetros!$R$7)*(tbl_geral[mês]=relatório!C4)*(tbl_geral[ano]=relatório!C5)*(tbl_geral[Descrição da TOP]=relatório!C6))

    but when I try to filter it to show only the columns I want: {0;1;1;1;1;1;1;1;1;1;0;0;0;0;1;1;1;0;0;0},

    It shows an error message:

    #VALUE!

    See the expression below:

    =FILTER(FILTER(tbl_geral;(tbl_geral[Fantasia da Empresa1]=parâmetros!$R$7)*(tbl_geral[mês]=relatório!C4)*(tbl_geral[ano]=relatório!C5)*(tbl_geral[Descrição da TOP]=relatório!C6));{0;1;1;1;1;1;1;1;1;1;0;0;0;0;1;1;1;0;0;0})

    Note that my set up uses ";" instead of ",". It is OK. It is not the problem.

    Chuck Jaeger

    (October 17, 2020 - 8:02 pm)

    This FILTER function trick has definitely helped me in day-to-day work, but I have yet to figure out the Excel Dynamic Arrays lingo to solve a similar problem (without using Power Query).

    I need to do something similar to math set function against 2 tables:…
    Table1 has values: alpha, bravo, rabbit
    Table2 has values: alpha, bravo, squirrel
    I'm stumped on how to write a dynamic array formula that will get a UNION of the two tables with the UNIQUEs:
    e.g. =UNIQUE(Table1[Table1] & Table2[Table2]) -> desired result: alpha, bravo, rabbit, squirrel
    And also wondering how to get a Table2 without matching entries from Table1:
    e.g. desired result: squirrel

    Colin Wilson

    (October 17, 2020 - 8:02 pm)

    Hi Leila – Thank you for your videos, they are really helpful. A quick question on the above, how would you accomplish the same results but if the columns you are filtering are dynamic i.e. linked to a data validation list (and the data validation contains a blank option). Depending on the data validation selection the column numbers would change and hence the include argument logic would need to be dynamic to remove/include the correct columns. I hope this makes sense?

    Steven Nye

    (October 17, 2020 - 8:02 pm)

    Wow, that was an excellent video!

    Michael Tedford

    (October 17, 2020 - 8:02 pm)

    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.

    Maggy M.

    (October 17, 2020 - 8:02 pm)

    Hi Leila! Thank you so much for sharing this trick. What if the arrays are in different tabs? Can you perform the filter/unique function on multiple arrays that are not on the same sheet?

    Yash Bheda

    (October 17, 2020 - 8:02 pm)

    Your videos are very nice. I have a question however- if I have a column in 2 separate tables with same data, for e.g. Department. Can I get a unique list from 2 tables? Example my table 1 has department name A to D and my table 2 has E, what I want is a unique list from A to E. Is this possible? Your help would he highly appreciated 🤗

    Ashutosh Negi

    (October 17, 2020 - 8:02 pm)

    amazing

    Mohamed Al Harthy - Mash

    (October 17, 2020 - 8:02 pm)

    Hi Leila,

    I am a great fan of your channel, and I must say that I get most of my issues resolved by just looking at your channel. However, I came across these 2 problems that I struggling to see how I can resolve it using the filter function

    1- How can use Filter function where under the "Include" I use a spill range. So for example, I created a unique dates values from my table using the UNIQUE function. Now in my Filter function and in the include variable, I want to reference this Date Spill range. e.g.
    =FILETER(My_Table,Mytable[My_dates]=A2#), where A2# is referencing this unique spill range of all the dates from My_Table. I get #N/A error

    2- in My_Table where I have 3 columns: Col1, Just string values e.g. A, B, C, D; Col2, Dates values e.g. 26/01/2020, 27/01/2020; Col3 just numeric values the table looks like will be as below;

    Col1 Col2 Col3

    A 26/01/2020 1058

    D 26/01/2020 111

    A 26/01/2020 104

    B 26/01/2020 83

    C 26/01/2020 75

    A 27/01/2020 69

    C 27/01/2020 68

    D 27/01/2020 60

    B 27/01/2020 46

    C 28/01/2020 44

    C 28/01/2020 40

    B 28/01/2020 36

    A 28/01/2020 36

    My question, how can I use Filter function or any of the dynamic arrays function to get the following

    Col1 Col2 Total Col3

    A 26/01/2020 1162

    27/01/2020 69

    28/01/2020 36

    B 26/01/2020 83

    27/01/2020 46

    28/01/2020 36

    C 26/01/2020 75

    27/01/2020 68

    28/01/2020 84

    D 26/01/2020 111

    27/01/2020 60

    Basically grouping or summing the numeric values per date and per text value

    I know I can do this with Pivot table or other normal functions, but reason I want dynamic arrays function is because its dynamic =and MY_Table gets updated and changes a lot and my dashboard will be automatically updated as it will be reading from these dynamic arrays…

    Any help will help appreciated.

    Thanks
    Mash

    mgnielsen707

    (October 17, 2020 - 8:02 pm)

    Thank you for this tutorial! I was trying to sort this out today. Many thanks

    jo zw

    (October 17, 2020 - 8:02 pm)

    Leila, do you know whether people don’t have O365 subscription excel can view and update the excel that has filter function? Thank you.

    Artur Pilipczuk

    (October 17, 2020 - 8:02 pm)

    One problem. On windows one needs to use comma as list separator to make it work. In some countries semicolon is used instead. Then this trick doesn't work.

    EXCEL'N CRICKET

    (October 17, 2020 - 8:02 pm)

    Thanks for the information and trick.

    Laura Morales

    (October 17, 2020 - 8:02 pm)

    Thank you so much! Amazing.

    Adam

    (October 17, 2020 - 8:02 pm)

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

    Jeff Favret

    (October 17, 2020 - 8:02 pm)

    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!

    Bart Titulaer

    (October 17, 2020 - 8:02 pm)

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

    David Oxenlöv

    (October 17, 2020 - 8:02 pm)

    Hello im using swedish version of excel. The filtering out specific columns using array constants doesnt work for me. Is it possibly something diffrent between the different languages?

    Digital Library

    (October 17, 2020 - 8:02 pm)

    wow..amazing..i have been influenced by you a lot ans started a similar channel which is this channel ..its all about ms excel and i also make videos on English grammar. Have a look i would be glad.

    B Day

    (October 17, 2020 - 8:02 pm)

    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!

    K2r

    (October 17, 2020 - 8:02 pm)

    Thanks you Leila. Very interessant

    vimal kartik

    (October 17, 2020 - 8:02 pm)

    Thank 🙌 Mam for the workbook & Content

    Shoukat Siddiqi

    (October 17, 2020 - 8:02 pm)

    It is very informative video for me. How can we apply filter if we want to apply between 2 value from between value 500 to 1000
    Thanks

    Ram Iyer

    (October 17, 2020 - 8:02 pm)

    You are a genius 👍🏻

    Kratos Jallad

    (October 17, 2020 - 8:02 pm)

    Awesome job
    Can you make a video on how to make a dynamic list

    N P

    (October 17, 2020 - 8:02 pm)

    Is there any method to track changes (without vba) which is not shared online, but by email attachments

    Shady Kamal

    (October 17, 2020 - 8:02 pm)

    Hi, how i do all of this function on excel 2010.

    Dan Carter

    (October 17, 2020 - 8:02 pm)

    How can this Filter function work with Dynamic Chart? Does the Filter formula work within Name Manager when referencing multiple adjacent columns?

    Madhan Ganesh

    (October 17, 2020 - 8:02 pm)

    i am using this excel 2016 version,is this function available in that version

    Tom Chrysostomou

    (October 17, 2020 - 8:02 pm)

    Excellent video, explained fantastically!

    Ronak Surana

    (October 17, 2020 - 8:02 pm)

    Hi Leila, are you planning any course on Power Pivot and DAX as well. Really looking forward for that course after learning power query

    adam 8t8

    (October 17, 2020 - 8:02 pm)

    Hi,i hv a question about sumif/sumifs , how to use that formula to collect data for today only (current date),because im using google form to collect school canteen order by students..how to know cumulative order for today only by class? Please help me 🙏🙏🙏

Leave a Reply

Your email address will not be published. Required fields are marked *