How to Create a Heat Map in Google Sheets

Different visual representation techniques, such as charts, line graphs, and heat maps, allow you to quickly analyze a set of data. For example, you can use heat maps to understand student achievement. This is because by viewing the heat map, you can identify students who failed and who scored above average.

Well, this is just a one-time use case of using heat maps to visualize data. Google Sheets also allows you to create a heat map. But did you know that you can create two types of heat maps in Google Sheets? Yes! In particular, you can create either a color gradient heat map or a single color heat map.

This guide will walk you through the basic steps to create a heat map in Google Sheets. This includes both the color ramp and the single color heat map. That said, let us check out the guide.

What is a heat map in Google Sheets

We will not go into the technical details. Simply put, a heat map is a technique that uses different colors or a single color to give a visual clue as to how data varies in a specific data set. So you can use heat maps to simplify and understand complex data.

In addition, it is divided into two dimensions. Firstly, when it comes to who can use it, it can be used for almost any profession you can think of.

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

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

Steps to Create a Gradient Color Heat Map in Google Sheets

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

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

2nd step: On the top menu bar, click the Format button.

Step 3: This will open a drop-down list of options, from which you need to select conditional formatting.

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

Step 5: There is a Preview button which has all of the default color gradient heat map templates to choose from. Note that the arrangement of the colors is such that the color on the left applies to the lower value. At the same time, the color on the right is applied to high values.

There is another option called Customize Color Scale. Using this you can give your own color scheme to this color gradient heat map.

Step 6: You can also enter a minimum value, an average value, and a maximum value, which will create the color gradient heat map accordingly.

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

The above color gradient heatmap method applies colors based on the values ​​in the cell. Let’s understand this with an example. Suppose you have the values ​​70 and 90 and you have 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, a dark red color will apply to 70, creating a gradient effect. Now let’s see how to create a single color heat map in Google Sheets.

Steps for a single color heat map in Google Sheets

Monochrome heat maps are dynamic. This means that whenever you change the value in the cells, the color will automatically change to reflect the formatting. The section below will walk you through how to create a single color heat map.

Step 1: Select the values ​​for which you want to create a single color heat map in Google Sheets.

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

Step 3: In the Conditional Formatting pane, select the Single Color option.

Step 4: Now, under the Format Rules heading, click on Format Cells If option. From the drop-down list, select the Greater than option.

Step 5: In the Greater than field, enter the appropriate value depending on the data set you have chosen. In our case, we will enter 40.

Step 6: Right below the formatting rules you will find the Formatting Style option. Here you can select the color you want to highlight cells above 40 with.

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

There ! You created a monochrome heat map in Google Sheets. Also, you can do the same steps to highlight other values, say less than 40. However, this time select a different color to quickly analyze them.

Use the heat map to analyze data quickly

Here is. The color gradient heat map is useful when you don’t have fixed criteria. For example, if you want to know if students score less than 40 points, all cells below 40 will have different shades of the chosen color. Therefore, create a gradient. But if you have fixed criteria, where you just want to know the students who are under 40, then all cells containing those values ​​will be colored with one color.