4

How To Filter By Color in Google Sheets

Posted by Robert Hayes on March 27, 2019

If you use Google Sheets for your spreadsheet needs, then you know it’s a versatile tool with a quick learning curve and many features. Unfortunately, that quick learning curve (and the price – free!) mean that it doesn’t have every single feature that its more powerful big brother, Excel, can bring to the table. For most users that’s never a problem…until the moment comes when you really need a feature that Sheets just doesn’t have built-in. Fortunately, there are ways around many of the limitations of Google Sheets. In this article, I will show you how to filter your Sheets spreadsheets using cell color as a criterion.

Sheets has a powerful conditional formatting tool, but it is data-reliant; it doesn’t work on the basis of cell attributes, only cell contents. So it would seem that there’s no way to filter by cell color in Sheets, but actually 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 a Sheets add-on. I’ll show you how to do both.

Use a script to filter by color in Google Sheets

This approach is not technically filtering by color, but it gets the same thing done. The principal downside is that you have to add a new data column to your sheet to make it work. However, you can always hide the new column, so it won’t interfere with the visual impact of your sheet. You’ll also need to add a script to the sheet in which you want to do the filtering. Here’s how it works.

 

  1. Open Google Sheets on the sheet you want to filter.
  2. Select Tools and Script Editor.
  3. Select File and New to create a new script.
  4. Type or paste:   ‘function getHex(input) { return SpreadsheetApp.getActiveSpreadsheet().getRange(input).getBackgrounds(); }’. (Don’t include the quotes.)

How to filter by color in Google Sheets-2

Now return to your sheet, select the first cell in your new column, and paste ‘=getHex(“A1:A14”)’ into the formula bar. (Replace ‘A1:A14’ with the range of your actual data cells, of course.) For our sample data, the result looks something like this:

Notice that column B, where I pasted the calls to getHex(), now contains the hexadecimal color code of the corresponding cells in column A. If you don’t know the hexadecimal color codes, don’t worry about it; you don’t need to understand it to be able to use it.

Now we can use Sheets’ built-in conditional formatting to manipulate our data according to its color.

  1. Select Format and Conditional formatting.
  2. Select Add new rule and set the cell range you want to apply it to.
  3. Select Format cells if… and Text contains.
  4. Type a value into the box below that you want to sort by.
  5. Add a Formatting style and select Done.

Once the conditional formatting has been applied, you have filtered by color within Google Sheets. It’s a bit inelegant, but it does the job. You can also hide column B to prevent people asking “what are those funny-looking numbers next to that list of color-coded star names?”

Using the Sort Range Plus add-on

If you’d rather not get involved in the script editor and everything it entails, the other approach is to use an add-on. The add-on basically does the same thing, but without creating a data column to store the hexadecimal values. 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.

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!

Do you have other questions about using Google Sheets? We have lots of great tutorials on getting the most out of your Sheets experience. Among many other things, we’ll teach you how to get a word count in Sheets, how to hide columns in Sheets (especially useful if you use the script method above!), how to use charts in Sheets, and how to use pivot tables in Sheets!

4 thoughts on “How To Filter By Color in Google Sheets”

Ankit Parsurampuria says:
Good one. Works flawlessly for a single cell, but not for a list of cells. For a long list of cells, you need to take help of Excel (see attached pic).
Reply
RabbitRescue says:
Give your colour a value:
A work around is to create columns that can be sorted eg a “Yes” column. If you colour the “Yes” yes cell or row eg pink then you can sort by “Yes” which gives a sort by pink by default.
Reply
Jared says:
The simplest solution would to be create an extra column and in each row of the column, write in the color e.g. Green, Red, Blue, etc. Color each cell with the respective color in the cell if you want. Then you can sort from A-Z and it will be sorted by the name of the color.
Reply
Sekhar says:
New Excel Addon, color arranger has some more features, of choosing the column and color to arrange, but it also has a limitation of cannot reset back to previous filter or previous sort.
Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.