Do you want to create a heat map to visualize your data? This post is going to show you how to turn any range of numbers into a heatmap in Google Sheets!

Heatmaps are a great way to add context to your data in order to pinpoint problems and fix them as quickly as possible. Heat maps that use color to distinguish data will help you to simplify and understand complex data.

A heat map is a great way to visualize such data where variables are spread across rows and columns where each cell color represents the value assigned to it.

Heat maps make it easy to visualize and analyze complex data. They are widely used by scientists, analysts, and marketers to perform fundamental data analysis and identify common patterns.

Heat maps are simple to create and can be an excellent visual analysis added to your arsenal to get quick data insights.

This post will show you 2 ways to make a heatmap visual in Google Sheets. Get your copy of the example workbook used in this post to follow along.

What is a Heat Map?

A heat map (or heatmap) is a data visualization technique that shows the magnitude of values in a table by color.

In simple words, a heat map is actually a color-coded table. It quickly conveys information by combining a standard table with a color palette. They are used in reports to draw attention to specific data points. 

The value in each heat map cell has a color that conveys some meaning. It usually ranges from light to dark color where the lighter values represent smaller values. 

Typically, warm-to-cool color schemes are employed while creating a heat map, representing data in the form of hot and cold spots.

This article will teach you two different ways to create a heat map in Google Sheets. Are you ready?

How to Make a Heat Map with Conditional Formatting

You can create a heat map for any numerical dataset. For example, the dataset shown above is of the year and month-wise sales.

At first glance, understanding this sales data is pretty challenging.

However, if you use conditional formatting with the predefined color schemes available, it can help you read this data with ease and precision.

That’s precisely what heat maps do! They allow you to understand the data with ease using color schemes.

Single-Color Heat Map in Google Sheets

Here’s how to create a single-colored heat map in Google Sheets using the following steps.

1. Select the range of values where you want to make a heatmap.

Remember that you do not select the column and row headers, just select numerical values.

💡 Tip: You can use the Ctrl + Shift keys to select the range of cells without touching the mouse. Select the top left cell then press and hold Ctrl + Shift keys. Then press the right arrow key once to move the range across the columns. Then press the down arrow key once to select all the rows.

This will select your entire range of values.

2. Go to the Format tab. It will open all the available formatting options.
3. Select Conditional Formatting from the drop-down menu.

The Conditional Formatting window will open on the extreme right side of the screen.

4. Click on Add another rule.

The conditional formatting creates a basic layout of the rule as soon as it is initiated and allows you to modify it based on your requirements.

There are two options to apply conditional formatting. Single Color allows you to apply one single color to the selected range based on the Format Rules.

5. Choose Color Scale from the conditional formatting pane that allows you to apply a color scale to the selected range.
6. Under Format Rules, if you click on Preview inside the Color Scale settings, you will see a list of predefined color scale options.

📝 Note: It’s essential to understand that the color on the left corresponds to a low value, while the color on the right corresponds to a high value. You can also change the color scheme.

7. Choose the Gradient Color to which you would like to apply. I prefer the default light green shade, but you are free to choose from Yellow or Red as well.
8. Now press the Done button.

That’s it! Your heat map is ready, as shown above.

Moreover, because of this color scheme, it is now easier to read the data. You can easily identify that the values with dark green shades are the ones with fewer sales and those with a lighter shade are the highest sales.

You’re not limited to the default settings.

Instead, you can set your own colors, measure the min, max, and midpoints, and even define where the midpoint lies.

This will be discussed more when working with the multi-colored heat map in our next section.

But, these customizations are also possible to achieve inside the single-color scheme. You can play around it with the single-color scheme and let me know your feedback in the comment section below.

Multi-Color Heat Map in Google Sheets

To create a multi-color heat map, everything works the same as well.

Follow the first five steps from the previous example of creating a single-colored heat map.

6. Click on Preview under the Format rules and you can see multiple color scales to choose from based on the cell values.
7. This time around, go with the multi-colored scheme specifically the one with the name Green to yellow to red.
8. Hit the Done button to apply this color scheme to the selected cells.

The output should be as shown above. The color scale is moving from lowest value to highest value in terms of Green to yellow to red.

Styling the Heat Maps in Google Sheets using Min Mid and Max Point

Customizing the heat map is just as important as creating one using the previous steps.

For Example, you can see in the multi-colored heat map that the lowest sales values are marked as green, and the highest sales values are going through the red color scale.

But perhaps you want the sales values below $1000 to be marked as red so that you can do the root cause analysis for those. Whereas, sales values above $8000 should be marked as green.

There is no such color scale that runs from Red to yellow to green. But, you can use the minpoint, midpoint, and maxpoint to set the custom minimum, middle, and maximum values to the selected data and get this customization done.

Follow the first seven steps as shown in the previous two examples. These steps will allow you to navigate to the conditional formatting, select the color scale, change the color scale and set it to one of your choices.

