Skip to Content

How to Create a Heat Map in Google Sheets – A Step-By-Step Guide

Posted on Last updated:

Are you looking for a more efficient way to analyze data in Google Sheets? Or you want to create visually appealing heat maps so you can easily spot outliers, trends, or patterns in your data? If the answer to these questions is yes, then you have landed in the right place. 

In today’s guide, we will give you a detailed walkthrough on how to create a heat map in Google Sheets. And guess what? You don’t need a ton of experience to do this as the entire process is pretty straightforward. 

Whether you’re a researcher, a data analyst, a student, or a business professional, knowing how to create a heat map in Google Sheets will make your job a lot easier. But before we get into the practical aspect of today’s guide, let’s start with the basics- What is a heat map?

What is a heat map?

A heat map is a graphical representation of data, where values are represented by color variations. Using a color-coded system, a heat map allows users to analyze and keep track of data.

Let’s assume you teach in a high school and you want to easily identify students who failed or passed your course, you can use a heat map to achieve this. Also, if you’re dealing with sales data, you can use a heat map in Google Sheets to easily spot the best and worst performing sales rep. 

The image below is an example of a heat map in Google Sheets. If you look closely at the image below, you’ll notice we used a variety of color gradients. A look at our spreadsheet shows that cells with values that are lower are colored in red, while those with higher values are colored green. By simply looking at the image, you’ll instantly identify low or high data points. 

The cool thing about creating a heat map in Google Sheets is that it is pretty straightforward. Plus, you only need to use the formatting menu to get this done. Using the formatting menu, you can do things like add conditional formatting rules and set alternating colors. 

Now that you know what a heat map is, it’s time to jump into the practical aspect of this tutorial, where we will show you how to create a heat map in Google Sheets. 

Creating a heat map on Google Sheets

As we mentioned right from the get-go, creating a heat map in Google Sheets is a pretty straightforward process and in this section, we will show you how you can get this done in a few simple steps. 

The method we will start with is how to create a heat map in Google Sheets with gradients. For this guide, we will use the following sample data: 

Now that we have our sample data, it’s time to jump into the exciting part- creating the heat map. 

Steps 1: Chose the cells where you want to create a heat map

To create a heat map in Google Sheets with gradients, you need to first select the cells where you want to create the heat map. So for our guide, we will go ahead and select all cells with data from 2018 all the way to 2020. Here is what our worksheet looks like after selecting the cells where we would like to create a heat map for. 

Step 2: Format > Conditional formatting

With the cells where you’d like to create a heat map selected across your spreadsheet, navigate to the Format menu and select the Conditional formatting option. 

Step 3: Tweak conditional formatting rules

After selecting the option for Conditional formatting, Google will automatically launch the Conditional format rules editor to the extreme right of your spreadsheet. Navigate to that section and select the option for “Add another rule.” 

Step 4: Conditional rule pane > Color scale

Having selected the option for “Add another rule,” you need to also choose the option for “Color Scale.” With that option locked in, click on Preview. This will allow you to select from pre-specified color gradient options. 

Keep in mind that the color on the left is typically applied to the low value while the color to the right is applied to high values. Not just that, you can also customize the colors as you want. So feel free to select an option that works for you. 

After selecting your preferred gradient option, click on done and watch Google Sheets perform it’s magic. 

If you followed the steps exactly as we showed you above, you should have a worksheet that now looks like this: 

That wasn’t too difficult, was it? 

Creating a Heat Map in Google Sheets using Single Color

When you use the Color Scale option to create a heat map in Google Sheets, what typically happens is that the gradient is applied based on the value in the cell. 

Let’s say you have the values 60 and 70, which are both low, what the color scale option does is highlights both values in red. However, since 60 is lower than 70, it gets a darker shade of red. 

That said, in some peculiar cases, you may want to create buckets and highlight the accompanying cells based on the bucket it falls in. 

For instance, all values below 100 will be in red while values above 400 will be in green. This is particularly helpful in cases where you have a fixed rule and don’t care about gradients. For example, if a student scores below 40, such a student has failed regardless of whether he/she scored 39 or 5. 

Now that you know the concept behind using the single color option to create a heat map in Google Sheets, let’s show you how to create a heatmap in Google Sheets following these specific rules. 

For this example, we will use the same sample data used in the previous example: 

Step 1: Selects cells where you want to create the heatmap

To create a heat map in Google Sheets with a single color, you need to highlight the cells where you want to create the heatmap. For this tutorial, we will select all cells featured in Columns B, C, and D. 

Step 2: Format > Conditional formatting 

Done selecting the cells where you’d like to create your heatmap? Great. Now, head to the Format menu and select the option for Conditional formatting.

Step 3: Makes changes via the conditional formatting pane

After selecting the option for Conditional formatting, Google automatically launches the Conditional format rules editor. You’ll typically find it on the extreme right of your spreadsheet. 

As you did in the first method we showed you, click on the option for “Add new rule.”

The video below provides better insight into how to execute this step: 

Step 4: Conditional rule pane > Single color

With the option for “Add another rule” selected, instead of choosing the option for Color scale as we did in the previous method, for this method, we will select the option for Single color. 

Step 5: Customize the Single color option

After choosing the option for Single Color, you’ll notice a few other options underneath that. Now, click on the “Format cells if” dropdown and select Greater than.

Step 5.1: Customize the Single color option

