Sometimes using color-changing cells in a worksheet can be a handy visual aid for quickly evaluating data. If you want this feature on Google Sheets, it is very easy to configure it to have a cell change color based on the data.
How to change cell color in Google Sheets
For this example, let’s create a spreadsheet to record the number of miles I have driven in a week. I’m trying to get in shape, so I created the following spreadsheet to keep track of my runs:
Ideally, I would like to run 10 miles every week and then log every week to see which weeks I did well and in which I didn’t hit my goal. To make it easier for you to see my performance each week, I want the “Total” cell to turn red when I have run less than 10 miles in the week. As soon as I run 10 miles or more, I want it to turn green.
First of all: I set up a
SUM function that adds the mileage of the week.
Next, I selected the sum total cell, clicked “Format”, then clicked “Conditional Formatting”.
When you open this window, you will find many options. First of all I want to configure it so that when I run less 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 the one labeled “Value or Formula” I entered “10”. I clicked on 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 less than 10 miles.
Now that the rule is set up, I clicked “Done” at the bottom. In the rules menu, I clicked on “Add another rule”.
This time I entered the conditions for the cell to turn green – that’s when I ran 10 miles or more. 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 have configured it to make the text bold to really show my success.
The image below shows what this result looks like.
I clicked Done and the cell now shows the rules.
Now to test it. If I set up the spreadsheet with data equal to less than 10 miles, the cell stays red.
However, if I achieved my goal. Not only did the cell turn green, but the text was automatically made bold.
Now for the hard part; really race!
Now that you know how to make an automatic color change cell, the next thing to do is have it automatically convert currenices or translate any language in Google Sheets. Don’t forget to check out these add-ons to make Google Sheets better.
Is this article useful?