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 2. Total comments: 105

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

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

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

  4. 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"))

  5. 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)

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

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

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

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

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

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

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

  13. 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 ?

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

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

  16. Can the input range to a listbox form control be the spill range of =UNIQUE(....)?

    I want the input range to a list box form control to be all the DIFFERENTsales reps in column A without duplicates.

    1. Hello!
      Extract unique data to any empty column. Use this instruction.
      Then, using the formula, get a list without empty cells:

      =OFFSET($D$1,,,COUNTA($D$1:$D$200))

      Copy the entire formula from the formula bar.
      Select cell, which is where you want the dropdown to be shown.
      Go to Data > Data Validation.
      Paste the formula as the Source.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  17. Thanks for your answer Alexander! Unfortunately, your solution requires 3 separate formulas. However, I want to give an option to filter "all colors" as well as individual colors in the same formula.

    =FILTER(A2:C7,(B2:B7="{blue, red, yellow, all colors}")*(C2:C7>5)).

    I want to give a user something like drop down list where he/she can select color from the list "blue", "red", "yellow" as well as "all colors". I can do individuals colors, but I can not do "all colors" option. I also tried to use "*" symbol, but FILTER function does not support wildcards.

    I would appreciate your comment/solution to this problem! Thanks!

  18. Hi, I love all the examples! Do you know if FILTER can be used directly in data validation source?

    That is, I would like to have a drop down list with items depending on selection in another dropdown list.
    I tried this formula in cell AB3:
    =FILTER(B1list;$AA$3=BtoB1;"")
    and it works nicely in a cell, but when I try to use it in data validation --> list --> source it says 'The Source currently evaluates to an error".
    (AA3 is where the user is supposed to select the first value and I would like them to only see relevant items from the list BtoB1 list in cell AB3)

    Cheers,
    Kamil

  19. Hello, excellent article, thanks!

    Is there any way to limit the number of rows a FILTER function returns? For exmaple, the function returns 10 rows but I have only space for 5 (and I cannot delete the data below that)... is this feasible?

    Thanks!

  20. Great post! This is so powerful and I learned so much!!

    Is there a way to Filter Non-Adjacent Columns and define the Array Constant using a formula (or in some way dynamically)?

    Here's what I mean: I would like the {1,0,1} to change to {1,1,0} based on other criteria in my workbook.

    =FILTER(FILTER(A2:C13, B2:B13=F1), {1,0,1})

    Can change to:

    =FILTER(FILTER(A2:C13, B2:B13=F1), {1,1,0})

    Is this possible?
    Thanks again for this great post!!!!

    1. Hi!

      It' difficult to say anything certain without knowing exactly what kind of criteria you have in your workbook. Anyway, I believe the simplest approach that you can try is nested IFs.

      For example, if K1=1, return {1,0,1}, if K1=2, then return {1,1,0}:

      =IF(K1=1, FILTER(FILTER(A2:C13, B2:B13=F1), {1,0,1}), IF(K1=2, FILTER(FILTER(A2:C13,B2:B13=F1), {1,1,0}),""))

      1. Thank you Svetlana. That is not quite what I'm hoping to accomplish. I would like to create the Array Constant Dynamically. So in this example, cells A1, A2, A3 might contain 1's and 0's that would be used to populate the Array Constant. So if A1=1, A2=0, and A3=1, the Array Constant would be {1,0,1}

        In short, I would like to build the {1,0,1} parameter using a formula.

        Is this possible?

        Thank you again! SJ

        1. Oh, that makes things even easier :)

          Instead of an array constant, you can use a regular range reference. For example:

          =FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1)

          Where J1:L1 are the cells containing your 1 and 0 values.

          1. OMG Svetlana you are the absolute best!!!!

            One more question. This works when I use a horizontal range but not when I use a vertical range. Just curious why? And any way I can work around this?

            As an example:
            =FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1)
            works fine

            =FILTER(FILTER(A2:C13, B2:B13=F1), J1:J3)
            does not

            Thank you so much - this has save me countless hours!!! You are like an Excel Angel!!!

            1. Thank you :)

              As for your question, it's because {1,0,1} is a horizonal array, so it requires a compatible horizontal range.

              If your data was organized horizontally in rows (i.e. if you transposed your table), then the FILTER formula would require a vertical array constant like {1;0;1} to return data from specific rows. And you would replace that array constant with a vertical range.

              We have a couple of in-depth tutorials on Excel arrays and traditional CSE array formulas. If you are curious, you can check them out:

              Excel array formulas, functions and constants (among other things, it explains the difference between vertical and horizonal arrays)

              Excel array formula examples for beginners and advanced users

  21. Is it possible to use the Filter Function to filter on format, in this specific case, color? I'm trying to avoid VBA for this specific workbook.
    Thanks

  22. Regarding using aggregate functions wrapped around filter results ... set {is empty] to zero works fine for most aggregate functions such as SUM, but is problematic for COUNT functions. The intention behind wrapping the filter function with COUNT is to count rows. If no rows exist matching the filter criteria, then , I want the COUNT function to return zero. Maybe I am missing something, but this is a lot harder to do than I thought. Maybe I need to abandon the FILTER and UNIQUE function to use other methods. Any suggestions? Thanks

    1. Hello!
      You have not listed the formulas that are causing the problem. But I think using function IFERROR will help replace the error message with zero.
      If there is anything else I can help you with, please let me know.

  23. How do i create a filter on an ever changing range (sometimes 100 rows, sometimes 250, or even 10) - do i have to preset the filter to cover x number of rows, or can i create dynamic range

      1. Hi Alexander,

        Can we filter by connecting to a dynamic range via a pivot table?

  24. Hi,

    In your examples, how do you return just Name and Wins for example

  25. Hello!

    One doubt:

    How do I Add a last row with column sum, using filter? Could I do that?

    1. Hi Lincoln,

      This can be done by using the SUM function with a spill range reference (# symbol).

      Assuming the FILTER formula is in E4, the SUM formula will be as simple as this:
      =SUM(E4#)

      Just make sure there are enough empty rows between the FILTER and SUM formula cells. If FILTER does not have the required number of rows to fill with the results, a #SPILL error will occur.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)