4

How To Filter By Color in Google Sheets

Posted by Robert Hayes on July 29, 2019

Ever since Google Sheets rolled out in a test version back in 2006, spreadsheet fans have learned to appreciate it as 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 just 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 doesn’t have built-in. Fortunately, there are ways around many of the limitations of Google Sheets. One feature many users have expressed a desire for is the ability to filter by color. 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 based on the data stored in the cells, not on the attributes of the cell such as color. 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.
  6. Right-click on the hexadecimal column and select ‘Hide Column’ to clean up the spreadsheet visually. 

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.

We’ll teach you how to get a word count in Sheets

We’ve got a guide on how to hide columns in Sheets (especially useful if you use the script method above!)

Here’s our walkthrough on how to use charts in Sheets.

Of course we have a guide to using pivot tables in Sheets!

We created a handy tutorial on how to calculate time in Google Sheets.

Here’s our walkthrough of how to delete all the empty rows and cells from a Sheet.

We’ve got a solid guide to selecting alternating rows in Sheets.

Need to turn that spreadsheet on its side? We’ll show you how to convert rows to columns in Sheets.

Here’s a great way to swap two rows 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.