• Below the Preview under the Format rules section, there are three options for you that you can customize.
  • Minpoint – You can set it to either a Minimum value from your data, a Number, a Percent value, or a Percentile value.
  • Midpoint – It shows the middle point of your data (or Median). contains the same options as the Minpoint. You can set it to a Number, Percent, or Percentile. You can also choose None to this ignore this point.
  • Maxpoint – It is the maximum point of your data which can either be a Maximum value, Number, Percent, or Percentile.

📝 Note: By default, the Minpoint is set to the minimum value, Midpoint is set to the 50th percentile, and Maxpoint is set to the maximum value.

8. From the dropdown present under Midpoint, select Number and add the value 700 inside the input box to the right. Also, set the color to red. You can choose it through a dropdown menu from the Min point color.
9. Keep the Midpoint value as it is (50th Percentile). You just need to change the color of the midpoint to yellow.
10. Change the Maxpoint to a Number through the dropdown and add 8000 in the corresponding input box. You also need to set the color to green through the Maxpoint color dropdown.
11. Click on the Done button to apply these customized settings to your selected layout.

Bingo! You just customized the heat map!

Notes for Customization

• The heat map is dynamic. Meaning, whenever you change the value of a specific cell, the color scheme automatically updates based on the new value.
• The preferred way to customize the heat map is by setting the Minpoint and Maxpoint to Numbers. The Midpoint however is preferred to be the 50th Percentile which is also the median of your data.
• If you want, you can change the Midpoint as well to a Number and increase the dynamicity of your heat map.
• You can also apply the Minpoint, Midpoint, and Maxpoint to the single-color scaled heat map.

How to Make a Heat Map with an App Script

The script will allow the user to apply a heat map to the selected range.

It will essentially be a shortcut to manually setting up the conditional formatting. You will now see how to automate the process of creating a heat map in Google Sheets using the Apps Script.

Launch the Apps Script editor.

Go to the Extensions menu in Google Sheets and select the Apps Script option.

function hitMap() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apps Script");
var range = sheet.getRange("B2:D13");

//Creating rule_1 for applying conditional formatting 
var rule_1 = SpreadsheetApp.newConditionalFormatRule()
            .whenNumberBetween(0, 700)
            .setBackground("#E67C73")
            .setRanges([range])
            .build();
//Applying rule_1 to the values that match the criteria in range            
var rules_1 = sheet.getConditionalFormatRules();
rules_1.push(rule_1);
sheet.setConditionalFormatRules(rules_1);

//Creating rule_2 for applying conditional formatting
var rule_2 = SpreadsheetApp.newConditionalFormatRule()
            .whenNumberBetween(700, 1400)
            .setBackground("#FFD666")
            .setRanges([range])
            .build();
//Applying rule_2 to the values that match the criteria in range            
var rules_2 = sheet.getConditionalFormatRules();
rules_2.push(rule_2);
sheet.setConditionalFormatRules(rules_2);

//Creating rule_3 for applying conditional formatting
var rule_3 = SpreadsheetApp.newConditionalFormatRule()
            .whenNumberBetween(1400, 10000)
            .setBackground("#57BB8A")
            .setRanges([range])
            .build();
//Applying rule_3 to the values that match the criteria in range            
var rules_3 = sheet.getConditionalFormatRules();
rules_3.push(rule_3);
sheet.setConditionalFormatRules(rules_3);
  //rule.setBackground("#57BB8A")
}

Inside the Apps Script project, copy and paste the above script which automates the process of creating a heat map.

Even though the script looks a bit lengthy, it is just a simple one with duplicating blocks for each color scheme.

The getSheetByName() method gets the sheet with the name Apps Script and results are stored in a variable named sheet.

The code then sets the range as B2:D13 with the getRange() method so that conditional formatting can be implemented in those cells.

A new conditional format rule with the names rule_1, rule_2, and rule_3 are created for the different color schemes with the newConditionalFormatRule() method.

Various properties such as the colors and values for the conditional format are set.

Click on the triangular Run button from the project menu bar which runs this script and applies the conditional formatting to range B2:D13 inside the sheet named Apps Script.

The above output is above where the heat map shows the color scale red for all the values between 0 to 700, yellow for all the values between 700 to 1400, and the color green for all the values between 1400 to 10000.

📝 Note: This is a note!

One key difference between the color-scaled heat map and one created using the Apps Script is the color intensity of the cells. When you use the color-scaled heat map, the cell color intensity varies based on the values.

But with the script approach, every value that falls between two values will set the same color (red, yellow, or green) to the cell background.

You could of course create a few more rules to create a gradient color in between.

Conclusion

A heatmap is a visual representation of your data where different color patterns represent values ​​that help you determine if your data goals are being met. We can use two or more colors to see patterns in the data.

The heat map is dynamic. This means changing the cell values will automatically adjust the color to reflect the new value.

Using Visualization Dashboards and representations like a Heat Map in Google Sheets, you can get deeper context on significant Numbers, Trends, Metrics, Key Performance Indicators, etc.

By using the Apps Script, you are able to automate the entire task of creating a heat map in Google Sheets. Though in this example, all the values are following three color rules, you are free to create more such rules for advanced customizations inside the heat map.

Have you ever used a heat map in Google Sheets? Which method do you prefer for creating a heat map visual? Let me know in the comments below!