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.
Here are the steps to create a gradient heat map in Google Sheets:
This would create the color the cells based on the values to create the heat map.
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:
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).
Related: Creating a Heat Map in Excel.
You May Also like the following Google Sheets Tutorials:
Check out my reviews of some useful workplace tools:
How to Use OR Function in Google Sheets (with Examples)
How to Use IMPORTRANGE Function in Google Sheets (with Examples)
How to Use IMPORTDATA function in Google Sheets
Calculate the Number of Days Between Two Dates in Google Sheets
Using IFS Function in Google Sheets to Test Multiple Conditions