How to make a heat map in Google Sheets

You can quickly analyze a data set using various visual representation techniques such as charts, line charts, and heat maps. To the example, you can use heatmaps to understand the student’s outcome. This is because by viewing the heat map you can identify students who failed and achieved above average grades.

Well this is just a single use case for using heat maps to visualize the data. You can also use Google Sheets to create a heat map. But did you know that you can create two types of heat maps in Google Sheets? Yes indeed! Specifically, you can either create a gradient heat map or a solid color heat map.

This guide will walk you through the basic steps to create a heat map in Google Sheets. This includes both the gradient and the monochrome heatmap. Let’s look at the instructions.

What is a heat map in Google Sheets?

We will not go into technical details. In simple terms, a heat map is a technique that uses different colors or a single color to give a visual indication of how the data varies in a given data set. This is how you can use heatmaps to simplify and understand complex data.

It is also distributed in two dimensions. First, as to who can use it, it can be used for almost any profession imaginable.

A teacher can use a heat map to show how many students have scored 90 or more. A sales manager can identify the top performing sales person by creating a sales heat map.

Now that you have an idea of ​​what a heat map is, let’s go through the steps to create a heat map in Google Sheets.

Steps to Create a Gradient Heat Map in Google Sheets

For both processes, we use conditional formatting to create a heat map. Below are the steps to create a gradient heat map in Google Sheets.

Step 1: In the Google Sheet, select the cells that you want to create a gradient heat map for.

Select values ​​from Google Sheets

Step 2: Click the Format button in the top menu bar.

Formatting option

Step 3: This will open a drop-down list of options from which you must select Conditional Formatting.

Conditional formatting of Google Sheets

Step 4: A new sidebar titled Conditional Formatting Rules will open on the right. Here you need to select the Color Scale option.

Conditional formatting of the color scale

Step 5: There’s a Preview button that has all of the standard gradient heat map templates to choose from. Note that the order of the colors is such that the color on the left is for the low value. At the same time, the color on the right applies to the high values.

There is another option called Customize Color Scale. This allows you to assign your own color scheme to this gradient heat map.

Preview of heatmap templates with color gradients

Step 6: You can also enter a minimum, mean, and maximum value, which will create the gradient heatmap accordingly.

Enter    values

Step 7: After you’ve chosen the color scheme, click the Done button. This creates a gradient heat map for the selected record.

The above gradient heat map method applies colors based on the values ​​in the cell. Let’s get that with one example. Assuming you have the values ​​70 and 90 and have set white for the min value and red for the max value, then both values ​​are highlighted in red.

However, since 70 is less than 90, a dark red color is assigned to 70, creating a gradient effect. Now let’s see how to do a monochrome heat map in Google Sheets.

Steps to the monochrome heatmap in Google Sheets

Monochrome heatmaps are dynamic. This means that every time you change the value in the cells, the color will automatically change to reflect the formatting. In the following section you will learn how to create a monochrome heat map.

Step 1: Select the values ​​that you want to create a solid color heatmap for in Google Sheets.

Select values ​​from Google Sheets

Step 2: Go to the Format tab and select the Conditional Formatting option.

Conditional formatting of Google Sheets

Step 3: In the Conditional Formatting area, choose Single Color.

Monochrome heat map

Step 4: Now under the Format Rules heading, click the Format cells when option. Select Greater Than from the drop-down list.

Format rules

Step 5: In the Greater than field, enter the appropriate value according to the selected record. In our case we enter 40.

Enter    greater than value

Step 6: Right below the formatting rules you will find the option formatting style. Here you can choose the color you want to highlight the cells over 40.

Formatting style Heatmap Google Sheets

Step 7: After you are done with everything, just hit the Done button. You will find that Sheets highlights all cells over 40 in the selected color.

Google Sheets with a monochrome heatmap

Voila! You have created a solid color heat map in Google Sheets. Also, you can follow the same steps to highlight other values, such as less than 40, but this time choose a different color for quick analysis.

Use the heat map to quickly analyze data

There you have it. The gradient heat map is useful when you don’t have a set of criteria. To the exampleif you want to know if students get less than 40 points, all cells under 40 are given different shades of the chosen color. Therefore create a gradient. However, if you have fixed criteria where you only want to know about students who are less than 40, all cells with those values ​​will be colored in a single color.

Related Posts