Filter Data by Color in Google Sheets 🔎 | 2 min easy guide

In this article, we will have a look at how to filter data by color in Google Sheets.

Suppose that we have a sample table that has some rows colored. Let’s say they represent the target entries. We want to filter all the rows by color. By default, if we want to filter data in Google Sheets, we can do it on the basis of the data filled but not on the basis of the background color. This option is provided in Microsoft excel directly but not in Google Sheets. 

To filter data by color in Google Sheets we have to make use of the Google Apps Script. This feature allows us to implement niche features that are not available by default. 

In the following part of the article we’ll learn how to filter data by color in Google Sheets using Google Apps script.

How to filter data by color in Google Sheets using Google Apps Script

Every color has a hex code associated with it. Using this hex code we can identify which two or more cells have the same colors. 

function GetCellColorCode(input) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var currCell = spreadsheet.getRange(input);
var hexCode = currCell.getBackground();
return hexCode;
}

This code allows us to fetch the background color of a cell. Let’s see how to add this code to Google Apps Script Editor. 

Add custom function using Apps Script Editor 

Using App Script Editor can be overwhelming, especially if you’re not used to writing codes but it is actually pretty simple. Follow the given steps to add a custom function.

• Go to Extensions>App Script
• Name the Script “CellColor”
• A new window will open up. Go into Code.gs and paste the code written above. 
• Click on Save.

• Click on Deploy. You will further be asked for some input related to the permissions. Give suitable permissions and complete the form.
• Quit the Google Apps Script Editor.

A custom function will be created by the name of the function that you wrote. In the example, a function by the name of “GetCellColorCode” is created. In the same way, we can create any function that you want. 

How to use the Custom Function to filter data by color in Google Sheets

The way it works is that we allocate an additional column to store the hex codes of the entries. By sorting using the hex code we can finally get a table that is sorted according to the table. 

You can use the custom function as you use other functions. The syntax for the same is as follows.

=GetCellColorCode("A"&ROW())

Follow the given steps to sort the table according to their color.

• Allocate an additional column for the hex codes of the color.

• Fetch the hex codes using the custom function.

• Go to Data>Create a Filter. After selecting the options, multiple filter symbols will pop up in the column header. Click on the filter symbol above the color column. 
• You’ll be presented with a dropdown that allows you to have multiple options. Therein choose the color which you want to display and click OK.

• The data will be filtered and only those values that match the color code specified will be displayed in the table. The table with the filtered data is shown below.

Conclusion

We have learned how to filter data by color in Google Sheets using Google Apps Script Editor and filtering the cells by their color in Google Sheets. There is a lot more you can do with this. You can also sort, count and perform various other operations using this logic which is not available by default in Google Sheets.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Delete Empty rows in Google Sheets: In this article, we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.