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!

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.

Related:

Simon Batt Simon Batt

Simon Batt is a Computer Science graduate with a passion for cybersecurity.

Leave a Comment

Yeah! You've decided to leave a comment. That's fantastic! Check out our comment policy here. Let's have a personal and meaningful conversation.