How to get conditional formatting in Google Sheets

Google Sheets with conditional colors (red for days with <60,000 vaccines administered)

Imagine a situation where you want to have conditional formatting in a graph in google sheets.

Examples of where you’d want to use it:
- Showing profit/loss with different colours
- Scatter plot with each group of points in a different point shape or colour

In Tableau, you’d just drag the pill over to color and be done with it.In google sheets, you need a hack or workaround as described here:

Here is an example sheet with daily vaccination figures in Kerala, India. In my example, I want to highlight in red the days in which daily vaccinations are below a certain threshold number.

Here is a sample of the original data:

+-----------+-----+--------+
|   Date    | Day | Number |
+-----------+-----+--------+
| 3/18/2021 | Thu | 148188 |
| 3/19/2021 | Fri | 143698 |
| 3/20/2021 | Sat | 116747 |
| 3/21/2021 | Sun |   5441 |
| 3/22/2021 | Mon | 146595 |
| 3/23/2021 | Tue | 137504 |
| 3/24/2021 | Wed |  63294 |
| 3/25/2021 | Thu | 158627 |
| 3/26/2021 | Fri | 130954 |
| 3/27/2021 | Sat | 116610 |
+-----------+-----+--------+
Links: Data Source , Govt Dashboard

This time series gives us this very simple chart:

Single colour bar graph with7 day moving average line

Here’s how to conditionally colour it:

Step 1: duplicate data into two columns based on your condition.

=IF(condition, value if true, value if false)

In the vaccination sheet, the condition I wanted was if the daily vaccination is >60000

=IF(C1 > 60000, C1, 0) for the first column of numbers
=IF(C1 > 60000, 0, C1) for the second column of numbers
+-----------+-----+------+--------+
| 3/18/2021 | Thu |    0 | 148188 |
| 3/19/2021 | Fri |    0 | 143698 |
| 3/20/2021 | Sat |    0 | 116747 |
| 3/21/2021 | Sun | 5441 |      0 |
| 3/22/2021 | Mon |    0 | 146595 |
| 3/23/2021 | Tue |    0 | 137504 |
| 3/24/2021 | Wed |    0 |  63294 |
| 3/25/2021 | Thu |    0 | 158627 |
| 3/26/2021 | Fri |    0 | 130954 |
| 3/27/2021 | Sat |    0 | 116610 |
+-----------+-----+------+--------+

Don’t forget to *paste as values* over the results of the split, in the two resulting columns. (cmd+shift+v ) That’s just best practice and prevents errors when the source data changes.

Step 2: Make the plot/chart