How to Filter By Color in Google Sheets (Using a Formula)

There are some times I wish Google Sheets had some more functionalities like MS Excel.

One such functionality that isn’t available in Google Sheets is the ability to filter by color.

For example, suppose I have a dataset as shown below and I want to filter all the rows where the record has been colored in a specific color.

Unfortunately, there is no direct way to filter by color in Google Sheets.

But there are a few indirect ways

In this tutorial, I will show you a couple of methods you can use to filter rows based on the color as the criteria in Google Sheets.

Using Google Apps Script to Filter by Color

Suppose you have a dataset as shown below and you want to filter all the rows where the cells have been colored.

Filter by Color in Google Sheets - Dataset

For Google Sheets to filter a dataset, it needs to be able to use criteria in the cells. By using this criterion, it filters all the records that match the criteria.

And since the color is not a value by itself, we can not filter based on it by default.

By using a Google Apps Script, you can convert the color into a HEX code (which is a code assigned to each color).

Once you have the HEX code, you can now filter the data based on it.

Below is the script that will create a function in your Google Sheets document that you can use to get the HEX code of the color that has been applied to the cells.

function GetCellColorCode(input) 
{ 
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var cell = ss.getRange(input); 
var result = cell.getBackground(); 
return result 
}

The above function takes the range as input and returns the background color hex code of the input cell/range.

Once you have added this code to the Google Apps Script Editor, you will be able to use the function ‘GetCellColorCode’ to get the HEX code of the cell color in a separate cell.

How to add the code to the Google Script Editor is covered later in this section.

Let me first show you how this formula works.

Enter the following formula in the adjacent column of the dataset

=GetCellColorCode("A"&ROW())

Formula to Filter Cells by Color in Google Sheets

The above formula uses the function we created (GetCellColorCode) and inputs the range such as A2 or A3 or A4 – depending on the cell.

Since I didn’t want this to be manual, I have used “A”&ROW() – which returns the cell address based on the row in which the formula is used. In row 2, this would return A2, in row 3, it would return A3, and so on.

Once you enter this formula for all the records, you will get a result as shown below:

The cells that have no color return the hex code #ffffff and the cells that have a color return the hex code of that color.

Now that you have the hex codes in a separate column, you can easily filter by color by actually filtering by hex code value.

Below are the steps to filter all the rows where the color is yellow in the above dataset:

  1. Select any cell in the dataset
  2. Click the Data tabClick the data tab in the menu
  3. In the options that appear, click on ‘Create a Filter’. This will add filters to all the header cells in the dataset (a downward pointing arrow icon will appear at the right of each header cell).Click on Create filter
  4. Click the Filter icon for the ‘Hex Code’ columnClick the filter icon of the Hex Code column header
  5. In the filter options that appear in the box, deselect all color hex codes and select the one based on which you want to filter the dataset.Select the color hex code based on which you want to filter in Google Sheets
  6. Click Ok.

This will filter the rows based on the selected color hex code (as shown below).

Filtered rows based on cell color in Google Sheets

A few things to know about using the custom formula created using the script:

  1. This function is only available to the Google Sheet document in which you have added the script. It will not be available in any existing or new Google Sheets document. If you want it to use in other documents, you need to add it there as well.
  2. This formula is not dynamic. This means that if you change the color of any cell, the formula would not update automatically. Sometimes, it doesn’t even update when you refresh the Sheet. The best way would be to use the formula in a new column and delete the existing one.

How to Add the Script to Google Script Editor

Below are the steps to add the script to filter by color in the Google Sheets script editor:

  1. Copy the above script code
  2. Click on Tool tabClick the tools tab
  3. Click on Script EditorScript Editor in Tools tab in Google Sheets
  4. In the Script editor that opens, paste the code in the Code.gs window (you can delete any pre-existing code there if you don’t want it)Copy and Paste the code inot Google Script Editor
  5. Click on the Save button in the toolbarClick on the Save cide icon in the toolbar
  6. Close the Script editor.

The above steps add the code to create a function to filter by color to that specific Google Sheets document.

Now you can use the function that you have created in any of the worksheets in the Google Sheets document.

Note: When you create a custom function, Google Sheets does not show you the function name in worksheets when you type the first few alphabets of the function name. You need to know the exact name of the function and the inputs it takes to make it work.

Using an Add-on to Filter By Color in Google Sheets

One great thing about Google Sheets is that it has tons of amazing add-ons available for use. All you need to do is search for one and start using it. And it hardly takes more than a few clicks.

So if you want to filter by color in Google Sheets (something which can’t be done natively by Google Sheets), you can easily get this done with a simple add-on.

The add-on that you can use is called the Sort by Color.

It doesn’t directly allow you to filter the rows, but instead, sort these. Once you have all the sorted colored rows, you can easily work on these and if you need, even filter/hide these.

Below are the steps to add this add-on in your Google Sheets document:

  1. Open the Google Sheets document in which you want to change the case of text
  2. Click the ‘Add-ons’ tabclick on Add-ons
  3. Click on ‘Get add-ons’Click on Get Add-ons
  4. In the Add-ons dialog box that opens, search for ‘Filter By Color’ in the field in the top-rightType Filter by Color in the Get Addon search field
  5. In the list of add-ons that are shown, click on the Blue buttons for the ‘Sheetgo’ add-onClick to add the Sheetgo addon
  6. In the dialog box that appears, it may ask you to confirm your account by logging in to your Gmail. Enter the credentials and click on the blue ‘Allow’ button.Click on Allow button to add the add-on to your Google Sheets

Once you have done all the above steps, it will add the add-on to your Google Sheets document.

Now, you can access the options to sort the data using this add-on.

Below is a video that shows how to use the Filter by Color functionality of this add-on.

Note: While this add-on is amazing, I personally find the script methods easier as it’s less complicated. The add-on, however, allows you to filter by color and then export the results in some other Google Sheets document as well. So if that’s what you want, give this add-on a try,

You may also like the following Google Sheets tutorials: