How to Create a Heat Map in Google Sheets
Different visual representation techniques, such as charts, line graphs, and heat maps, let you analyze a data set quickly. For example, you can make use of heat maps to understand the result of students. This is because, by viewing the heat map, you can identify students who have failed and scored more than average grades.
Well, this is just a single use case of using heat maps to visualize the data. Google Sheets also allows you to create a heat map. But did you know you can create two types of heat maps in Google Sheets? Yes! Notably, you can create either a color gradient heat map or a single-color heat map.
This guide will walk you through the fundamental steps to create a heat map in Google Sheets. This includes both the color gradient and the single-color heat map. With that said, let us check out the guide.
What Is a Heat Map in Google Sheets
We won’t go down to technicalities. In simple words, a heat map is a technique that uses different colors or a single color to give a visual clue on how the data varies in a specific data set. So, you can make use of heat maps to simplify and understand complex data.
Moreover, it is spread over in two dimensions. First, as far as the question of who can use it is concerned, it can be used for almost every profession you can think of.
A teacher can use a heat map to show how many students scored 90 marks or above. A sales manager can identify the best-performing sales rep by creating a sales heat map.
Now that you have an idea about what a heat map is let us go through the steps on creating a heat map in Google Sheets.
Steps to Create Color Gradient Heat Map in Google Sheets
For both processes, we will make use of conditional formatting to create a heat map. Below are the steps to create a color gradient heat map in Google Sheets.
Step 1: In the Google Sheet, select the cells you want to create a color gradient heat map.
Step 2: From the top Menu bar, click on 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 format rules will open up on the right-side. Here, you need to select the Color Scale option.
Step 5: There is a Preview button that has all the default color gradient heat map templates to choose from. Note that the arrangement of colors is such that the color on the left applies to 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. Using this, you can give your own color scheme to this color gradient heat map.
Step 6: You can also enter a min-value, mid-value, and max-value, which will create the color gradient heat map accordingly.
Step 7: After selecting the color scheme, hit the Done button. This will create a color gradient heat map for the selected data set..
The above color gradient heat map method applies colors based on the values in the cell. Let’s understand this with an example. Say you have values 70 and 90 and have set white for min-value and red for max-value, then both values will be highlighted in the red color.
But since 70 is smaller than 90, a dark red color will apply to 70, creating a gradient effect. So now, let us check out how you can create a single color heat map in Google Sheets.
Steps to Single Color Heat Map in Google Sheets
Single color heat maps are dynamic. This means every time you change the value in the cells, and it will change color automatically to reflect the formatting. The below section will tell you how you can 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.
Step 2: Head over 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 the 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 as per the data set that you have chosen. In our case, we will enter 40.
Step 6: Just below the Formatting rules, you will find the Formatting style option. Here, you can select the color which you want to highlight the cells above 40.
Step 7: After you are done with everything, simply hit the Done button. You will notice that Sheets will highlight all the cells above 40 in the selected color.
Voila! You have created a single-color heat map in Google Sheets. Besides, you can perform the same steps to highlight other values, say less than 40. However, this time select a different color to analyze them quickly.
Use Heat Map to Analyze Data Quickly
There you have it. The color gradient heat map is helpful when you don’t have fixed criteria. For example, if you want to know about students scoring less than 40 marks, all the cells below 40 will be given different shades of the chosen color. Hence, creating a gradient. But if you have fixed criteria, where you just want to know about students who got less than 40, then all the cells containing those values will be colored in a single color.