How to Automatically Change Cell Color in Google Sheets

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:

Color Cells Start Sheets

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.

Sum of leaf color cellsSum of leaf color cells

Next, I selected the sum total cell, clicked “Format”, then clicked “Conditional Formatting”.

Sheets Color Cells ConditionalSheets Color Cells Conditional

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.

Color Cells Red Cell SheetsColor Cells Red Cell Sheets

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

Color Cells Red Cell Complete SheetsColor Cells Red Cell Complete Sheets

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

Color Cells Another SheetsColor Cells Another Sheets

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.

Color Cells Green Cell SheetsColor Cells Green Cell Sheets

I clicked Done and the cell now shows the rules.

Sheet color cell rulesSheet color cell rules

Now to test it. If I set up the spreadsheet with data equal to less than 10 miles, the cell stays red.

Color Cells Final Red SheetsColor Cells Final Red Sheets

However, if I achieved my goal. Not only did the cell turn green, but the text was automatically made bold.

Leaves Color Cells Final greenLeaves Color Cells Final green

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.

Related:

Simon batt
Simon batt

Simon Batt is a computer science graduate with a passion for cybersecurity.

Is this article useful?

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window, document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘400239050508508’);
fbq(‘track’, ‘PageView’);

Leave a Reply