How to Create a Heat Map in Google Sheets

How to Create a Heat Map in Google Sheets
How to Create a Heat Map in Google Sheets – Sheetaki

Creating a heat map in Google Sheets helps you visualize the extremities in your dataset.

One of the best ways to illustrate data is to use a heat map. It uses colors to provide immediate visual cues on the magnitude of each value contained within. When you look at a heat map, you can immediately point out a particular dataset’s peak and low values.

Heat maps can be implemented in many situations. This tool is often used in weather forecasts to show the temperature differences across a country. At a glance, you can easily identify the places with the hottest or coldest temperatures.

It’s easy to create a heat map; the best part is that you can make one using Google Sheets! So, before throwing in your dime for some expensive heat map generator, check out this guide first.

Now, let’s learn how to create a heat map in Google Sheets!



A Real Example of a Heat Map in Google Sheets

Let’s say you have a spreadsheet that contains the highest recorded temperatures of different states in recent years.

Creating a Heat Map in Google Sheets

By scanning through the dataset, you can already identify the hottest temperatures of each state in different years. However, determining the overall highest or lowest value requires some considerable time and effort. Here is where a heat map comes in handy.

Thankfully, Google Sheets offers you a formatting tool to transform your plain dataset into a heat map, similar to this one:

A Real Example of a Heat Map in Google Sheets

That’s so much better, isn’t it? In contrast to the plain dataset earlier, you can now easily compare temperature values across states. You can see that the higher the value, the more intense its color is.

Creating a heat map for your dataset in Google Sheets is quite easy. In the succeeding sections, we’ll demonstrate how to do it step-by-step.



When to Use a Heat Map?

Before creating a heat map, you must ensure that your dataset is in the proper format. You can’t just use a heat map for any type of data. Otherwise, you’ll only end up with a confusing dataset.

So, when should you use a heat map?

Heat maps work well for datasets with two variables plotted as row and column categories, just like our example earlier. Also, you can implement heat maps for Pivot Table reports to summarize your data better.



How to Create a Gradient Heat Map in Google Sheets

At this point, let’s get into detail on how to create a typical multi-colored or gradient heat map in Google Sheets. For you to follow along, click the link below to make a copy of our example dataset.

Open your copy of the example spreadsheet and perform the instructions below:
  1. Our goal is to recreate the heat map shown in our previous example. We’ll use the same set of data for this activity.
    Example data for creating a gradient heat map in Google Sheets
  2. To start with, select the cell range which contains the data for our heat map. Highlight cell range B3:G17, as shown in the image below.
    Highlight the cell range for the gradient heat map
  3. Next, click the Format menu, and then choose Conditional formatting.
    Access the Conditional formatting control to create a heat map
  4. Upon clicking, the Conditional format rules will appear on your screen. From here, select the Color scale tab.
    Define the Color scale for the gradient heat map

    You’ll notice that a default color will be applied to your dataset once you click Color scale.
    An example of a gradient heat map in Google Sheets

    Great! We just transformed our dataset into a heat map. But wait—the default color seems to be inappropriate for the theme. Since we’re dealing with high-temperature values, it’s just proper to use warmer colors instead. To do so, we need to override the default color by setting our own customized shade.
  5. Under the Color scale tab, we can configure the formatting rules of our selected cell range. Here, we will specify the colors of the Minpoint and Maxpoint of the dataset to override the default color and give it a warm, gradient effect.
    Overriding the default color of the heat map
  6. Let’s start with Minpoint first. Click on the drop-down button under Minpoint, and select Min value. Afterward, set the Min point color to white. This will be the color of the minimum value in our dataset.
    Changing the Minpoint of a heat map
  7. Then, let’s proceed with Maxpoint. Click the drop-down button and choose Max value. Set the Max point color to dark yellow, as shown below.
    Changing the Maxpoint of a heat map
  8. After that, click Done on the Conditional format rules panel. Your dataset will immediately update to reflect the changes you made.
    A gradient heat map in Google Sheets

That’s it! You have just created a multi-colored heat map in Google Sheets.



How to Create a Single Color Heat Map in Google Sheets

Aside from gradient color, you can also create a single-color heat map in Google Sheets. This is useful for instances where you must highlight values that have reached a certain condition or threshold.

Here’s how to do it:

  1. First, highlight the cell range you need for the single-color heat map.
    Example data for the single color heat map
  2. Next, access the Conditional format rules panel by clicking the Format menu, and then choose Conditional formatting.
  3. From the Conditional format rules panel, select the Single color tab.
  4. Under Single color, you can define your own formatting rules to fill the cells in your dataset with a specific color depending on the condition. In the example below, a rule is defined to force Google Sheets to fill a cell with red if it is greater than 50.
    Formatting the style of the single color heat map
  5. Once the conditional format rule is applied, your dataset will transform into a single color heat map, like the one below.
    An example of a single-color heat map in Google Sheets

There you have it, a single-color heat map in Google Sheets! You can always add more conditional format rules to highlight more cells depending on what you need to emphasize.

That’s about it when it comes to creating heat maps in Google Sheets. Heat maps can level up how you present your datasets.

Interested in learning more about spreadsheets? Check out our other Google Sheets articles.

You can stay up to date with other Google sheets essentials by subscribing to our newsletter.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Leave a Reply

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

You May Also Like