How to Create a Heatmap in Google Sheets

Different visual representation techniques such as charts, line charts, and heat maps allow you to quickly analyze a dataset. For example, you can use heat maps to understand students’ outcome. This is because by viewing the heatmap, you can identify students who are failing and scoring above average.

This is just a one-time use case of using heatmaps to visualize data. Google Sheets also lets you create a heatmap. But did you know that you can create two types of heatmaps in Google Sheets? Yeah! Specifically, you can create a color gradient heatmap or a monochrome heatmap.

This guide will walk you through the basic steps of creating a heatmap in Google Sheets. This includes both the color gradient and the monochrome heatmap. That said, let’s check out the guide.

What is a Heatmap in Google Sheets?

We will not go into technical details. In simple words, a heatmap is a technique that uses different colors or a single color to give a visual clue as to how data has changed in a given dataset. Thus, you can leverage heatmaps to simplify and understand complex data.

Moreover, it spans two dimensions. First, when it comes to the question of who can use it, it can be used for almost any profession you can think of.

A teacher can use a heatmap to show how many students scored 90 or more. A sales manager can identify the best performing sales rep by creating a sales heatmap.

Now that you have an idea of ​​what a heatmap is, let’s review the steps to create a heatmap in Google Sheets.

Steps to Create a Color Gradient Heatmap in Google Sheets

We will use conditional formatting to create a heatmap for both processes. Below are the steps to create a color gradient heatmap in Google Sheets.

Stage 1: In the Google Spreadsheet, select the cells for which you want to create a color gradient heatmap.

Step 2: From the Top Menu bar, click the Format button.

Stage 3: This will open a drop-down list of options where you need to select Conditional formatting.

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

Step 5: There is a Preview button with all the default color gradient heatmap templates to choose from. Note that the order of the colors is such that the left color is applied to the lower value. Also, the right color applies to higher values.

There is another option called customize color scheme. Using this, you can give this color gradient heatmap your own color scheme.

Step 6: You can also enter a minimum value, median value, and maximum value that will generate the color gradient heatmap accordingly.

Step 7: After selecting the color scheme, press the Done button. This will generate a color gradient heatmap for the selected dataset.

The color gradient heatmap method above applies the colors based on the values ​​in the cell. Let’s understand this with an example. Let’s say you have the values ​​70 and 90 and set white for the minimum value and red for the maximum value, then both values ​​will be highlighted in red.

But since 70 is smaller than 90, applying a deep red color to 70 creates a gradient effect. Now let’s check out how you can create a monochrome heatmap in Google Sheets.

Steps of Monochrome Heatmap in Google Sheets

Monochrome heatmaps are dynamic. This means that every time you change the value in the cells and the color will automatically change to reflect the formatting. The following section will tell you how you can create a monochrome heatmap.

Stage 1: Select the values ​​for which you want to create a monochrome heatmap in Google Sheets.

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

Stage 3: In the Conditional formatting pane, select Monochrome.

Step 4: Now, under the heading Format rules, click on Format cells. Select Greater than from the drop-down list.

Step 5: In the Greater than field, enter the appropriate value for the data set you selected. In our case, we will enter 40.

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

Step 7: Once you’ve done everything, simply hit the Done button. You will notice that Sheets will highlight all cells above 40 in the selected color.

Request! You’ve created a monochrome heatmap in Google Sheets. You can also follow the same steps to highlight other values ​​less than 40. However, choose a different color this time to quickly analyze them.

Use the Heatmap to Quickly Analyze Data

Here you got it. The color gradient heatmap is helpful when you don’t have fixed criteria. For example, if you want to learn about students who scored less than 40 points, all cells below 40 will be given different shades of the selected color. Therefore, create a gradient. However, if you have fixed criteria that you want to know about students who scored less than 40, all cells containing those values ​​will be colored with a single color.