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!
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.