Do you need to filter based on cell color in Google Sheets?

Using color codes to highlight the essential data cells inside Google Sheets is a common practice. This is often used to show status or other important information. For example, if your cell color is red, it means the values in that cell might need attention.

This practice makes your cells pop out, and the critical data is always in front of the eyes of an analyst or the manager.

However, filtering the cells that you have highlighted with color becomes difficult. Thankfully, Google Sheets allow you to do so in several ways.

Throughout this article, you will discover the methods of filtering the colored cells in Google Sheets. Get your copy of the example workbook and follow along!

Filter by Color with Filter Toggles

A default way to filter your data through Google Sheets is using the Filter Toggles.

It is a drop-down where you can select the cells you want to show and hide the ones you don’t wish to show. More interestingly, it also has the option to filter the data by cell color.

To use this method, you must first apply a filter to your data.

1. Select any cell from your data range or select the entire data you want to apply a filter to.
2. Click the Data menu from the Menu bar ribbon.
3. Click on the Create a filter option.

Once you click on Create a filter option, a filter will be applied to your data. You can see an inversed pyramid-like structure applied on the right-hand side of your column headers. This is your Filter Toggle.

💡 Tip: You can also apply the Filter Toggle to your data from the Toolbar. There is a funnel symbol and when you click this, the Filter Toggle will be applied to your entire data range.

4. Click on the Filter Toggle from any of the data columns, and you will see a range of filtering as well as sorting options available.

This article will specifically discuss filtering by color, but there are other options such as sorting your data by color available in the filter toggles menu.

5. Click on the Filter by color dropdown to see filtering data by color options.
6. Click on Fill Color.
7. Select the color on which you want to filter the data. In this example, the data is going to be filtered on the green fill color.

📝 Note: The white color is a default cell color, so even if you filter the data based on it, you will get all those rows that have the fill color as white.

You will now only see those cells with green fill color, as shown in the screenshot above.

Filter by Color with the FILTER Function

There is another way to filter the data by cell color in Google Sheets. You can use the built-in FILTER function. This function takes the entire data as an argument and then returns the specific cells that meet the criteria you mention.

The beautiful thing about using this function is that you don’t change the original data. The function operates in a way that you don’t filter the original data but create a filtered layout in different cells based on your criteria.

If you want to know more about filtering and the FILTER function, check out this article about filtering data in Google Sheets.

Since you want to filter the colored cells out, you need to extract the hex code with a custom function and then use it as a condition to return only the colored cells as output.

First, you need to get the cell color based on which you want to filter the data. The custom GetColor() function allows you to extract the hexadecimal code of the cell color.

=GetColor(A1)

The above formula will get the fill color hex code from cell A1.

📝 Note: Make sure cell A1 is colored with a cell color that your source data has.

=FILTER(Data!A1:D11,GetColor(Data!A1:A11)=B1)

The above formula will return the filtered view of the data where the cell color is green.

Explanation

The first argument for the FILTER function holds the data range on which the filter needs to be applied.

The second argument is a condition based on which the filter should be applied. The GetColor() function only takes one column as an argument, so you must provide all cells from column A of the data as an input.

The condition used to filter the data is when the hex color codes match the value in cell B1.

Finally, the FILTER function then only returns those cells with the matching colors.

📝 Note: The FILTER function is an array function. Whenever you call it, you will get an array of cells that satisfy a specific filtering condition.

Filter by Color with a Helper Column

You can also achieve the same result using a helper column that allows you to filter the data based on the hexadecimal color code of your cells.

=GetColor(A2:A11)

In cell E2 of your demo data, add the above formula to generate hexadecimal color codes for each cell in column A. It should populate the hexadecimal cell color codes for each row.

Now, Use the Filter Toggle and select the color code #00ff00 from column E.

Click on the OK button to apply this filter to the data.

This will filter the entire data based on cell value from column E.

Conclusions

This post showed several ways to filter your data based on colored cells.

The first method was using the Filter Toggle which is a standard method and widely used to filter the data based on cell colors or other conditions.

The FILTER function can also if combined with the GetColor() function, and returns the filtered view of your data for cells with a specific color.

Finally, you can create a helper column using the GetColor() function that returns the hexadecimal color codes for the cells and then uses the Filter Toggle to only show cells with a specific color.

Have you ever needed to filter data based on cell fill color? How did you get it done? Let me know in the comments!