How to Create a Heat Map in Google Sheets

How to Create a Heat Map in Google Sheets


Heat map in featured google sheets

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.

Heat maps used by Google Sheets


Heat maps used by Google Sheets

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.

Select the values ​​google Sheets


Select the values ​​google Sheets

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

Formatting option


Formatting option

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

Conditional formatting of google sheets


Conditional formatting of google sheets

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

Conditional formatting of the color scale


Conditional formatting of the color scale

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.

Preview Heat Map Templates in Gradient Colors


Preview Heat Map Templates in Gradient Colors

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.

Enter values


Enter values

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.

Select the values ​​google Sheets


Select the values ​​google Sheets

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

Conditional formatting of google sheets


Conditional formatting of google sheets

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

Heat map in one color


Heat map in one color

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

Formatting rules


Formatting rules

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.

Enter a value greater than


Enter a value greater than

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.

Heat map google sheet formatting style


Heat map google sheet formatting style

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.

One color heat map google sheets


One color heat map google sheets

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.

Last updated on August 24, 2021

The above article may contain affiliate links that help support Guiding Tech. However, this does not affect our editorial integrity. The content remains impartial and authentic.

(function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(d.getElementById(id))return;js=d.createElement(s);js.id=id;js.src=”http://connect.facebook.net/en_US/sdk.js#xfbml=1&version=v2.5&appId=1652915528311925″;fjs.parentNode.insertBefore(js,fjs);}(document,’script’,’facebook-jssdk’));

Leave a Reply

Your email address will not be published. Required fields are marked *