Comments on: Excel FILTER function with formula examples

The introduction of the FILTER function in Excel 365 has become a long-awaited alternative to the conventional methods. Unlike the Filter feature that needs to be re-applied with each data change, Excel formulas recalculate automatically, so you need to set up your filter just once! Continue reading →

Comments page 4. Total comments: 341

  1. Sir, I want to ask whether data from non-adjecnt column can be filtered (via filter function) to a specified rannge of columns (that is output results should be in a slected range of column or columns rather than default inwhich data from different columns filterd based on number of columns from which it was driven?
    Is it Possible? and if possible then kindly share example formula

    Thanks & regards,

    1. Hi!
      If I understand your task correctly, pay attention to the following paragraph of the article above: Example 2. Filter non-adjacent columns.
      Hope this is what you need.

  2. Hi,
    i wants write multiple Value at one Cell of ie. B2 Cell i am writing value of many countries like pakistan Iran India . than how can put formula for that

  3. Thanks for the article! Just wondering if the FILTER function can be used with a large number of "include" options. I understand how it would work for a "this or that" type scenario, but I've got a large dataset and want to get the results for about 200 different options. Is there a better option than
    = FILTER (Range, (Range = Option 1) + (Range = Option 2) + (Range = Option 3)+ ... + (Range = Option 200))

    1. I am hoping I understand you correctly and am going to offer what I think is a solution to your question as I just figured this out myself and was very pleased with how it worked.

      If your "Options" were arranged as a column of values (ie: E1:E200) per se, you could use the following formula to filter your data:

      = FILTER(Range, ISNUMBER(MATCH(Range,E1:E200,0)) )

      The MATCH function will return numbers in an array the same size of the 'Range' on lines where the 'Options' match. Then the ISNUMBER will convert those matches to a value of TRUE.

  4. Thanks for the article! I want to know if it's possible to use the Filter function (or similar) to actually apply the filter to the original table to display the results, rather than reproduce the results in a new set of cells like in your example. I am working with tables with 10+ columns so it's not practical to have the results side-by-side like in your example.
    I know this can be done using VBA (the code applies an autofilter with the criteria entered in a search box/linked cell), but I was hoping to be able to work with non-macro enabled workbooks if possible.
    Any tips would be most appreciated!

      1. Thanks for your reply. I get that you can use the Excel filter tool to do that, but then you have to manually enter/clear your filter each time you want to search. I work with large glossaries, so I just want to search for a term in one column (e.g. in an activex text box), and then it will apply the filter in real time as I type. I can do this using VBA (see working code below), but I'd like to do this without having to save and share macro-enabled workbooks with colleagues.

        I hope that makes sense!

        How it works in VBA:
        I insert an activex text box and link it to cell C1. I then assign the below macro to it. When I enter text in the box, it is replicated in cell C1 and the column is filtered based on the text in cell C1.

        Private Sub TextBox1_Change()
        Application.ScreenUpdating = False
        ActiveSheet.ListObjects("Monographs").Range.AutoFilter Field:=1, Criteria1:="*" & [C1] & "*", Operator:=xlFilterValues
        Application.ScreenUpdating = True
        End Sub

  5. I am trying to filter based on various dropdown menus which would correspond with columns in my dataset. The problem I'm having is I want to include an "All" option for each dropdown menu. I've been able to accomplish this with a series of if statements that gets complicated quickly. Is there an easy way to do this?

    For example if my dataset looks like this:

    state, age, gender, score

    Can I use dropdown menus and the filter function to allow for these combinations:

    NY, all ages, men, scores>50
    all states, <18, women, all scores
    etc...

      1. Yes, but not really for this purpose. I am building a dashboard using a filtered subset of the data. The user has dropdown menus (from data validation) to select their filters, then I use the filter function to select the data that the charts are built off of. I'm trying to determine if the filter function is sophisticated enough to do this.

        1. Hello!
          The FILTER function can also perform this task. Use the examples in the article above. If there is a specific question regarding the formula, I will try to help.

          1. Thank you. I am still having a hard time getting it to work properly. I have this function that works fine:

            =IF(Dashboard!V3="ALL",SORT(FILTER(A2:P665,(G2:G665>0)),8,1),SORT(FILTER(A2:P665,(G2:G665>0)*(C2:C665=Dashboard!V3)*(I2:I665=Dashboard!$X$3)),8,1))

            I want to be able to remove the if statement, then add more criteria like this:

            =SORT(FILTER(A2:P665,(G2:G665>0)*(C2:C665=Dashboard!V3)*(I2:I665=Dashboard!X3)),8,1)

            The problem is when Dashboard!V3 or Dashboard!X3 is "All" instead of an exact match for something in the respective column.

            1. Hi!
              It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

              1. Thanks for your help. I was able to figure it out using if statements that didn't get too complicated. In case anyone else has this problem, here was my solution (which assumes there are no blank cells):

                =filter(A:D,if(F1="ALL",A:A"",A:A=F1) * if(F2="ALL",B:B"",B:B=F2) * if(F3="ALL",C:C"",C:C=F3) * if(F4="ALL",D:D"",D:D=F4))

              2. I can appreciate that, it's why I tried to come up with a generic example in my original post. Can I go back to that example? If the data is in columns A-D is state, age, gender, score. I want to be able to filter on some or all of these in a formula. in cell F1 is the state I want, F2 is the age, F3 is gender and f4 score. I believe I can write a formula as such:

                =filter(A:D,(A:A=F1)*(B:B=F2)*(C:C=F3)*(D:D=F4))

                However what do I do if one of the values in F1-F4 has "All" as an option? can I leave it blank? Is there another workaround that doesn't include nested if statements? Or does the filter function simply not work in this instance?

                Sorry for so many questions, I'm really hoping I can get this to work and your webpage has been very helpful.

              3. Hello!
                If you have a condition on the value in cell F1, then the IF function is used to filter based on the condition. If F1 is empty, then the first filter is used, if it contains a value, the second filter is used.

  6. Hi!

    Thank you for your guide, it is much more useful than many others. Yet i have a problem. Is there any way how to edit filtered data? My situation is: I have a storage, trying to make some kind of "frontend" for users and i need to edit already filtered data. You search for item, you see how many is somewhere, you fill out how many you took from that amout and you update "database". I cant find any useful informations about it. Can you help me please?

    Thank you VERY much,
    Martin

      1. Thank you, I will try it out :)

  7. In your example under "Filter with multiple criteria (AND logic)" is it possible for the formula to ignore blank inputs.

    For example if I only place a value in Groups of A and leave Wins blank it shows all the A results, but if I add a value to wins then it narrows it down. And if I remove the Group value and only have wins it shows values of all wins above "2" ignoring the Group value since it's blank.

    I cant seem to figure this out.

    1. Hello!
      Unfortunately, I was unable to reproduce your issue. If you remove the Group value, the result is empty.

      1. Alexander, thank you for your response.

        I should explain better. If I had an inventory of cars at a dealer ship in a sheet with Make, model, type, etc... would I be able to create a filter where if I have 2 "And" values. Make and Type.

        example:=FILTER(invo,(invo[Make]=F2)*(invo[Type]=F3))

        Table=invo,
        *Make *Model *Type
        Chevy-Malibu-sedan
        Chevy-1500-pickup
        Chevy-2500-pickup
        Ford-explorer-suv
        Ford-F150-pickup
        Ford-F250 pickup
        Toyota-Corola-Sedan
        Toyota-Tundra-pickup
        Toyota-Tacoma-pickup

        If I only place 1 value in the filter such as "pickup" it will give me the a list of pickup in my inventory with mix of all 3 makes.

        But, if I type 2nd value of Make, I can narrow to all the "pickup" And "Chevy"

        Currently it only filters if both values are added.

        When running a Query in google sheets this seems to be the default "And" behavior but If I leave a value blank under the excel =filter it wont give results. If i use the "Or" "+" i get a mixture which can be used for certain sheets but not to narrow down inventory search.

        2. I was able to replicate with a bunch if if statements. It gets messy if you have more than two variables.

  8. I have values in columns A to Z
    Now, I want to get values from column A, B, C, K, N, and P if there is a "error" value in column Z. In column Z there are different values such as error, ready, not ready and so on. I need values from above mentioned columns if there is a "error" value in column Z. Please help me out.

    1. Hello!
      The FILTER function applies to only one range. So use it multiple times.

      =FILTER(A2:C7,Z2:Z7="error")

      Please re-check the article above since it covers your case.

  9. Thanks for sharing all this info, it's very complete and valuable.

    I am using Google Sheets for some years now due to the company's requirements, and after learn how the Filter formula works, I use it for everything, but I know the older versions of Excel didn't have this option, and with this article I could learn all that I needed about this amazing function in Excel.

    Thanks for this amazing job!

  10. Hello
    Thanks for publishing such a valuable article, but I still have a question I hope you can help me with.

    If we use the table of data you have used in this article (Name, Group, and Wins), what I want to do is filter out Group B and also determine the largest value for Group B. In other words, I want the filter to be able to determine that the largest value in Group B is 3 and that it belongs to Oliver.
    When I set my filter function to filter for Group B AND the largest value, I get an error as my filter is finding the largest number in the Wins column (which is 4 from both Groups A and C) and it does not correspond to Group B, therefore, the error.
    I want to keep the filter dynamic and thus wanted to use the filter function, but I can’t seem to make this work even though it sounds simple. Essentially, I need the filter function to look only at the number of wins in Group B and then determine the largest value of wins and then spit out Name, Group and the number corresponding to the largest wins.
    Any help would be greatly appreciated. Thanks for your time.

    1. Hello!
      You can use the MAXIFS function to add another condition to the filter. Pay attention also to the paragraph in the article above - Filter with multiple criteria (AND logic)

      =FILTER(A2:C13, (B2:B13=F1)*(C2:C13=MAXIFS(C2:C13,B2:B13,F1)),"No results")

      I hope my advice will help you solve your task.

  11. Hey do you know why FILTER formula is not working when I open the file on OneDrive? The formula works perfectly when the file is opened on local Excel. No error is shown. I have no idea what wrong, could you help me on this. Thank you.

    The formula that I key is as follow:
    IFERROR(IF(FILTER(D55:D69,G55:G69<0)=0,"",FILTER(D55:D69,G55:G69<0)),"")

  12. Hello,
    After creating a filtered table (using this filter function) I need to extract the resulting table into a data table in which every cell would be filled with the data and not a reflection of the formula used.
    Is it possible and how?

    Thank you in advance.

  13. Hello,

    I've been trying to filter a data set that will return results based on two user input options (example: InpA and InpB). I tried using the information from the "Multiple AND/OR" article, but the formula that I use seems to exclude the results that contain the inputs.

    "InpA" data is found in the first column, however, "InpB" data can be listed in one of 22 columns per row. I thought that I might need to create "OR" statements for the function to include each column (example table is below). Any insight would be greatly appreciated! Thank you!

    The formula structure is: (FILTER(TT_Merge!C5:Z148,(TT_Merge!C5:C148=C5)*((TT_Merge!D5:D148=Dashboard!E5)+(TT_Merge!E5:E148=Dashboard!E5)+(TT_Merge!F5:F148=Dashboard!E5)+(TT_Merge!G5:G148=Dashboard!E5)......+(TT_Merge!Y5:Y148=Dashboard!E5)),"No Accounts Found"

    C1 C2 C3 C4 C5 C6 .....
    Assay ST-1 ST-2 ST-3 ST-4 ST-5 ST-6
    R1 ASA STA STB STC
    R2 ASB STA STC
    R3 ASC STB STA STD STG
    R4 ASF STF STC

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work.
      Here's an example of a formula that works. I assume it is similar to your conditions.

      =FILTER(A2:D13, (B2:B13=F2)*((C2:C13=1)+(D2:D13=2)), "No results")

  14. Hello Everyone,

    Anyone help me out, I have mentioned below my question.

    If A1 is ABC then takes filter list value from 'sheet 1' and if A1 is XYZ then takes filter list value from 'sheet 2'

    I have tried this formula with IF function but I get an error.

    1. Hi!
      I can't guess which formula doesn't work for you. But this formula works:

      =IF(A1="ABC",FILTER('1'!A2:C13, '1'!B2:B13='1'!F1, "No results"),FILTER('2'!B1:M3,'2'!B2:M2= '2'!B6, "No results"))

  15. Hi Everyone,

    I have an Excel file for daily reports of a project, each day has a sheet, named the date of that day, so we add a sheet every day.
    in the "summary" sheet I want to have a table that automatically:
    1 - read the date from a cell in the same table.
    2 - look for a sheet with the name of that date
    3 - read and bring a value from a cell of that sheet into this new table cell
    Can you please let me know if you know how to do it?
    Thank you

  16. Hi,

    I am using the formula FILTER(IF(Data!A4:N143="","",Data!A4:N143),Data!F4:F143=A1) this is awesome and really working well. I do have one issue though and that is that I am getting the #VALUE! error on cells where the data its pulling through contains a lot of text. If I amend the cell to a few words it pulls through in the filter with no issues.

    Is there a way that I can make it pull through all the text in the cell?

    Thanks

  17. Can the array be from another workbook on my server?
    The workbook on the server updates every 30 minutes with fresh data, I would like to give my employee a lookup spreadsheet that extracts from the file on the server.

  18. Hello,

    For my work, i need to extract and filter information from 2 columns, but count how many times no information was added.
    Eg:
    (Column J 2-300) (Column L 2-300)
    Acknowledged by Context
    Guard.1 Information added
    Guard.2 (BLANK)
    Guard.1 (BLANK)
    Guard.3 Information added

    I have attempted to use the Filter formula, unique formula and xlookup formula, but i am getting no success.
    I am unable to use a pivot table as i need to be able to extract the information for the day into a separate table, then export this information to a separate tab within the same spreadsheet.

    Do you have any ideas on how this can work??

    1. Hello!
      To extract rows with empty cells in a column, you can use a formula like this:

      =FILTER(A1:C100,ISBLANK(B1:B100))

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Hi Alexander,

        The issue is that not only do i need to filter to locate the blank spaces, I also need to filter to match the guard who has left the area blank (not added context) and count them to place into another table.

        so the end goal is once i enter the raw data, with using the formulas, to fill in a graph which will be below:
        GUARD NAME - NO CONTEXT ADDED
        Guard.1 - (amount of no context added)

        Regards

        1. Hi!
          If I understood correctly, your summary table has 2 columns. You cannot create it with one formula. With the formula I gave you, you can fill in the first column.

          =FILTER(A1:A100,ISBLANK(B1:B100))

          To fill the second column, the COUNTIFS function can be used:

          =COUNTIFS(A1:A100,D1,B1:B100,"")

          Hope this is what you need.

  19. Hello and thank you for a great article. I am trying to do a filter where I can use and, and or in my logic. Let me see if I can make sense of what I want. Here is my table called roster with 4 columns:

    Column A Column B Column C Column D
    Input 1 User 1 Data 1
    Input 2 User 2 Data 1
    Input 1 User 3 Data 1

    So here is what I want the outcome to be. If column a equals 1 and Column C equals Data 1 or Column D equals Data 1then list Column B in Column E. So it would look like this on Column E (the columns above are in a table labeled roster)

    Data 1
    ____________
    User 1
    User 3

    Here is my formula I have but I keep getting all three users

    FILTER(Roster[[Column B]:[Column B]],(ISNUMBER(SEARCH("Input 1*",Roster[[Column A]:[Column A]])))*(ISNUMBER(SEARCH("Data 1",Roster[[Column C]:[Column C]])))+(ISNUMBER(SEARCH("Data 1",Roster[[Column D]:[Column D]]))),"No Users")

    1. Hi!
      I cannot test your formula as it contains references to your data tables. Here's a sample formula for you:

      =FILTER(B1:B3,(A1:A3="Input 1"*((C1:C3="Data1")+(D1:D3="Data1"))))

      There are 3 conditions used here.

  20. I'm trying to use drop down lists to filter a table on two variables (teacher and period) so I'm trying this:

    =FILTER(Table2[Student Name],(Table2[1]=B1))

    This returns the column "Student Name" from the table "Table 2" based on the data in column "1" matching the data in cell B1.

    What I want to do is have a second cell, C1, control the column name "1" but I can't figure out the right way to format it.

    1. Hello!
      If I understood the problem correctly, you can use different FILTER formulas for different C1 values using the IF function.
      For example:

      =IF(C1=1,FILTER(A2:C13, B2:B13=F1, "No results"),IF(C1=2,FILTER(A2:C13, C2:C13=F1, "No results"),"0"))

  21. I have a column of text values and wish to filter based on the length of each value. I only want to return values which are 5 characters long. I realised I could do this easily by creating a second column which contains the length of each value in the first column, but it would be more elegant to do this without having to create additional meta-data.

    1. Hello!
      To set a filter conditionally, you can use the recommendations from this article.
      The formula might be something like this:

      =FILTER(A1:A10,LEN(A1:A10)<5)

  22. Is there anyway for the "include" part of the formula to be based on multiple criteria in the same column? Using the first example in your article, could I make the filter array return all of the "b" and "c" into one output?

      1. Oaky - sorry - I read that again carefully. I had missed the "Or Logic" section initially. Thanks so much - that scenario fits perfectly!

  23. Hi!

    Is it possible to use this formula to filter multiple sheets? Do i need to apply it together with VBA loop through multiple sheets?

    Thanks.

  24. I'm trying to filter the data but be able to edit the data that's filtered. With dynamic filtering it's a preview of the data in the table. How do I edit the data that's filtered with dynamic filter?

  25. Hi, Thank you for such wonderful explanations with examples.
    I have a question related to limiting the number of rows returned by FILTER.

    In my code I have used something like this to return data consisting of limited columns (columns 1 and 3 in code with {1\3}) but with unlimited rows.

    How and where should my condition for limiting the number of rows go in the following code

    =FILTER(INDEX(mD!D9:F19;SEQUENCE(ROWS(mD!D9:F19));{1\3});(mD!X9:X19=TRUE)).

    I tried adding the rows before the {1\3}, but it did not work.

    Since the code I have has construction FILTER(INDEX...) different from the example here INDEX(FILTER...), I am not able to interpret the solution

  26. Thank you for the update and this wonderful article. If I could ask a question:

    Is it possible to loop through a column of values for the criteria? For example, if I have something like this:

    Sheet2.Range("A2").Formula2 = "=FILTER(Sheet1!A2:J100,Sheet1!B1:B100=F1,"")"

    Is there anyway of looping through the values in the Column F directly or say in a VB macro?

    A VB macro might look like this:
    Sub Test()
    With ActiveSheet
    Sheet2.Range("A2").Formula2 = "=FILTER(Sheet1!A2:J100,Sheet1!B1:B100=F1,"")"
    End With
    End Sub

  27. The data I'm filtering can have multiple lines within a single cell. How can I set the workbook to autoFit the height when the data is pulled in?

  28. Can you please help me to "Filter on blanks". The data set has
    Column A has a list of Names
    Column B has performance grades 1-5 (of which some will be blank, so if column B is blank return the corresponding Name.

    =FILTER('Line by Line Data'!$A:$A,'Line by Line Data'!B:B="","") currently returns the following. What I am after is the data without 0

    0
    Bob Smith
    0
    0
    0
    0
    Jane Evans

  29. Can you please help me to "Filter cells containing specific text" from multiple worksheets or multiple tables?
    For example, FILTER(array, ISNUMBER(SEARCH("text", range)), "No results"). How can I added multiple tables that are in various worksheets to the above formula?

    Hope someone helps. Thank you

    1. Hello!
      The FILTER function works only with the data of the current sheet. You can combine data from multiple sheets into a current sheet using the Copy Sheets tool.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      To work with multiple tables on one sheet might be helpful - "Example 1. Filter multiple columns in ExcelExample 1. Filter multiple columns in Excel". Read if above.

  30. Hi Alexander - thanks for the article, I've found the FILTER function very useful for parsing data.

    I'm trying to apply your does not equal logic to exclude rows of data that contain specific names. For example: =FILTER(A1:H2500,(H1:H2500(("Acme Inc."))))

    This works well for a single instance like "Acme Inc.", but I would like to nest this so that I can exclude several companies tagged within column H (eg "Acme Inc."; "Smith Inc."; "Alex Inc.";). Is there a way to do this? I've tried creating an OR statement with the names with no success.

    Any help is much appreciated.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =FILTER(A1:B30,(A1:A30<>"Acme Inc.")*(A1:A30<>"Smith Inc.")*(A1:A30<>"Alex Inc."))

      Hope this is what you need.

    2. To clarify, the function above should read =FILTER(H1:H2500,(H1:H2500(("Acme Inc.")))) ...regardless I have not found a way to nest multiple names from the same column.

  31. Hello.

    I have a question.
    Is possible to use filter to return image/icon?
    I have a icon with a link attached in each row of my table and when I filter the table I would like that the icon also to appear in the result table...

    Is it possible with FILTER?

    Thank you.

    1. Hello!
      The filter works with data that is written in a cell. The icon is attached to the cell. Therefore, the filter does not see it.

  32. Not a question, but i just wanted to say that
    1) This is excellent (particularly when you match with [0,0,1,1,0,1...} to return specific columns, and
    2) Wrapping FILTER in a SORT function is particularly useful in my context.

    I can see all sorts of possibilities forreturning dynamic tables based on user-chosen check boxes (for example).

    Now i just need to work out how to identify unique strings of text within other strings of text in filtered array! But ill get there

      1. Thanks Alexander, i have looked here and cant quite work out exactly the right formula to give me the asnwer i need. I have left a question on that thread though.

        The most basic example of my problem is, i have 2 cells, lets say "1, 2" and "1, 3". I need to count the unique strings within those cells so im counting 1, 2, and 3 a single time each.

  33. Hi,

    This formula is amazing!! I have one problem though, my original data is in a separate workbook. When I first added the formula (to my new workbook) it filtered perfectly, but over time the original data changes, more rows gets added all the time, but the filter formula doesn't update to include those new rows, even when they meet all the criteria. I have set the array (in the formula) to be really big, to allow space for the new rows. Is this because its in a different workbook? Or could it be how I have saved it? Or is it because once it has been saved it doesnt update anymore?

    Thank you
    Rene

    1. Hello!
      In order to update the data, all the necessary files must be opened in Excel. Or click the "Refresh All" button in the Data menu

  34. Hi,

    Hope you are all well.

    I am a bit stuck. I am using INDEX and FILTER to list job numbers per site. My problem is that some sites have more job numbers raised against them than others, so to try and not have the spreadsheet huge and then having to scroll down loads, id like to have 2 columns for the job numbers.

    One site may have 14 job numbers, and I have limited it to show only 10, but I would like the remaining 4 to then overflow onto the column next to it, so all job numbers are still visible

    Is this possible?

    Many thanks in advance

    1. Hello!
      I kindly ask you to have a closer look at the following paragraph of the article above - How to limit the number of rows returned by FILTER function. It contains answers to your question.
      If something is still unclear, please feel free to ask.

      1. Hi Alexander,

        I had already read that, I have limited how many jobs numbers it shows, but I am in need of the remainder be shown in the column next to it.

        Example:
        I have 20 cells, over 2 columns, I have 14 job numbers, 10 of the job numbers are shown in the 1st column, the remaining 4 I want to show in the 2nd column. So where all the job numbers don't fit in the 1st column, they overflow into the 2nd column.

        Many thanks in advance

        1. Hello!
          You can specify numbers from 10 to 20, etc. in the formula. For example:

          =IFERROR(INDEX(FILTER(A2:C13, B2:B13=F1), {10;11;12;13;14;15;16;17;18;19}, {1,2}), "No result")

  35. Thanks for the wonderful guide ,
    However , I had a doubt. Is there any method that can provide me all the outputs (matches) in one blank cell instead of a list ?

    To put things into context , I have data in rows A2 to G12345 , I am looking for multiple matches against a Customer ID (having multiple occurrences) . I would therefore not want another list being shown as the output , instead , want to have all matches in cell J2 and if possible , separated by commas ? . Maybe a combination of formulas could do the trick ?

  36. Hi, greetings and thanks for those good examples on FILTER.

    Hope to clarify, when I selected any cell within the range of E4:G7 in worksheet "Basic FILTER formula", I noted that the whole range gets a "border" around it. And I think this is not a named range. May I seek for advice what's that about and how to do that? I noted when I selected the whole range of E4:G7, the name box showed E4#. What did that mean and the function of it?

    Appreciate for you guidance and thanks again.

    1. Hi James,

      A range highlighted with a blue border is called a spill range. It is created by Excel automatically when you use a dynamic array formula that returns multiple values like our FILTER formula. A spill range indicates that everything inside it is calculated by the formula in the topmost cell.

      E4# is a spill range reference that refers to the whole spill range. Unlike a regular range reference, a spill range ref is dynamic and automatically reflects changes in the range size.

      All this is part of Dynamic Arrays functionality introduced in Excel 365.

      For more information, please see Excel spill range explained.

      1. Hi @Svetlana, thanks so much. I read through the guide and it is now working and I clearly understood how it works.

      2. Thanks so much for the update. I shall read through more on the spill range and revert if I need further guidance.

  37. Hi,
    Any way to filter out specific columns without using the {1,0,0,0,1,…………} format? Would be helpful if one could use a match on column name instead somehow.

    Thanks!

    1. If source data is in a table named tbData to do this I use xmatch( "range of list of columns to match", tbData[#Headers] ). You can then pick which columns to include and they can be in a different order to the columns in the original table. Hope this helps.

  38. How to Excel 365 Filter with indirect array of addresses not continuous:
    =FILTER(INDIRECT({"$M$2";"$N$2";"$O$2";"$P$2";"$Q$2";"$R$2"}),
    INDIRECT({"$M$2";"$N$2";"$O$2";"$P$2";"$Q$2";"$R$2"})""
    )

    Intermediate Formula Evaluation shows:
    =
    FILTER(
    {"test5";#VALUE!;"test6";"";0;0},
    {"test5";#VALUE!;"test6";"";0;0}""
    )
    Final output shows #VALUE! instead of non-blank cell values.
    Thanks Ahead!

  39. I'm going crazy trying to figure out why I don't have access to the FILTER() function in Excel. I have MSFT 360 and have tried using both the "Beta Channel" and "Current Channel (Preview)" and neither update has installed the FILTER() function. I found an old article that referenced Microsoft 360 "Pro" but don't see any place to purchase that on the Microsoft website, just "family" or "business" editions. AFAICT, there's no difference in functionality on those versions, only the number of people who can use it. Can you please let me know what version I need in order to access this function or, perhaps, a workaround with a different function?

    Thank you!

    BTW, my Excel version is 16.50 if that helps

    1. Hi,
      If you really have an OFFICE 365 subscription, then the filter function should be there. You can read about this in more detail here.

      1. Thanks for your super fast reply. Yeah . . . it's a head scratcher for sure . . . I definitely have a 360 subscription (can log in and verify). Maybe it's because I'm running it on a Mac. Oh well. :(

        1. Hi,
          Unfortunately, I cannot help you here. But the FILTER function is also available on Mac. Look carefully at the link I gave you. FILTER is currently available to Microsoft 365 subscribers in Current Channel.

  40. Hi, Excellent info on the filter function... saved me many weeks of work!

    I wonder if you could help me on this:

    Am applying a filter on a price list, to return a set of products based on the type of category - and this works fine. But now I would like to eliminate rows which have become "obsolete" since price has changed. For example I have this data set:

    PRODUCT - PRICE - LAST PRICE CHANGE
    Coffee Brand A - $5 - 1/1/2020
    Tea Brand X - $1 - 5/12/2020
    Coffee Brand B - $4 - 1/5/2021
    Coffee Brand A - $6 - 4/30/2021

    I would like my output to IGNORE the first row, dated 1/1/2020 since the price for that brand (Coffee Brand A) has now changed on 4/30/2021.

    Is there a way to achieve this dynamically? I would need to include this as part of the FILTER function, since the table above would have been "built" by using a filter on the category called "Tea&Coffee".

    Thank you!

    1. Hello!
      Please try the following formula:

      =FILTER(A1:C6,(D1:D6="category")*(C1:C6=MAXIFS(C1:C6,A1:A6,A1:A6)))

      You can learn more about MAXIFS function in Excel in this article on our blog.

      1. Hi,

        I tried the formula and it worked perfect in selecting the correct row from the two dates. The problem I have is that I want all records to be returned, except the "obsolete" one. To use my example again:

        The full list is this:

        PRODUCT - PRICE - LAST PRICE CHANGE
        Coffee Brand A - $5 - 1/1/2020
        Tea Brand X - $1 - 5/12/2020
        Coffee Brand B - $4 - 1/5/2021
        Coffee Brand A - $6 - 4/30/2021

        Your suggested equation returned:

        PRODUCT - PRICE - LAST PRICE CHANGE
        Coffee Brand A - $6 - 4/30/2021

        What I would like to return is this:

        PRODUCT - PRICE - LAST PRICE CHANGE

        Tea Brand X - $1 - 5/12/2020
        Coffee Brand B - $4 - 1/5/2021
        Coffee Brand A - $6 - 4/30/2021

        Can you suggest a modification please?

        Thank you so much for your help.

        1. Hello!
          The formula I sent to you was created based on the description you provided in your first request.
          You wrote: "Am applying a filter on a price list, to return a set of products based on the type of category". If you need a complete list, then remove this condition from the filter.

          =FILTER(A1:C10,(C1:C10=MAXIFS(C1:C10,A1:A10,A1:A10)))

          Hope this is what you need.

        2. I think I found a workaround: I introduced a column which flags whether that row is "obsolete" or not. Then used an "*" in the filter to check against it.

          Equation now looks like this:

          =UNIQUE(FILTER(FILTER(T_PRI, (Table2[[#All],[CATEGORY]]=B17)*(Table2[[#All],[OBSOLETE]]="")),{1,1,1,1,0,1,1,0,0,0,1,1}))

  41. Hello
    Great tutorial.
    one more question: How to filter with the new FILTER-function multiple criteria (AND, OR) with wildcard(s), in a table column
    e.g.
    Table:
    Col A Col B Criteria for search in Language column: Result:
    Row 1 Country Language ma (eg. in cell D1) Germany german
    Row 2 Germany german re (eg. in cell D2) France french
    Row 3 France french isc (eg. in cell D3) Spain spanish
    Row 4 Italy italian
    Row 5 Spain spanish

      1. Thank you very much for your reply. That is correct, however I am locking for a possibility to enter more than one search string as in my example above mentioned (the format of the example was lost when transmitting the post that is why it is not easy to read)

        - The Table is in Col A and B Row 1 to 5 named Countries
        - The headers of the table are named "Country" and "Language"
        - The Search Strings (only part of the words in the table) are in Column D Row 1 to 3 (ma, re, isc - who are in normal search with wildcards "*" or "?")
        - The Filter-Function for the result should be in Cell "F1” (the search should be an “OR” search how would it be with an “AND” search ?

        How to distinguish in the Filter Function between exact match, with Wildcard “*” or with Wildcard “?”

        1. Hello!
          Sorry, it's not quite clear what you are trying to achieve.
          The SEARCH function does not use wildcards. Which formula do you want to use?
          Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the expected result.
          It’ll help me understand it better and find a solution for you.

        2. Hello,
          FILER function for more than one criteria:

          After long trials I got now the answer for filtering more than 1 Criteria in a PowerQuery Table.
          1. I have to define a named range for the search area, then define the FILTER formula
          2. Execute the PowerQuery and put it to the in the FILTER formula defined range.
          With this I am able to run the FILTER function with more than 1 criteria

          Eg.
          For Criteria 1 AND 2
          =FILTER(A2:H656;(ISTZAHL(SUCHEN(L2:L2;Betreff))*ISTZAHL(SUCHEN(L3:L3;Betreff)));"kein Resultat")

          For criteria 1 OR 2
          =FILTER(A2:H656;(ISTZAHL(SUCHEN(L2:L2;Betreff))+ISTZAHL(SUCHEN(L3:L3;Betreff)));"kein Resultat")

          For criteria 1 AND (2 OR 3)
          =FILTER(A2:H656;(ISTZAHL(SUCHEN(L2:L2;Betreff))*ISTZAHL(SUCHEN(L3:L3;Betreff))+ISTZAHL(SUCHEN(L4:L4;Betreff)));"kein Resultat")

          Ps.
          ISTZAHL equals Isnumber
          SUCHEN equals SEARCH
          Betreff is the named search range

          For changing between the 3 Filter options (FILTER function) I wrote a macro which changes the FILTER formula automatically

  42. 1. how to filter based on checkbox that contain cell value from table in different sheet?

    2. how to filter based on drop-down that have VBA code which helps to click multi selections that contain cells value from table in different sheet?

  43. Is there a way to use =FILTER across multiple worksheets in a single formula? I'm using it to pull rows that meet certain criteria, but the reports I have to work with can have many worksheets to sift through.

    An example:
    =FILTER(Sheet1!A6:J1667,(Sheet1!G6:G1667="VariableX")+(Sheet1!G6:G1667="VariableY")+(Sheet1!G6:G1667="VariableZ"),"")

    Currently I am pasting a new =FILTER formula below the lowest returned line:
    =FILTER(Sheet2!A6:J1667,(Sheet1!G6:G1667="VariableX")+(Sheet2!G6:G1667="VariableY")+(Sheet2!G6:G1667="VariableZ"),"")

    And so on for how ever many sheets there happen to be. If I was able to build in [filter Sheet1 then also filter SheetN+1 until they are all filtered], it would save me a good deal of time.

  44. Hi Alexander!
    I am mixing up rows and columns some how but found that Svetlana had replied in an earlier question that I could use to get what I was looking for. Only returning specific columns.
    FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1) :)

  45. It did!! Thank you Alexander!

    Could you see why this works and filters.
    FILTER(Data!A:G;Data!G:G="Covid";"No results")

    While this doesn't.
    FILTER(FILTER(Data!A:G;Data!G:G="Covid";"No results");{1;0;0;1;1})

    I constantly get #VALUE!

    Jen

  46. Great tutorial!

    One thing I don't see solved is as for my case.
    I have cell values that sometimes have data and sometimes they are empty.
    I am using the cells for input to my Filter function.
    A B C D
    1 Month Article Cost Owner
    2 202103 John

    FILTER(array, (range1=criteria1) * (range2=criteria2), "No results")
    FILTER(array, (range1=202103) * (range2=)* (range3=)* (range4=John), "No results")

    In the case I don't have any input criteria as in Column B and Column C above.
    It will send the input filter-out-all-rows-where range 1= 202103, range 2=empty, range 3=empty, range 4=John
    How do I get around that I want to have the possibility to use all criterias but sometimes they are empty.
    I currently have 11 cells that I would like to use for criterias.

    Thank you!

    1. Hello!
      Add additional conditions to check an empty criterion cell.

      FILTER(array, ((range1=criteria1)+(criteria1="")) * ((range2=criteria2) + (criteria2="")), “No results”)

      I hope my advice will help you solve your task.

  47. I am using a Filter list to populate another sheet. Based on the names that appear in this new list, I am writing some static formulas/remarks. The problem I am having is that when the Filter list updates, sometimes the new data/names appear ON TOP of the original list, due to which the static data no longer matches with that which was imported from the Filter List. Is there any way to add the new entries from the filter list at the bottom? Or to tie the static data to the dynamic data?

  48. I have an excel column that contains text in around 1000 cells. In each cell, some text is BOLD, some text is ITALALICS, and some text is in REGULAR fonts. Thus, each cell has all three fonts (bold, italics & regular) in it.

    I want to filter cells that contain the text "because" in bold fonts only.

    Can you please help to write FILTER FORMULA for this.

    Yours sincerely,
    Prakash Dave.

    1. Hello!
      Your formatting does not change the "TEXT" format of the cell. Therefore, using Excel formulas, your task cannot be completed.

  49. Is there any way of using array as criteria in a Filter function?
    For instance, in your SUM, AVERAGE... examples - instead of F1 providing the Group as the criteria, calculate SUM for {"A","B","C"...}. The array could have been created using UNIQUE. And so the the output of the SUM will be an array.
    I have a similar problem, and I'm just getting error messages.

    1. Hello!
      For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      1. Hi,

        I'm referring to the section in this webpage titled: "Filter and calculate (Sum, Average, Min, Max, etc.)"
        I want to have something like the following formula:
        =SUM(FILTER(C2:C13,B2:B13=UNIQUE(B2:B13),0))

        Source data is as per the section in the webpage.
        My aim is to get results that show:
        Group, Wins
        A, 8
        B, 11
        C, 5

        ...and for it to be generated dynamically without knowing beforehand how many groups there are in the source data.
        I get #N/A when I try this.

        1. Hello!
          The FILTER function cannot compare two arrays. You can use these formulas:
          In cell E4:

          =IF(SORT(UNIQUE(B2:B100),1,1)=0,"",SORT(UNIQUE(B2:B100),1,1))

          In cell F4:

          =IF(E4<>"",SUM(FILTER(C2:C100,B2:B1100=E4, 0)),"")

          Copy this formula down along the column F.
          I hope my advice will help you solve your task.

          1. Thanks for that, Alexander. It is useful to know what FILTER *can't* do.
            Is there anywhere that clearly documents the *details* of the new(ish) array and dynamic array functionality? It seems to be a lot for trial and error and searching various blogs.
            I'm looking for something that better explains the interfaces - i.e. where you can and can't use them.

            By the way, there's a slight typo in your formula for cell F4 it should read:
            =IF(E4"",SUM(FILTER(C2:C100,B2:B100=E4, 0)),"")

  50. Hello

    I was wondering if there is any way to automatically insert rows if the filtered data requires more space than available. I was thinking about a dynamic page break which increase and decrease with the data required to be displayed.

    Please advise.

    Thanks
    Z

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)