3

How To Filter By Color in Google Sheets

Posted by Jamie on November 27, 2018

A Techjunkie reader asked last week how to filter by color in Google Sheets. I’ve written lots of guides on Google Sheets, and have a good knowledge of all its capabilities—and the short answer is, unfortunately, Google Sheets will not let you filter by color alone. The conditional formatting tool allows a lot of ways to arrange a sheet but it relies on data to do its work.

And the long answer is… you sort of can. There are two workarounds: one uses a script and one uses a Google Sheets addon. Neither are perfect, but they can get the job done.

Use a script to filter by color in Google Sheets

I’m a bit of a novice when it comes to scripts, but someone on the Google Docs Help Forum posted one that allows you to identify cells by their color and then print the hex value of that color. While not strictly filtering by color, you can then add conditional formatting to collect the hex data and sort by that.

It requires adding an additional column of data, but 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();

}’.

How to filter by color in Google Sheets-2

Now return to your sheet, select a cell and paste ‘=getHex(“A1:A6”)’ into the formula bar.

In this example, the formula will read the color of cells A1 to A6 and print their hex value. If you look at the screenshot below, the script correctly identifies cell colors as white until cell A5, which is identified as red. From there, you can use conditional formatting to select any color you like.

  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.

There is a third-party add-on that can apparently help, although reviews are mixed.

How to filter by color in Google Sheets-4

Using the Sort By Color add-on

If you check out the Google Sheets add-on page, there is a plugin called Sort By Color. It sounds like just the thing. The benefit of this add-on is that it can sort by cell color, but the downside is that you cannot change the order or customize it. Some users say it works great, while others are less pleased. When I tested it, it worked fine, so it’s certainly worth a try.

  1. Visit the Sort by Color 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 by Color.
  4. Use the add-on to sort.

This add-on hasn’t been updated since 2015, and Google has made quite a few improvements to Sheets since then, but the add-on still seems to work. In my test, I colored some cells and applied the add-on. I was presented with two options: sort by cell color, and sort by font color. I chose cell color, and the selected column was reordered purely by color.

But as I mentioned, you cannot set the order of the sorting, which seems a rather major flaw. In my test, it appeared to sort the rows in order of lightness. You’d have to format your cells accordingly, which may not work in all situations.

Google Sheets is an awesome app for most things but it still isn’t quite the powerhouse of Microsoft Excel, which can work wonders with its own functionality, or with VB scripting. But you have to pay for the privilege. If you really must filter by color regardless of the data contained within a cell, you may have to consider exporting the sheet into Excel, performing the format, and then importing it back into Google Sheets again. But if you have Excel and want to perform advanced actions like this, you might as well use Excel anyway!

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

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 *