How To Filter By Color in Google Sheets

A Techjunkie reader asked last week how to filter by color in Google Sheets. As I have written lots of guides on the application, the question was forwarded on to me. The question was ‘If I need to filter cells purely by color and not by content, how can I do it?’ The short answer is unfortunately, you cannot do it. The longer answer is, that you still cannot do it, not properly anyway.

Google has not yet added the ability to sort 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. Fortunately, 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.

How to filter by color in Google Sheets-2

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 a script 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 isn’t elegant by any stretch of the imagination but it sort of 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();

}’.

  1. Go back 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 image, the script correctly identifies cell colors as white until cell A5 which is identifies 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 sort of filtered by color within Google Sheets. As mentioned, it is quite inelegant but at this time there is no built-in way of doing it.

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 that I know of. Some users have said it works great, while others have said it doesn’t work.

  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 have made quite a few improvements to Sheets since then. However, when I tested it, it worked fine. I colored some cells and applied the add-on. I saw two options, sort by cell color and sort by font color. I chose cell color and the selected column was reordered purely by color. So it does work.

The downside is that you cannot set the order of the sorting. In my test, it looks like it sorts it in order of lightness. This limitation does mean if you use this add-on you will have to format your cells accordingly, which may not work in all situations.

Google Sheets is an awesome app for most things but has some limitations. Sorting options is one of those limitations. While Excel can work wonders either inherently or using VB scripting, you do 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. Although if you have Excel and want to perform advanced actions like this, you may as well use Excel anyway!

Posted by Jamie on March 16, 2017

Leave a Reply

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