How to Automatically Change Cell Color in Google Sheets

Sheets Color Cells Featured

Sometimes using color-changing cells in a spreadsheet can be a handy visual aid for quickly assessing data. If you’d like this feature on Google Sheets, it’s very easy to set it up so that a cell changes color depending on the data.

How to Change Cell Color in Google Sheets

For this example, let’s make a spreadsheet for recording how many miles I’ve run in a week. I’m trying to get into shape, so I made the following spreadsheet to track how my runs have gone:

Sheets Color Cells Start

Ideally, I’d like to run 10 miles every week, then record each week to see which weeks I did well and in which I didn’t achieve my goal. To make it easier to see how well I did each week, I want the “Total” cell to turn red when I’ve run fewer than 10 miles in the week. As soon as I run 10 miles or over, I want it to turn green.

First things first: I set up a basic SUM function that adds up the week’s mileage.

Sheets Color Cells Sum

Then, I selected the total sum cell, clicked “Format,” and then “Conditional formatting.”

Sheets Color Cells Conditional

When you open this window, you’ll find a lot of options. Firstly, I want to set it up so that when I run fewer than 10 miles, the cell turns red.

I clicked the drop-down menu under “Format cells if … ” and selected “Less than.” In the box under that labeled “Value or formula,” I entered “10.” I clicked the box under “Formatting style” and the one with a red background. The end result is shown in the following image.

Sheets Color Cells Red Cell

Now my cell turns red when I run fewer than 10 miles.

Sheets Color Cells Red Cell Complete

Now that the rule is set up, I clicked “Done” at the bottom. In the rules menu, I clicked “Add another rule.”

Sheets Color Cells Another

This time, I entered the conditions for turning the cell green – that’s when I’ve run 10 or more miles. For the formula, I set it to “Greater than or equal to” and the number to “10.” By default, the cell is set to green, so I don’t need to change that; however, I did set it to make the text bold to really show off my success.

The image below shows what that result looks like.

Sheets Color Cells Green Cell

I clicked Done, and the cell now shows the rules.

Sheets Color Cells Rules

Now to test it out. If I set up the spreadsheet with data that equals fewer than 10 miles, the cell stays red.

Sheets Color Cells Final Red

However, if I did meet my goal. Not only did the cell turn green, but the text was bolded automatically.

Sheets Color Cells Final Green

Now for the hard part; actually doing the running!

Set a Color Scale for a Whole Column

As you may have guessed, you can conditionally format an entire column just like you can a cell by clicking the column (instead of a cell), then clicking “Format -> Conditional formatting” and going through the same process we went through above.

Auto Fill Colour Cell Google Sheets Scale X

But there’s a whole extra layer under “Conditional format rules”. If you want all the data in a column to be colored on a scale (so, for example, going from clear through light green, slightly darker green, dark green and very dark green), you can click “Color scale” under Conditional format rules.

Auto Fill Colour Cell Google Sheets Scale 2

Here, you can set a minpoint, midpoint and maxpoint, based on the numbers in the column, so that each cell will be assigned a color on the scale based on its value.

Auto Fill Colour Cell Google Sheets Scale Dropdown

You can enter numbers into these ‘point’ boxes based on what you know of the data in the column, but Google Sheets automatically calculates what the minpoint, midpoint and maxpoint in the column are, and will automatically fill those boxes once you’ve selected a unit from the dropdown menu (Number, Percent or Percentile).

Now that you know how to make an automatic color-changing cell, the next thing to do is to get it to automatically convert currenices or translate any language in Google Sheets. Don’t forget to check out these addons to make Google Sheets better.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox