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

Filter Data by Color in Google Sheets 🔎 | 2 min easy guide
Reading Time: 4 minutes

In this article, we would have a look at how to Filter Data by Color in Google Sheets.

Filter Data by Color in Google Sheets
Filter data by color in Google Sheets

Let’s say you have a sample table that has some rows colored. Let’s say they represent the target entries. You want to filter all the rows by color. By default, if you want to filter data in Google Sheets, you 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 you have to make use of the Google Apps Script. This feature allows us to implement niche functions that are not available by default. 

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 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 you 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 pretty simple. Follow the given steps to add a custom function.

  • Go to Extensions>App Script
  • Name your Script “CellColor”
  • A new window will open up. Go into Code.gs script and paste the code written above. 
  • Click on Save
Custom function using Apps Script editor
Figure 1: Custom Function using Apps Script Editor
  • 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.

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

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

The way it would work is that we would allocate an additional columns to store the hex codes of the entries. By sorting using the hex code we would 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 would be as follows.

=GetCellColorCode(“A”&ROW())

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

  • Allocate an additional column for the hex codes of the color.
  • Fetch the Hex Codes using the custom function.
Adding color codes
Figure 2: Adding Color Codes
  • Go to Data>Create a Filter. After selecting the options, multiple filter symbols will pop up on your column header. Click on the filter symbol above the color column. 
  • You’ll be presented with a dropdown that will allow you to have multiple options. Therein choose the color which you want to display and click “OK”.
Filter data by color in Google Sheets
Figure 3: Filter Data by Color in Google Sheets
  • Your table would then finally look like this.
Filterd data
Figure 4: Filtered data

Conclusion

Hence we saw how to filter data by color in Google Sheets using Google Apps Script Editor and filtering data. There is much 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.

13 responses to “Filter Data by Color in Google Sheets 🔎 | 2 min easy guide”

Leave a Reply