Can You Filter By Color In Google Sheets

At that place are some times I wish Google Sheets had some more functionalities like MS Excel.

One such functionality that isn't bachelor in Google Sheets is the power to filter by color.

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

Unfortunately, at that place is no direct way to filter past color in Google Sheets.

But there are a few indirect ways

In this tutorial, I will show y'all a couple of methods you tin 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 take been colored.

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 friction match the criteria.

And since the color is not a value by itself, we tin not filter based on information technology by default.

Past using a Google Apps Script, y'all tin convert the color into a HEX code (which is a code assigned to each color).

Once you take the HEX code, you can now filter the information based on it.

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

role GetCellColorCode(input)  {  var ss = SpreadsheetApp.getActiveSpreadsheet();  var jail cell = ss.getRange(input);  var upshot = cell.getBackground();  return event  }

The in a higher place function takes the range as input and returns the background color hex code of the input prison cell/range.

One time yous have added this code to the Google Apps Script Editor, you volition be able to use the function 'GetCellColorCode' to go the HEX code of the cell color in a split cell.

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

Permit me first testify you how this formula works.

Enter the following formula in the next cavalcade of the dataset

          =          GetCellColorCode          (          "A"          &          ROW          (          )          )

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

Since I didn't want this to exist transmission, I have used "A" & ROW ( ) – which returns the prison 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.

One time you enter this formula for all the records, you will get a consequence as shown beneath:

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

Now that you have the hex codes in a separate column, y'all can easily filter by color past actually filtering past hex lawmaking value.

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

Select any cell in the dataset
Click the Information tab
In the options that appear, click on 'Create a Filter'. This will add filters to all the header cells in the dataset (a down pointing pointer icon will appear at the right of each header cell).
Click the Filter icon for the 'Hex Code' cavalcade
In the filter options that appear in the box, deselect all color hex codes and select the 1 based on which you lot want to filter the dataset.
Click Ok.

This volition filter the rows based on the selected colour hex code (as shown below).

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

This role is only available to the Google Canvas document in which yous accept added the script. It volition not be available in any existing or new Google Sheets document. If you want information technology to use in other documents, y'all need to add it at that place besides.
This formula is not dynamic. This ways that if yous change the colour of any cell, the formula would not update automatically. Sometimes, it doesn't even update when you refresh the Sheet. The best manner 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 colour in the Google Sheets script editor:

Copy the above script lawmaking
Click on Tool tab
Click on Script Editor
In the Script editor that opens, paste the code in the Code.gs window (you can delete any pre-existing code at that place if you don't want it)
Click on the Relieve button in the toolbar
Shut the Script editor.

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

Now you can use the function that y'all have created in whatever of the worksheets in the Google Sheets document.

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

Using an Improver to Filter By Color in Google Sheets

I great thing about Google Sheets is that it has tons of amazing add-ons available for use. All you demand to do is search for ane and kickoff using it. And it inappreciably takes more than a few clicks.

Then if you want to filter by color in Google Sheets (something which can't be washed natively past Google Sheets), you can easily get this done with a elementary improver.

The addition that you can apply is called the Sort past Color.

Also read: How to sort by color in Google Sheets

Information technology doesn't directly allow you to filter the rows, but instead, sort these. One time you have all the sorted colored rows, you can easily work on these and if y'all need, even filter/hide these.

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

Open the Google Sheets document in which you want to change the case of text
Click the 'Add-ons' tab
Click on 'Get add together-ons'
In the Add-ons dialog box that opens, search for 'Filter Past Color' in the field in the top-right
In the list of add-ons that are shown, click on the Blue buttons for the 'Sheetgo' addition
In the dialog box that appears, information technology may ask you to confirm your account by logging in to your Gmail. Enter the credentials and click on the blue 'Permit' button.

Once you have done all the in a higher place steps, it will add the add together-on to your Google Sheets document.

Now, y'all tin access the options to sort the data using this add together-on.

Below is a video that shows how to use the Filter past Color functionality of this addition.

Note: While this add-on is astonishing, I personally observe the script methods easier as information technology'southward less complicated. The add-on, however, allows you to filter by color and then export the results in some other Google Sheets document also. And so if that'southward what you desire, give this add-on a try,

Y'all may also similar the following Google Sheets tutorials:

How to Delete Empty Rows in Google Sheets
How to Insert CheckBox (Tick Box) in Google Sheets
How to Transpose Data in Google Sheets
Google Apps Script for Google Sheets – Easy Beginner'southward Guide
How to Color Alternate Rows in Google Sheets
How to Hide Columns in Google Sheets
How to Count Cells with Specific Text in Google Sheets
FILTER Office in Google Sheets