How to filter in Google Sheets by condition

by Alexander Trifuntov, updated on May 20, 2024

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.

How to create a filter in Google Sheets
Build Google Sheets filter based on cell value
How to filter by color in Google Sheets
- Filter by one color
- Filter by multiple colors
Filter by condition in Google Sheets
Can Google Sheets filter by multiple conditions?
- Video: filter by multiple conditions, easy way
Can Google Sheets filter multiple columns?
How to turn off filter in spreadsheets
How to filter without affecting other users — Google Sheets filter views

How to create a filter in Google Sheets

1. Select the range

For Google Sheets to filter your whole table, just click a single cell within it and proceed to step 2 — turning the filter on. This will work like Ctrl+A: your entire used range (all cells with data before the first blank row & column) will be selected automatically.

But to filter only a certain part of your table, or to include completely blank rows & columns in your selection, you must first specify the range of interest. You can do that in one of the following ways:

Select entire columns (or rows). (If you're not sure what I mean, I explained how to do that in one of my previous articles.)
Select the range manually.

2. Turn Google Sheets filter on

Then either go to Data > Create a filter in the Google Sheets menu: Enable Google spreadsheet filter using the menu.

Or simply click the corresponding filter icon in Google Sheets toolbar:

Tip. I suggest you freeze the first row of your table: headers with filter buttons. This way you won't have to scroll all the way up to adjust filter settings.

Once you do this, each column header will have this kind of an upside-down pyramid icon: You can filter your table now.

It means you created a filter in Google Sheets successfully and are ready to play with it.

Build Google Sheets filter based on cell value

The most basic filter you can do is by value:

Pick the column you'd like to filter.
Click that filter icon next to its header.
Uncheck all those values you want to hide temporarily (click on each so checkmarks disappear):
Confirm with the OK button.

You'll see how values without checkmarks instantly disappear from the screen. They are still there, in your table, you just don't see them displayed anymore 🙂

You'll also notice that the filter icon has changed next to this column header: Different icon and missing row numbers indicate the active filter.

Filter Google Sheets by values for multiple columns

Similarly, Google Sheets lets you filter multiple columns based on cell values.

Let me show you and create an additional filter for the Product column. I'll keep only the info for Dark and Extra Dark Chocolate: How Google Sheets filters multiple columns by values.

As a result, there are even fewer displayed rows than before. The remaining ones meet all requirements from both columns: Filter icons next to column headers indicate that the filter is active for two columns.

How to filter by color in Google Sheets

If you use different font and fill colors to present all data in the best way possible, there's one invaluable option for you in Google Sheets — filter by color.

For example, I set up conditional formatting in my list of orders:

If the number of items is less than 50, the number (its font color) turns red.
If greater than or equal to 100, the fill color becomes green.

Let's try and filter this table by color in column E.

Filter by one color

I'll start with the standard Google Sheets tool that lets you filter by one fill or font color at a time.

Example 1. Filter by text color in Google Sheets

To filter data by font color and keep only red numbers, do this:

Create a filter.
Click the filter icon on the column of interest and pick the color you want to keep visible in your table. Other rows will be hidden: Filter by color > Text color > red:

As you can see, this filter by color fetches only those Google Sheets rows where numbers are red: Only numbers in red remain visible in the Qty. column.

Example 2. Filter by fill color in Google Sheets

Another way would be to filter by fill color in Google Sheets.

Let's keep only rows with the green background color. This time, select the following from the menu: Filter by color > Fill color > light green 3: Filter Google Sheets by light green fill color.

Now only rows where Qty cells are colored green remain: Have only rows with the light green fill color visible.

Filter by multiple colors in Google Sheets

As you may already know, the standard Google Sheets Filter by color tool has its limits. It doesn't allow filtering by both fill & text color, nor does it support filtering by several font or fill hues.

Luckily, our Function by Color add-on for Google Sheets fills this gap 😎

Onboard this tool, you will find CELLCOLOR function. Paired with Google Sheets FILTER function and its ability to work with the OR logic, it will let you filter by multiple font colors, fill colors, and even combos of fill and font colors together.

Intrigued? 😏

Learn about this possibility in another article devoted to the FILTER function.

Filter by condition in Google Sheets

Much like filtering by value or color, filter by condition lets you sift through your data — only with precision. You no longer need to manually tick off every entry from a list. Instead, you specify criteria such as text containing, starting with, or exactly matching certain phrases. Additionally, you can filter by dates, blanks, or numbers.

For starters, enable the filter.
Then click the filter icon on the column you want to work with.
Pick Filter by condition.
Click the None field.

You'll see all conditions available to filter in Google Sheets: See all filter conditions available in Google Sheets.

Filter blanks in Google Sheets

The first two options in the drop-down will show you all rows where cells in a certain column are missing data (Is empty): Filter blanks in the Region column in Google Sheets.

or, on the contrary, hide such rows from the display (Is not empty): Hide blanks and show only rows with data.

Tip. If blanks appear in your filter when they shouldn't, make sure these cells don't contain spaces ( ), empty strings (=""), or even text wrapped to the bottom after the line break ().

Filter by text: Partial matches

Example 1. Text contains

This option will filter those Google Sheets rows where cells contain specific numbers, parts of the words, etc.

You can even use special wildcard characters to create masks and look for some specific symbols in different positions within cells:

asterisk (*) is used for any amount of characters
a question mark (?) replaces a single symbol

Hence, you can get the same result by entering various wildcard char combos: Dark, D*k, D??k Same result, 3 different filtering options with wildcard characters.

Tip. If you're into formulas and regular expressions rather than simple patterns, here's a custom formula to filter by this condition in Google Sheets:

=REGEXMATCH(D:D,"Dark")

Example 2. Text does not contain

I believe you've already guessed that the conditions here can be the same as in Text contains above.

Yet, the result will be the opposite. The value you enter will be hidden from the view by this Google Sheets filter.

Tip. As for the custom formula to filter by this condition in Google Sheets, it will require regex. It will look as follows:

=REGEXMATCH(D:D,"Dark")=FALSE

Example 3. Text starts with

For this condition, enter the first character(s) (one or more) of the value of interest, e.g. Milk: Filter all Products that start with the word 'Milk'.

Note. Wildcard characters don't work with Text starts with.

Example 4. Text ends with

Alternatively, enter the last characters of the text you want to filter and fetch in Google Sheets.

Note. Wildcard characters are not allowed for Text ends with.

Find exact text matches

This one is similar to a filter based on cell value. Only instead of ticking off each value from the list, you directly enter what you want to see. White Chocolate, for example.

Note. Entries that contain something other than that won't be displayed. Thus, you can't use wildcard characters here.

Tip. If you want to filter your Google Sheets using a custom formula and look for all records that contain only White Chocolate, use the following:

=D:D="White Chocolate"

How to filter by date in Google Sheets

Google Sheets knows how to filter your tables by dates. There are three conditions based on which your spreadsheet fetches rows with:

exact date
date before the exact date
date after the exact date

Some other periods of time are available to ease your job of setting up the filter, such as today, tomorrow, yesterday, in the past week, in the past month, in the past year. Filter by date in Google Sheets when it's before another exact date.

Note. Make sure to enter dates in the format required by your spreadsheet regional settings rather than the looks in the table. You will learn more about date formats in this article.

Google Sheets filter with numbers

Your Google Sheets can filter numbers by the next conditions:

greater than
greater than or equal to
less than
less than or equal to
is equal to
is not equal to
is between
is not between

The last two (is (not) between) require two different numbers to indicate the starting and ending points of the desired interval.

For instance, let's fetch rows where Qty. numbers in column E are greater than or equal to 100. But this time, let's put our criteria value to G1 and use the cell reference as the condition:

=$G$1 Show only values that are 100 or greater.

Note. If you change the number you refer to (100 in my case), the displayed range won't update automatically. Click the Filter icon on your Google Sheets column and then OK to trigger the update.

Tip. The custom formula can be used for this kind of filter as well:

=E:E>$G$1

Custom formulas to filter by condition in Google Sheets

Each Google Sheets filter criteria I've mentioned so far can be replaced by custom formulas that will fetch the same result. (You will find formula examples in the corresponding article sections above.)

Yet, I believe formulas are a last resort: for when the condition is too complex for the default Google Sheets filter tool. Like Google Sheets filter by multiple conditions in one column (OR logic). For example, want to see all Milk and Dark goods? Use this custom formula:

=OR(REGEXMATCH(D:D,"Dark"),REGEXMATCH(D:D,"Milk")) Set up Google Sheets filter by multiple conditions using a custom formula.

This is not the most flexible filter though I believe. If you're interested in formulas, I advise you to check out Google Sheets FILTER function capable of so much more.

Or share your case in the comments section and we'll figure out a custom formula to filter just for you ;)

Can Google Sheets filter by multiple conditions?

If you mean in the same column — yes, Google Sheets can filter by multiple conditions but only using the Custom formula option from Filter by condition.

Such operators as AND and OR will help you list several criteria, just like in the example above.

If this is not enough or too inconvenient, there’s a better solution — a special add-on for Google Sheets called Filter and Extract Data. It filters multiple rows and columns, each with lots of criteria applied.

Why try it? Well, the extension is user-friendly, so you won’t have to doubt your own actions. The tool won’t alter your source data at all — it will copy and paste the filtered range wherever you decide.

Tip. Feel free to jump below to the video about the tool right away.

Once you install the add-on, you will find it under the Extensions tab in Google Sheets: Spreadsheet menu where you start the extensions.

The first step you’ll see is the only one there is: Set up multiple conditions in the Filter and Extract add-on and see the preview right away.

I'll use the add-on to filter my Google Sheets table of sales: A1:F69
The columns I want to fetch are Date, Region, Product, and Total Sales.
Now it’s time to compose multiple conditions. I'll try and get all sales of milk and hazelnut chocolate for September 2024.
While you thread your criteria, the formula from the preview area at the bottom of the tool will modify itself accordingly. Click Preview result to peep at the found matches.
Select the upper leftmost cells to extract all matches there.
And hit either Paste result (to return all matches as values) or Insert formula (to insert a formula with its outcome):

If you'd like to get to know Filter and Extract Data better, I encourage you to install it from the Google Workspace Marketplace:

Make sure to check out its help page to learn more about its possibilities.

Video: filter by multiple conditions, easy way

Filter and Extract Data is the best and the easiest way there is to filter your data in Google Sheets. Watch this demo video to learn all the benefits of owning the tool:

Can Google Sheets filter multiple columns?

Yes, Google Sheets can filter multiple columns simultaneously.

Whether it's by values or by condition, you just need to enable the criteria for each required column. For instance, filter all sales of:

all kinds of Dark chocolate: Text contains Dark
sold before October 1, 2024: Date is before exact date — 1/10/2024
with total sales of $200 and more: Greater than or equal to 200

Or use the add-on from just above to ease things out.

How to turn off filter in spreadsheets

Turn off filter by color

To remove any filtering by color, simply go to Filter by color > None, and all formerly filtered rows will appear anew: Turn off filtering by color In Google Sheets.

Turn off filter from specific columns

To reset the filter on a specific column, click the filter icon and:

if you're using Filter by values, hit Select all
if you're using Filter by condition, click the drop-down with the selected criteria and pick None

Then click the green button at the bottom of the menu saying OK.

Turn off filter completely

To turn off the filter completely and display all the rows again, go to the Data menu and pick Remove filter: Remove filter using the Google Sheets menu.

How to filter without affecting other users — Google Sheets filter views

One of the main advantages of Google Sheets is the possibility for several people to work with tables simultaneously.

And I bet you know how tweaking filters in Google Sheets can sometimes cause confusion or disrupt your team's workflow. It's like trying to find your own space in a busy café by rearranging furniture. But what if you could carve out your own cozy corner without disturbing anyone else?

That's where Google Sheets filter views come in! They're like your own private booth, letting you customize your view without bothering anyone else, and save it for later use even! Different collaborators can create their views in the same spreadsheet without interfering with each other.

So how do you filter without affecting other users exactly?

Option 1. Creating new filter views in Google Sheets

To create a Google Sheets filter view, click Data > Filter views > Create new filter view. Create filter view in spreadsheets.

Then filter your data by any required criteria and hit Save view: Filter your Google Sheets while the filter view is created and save for later use.

Name your filter view to easily recognize it among other views and click Save: Enter the name for your temporary filter view to save it.

Option 2. Saving existing filter as filter view

If you already have a particular filter set up in your Google Sheets table (as I do), go to Data > Save as filter view: Save data as filter view from the spreadsheet menu.

Then enter its name and click Save: A special pop-up window to enter the name of your future filter view.

An additional bar will appear above the rows with the following:

Your filter view name. You can quickly rename it by clicking on the current name and entering a new one.
The range in use, which you can update in the same way as the name.
Options icon also letting you update the range, rename the view, duplicate or delete it.
Exit icon to save & close any Google Sheets filter view.

You can switch between saved filters for this sheet via the spreadsheet menu: Data > Change view: My saved and enabled filter views in the Change view menu.

In both cases, all changes are saved automatically when you close the view.

But if you no longer need the views, remove them separately or all together via the spreadsheet menu: Data > View options > Delete view / Delete all views in tab: Options to delete the active or all views reside under the Data tab.

Note. All users with editing access to the file (including the spreadsheet owner) can see, use and delete all filter views created in this Google Sheets (yes, even the ones created by other users).

If you can only view the spreadsheet, you'll be able to create and apply filter views for yourself, but nothing will be saved upon closing the file. For that, you need permission to edit the spreadsheet.

That's how you filter Google Sheets by condition and work with filter views 😎 If you have any questions or you'd like to share some thoughts — let's discuss them in the comments section!

You may also be interested in

Table of contents

Products
Windows
Ultimate Suite for Excel Shared Email Templates for Outlook Mail Merge for Outlook Auto BCC for Outlook
Mac Os
Text Toolkit for Excel Shared Email Templates for Outlook Mail Merge for Outlook
Google Workspace
Add-ons for Google Sheets Add-ons for Google Docs
Ultimate Suite
70+ professional tools for Microsoft Excel

This comprehensive set of time-saving tools covers over 300 use cases to help you accomplish any task impeccably without errors or delays.

Merge multiple worksheets

Compare sheets for differences

Remove duplicates

Merge cells in Excel

Calculate dates and times

Combine sheets by column headers

Trim extra spaces

Count characters and words

Convert text to number

See all tools

Works for: Windows
Read more about Ultimate Suite
Shared Email Templates
Reply to routine emails faster than ever

Do not waste your time on composing repetitive emails from scratch in a tedious keystroke-by-keystroke way. Type your response just once, save it as a template and reuse whenever you want.

Quick replies for routine emails

Fully customizable templates

Gorgeous html-based designs

Dynamic templates with fillable fields

Comfortable template sharing

Custom-tailored bulk mailings

Company and team-wide signatures

Easy user and team management

Works for: Windows•macOS•Outlook Online
Read more about Shared Email Templates
Text Toolkit for Excel
35+ handy options to make your text cells perfect

Instead of building formulas or performing intricate multi-step operations, start the add-in and have any text manipulation accomplished with a mouse click.

Trim extra spaces

Convert text to numbers

Count characters and words

Change letter case

Extract text or numbers

Split cells by symbol, string, or mask

Replace unwanted characters

Eliminate leading zeros

Find, extract, replace, and remove strings by means of regexes

Merge columns, rows, or cells into one

Combine rows by duplicates

Works for: macOS•Excel Online
Read more about Text Toolkit
40+ tools to boost your Google Sheets
This smart package will ease many routine operations and solve complex tedious tasks in your spreadsheets.
Read more about Power Tools Try it for free

Add-ons for Google Sheets

Merge Sheets Function by Color Remove Duplicates Compare Sheets Substring Batch Toolkit Easy Mail Merge Advanced Find and Replace See all products

Add-ons for Google Docs

Doc Tools Styles for Google Docs Easy Mail Merge Search & Navigate
Mail Merge
Ideal for newsletters, proposals, and greetings addressed to your personal contacts

Mail Merge is a time-saving approach to organizing your personal email events. It offers:

Customizable and adaptive mail merge templates

Personalized merge fields depending on the recipient or context

Dynamic attachments and images

Mailing lists based on Excel tables

"Send immediately" and "send later" scheduling

Works for: Windows•macOS•Outlook Online
Read more about Mail Merge
Auto BCC for Microsoft Outlook
Automatically BCC your emails using simple rules

Automatically send CC or BCC copies of Outlook emails. Create a BCC rule for all outgoing messages or when specific conditions are met.

Copy your outgoing e-mails automatically

Auto BCC yourself

Key phrases for each condition

Rules for different profiles / accounts

Works for: Windows
Read more about Auto BCC
Downloads
- Current versions
- Archived versions
Services
Documentation
Support
Blog
About us
Purchase

33 comments

Ashwin says:
2024-08-27 at 10:42 am

what is the Custom Formula to exclude multiple text or values in single column
1. Natalia Sharashova (Ablebits Team) says:
  2024-08-27 at 1:00 pm
  
  Hello Ashwin,
  
  Unfortunately, Google Sheets' built-in filter doesn't support complex exclusion logic directly via a custom formula. I'd recommend using the FILTER function or QUERY function instead.
Andrew C says:
2023-11-22 at 3:05 am

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.
1. Natalia Sharashova (Ablebits Team) says:
  2023-11-22 at 10:22 am
  
  Hello Andrew C,
  
  For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) 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.
  1. Andrew C says:
    2023-11-24 at 1:35 am
    
    Got it. I hope this can help. I've opened editor access to support@apps4gs.com
    1. Natalia Sharashova (Ablebits Team) says:
      2023-11-27 at 10:45 am
      
      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!
Fred says:
2023-07-03 at 12:55 am

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. )
1. Natalia Sharashova (Ablebits Team) says:
  2023-07-04 at 12:33 pm
  
  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?
Lycoeur says:
2023-04-27 at 9:21 pm

Hello,
Thanks for the post !
Useful!
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 ?
=$i3=$E$1
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.
1. Natalia Sharashova (Ablebits Team) says:
  2023-04-28 at 3:03 pm
  
  Thank you for your feedback, Lycoeur!
  
  For me to understand your task better, please share an editable copy of your sheet with me: support@apps4gs.com. 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.
Biswadeep says:
2023-03-31 at 1:52 pm

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.
1. Natalia Sharashova (Ablebits Team) says:
  2023-04-04 at 12:12 pm
  
  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.
Dech says:
2022-11-21 at 11:21 am

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
https://docs.google.com/spreadsheets/d/1EQ83uSv0n5o3HtHcPTeIvpPCzfIYIMniL3XvhWtHwgs/edit#gid=145226926
1. Natalia Sharashova (Ablebits Team) says:
  2022-11-22 at 9:41 am
  
  Hello Dech,
  
  We're always ready to help you, but we do not cover the programming area (script-related questions).
  
  You may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links:
  https://developers.google.com/apps-script/overview
  
  I hope you’ll find this information helpful. Sorry I can't assist you better.
  1. Dech says:
    2022-12-08 at 12:29 am
    
    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: https://developers.google.com/apps-script/reference/spreadsheet/filter-criteria-builder
    
    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()
    .whenTextContains(condition)
    .build();
    filter.setColumnFilterCriteria(2, criteria);
    }
    1. Natalia Sharashova (Ablebits Team) says:
      2022-12-09 at 3:10 pm
      
      Hi Dech,
      
      Big thanks for sharing your solution on our blog! I believe it'll come in handy for other users with similar tasks :)
