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.

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

1) Select the range of data.

Sheets11 - 1

2) Right click and choose “Conditional formatting”.

Sheets11 - 2

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”.

Sheets11 - 3

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.

Sheets11 - 4

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).

Sheets11 - 5

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.

Sheets11 - 6

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

Sheets11 - 7


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”.

Sheets11 - 8

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”.

Sheets11 - 9

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”.

Sheets11 - 10  Sheets11 - 11

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.

Sheets11 - 12

  Sheets11 - 13Sheets11 - 14

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

Sheets11 - 15

Watch the video explanation on YouTube:

Conditional Formatting – Part 1


Conditions

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?

 Sheets11 - 16

Watch my video explanation of these on YouTube:

Conditional Formatting – Part 2

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.

Sheets11 - 22

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

Sheets11 - 18

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

Sheets11 - 19

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

=$d2<0.5

Sheets11 - 20

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

Sheets11 - 21

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 (see my post on absolute references) 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.

Sheets11 - 23

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!

For an extra use of conditional formatting and custom formulas, see my post on using the functions ISEMAIL, ISNUMBER, ISURL and NOT, to check that emails addresses, etc are ok.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


6 thoughts on “Google Sheets – Conditional Formatting & custom formulas

  1. Hello,

    This is truly a nice introduction to Conditional Formatting for Google Sheets. I wish to share a sheet I have created where I have created or acquired different tests for ConFor and mostly using “Custom formulas”.

    https://docs.google.com/spreadsheets/d/1ScDqtZvhsmf2iByAuxHBu0-ESpX_TE5pdtBvAxOJ99E/edit?usp=sharing

    I’m sure your readers can use this as a resource on how to use ConFor for different ways to highlight data.

    James/mreighties🙂

    • Hi James – Yes, this is a great sheet. I think this is where I learnt some of my conditional formatting tricks! I’ll add a link on my blog plus post it in the Google Plus groups. Thanks!

  2. I wonder if you could assist me. I have a column for checking off “X” if a task is done. I have another column containing the due date for the task. I would like the due date to turn Red if (and only if) the date is past AND the done column is empty (no checked off). I’m not sure how to express that as a “custom formula”. Can you help or point me in the right direction? lance@icohere.com — THANK YOU!!

  3. Hi Lance – Good question and this is a great example to share wth anyone reading this post.

    It’s an expansion of the one above: =and($B1<today(),$A1="") This is where the dates are in the B column, and the X's are in the A column. So you'll need to adjust the columns to your situation.

    Basically, it's checking that the date in B1 is before today's date AND the cell in A1 (task done or not) is empty. If it is, it'll change the colour.

    In a few weeks time, I'm going to be posting some more advanced posts using Sheets, in particular, ones such as further uses of custom formula, so watch this space.🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s