Google Sheets – Conditional Formatting & custom formulas

Sometimes we spend time colouring in our sheets so that the data is more visible, in a way that it can be read and understood really quickly and that the most important points can be ascertained with ease. Wouldn’t it be great if Sheets could colour your data automatically, depending on a set of conditions? Well, of course, it can. One key way is ‘Conditional Formatting’.

Let’s go through an example, to show how easy it is to do and yet how effective it can be.

Conditional Formatting – Basics

Here I have some exam results and I want to highlight those who have passed.

1) Select the range of data.

2) Right click and choose “Conditional formatting”.

3) This opens up the Conditional format rules dialogue box. Under “Apply to range” it shows us the range we just highlighted and the one which we’re going to add the formatting to. Underneath in the “Format cells if…” part, click on “Cell is not empty”.

4) This opens the various conditions. Let’s start with a common one. Click “greater than” as we want to highlight those with an exam mark better than a minimum, i.e. they have passed.

5) A box will appear below to type in the value. Let’s type in 0.6, as I want to know who has more than 60%. (Remember it’s 0.6 not 60 we need to write here as it’s a percentage).

Below this, you have a choice of formats. You can add Bold, Italics, Strikethrough, or you can change the font or cell colour. Probably, the last two are the most common. I’m going to fill the cells with green for those who have passed, so I click on the fill box and the colour palette opens.

Just select the colour you want. Then click “Done” at the bottom of the dialogue box.

As you can see, this colours all those above 60% in green.

---

Setting multiple conditions

You can set more than one rule at a time. So, let’s colour those who have failed in red. The process is very similar to before.

1) Select the range and right-click, then select “Conditional formatting”.

2) In the rules, you’ll see the one you created before, with a summary of what it does. Below it click “Add new rule”.

3) From now on it’s the same process as before, except we’re going to select a different condition. Click “Cell is not empty”, then “Less than or equal to”.

4) Type in “0.6” in the value box that appears, and this time let’s colour it red, by clicking on the fill colour and selecting a shade of red.

Now we have those who failed the exam highlighted in red.

---

Conditions – options

We can set up all sorts of conditions using the list below and as you saw from above, these can be combined in countless ways.

Let’s look at a few of the options Sheets gives us. There are 5 groups:

1. Is the cell empty or not?
2. Text – What does it contain, start with, end with, or does it match an exact word or phrase?
3. Date – Does the date match another? Or is it one before or after the specified date?
4. Numerical – Is the number the greater, less, equal or between some number(s)?
5. Does the cell match a specified formula you’ve set?

Personally, I think the first 4 groups are easy to set up and are similar to the way I described above. The last one however, custom formulas, is very different and can be a little complicated sometimes, so, as this is all about the basics, I’m just going to show one useful example of a formula you can use.

---

Custom formula

Here we have some performance results of some students. I want to highlight those who didn’t achieve the minimum 50%. AND this time I don’t want to just colour in the performance figure, I want to colour in the whole row. This is where a custom formula helps.

1) Select all your data except the Headers.

2) As before, right click and click Conditional Formatting. Click on “Cell is not empty”.

3) At the bottom of the list of conditions, click on “Custom formula is”.

4) Type in the following formula and click “Done”.

=$d2<0.5

You will see that all the rows that contain a result of under 50% are now highlighted. Magic!

So, how did that work? Let’s look at the formula and understand what’s happening.

=$D2<0.5

The equals sign, tells Sheets it’s a formula. Then I tell it that if D2 is less than 0.5 (50%), then fill it green. The dollar sign makes sure that the column D never changes as you move columns, but as the row number doesn’t have a dollar sign it can change as you move rows.

So, let’s look at cell by cell. In cell A2, the formula is still referring to D2, as the dollar sign made sure the column didn’t change, even though we’re in column A, and we are still in row 2. The same goes for cells B3 and C3. So, all of them look at D2 to see if the value is less than 0.5 (50%), which in this case it wasn’t, so it hasn’t changed the colour of any cells.

In A3 we notice that the formula has changed to D3. This is because the dollar sign has prevented the column changing, but this time we are on the row below, and as there is no dollar sign next to the row number, it changes by 1, to row 3. This time the cells in that row are looking at D3 to see if it is less than 50%, which again it isn’t. And so on. D5 was under 50%, so it coloured the row green.

This is more difficult to describe than it really is! Custom formulas give you greater scope to control how you format your data, so you are not just limited to the preset list you are given. Play around with them, they are a wonderful tool!

---