How to Create a Heatmap in Google Sheets

In this tutorial, I will try to teach you how you can create a heatmap in Google Sheets!

Let’s start!

Heatmaps: what they are?

Now, what are heatmaps? Before you can learn how to create one; it is important that you understand what they are and, the benefits of using them in your spreadsheet.

Heatmaps are basically a feature in Google Sheets, which add context to your data by providing a visual reference. This way, you can get an analytical representation of your data; moreover, useful insights as well.

How to Create a Heatmap in Google Sheets

Using a heatmap can save you time; as it offers you a visually informative picture at first glance, so, you have an initial idea of what your data is representing and thus, sets you up for you to study it in further detail.

Heatmaps offer a visual emphasis to your dataset; which can make your work easier and, the detailed analysis much less tedious.

Heatmaps: How to create?

Heatmaps are especially well-suited for information which has data both in the rows and the columns. For instance, I will use an example, where I have a list of students and their respective test scores.

Steps to create a heatmap:

1. Let’s take the data shown below as my example.

create a heatmap step 1 with example data sheet

To look at this data at first can be a bit frustrating since it requires a good amount of time to analyze and understand. This is where heatmaps can come in handy. If I format this data with a heatmap, then, any viewer can be able to conduct a basic analysis at first glance.

This is how you can achieve this:

Firstly, select all of the data (or, all of the relevant cells) by clicking on them. Once selected, find the “Format” option from the menu at the top of your spreadsheet and click on it. From the drop-down menu, click on the “Conditional Formatting” feature.

After selecting cell goto Conditional Formatting option from menue Format option

2. From the dialogue box which will pop up, click on the “Color scale” option.

In the pop-up box of Conditional Format Rule go-to Color scale tab

There are two options you have here:

You can either work with the default color schemes or, create your own color scheme.

The default color schemes

The colors depict the values as going from lower to higher in the direction; left to right. In my example, low values are concerning and high values are acceptable. Therefore, I will choose the color combination of dark red-light red-white. You can see this combination given in the top right corner.

Results after choosing the color combination of dark red-light red-white

As you can see now, this new format has given depth and context to my data and anyone can understand the scenario at first glance. In this way, analyzing the information provided will be much less time-consuming and less boring.

Creating Your Custom Color Scheme

If you wanted to use your own scheme of colors to format the data, then you can just select the colors for the minimum values, maximum values and, the average values (midpoint). These options are available right below the default option.

Creating Custom Color Scheme
  • Min value: The Min value can be adjusted to Min value, Number, Percent and, Percentile.
  • Mid value: The Mid value can be adjusted to Mid value, Number, Percent and, Percentile.
  • Max value: The Max value can be adjusted to Max value, Number, Percent and, Percentile.

You can take your time to decide on the colors for each of the values. You can either choose the colors from the set of colors already provided or, fashion your own color by doing the following steps:

  1. Click on the icon which looks like a can of color, which is given beside each menu for each value.
  2. From the drop-down menu which features a color palette, click on the “+” icon given under “Cusotm”.
Select the Custom Colors

Closing Thoughts

Well, I hope my article on how you can create a heatmap in Google Sheets was helpful for you.

Best of luck!

Read More Tutorial Articles On:

How to Filter in Multiple Columns in Google Sheets

How to Group a Pivot Table by Month in Google Sheets

Floor Function in Google Sheets

How to Underline and Double Underline in Google Sheets

How to Use the Find and Replace Function in Google Sheets

Was this article helpful?
YesNo


Related More Articles

Leave a Comment