SheetPointers Logo

How to create a heatmap in Google Sheets (Step by Step)

Heatmaps are quick ways to visualize and find anomalies in data, and Google Sheets allows us to implement them very easily. Let's create a Google Sheets Heatmap!
Google Sheets Heatmap

Using Google Sheets’ conditional formatting feature, we can alter the color of a cell-based on its value. We’ll show you how to create both single color and multi-color heatmaps.

Conditional formatting heatmaps are a great way to show you a spike in sales or a dip in a student’s grades. This allows us to pinpoint problems and fix them asap.

Here’s how to create a heatmap in Google Sheets:

  1. Select the area you would like to apply the heatmap to. This can be a column, row, or two-dimensional array.
  1. Right-click/two-finger tap to open the options menu and click “conditional formatting” OR click “Format” then “Conditional Formatting” in the toolbar
Select Conditional Formatting in Google Sheets
  1. After the Conditional Formatting menu opens up, select “Color Scale.” Now we have our Heatmap!
Open Conditional Formatting Panel

Multi-Color Heatmap

Multi-color heatmaps make it even easier to spot data that’s higher or lower than the usual range by adding eye-catching colors to your maximum and minimum values. 

After following the steps above (select the area and apply conditional formatting), make sure to click the “color scale” column. 

We can head over to “Max Value,” and click the paint bucket color selector icon to choose a color. I chose Green for the max value and Red for the min value.

Multi color Heatmap of Ice cream sales

When we look at the heatmap we can see which days brought us the most ice cream sales. But, on Thursday of Week 2 we had 42 sales which is way more than any of the other days, pretty much drowning out the other variation.

Digging Deeper (Advanced Settings)

To show greater detail, we can adjust the min and max points. Let’s say that we need to sell exactly 5 ice cream cones to break even. I would want to place any numbers below 5 in red (since I’m losing money).

Changing the midpoint

Locate the “midpoint” section in the Color Scale tab and change the option to “Number” and set it to 5 (or whatever your break-even price is). Now we can see which days we were losing the most money, however, we can’t see many variations in the green cells. To do this, let’s exclude outliers from our heatmap

Multicolor heatmap

Excluding Outliers in your Heatmap

If we usually have high sales at 10 ice cream cones per week, most of our data will be below 10, so there is no reason to “dilute” the scale with an outlier such as 42.

Let’s set the “Maxpoint” to a number, then make it 10.

Heatmap with outliers excluded

Now all numbers above 10 will be this exact dark green, allowing us to see more detail where it is needed.

The best thing about this conditional formatting heatmap is that it’s dynamic meaning it will change whenever you enter new data.

Applications

Now let’s see how we can use heatmaps in real-world situations.

We can use heatmaps to see which students scored well and who performed badly on each exam. This allows educators to better serve their students.

First, we’ll select our grades, then apply conditional formatting (shown above).

Student Grades Heatmap

Make sure you reorder the conditional formatting rules so that this one takes precedence over the one already there or else you’ll end up with this.

To reorder the conditional formatting rules just head over to the conditional formatting bar. Hover over a rule, then hold down when three vertical dots appear. Simply drag it above the previous rule.

Grades Heatmap Error

However, our current spreadsheet shows us the highest and lowest numbers. To show the highest numbers of just one test, we need to select each column individually.

To do this open up our conditional Formatting rule, and click on the grid icon next to the range input field. While holding the “CTRL” or “CMD” key select the first cell and drag your mouse pointer down to the end of the column like this:

Fixed Error of Grades Heatmap

Now click done “add another rule.” This automatically saves and duplicates the current rule, so you can simply repeat the process for each column.

Final Result

Final Grades Heatmap

Now we can see how students are performing with a simple Google Sheets heatmap.

Taking Heatmaps to the Next Level

Way back in seventh grade science, we were tasked with monitoring plants after we altered a variable in their environment. Our group decided to add sponges and paper towels (spowels) to see if the plant would last longer in a simulated drought.

Keeping neat data is important, and I forced my group to use a spreadsheet instead of a table (like everyone else). And lets just say, spreadsheets are very powerful.

Plant Growth Heatmap

Now that I’m looking at it, I probably should’ve put a label stating that we were measuring plant height in cm (it was seventh grade). 

But we were able to use conditional formatting to track the growth of each plant, but the coolest part was tracking the plant’s color. 

Plant color heatmap

We wanted to track the color of the plant to track it’s health, so we set up conditional formatting so the exact colors (recorded in PMS) of each plant were shown in each cell. 

I’ll just say that our final presentation was pretty cool. Remember, spreadsheets are always better than paper data tables.

About Us

SheetPointers was created by Andrew Lenart and Nikhil Radosevich 

Recent Posts

Follow Us

Weekly Tutorial

Sign up for our Newsletter

We’ll send you a few emails a month with cools tips and tricks. We will never send spam. Unsubscribe any time!