Creating a Heat Map in Google Sheets (Step-by-Step Tutorial)

Sumit
Last updated November 10, 2020

A heat map is a good visual analysis layer to add to your data set to get some insights quickly.

For example, if you have students marks in columns, you can use a heat map in Google Sheets to identify students who failed or students who scored above 90. Similarly, if you have sales data, you can create a heat map to identify top/bottom performing sales reps.

Below is an example of a Heat Map in Google Sheets, where a gradient of colors is used. Cells that contain values that are lower as compared to the data set are colored red, and the ones that are higher are colored green.

This allows you to identify data points that are high/low instantly.

Now let’s see how to create heat maps in Google Sheets using some examples.

Creating a Heat Map in Google Sheets (Gradient)

Here are the steps to create a gradient heat map in Google Sheets:

Select the cells for which you want to create the heat map.
Go to the Format tab and click on Conditional Formatting.
In the Conditional Formatting Pane (on the right of the worksheet), click on Add new rule.
In the ‘Conditional formatting rules’ pane, click on Color Scale.
In the Color Scale options, click on Preview to select from some pre-specified options. Note that the color on the left is applied to the low value and on the color on the right is applied to high values. You can also customize the colors.
When you’ve selected the gradient you want, click on Done.

This would create the color the cells based on the values to create the heat map.

Creating a Heat Map in Google Sheets (Single Color)

When you use Color Scale, the gradient is applied based on the value in the cell.

For example, if you have the values 50 and 80, both low get highlighted in red. However, since 50 is smaller than 80, it gets a darker shade of red (hence the gradient).

In some cases, you may want to create buckets and highlight these cells based on the bucket it falls in.

For example, all values below 100 should be red, and all values above 400 should be green. This is helpful in cases where you have fixed criteria, and you don’t care about the gradient. For example, a student who scores below 35 fails (no matter if he/she score 34 or 10).

Here is how you can create a heat map in Google Sheets where cells above/below a thresh hold are highlighted:

Select the cells for which you want to create the heat map.
Go to the Format tab and click on Conditional Formatting.
In the Conditional Formatting Pane (on the right of the worksheet), click on Add new rule.
In the ‘Conditional formatting rules’ pane, click on Single Color.
In the ‘Format cells if’ drop-down, select Greater than.
In the field below it, enter 400.
In the ‘Formatting style’, select the color with which you want to highlight cells above 400.
Click Done.

This would highlight all the cells that have a value of more than 400.

Now you can repeat the same steps shown above, but this time, highlight all the cells that have the value of less than 100 in red.

Note that this heat map is dynamic. This means that if you change the values in the cells, the color of the cell would automatically change to reflect it’s value.

Caution: When you copy and paste cells from some other part of the worksheet on the heat map, the conditional formatting rules are erased. If you want to copy values in the heat map without erasing conditional formatting, paste only the values (right-click –> Paste Special –> Paste Values Only).