Make a Google Sheets Heat Map in 2 Mins (Very Easy)

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.

Heat Map in Google Sheets - Example

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:

  1. Select the cells for which you want to create the heat map.
    Heat Map in Google Sheets - select cells
  2. Go to the Format tab and click on Conditional Formatting.
Heat Map in Google Sheets - select conditional formatting
  1. In the Conditional Formatting Pane (on the right of the worksheet), click on Add new rule.
Heat Map in Google Sheets - add new rule
  1. In the ‘Conditional formatting rules’ pane, click on Color Scale.
Heat Map in Google Sheets - color scale
  1. 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.
Heat Map in Google Sheets - preview
  1. When you’ve selected the gradient you want, click on Done.
Heat Map in Google Sheets -Click 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:

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

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

Heat Map in Google Sheets - bucket

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.

Custom conditional formatting

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:

data for a geo heatmap

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:

  1. Select your data
Select the data
  1. Go to Insert  > Chart
Insert a chart
  1. In the Chart editor, go to Chart type and choose Geo map
Select geo map
  1. In the customize menu go to Geo
Customize the chart
  1. Step 5: Select the colors you want to use for your gradient
Select the colors

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

A finished geo heatmap

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

data for a calendar heatmap
  1. Select the data
  2. Go to Format then conditional formatting
Conditionally format the calendar
  1. Click add rule
Add a new conditional formatting rule
  1. Under the conditional formatting menu go to color scale
Select a color scale
  1. Set the color gradient you want to use for your heat map. You can also create your own custom color scheme.
  2. You can make the values invisible by using a custom number format. Select all your values, and go to Format > Number
  3. Select Custom number format.
  4. In the custom number format type in three semicolons then click Apply
the semi colons

You will get a calendar heat map that looks like this:

finished calendar heatmap

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:

Spreadsheet Expert at Productivity Spot | + posts

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.

Popular Posts
You May Also Like