Shuvo says:
2022-11-10 at 8:16 pm

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.

https://docs.google.com/spreadsheets/d/1h5PW52PoMUxXtrqEmfXSCWu_OKXVO8IwUbHqcqSPRzs/edit?usp=share_link

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.

Thanks

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.

Thanks
1. Natalia Sharashova (Ablebits Team) says:
  2022-11-11 at 12:00 pm
  
  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).
Eliezer Emanuel Veras Vargas says:
2021-09-08 at 12:53 pm

Thanks so much!
JC Dill says:
2021-04-30 at 5:15 pm

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?
1. Natalia Sharashova (Ablebits Team) says:
  2021-05-03 at 9:02 am
  
  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.
Max says:
2019-07-17 at 4:17 pm

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.
1. Natalia Sharashova (Ablebits Team) says:
  2019-08-27 at 7:35 am
  
  Hello Max,
  
  Please consider sharing your spreadsheet with us as well - support@4-bits.com. 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.
Tom says:
2019-06-11 at 2:01 pm

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?
1. Natalia Sharashova (Ablebits Team) says:
  2019-06-12 at 12:32 pm
  
  Hi Tom,
  
  Would you mind sharing a sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get? We'd look into it.
  Note. We keep support@4-bits.com 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.
John says:
2019-05-03 at 2:44 pm

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,"\?")

Thanks!
1. Natalia Sharashova (Ablebits Team) says:
  2019-05-08 at 9:40 am
  
  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.
Chris Beall says:
2019-03-18 at 2:07 pm

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(.
1. Natalia Sharashova (Ablebits Team) says:
  2019-05-09 at 8:21 am
  
  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.
Greg says:
2019-01-04 at 12:01 pm

Thank you. Your article helped me. Have a nice day and Happy New 2019 Year! :)
Howard E Maxwell says:
2018-09-21 at 9:09 pm

the below link is shared publicly

https://docs.google.com/spreadsheets/d/1hWGAaDTnR_08OcB950dPczHpjvuHyVpohghOQD8QTW4/edit?usp=sharing

If someone could help me come up with a filter that shows any occurrence of the word Blue in either Column B OR Column C

Thank You in advance.
Harinder Singh says:
2018-07-24 at 9:47 am

pehn di lann, assi khotte haan, assi kanjar haan.
James Mapes says:
2018-05-18 at 1:18 am

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