How To Filter By Color in Google Sheets

Google rolled out its first test version Sheets all the way back in 2006, and quickly expanded the test version into the functional version that many people use today. Spreadsheet users like Sheets because it is a versatile tool with a quick learning curve and many features.

The reality is that every software program is a balancing act between many factors – the difficulty of the learning curve, the feature set, the reliability of the program, the cost of the program, and so forth.

No program is perfect; all of them have to make tradeoffs between areas of performance. So while Google Sheets has a very low learning curve and the ideal price (free!), this is balanced out by its somewhat limited feature set. Sheets is almost as feature rich as Excel though it’s more of a pain to do more advanced things like pivot tables with Sheets compared to Excel and Sheets doesn’t have quite as many features.

For most users that’s never a problem… until the moment comes when you really need a feature that Sheets doesn’t have built-in.

Fortunately, there are ways around the limitations of Sheets. One feature many users have expressed a desire for is the ability to filter by color. In this article, I will show you a couple of different ways to filter by color in Google Sheets.

Is there a way to filter by color in Google Sheets?

Yes, in fact, there are at least a couple of different ways to accomplish to filter by color in Sheets.

Sheets has a powerful conditional formatting tool, but it is based on the data stored in the cells, not on the attributes of the cell such as color. For a fuller look at conditional formatting, check out our guide to conditional formatting in Sheets.

So it would seem that there’s no way to filter by cell color in Sheets, but there are a couple of different ways to do it.

One way is to use a script to identify cells by their color and then store the hex value of that color in another cell; you can then filter based on THAT cell’s contents and have the result look the same as though you were filtering by color.

The other approach uses Sheets add-ons; there are a couple of good ones that contain this functionality. I’ll show you how to use both of these methods.

How do I filter in Google Sheets?

This approach is for the more technical oriented user or Google Apps power user as it uses Google App Script with the Google Script Editor to create a function that you can call from Google Sheets.

In this example scenario, you have a Sheet that has all the outstanding tickets (a way of tracking customer support issues), color-coded by priority: low, medium, high, and urgent. Sorting by color might be useful in this scenario and you can apply the idea to any situation where you might want to sort a sheet by color.

The first method is using Google Apps Scripts, which technical users and advanced Google Apps users might find useful because it gives you a lot of flexibility and power to extend the capabilities of Google Sheets.

Most Sheets users will want to skip ahead to an “Add on,” which is much easier and quicker to implement. Add ons are often the easiest way to get things done that are not core feature of Google Sheets.

The Google Apps Script Method

Let’s start by copying and pasting the script itself into the Google Apps Script Editor.

1. First, Open Google Sheets on the sheet you want to filter.
2. Next, Select Script Editor from the Tools dropdown menu.
3. Copy and paste the following code:


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


You can now call the function you created in Google Apps Script from cells in your sheet.

Call the Function from Your Sheet

Now that you have the script ready to go, you can call the function in the script from the Sheet. First, though, create a special column called “colors” or something like that.

To retrieve these hex codes, use the following function call in each cell that has a row that has a cell color coded which is C2 in this case:

=GetCellColorCode("B"&ROW())

Note that the parameters reference B, which is the column that’s color-coded from and the row. In this example, it’s column B but, of course, adjust the column number to fit your situation.

Then you can simply copy the function to each of the other cells in that column. The result will be that you’ll have a column of hex codes one for each color you chose for color-coding.

Create the Filter

Now that you have the script and the function call in the Sheet completed, you’ll create a filter so you can filter by one or more colors:

1. Select the column header of the colors column.
2. From the Data pull-down menu, select Create a Filter
3. Then clear the hex codes for the colors you want to use in your filter.
4. Finally, select one or more colors (hex codes) you do want to see.

This would return all rows where the priority was urgent (orange). Of course, you could select any of the colors or more than one color depending on what you wanted to see in the Sheet.

For power users, using a Google Apps Script solution gives you a lot of flexibility and power to extend the capabilities of Google Sheets. For most users, it’s easier to just use an add-on.

Using the Sort Range Plus add-on

I’ve found a working sort-by-color add-on at the Google Sheets add-on page. The benefit of this add-on is that it can sort by cell color or by text color, but the downside is that you cannot change the order or customize it.

1. Visit the Sort Range Plus page and select the blue + Free button in the top right to install it.
2. Select the cells in your sheet that you want to sort by color.
3. Select Add-ons and Sort Range Plus.
4. Select Sort Range.
5. Use the add-on to sort.

In the settings for the app, you can change the sort order to one of three preset types which gives you a bit of customization ability.

Using the ColorArranger add-on

Another useful add-on for Sheets is Color Arranger. ColorArranger lets you sort Sheets by using a visual representation of the background color of cells.

The add-on’s main features include sorting from a large number of colors, automatic detection of data and sort columns, a “nearest match” option that puts similar colors close together, and fine-tuning of the sort order by dragging colors on the palette.

Using ColorArranger is simple. Just load your sheet, assign the colors to the data column, and select Add-Ons->Color Arranger->Sort by color. A dialog box will appear with your data column autodetected. Hat tip to TechJunkie reader Sekhar for letting us know about this add-on!

We have quite a few Google Sheets articles on TechJunkie meant to help you build your skills, including these how-to articles:

• Guide to Sheets functions
• How to Hide Columns in Google Sheets
• How To Add a Chart and Edit the Legend in Google Sheets
• Using Pivot Tables in Sheets!

I hope you’ve found this a helpful guide to using cell color as a tool for sorting and filtering in Google Sheets. If you have your own suggestions for doing this, please feel free to share them with us in the comments section below!