Done selecting the option for Greater than? Great, but we aren’t done yet. Now, we need to type a number in the box for “Value or Formula.” For this guide, we will use 400. So let’s go ahead and enter that number in the box provided. 

Step 6: Tweak formatting style

While we would opt for the default formatting style for this guide, feel free to choose a formatting style that works for you. With that done, all you need to do is click on done and wait for a few seconds for the changes to reflect in your spreadsheet. 

If you followed the steps exactly as we showed you, you’ll have a spreadsheet that looks exactly like this: notice how cells with values greater than 400 were highlighted in green? 

Having done that, we need to repeat the steps highlighted above. But this time, we only want to highlight cells with values that are less than 100. Here is how to go about that. 

Step 1: Select the cells you want to create a heat map for

To select cells with values less than 100, you need to go back to your spreadsheet and choose the cells you want to create a heatmap for. 

Step 2: Tweak conditional format rules

With the cells we want to create our heatmap for selected, navigate to the conditional format rules editor and select the option “Add new rule.” 

Step 3: Select Single color

Once a new rule has been added, click on the Single color menu, scroll down to the “Format cells if” dropdown, and choose the option for “Less than.”

After selecting the option for Less than, you’ll notice a box underneath that. Here we are going to enter 100. 

Step 4: Change the formatting style

To ensure the heatmap for values less than 100 is different from the heatmap for values greater than 400, we need to make changes to the formatting style. To do this, simply click on the formatting style and change it from default to red background. 

Once you have done that, you can click on “Done” to complete the process. 

If you followed all the steps detailed above, you should notice some obvious changes to your spreadsheet. Here is what ours looks like. 

Note: The heat map we have created so far is dynamic. What this simply means is that if we make any changes to the values in our cells, the color of the cell will be automatically changed to reflect its value. 

Tips: When you copy and paste cells from other parts of the worksheet, the conditional formatting rules don’t apply. However, if you want to copy the values in your spreadsheet along with the heat map, without erasing the formatting rules, you need to select the “Paste values only” option. To do this right click on the cell where you want to paste the value, select the option for Paste Special, and choose Paste Values Only. 

Advanced settings option

The exciting thing about creating a heat map in Google Sheets is that you have access to several advanced settings you can explore to add more depth to your project. For instance, there are advanced settings options, which you can access under the Color scale menu. With the advanced settings option, you can tweak the default format rules or add your own custom format rules, depending on what works for you. 

Not just that, you can also change the default color schemes provided by Google Sheets or create your own. 

Here is a picture of what the advanced settings options look like: 

From the image above, you’ll notice that there are three drop-down lists. Let’s quickly look at what you can do with these advanced settings 

  • Minpoint: This advanced settings option allows you to set the minpoint to Min value, Number, Percent, or Percentile. 
  • Midpoint: With this option, you can adjust the midpoint to either None, Number, or Percentage. 
  • Maxpoint: If you want to set the maxpoint to Min Value, Number, or Percentile, this is the setting you should use. 

What’s interesting about these advanced settings options is that you also get the opportunity to set the color of each of these points. By making these changes, you will have a whole different gradient for your heat map. 

Note: We aren’t going into detail on how to use the advanced settings option to create a heat map in Google and that’s because our goal with today’s guide is to keep things simple. If you ever need to create a heat map that is comprehensive and very detailed, we suggest you play around with a few of these advanced settings and see how the heat map comes out. 

Other remarkable examples of heat maps in Google Sheets 

In the example we looked at earlier, what we did was create a heat map in Google Sheets using a table. Now, we want to take things further by showing you another way to create heat maps. But instead of using a table, we will use a geo map. Sounds interesting right? Well, let’s get down to business. 

Creating a geographic heat map in Google Sheets 

Creating a geographic heat map in Google Sheets is just as easy as creating a heat map in Google Sheets using a table. To show you how easy the entire process is, we have put together a detailed step-by-step process to guide you. Here, check it out: 

For this section, we will use the following sample data to create a geographic heat map in Google Sheets. 

Step 1: Highlight the data in the spreadsheet

To create a geographic heat map in Google Sheets, the first thing we need to do is highlight the data we want to use to create the heat map. 

Step 2: Insert > Chart

With our data now highlighted, head to the Insert menu and select the option for Chart. 

If you did everything exactly as we detailed above, Google will automatically create a chart that looks like this: 

Step 3: Change the pie chart to a Geo chart

From the image above, you’ll notice that Google created a pie chart. However, since what we want is a geo map, we need to change it. Here is how you do that. 

Head over to the Chart editor on the extreme right for your spreadsheet. Under the option for chart type, change Pie chart to Geo chart.

After these changes have been effected, Google Sheets will automatically update the chart on your spreadsheet. Take a look at what ours look like: 

Looking at the geo heat map we generated above, you’ll notice that the country with the list sales appears in red while those with high sales appear in Green. 

Final thoughts

If you have always wanted to learn how to create a heat map in Google Sheets but don’t know where to start, we are sure today’s guide has simplified the entire process. 

In today’s Google Sheets tutorial, we gave you an overview of what a heat map is. After that, we showed you a practical step-by-step process of creating a heat map in Google Sheets. With everything we have covered so far, we are optimistic that you should be able to create a heat map in Google Sheets without sweating it. 

If you find today’s Google Sheets tutorial helpful, we are sure you’ll love the other exciting Google Sheets guide we have. Feel free to search our blog for more exciting Google Sheets tutorials. 

Other Google Sheets Resources You May Find Useful