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.
Step 2: Click the Format button in the top menu bar.
Step 3: This will open a drop-down list of options from which you must select Conditional Formatting.
Step 4: A new sidebar titled Conditional Formatting Rules will open on the right. Here you need to select the Color Scale option.
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.
Step 6: You can also enter a minimum, mean, and maximum value, which will create the gradient heatmap accordingly.
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.
Step 2: Go to the Format tab and select the Conditional Formatting option.
Step 3: In the Conditional Formatting area, choose Single Color.
Step 4: Now under the Format Rules heading, click the Format cells when option. Select Greater Than from the drop-down list.
Step 5: In the Greater than field, enter the appropriate value according to the selected record. In our case we enter 40.
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.
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.
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.