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:
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.
Then, I selected the total sum cell, clicked “Format,” and then “Conditional formatting.”
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.
Now my cell turns red when I run fewer than 10 miles.
Now that the rule is set up, I clicked “Done” at the bottom. In the rules menu, I clicked “Add another rule.”
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.
I clicked Done, and the cell now shows the rules.
Now to test it out. If I set up the spreadsheet with data that equals fewer than 10 miles, the cell stays red.
However, if I did meet my goal. Not only did the cell turn green, but the text was bolded automatically.
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.
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.
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.
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.