Google Sheets has a number of different ways in which you can visualize and easily analyze your data. One of these ways is a heat map. A heat map is a good visual analysis layer to add to your data set to get some insights quickly.
They are also surprisingly easy to create using heat map Google Sheets formatting. In this tutorial we will show you how to create a Google Sheets heat map.
What is a Heat Map
A heat map is a way of representing the distribution of data using color variations. It uses a color-coded system to help you analyze and keep track of your data.
For example, if you have students’ marks in columns, you can use a heatmap in Google Sheets to identify students who failed or students who scored above 90. Similarly, if you have sales data, you can create a Google spreadsheet heatmap 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. Creating a heat map in Google Sheets only requires the formatting menu. The format menu lets you do things like set alternating colors and add conditional formatting rules.
Now let’s see how to create heatmaps in Google Sheets using some examples.
In Short: How to Create a Heat Map in Google Sheets
You can easily add a heat map to a range of data in Google Sheets by selecting the data and navigating to Format > Conditional formatting > Add new rule > Color scale. Then you simply need to select the parameters that will apply to your heat map.
How to Create a Heat Map in Google Sheets (Gradient)
Here’s how to create a heat map in Google Sheets with gradient:
- 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 color gradient 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 of the cells based on the values to create the heat map.
How to Create 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 heatmap 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
- 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).
Related: Creating a Heat Map in Excel.
Advanced Settings
In the conditional formatting menu there are also some other advanced settings you can add to your rules. In the advanced settings you can change the format rules or add your own format rules. You can also change the default color schemes that are available and create your own.
There are three drop down lists:
Minpoint– lets you set the minpoint to Min value, Number, Percent, or Percentile.
Midpoint– lets you set the midpoint to None, Number, Percent, or Percentile.
Maxpoint– lets you set the maxpoint to Min value, Number, Percent, or Percentile.
You can also set the color for each of these points which will affect the gradient for your heat map.
Other Examples Of Heat Maps In Google Sheets
In the examples above we have seen a table heat map and geographical heat map. There are also other types of heat maps such as:
1. Geographic Heat Map in Google Sheets
You can use a geo chart to create a geographical heat map in Google Sheets. This can be used to visualize minimum and maximum data or the distribution of data based on the region.
Consider our sheet below:
By inserting a geo chart we can represent these information in the form of a map. Here’s how to make geographic heat map in Google Sheets:
- Select your data
- Go to Insert > Chart
- In the Chart editor, go to Chart type and choose Geo map
- In the customize menu go to Geo
- Step 5: Select the colors you want to use for your gradient
The result will be a geo map with gradient color whereby the country with the least sales is red and the country with the most sales is green. You can also use one single color and create a gradient out of it
2. Calendar Heat map
To make a calendar heat map you will have to replace the dates in the calendar with the actual values. Once you have done this you can then apply the conditional formatting
- Select the data
- Go to Format then conditional formatting
- Click add rule
- Under the conditional formatting menu go to color scale
- Set the color gradient you want to use for your heat map. You can also create your own custom color scheme.
- You can make the values invisible by using a custom number format. Select all your values, and go to Format > Number
- Select Custom number format.
- In the custom number format type in three semicolons then click Apply
You will get a calendar heat map that looks like this:
Frequently Asked Questions
Does Google Sheets Have a Heat Map?
Although there isn’t a specific template, you can create a heat map on Google Sheets using conditional formatting.
Does Google Sheets Have a Map Function?
Yes it does, however, the map function is not necessarily used to create heat maps. Instead, it is used to create an array of data from a data range, where each value is “mapped” to a new value based on a custom LAMBDA function.
Conclusion
In this article, we have shown you how to make a heat map in Google Sheets. You can make a color heat map or a heat map using the gradient of one single color. There are also other kinds of heat maps, such as a geographical heat maps or a calendar heat maps.
If you found this article useful, you can also learn about how to sort by color in Google Sheets.
You May Also like the Following Google Sheets Tutorials:
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
