How to filter in Google Sheets by condition

When analyzing data in Google Sheets, it's helpful to focus on specific parts of your dataset. Whether you're interested in certain values, colors, or dates, Google Sheets filters can assist you. In this article, I’ll guide you on how to set up conditions correctly while making sure you don't affect other users in shared spreadsheets, and share a few unique tips you won’t find elsewhere.

Google Sheets filter is a standard tool that lets you hide a part of your data that you don't need at the moment. This way you don't need to delete the data. You simply hide it temporarily and can display it back any time.

Table of contents


  1. Hello I'd like to ask if we can filter view from conditional formatting.

    I have a calendar which automatically recolors itself based on color using conditional formatting.

    right now I have 3 color indicators
    1. red for holidays and weekends.
    2. orange for ongoing projects phase A.
    3. blue for ongoing projects phase B.

    is it possible for me to filter rows only to appear based on the color orange (due to conditional formatting)? the cell is of course, blank.

    • Hello Andrew C,

      For me to be able to help you better, please share a small sample spreadsheet with us ( with 2 sheets: (1) a copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. I'll look into it and see if I can help.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Do not email there. Once you share the file, just confirm by replying to this comment.

      • Got it. I hope this can help. I've opened editor access to

        • Thank you for sharing the file, Andrew.

          You can't do that with the custom formula in the standard column filter because you'd have to get the formula for each column and it wouldn't work still. The conditions applied to several columns work simultaneously.

          However, there's a way to return the required dataset to another place of your file with a formula. Please look at cell E26, I placed the formula there:
          ={E3:NF3; FILTER(CELLCOLOR(E4:NF19,"fill",TRUE), (INDEX(CELLCOLOR(E4:NF19,"fill",TRUE), 0, MATCH(TODAY(), E3:NF3, 0)) = "light yellow 2") + (INDEX(CELLCOLOR(E4:NF19,"fill",TRUE), 0, MATCH(TODAY(), E3:NF3, 0)) = "#b7e1cd"))}

          It filters your original data by colored rows in the column with the today's date. Please note that formulas do not return colors. I put the colors you can see using conditional formatting rules, so check those as well to understand how it all works.

          Hope this helps!

  2. Thanks. Maybe I missed it but is there a simple way to dynamically display a filter of rows based on the active user email so with 1 FILTER VIEW it shows different ROWS based on the active user logged into the google sheet. (and of course each row within the google sheet that is active has a column that exactly matches the current logged in user email. )

    • Hello Fred,

      Do you mean you'd like to see the filter view of the user who's using it now? With their email address somewhere in the table as well?

  3. Hello,
    Thanks for the post !
    Do you know how, with a filter on custom fomula limking ot the content of a cell, i have a dynamic view when the said content change ?
    If I change the content of E1, I have to open again the filter and click on DONE again for it to actualize.

    I get I can go with the function FILTER, but this is not convenient when you wants to actually modified the content inside the table.

    • Thank you for your feedback, Lycoeur!

      For me to understand your task better, please share an editable copy of your sheet with me: If you have confidential information there, you can replace it with some irrelevant data, just keep the format. Please make sure your current filter is applied and specify what should be changed and where.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into it and try to help.

  4. I want to hide the rows with blank data and 1 column containing any value =0. I have tried with the filter view by conditions as well as value, but it is not static. I want to create that view for all the users of this google sheet. Please help.

    • Hello Biswadeep,

      Filter View is only visible by the user who's applying it. It's your way to filter the data without changing it directly.

      To filter the data so everyone could see it, use the standard Filter, not Filter view.

  5. Hello,
    you write here in article- when put condition into the CELL (instead of selecting condition in filtres directly): " Click the Filter icon on your Google Sheets column and then OK to update the results manually."

    Is there a way how to do it AUTOMATICALY ? the update(refresh) step? because then it would be REALLY AWESOME
    here is my simple sample data ....but i think that the question is clear at all.
    thank you

      • Hi Nat!

        so i tried the script thing ....aaand - it is for me like seeeing the matrix code :-) so I tried how deep the rabit hole goes and it goes far... :)

        shortly: after some week of research - I did it!!

        there is no script that aupdates or refreshes the filter (to my knowledge) but there is a way how to SET UP the filter by script - so the script sets up the filter again and again - and actually you can call it "refresh" or "update" of filter

        I have set TRIGGER to run the script everytime ANYTHING in the sheet changes (because i could find a way to trigger change in just one cell) and put my CONDITION of filter into some CELL (O1) - where i can change text according which is the data filtered

        here is the code:

        function automaktfiltr() {
        // Gets the existing filter on the sheet.
        const ss = SpreadsheetApp.getActiveSheet();
        let filter = ss.getFilter();
        condition = ss.getRange("O1").getValue()
        // Creates criteria that shows cells that contain "Northwest" and sets it to column B.
        let criteria = SpreadsheetApp.newFilterCriteria()
        filter.setColumnFilterCriteria(2, criteria);

        • Hi Dech,

          Big thanks for sharing your solution on our blog! I believe it'll come in handy for other users with similar tasks :)

  6. I have to face some problems with my Google sheets Data. I want to filter my google sheets data between two dates and also filter more conditions at the same time in the same sheets. Below are given some sample data.

    Basically, I want to filter data between two dates & two more conditions at the same time in the same sheets.

    Please help me with this issue.


    I am trying too hard to solve this issue but I can't solve this by myself. so if anyone solves this issue then please do this.

    I'm very glad for all of you.


    • Hello Shuvo,

      I can see that you already have a working formula on the Solution sheet. The only thing I'd like to point out is that the formula is looking for the value from A13 in column D (Product details) while it should be column E (Category).

  7. Thanks so much!

  8. I'm having problems sharing a sheet that I want users to be able to filter/sort, but not edit (not change cell values). In your example above, you talk about having an employee filter to look at only *their* data in the sheet - this looks like my use case, where the manager enters the data and the employee can filter and see it, but not edit it. I seem to be missing some important step in how this all works because when I share the link with others, they can't access any filter views. Can you fill me in on what I'm missing here?

    • Hello JC,

      When you share files and give users permissions to only view or comment on data, they can create their own filter views via Data > Filter views > Create new temporary filter vew. This menu is always accessible in spreadsheets. However, the users won't be able to see other users' filter views. Each user can create, save and see only his/her filter views.
      Also, I'd recommend sharing the spreadsheet directly with users by adding their email addresses in the sharing settings.

  9. Hello! Is it possible to filter by range, I try so
    custom formula =ISNA(MATCH(B:B; Settings!D2:D200; 0))=FALSE
    but for some reason it doesn't work.

    • Hello Max,

      Please consider sharing your spreadsheet with us as well - Once shared, please confirm by replying here since we don't monitor the Inbox of that email.
      We'll look into it and try to help.

  10. In $D$ i have some filed witch text.
    Data table with filters I have from D31:D.
    I add filter in custom formula =REGEXMATCH(D31:D;$D$3)
    And it works.
    But when I change data in D3 the results in my Table will refresh.
    Eny idea how to refresh this data table?

    • Hi Tom,

      Would you mind sharing a sample spreadsheet with us ( with your source data and the result you expect to get? We'd look into it.
      Note. We keep for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.

  11. Filter by, "Text Contains", is there an escape for the wildcards '*' and '?' ? Please include, or say there isn't, and offer the regex =REGEXMATCH(D:D,"\?")


    • Thank you very much for your feedback, John. :)

      The formula wasn't mentioned intentionally since:
      1) it works with textual values only, meaning the users would have to wrap their numbers in the TEXT function and then with the one you suggested.
      2) wildcard characters cannot be used in your formula. It looks for the exact sequences you indicate in "". Thus "\?" won't find anything since we don't have this in our cells.
      3) the function is case-sensitive and won't find anything if I enter "d" for all these "Dark Chocolate" cells.
      4) all of the above make the formula too complicated to use, leaving the easiest option of wildcard characters.

  12. Great explanation! I especially appreciate that you have shown equivalent custom formulas for the built-in criteria like 'Text contains', useful as a starting point for more complex criteria.
    One possible correction: you state "Note. The formula can refer only to the column it's applied to..." but it appears that has changed. Today I was able to apply this formula with success: =OR(A:A="horses",C:C="cat") referring to two different columns. This worked regardless of whether I attached this custom formula to column A or column C.
    This is a significant improvement for me, because it appears (to be tested...) that it will enable quite complex multi-column criteria, combined with the advantage that the filtered data can be edited, apparently not the case with results from QUERY( or FILTER(.

    • Thank you for your feedback, Chris.

      Unfortunately, the formula like you provided doesn't work for me. However, if I take the formula where I refer to column B in both conditions and apply it to column E - it works. Thank you for pointing that out.

  13. Thank you. Your article helped me. Have a nice day and Happy New 2019 Year! :)

  14. pehn di lann, assi khotte haan, assi kanjar haan.

  15. I want to create a spreadsheet that references a column for a specific suffix, then have it take data from another cell in a row that contains said suffix and total it in another cell.ia that possible?

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 :)