SheetPointers Logo

Lesson 4 – Conditional Formatting Fundamentals (Beautiful Color Coding, Understanding Formatting Equations)

Conditional Formatting lets you easily color code items based on the contents of their cells. For example, you can create tables where all numbers above 5 turn green, and those below turn red. This is a beautiful way to clearly display your data, making it easy for others to understand.

Table of Contents

  • 1. What is conditional Formatting
  • 2. Why is it useful (examples)
  • 3. How to do it
  • 4. More advanced features
  • 5. Small Project

What is conditional formatting?:

What conditional formatting does in a spreadsheet, is make you able to change the colors of cells, based on a condition. For example, in a certain set of cells, you can make it that all numbers greater than 5 turn green, and all numbers less than 5 turn red. The condition does not have to be a number however, you can make cells change color if it contains a certain word, or even if the cell is empty or not. This has a lot of uses in making spreadsheets visually appealing, as well as easier to understand. Before starting, we suggest refreshing yourself on the coordinate system inside Google Sheets here.

Why is it useful?

As previously mentioned, conditional formatting makes things easier to understand, and pop out. Take a look at the example below.

This is one of my personal spreadsheets, listing events that my robotics team has planned to do. Notice in column D, how all the cells which say “Yes” are green, and the ones that say “No” are red. This is all thanks to conditional formatting. I set this spreadsheet up so that if a cell says “Yes” it turns green, and if it says “No” it turns red. 

Notice how due to the colors, the spreadsheet is much easier to understand. At a quick glance we can see which events still need to be completed.

How to do it

As always, let’s begin with a situation. In the photo below, we have a rectangle of numbers in cells, which are either the value “5” or “6”.

Let’s say we wanted to easily spot whether a cell is labeled 5 or 6. If we leave it like so, it is very difficult and confusing to spot. Our task here is make that easier, by turning all the #5 cells red, and the #6 cells green.

Shows the template of what we want to color code and apply conditional formatting to.

Step 1: The first step we take is go to the task bar on the top and select “FORMAT” and select “CONDITIONAL FORMATTING.” This will bring us to the Conditional Formatting screen, where we select what we want to do.

Shows where to select conditional formatting

Step 2: We then have to select the area to which we want to apply the conditional formatting to, in this case, the rectangle of 5’s and 6’s. To do so, clear out whatever is in the “Apply to Range” box, and highlight the area (again in this case, the rectangle of 5’s and 6’s). There are many more advanced ways to specify the area you want to apply the condition to, but for now we will just stick to highlighting. 

Step 3: Now, if you scroll down, you will see “format cells if…”, and a drop-down menu. If you click that menu, it shows you all the different ways you can format cells. As you can see there is everything from “text contains” to “is greater than.” The options are pretty self-explanatory, feel free to explore them on your own time after this lesson.

For our case, we will select the “is equal to” option. If we click that, it gives us a value to enter. Let’s try 5 first, so enter 5 in the value portion. Lastly, we can now decide what we want to do with this cell, under “formatting style.” As said before, we want to turn each 5 red, so under formatting style, we can select the color red, using the paint bucket icon.

Step 4: Let’s take a look at what we just selected. If you notice, you can read it as a sentence. If “the cell is equal to 5, make the cell red.” You have to get in the habit of reading conditional formatting statements like this.

Shows how to read the conditional formatting tool box/rules

At this point, we should have all the cells with the number 5 colored red. Now in order to highlight the number 6 cells green, we have to repeat the same steps but change the formatting statement to say “if the cell is equal to 6, make the cell green.” If you get lost, here is a video displaying those steps.

If you take the time to look at the other format rules, you can see you have so many options of what you can do. There is absolutely everything from if a cell is empty or not, to if the cell contains a desired word. The way they work is quite easy to navigate, if you remember to navigate conditional formatting as reading a sentence. If you can do that, then it becomes easy. If you feel really adventurous, you can even try exploring Conditional Formatting with Formulas. We have linked an article here, from Google’s own forum. However, you may want to learn more about certain formulas, more specifically “If Statements.”

You can also reference Google’s own support article on Conditional Formatting here. If that article does not clarify anything you wish, you can also try your luck with this other article we found.

I hope you enjoyed today’s lesson. Remember to watch our videos going over the lessons if you get lost. I have attached the said video below here, hopefully, it helps! As always, happy spreadsheeting!! 🙂

About Us

SheetPointers was created by Andrew Lenart and Nikhil Radosevich 

Recent Posts

Follow Us

Weekly Tutorial

Sign up for our Newsletter

We’ll send you a few emails a month with cools tips and tricks. We will never send spam. Unsubscribe any time!