The heat map in Google Sheets is one of the easiest ways to visualize your data. It’s perfect for identifying patterns, plus saves you the stress of having to map out separate graphs or charts.
Because it’s attractive and easy to use, it’ll appeal to a wide range of users, including logical and creative thinkers.
Google Sheets heat map shines light on the high and low values, to make it easy for even a kid to interpret.
The fun fact is that heat maps are surprisingly easy to create using Google Sheets formatting.
Check out this course to get started with Google Sheets today! – Link Here
What is a Heat Map?
A heat map is a graphical representation of data where values are depicted using different colors. A heat map is a two-dimensional representation of data that provides an immediate summary of complex data.
Most times, it’s used to interpret users’ performance on a website. As a website owner, you get to use heat maps to determine what users love in your website and sections they are less concerned about.
How to Create a Google Sheets Heat Map (Gradient)
With a few simple steps, you can transform your complex data into a colored heat map.
Business owners use heat maps to simplify sales records and see what periods of sales were high and low.
For this article, our illustration will be a company’s sales record for 1 year. And for easier interpretation, make use of bright colors for low sales months and dark colors for high-sales months.
Let’s get right into it.
1. Select Data
First, you’ll have to input your data into Google Sheets and highlight the cells you want to create a heat map for.
You’ll notice that looking at this Sheet is a bit boring and challenging to understand, as it’s just a bunch of figures scrambled.
As we stated earlier, a heat map helps to shine a light on all details, so you’ll have an easier time identifying the contrasts.
3. Go to Format
Just at the right top of your screen, you’ll see the “Format tab”, click on it and scroll down to select “Conditional Formatting”.
4. Click Add Another Rule
After selecting “Conditional Formatting” a new panel will open, and you’ll click on “Add Another Rule” just like the screenshot below.
5. Select Colours
The conditional format panel rules open up a tab for you to get creative and customize the heat map however you want.
To do this, you’ll have to click on the “Colour Scale” option and select colors for the Minpoint, Midpoint, and Maxpoint.
- Minpoint: This is the minimum point of your data, and it can either be a minimum value from your data, a number, a percent value or a percentile value.
- Midpoint: This reflects the middle point of your data, and it’s the same options as the Minpoint, but with an addition of the “None” option, in case you want to ignore this point.
- Maxpoint: It can be set to the maximum value from your data. So you can choose the maximum value, number, percent value or percentile value.
6. Default Options
If you are in the mood to get creative, you can go ahead and select your colors. But if you are trying to save time, note that Google Sheets has a default set of colors you can use.
Check out this article on how to make Histograms in Google Sheets.
Tap on “Default” and a list of color options will pop up on your screen. Make your selection and note that, the colors to the right interpret the high values, and the colors to the left interpret the low values.
Once you are satisfied with your color selection, click “Done”, and your Google Sheets Heat Map is ready.
Now, you might be wondering, “What if I don’t want multiple colors on my heat map?”. Well, you have the option to use a single color as well.
How to Make a Google Sheets Heat Map in a Single Colour
Before we begin, note that “a single color” implies different shades of a particular color, so you know how to differentiate the high and low values.
Learn more about the If Function in Google Sheets with this article.
For instance, if the figures 30 and 50 are in your data, they’ll both get highlighted in green; however because 50 is higher, it gets a lighter shade of green.
The process for creating a single-colored heat map is quite simple too. For this tutorial, we’d be using a heat map to interpret data that doesn’t involve gradient, but rather a threshold; where 0-200 is categorized as low and 201-500 is categorized as high.
- Highlight the Data
Highlight the selected data > Format tab > Conditional Formatting > Add Rule
- Select Single Color
Now, this is where the difference starts compared to the heat map illustrated above.
Click on “Single Color” and select “Format Rules” where you’ll see a drop-down of options. After that, put in the figure you want just below the “Greater Than” tab, in this case, it’s 201.
- Formatting Style
Click on the formatting style and choose the color you want applicable to the data you highlighted. Then click “Done” and your heat map is ready.
From the screenshot above, you can see that only values higher than 201 were highlighted. That’s what you call a single-color heat map.
Customization Tips for Heat Maps in Google Sheets
- Changing the value in a specific cell automatically updates the color scheme
- When customizing, it’s preferable to use numbers for the Minpoint and Maxpoint and set the Midpoint to the 50th percentile.
- The Minpoint, Midpoint, and Maxpoint also apply to the single-colored heat map.
More Examples of Heat Maps in Google Sheets
Heat maps are used for quite several data representations. There’s the table heat map, which we have discussed already; so we’ll do a quick tutorial on Calendar and Geographical heat map.
How to Create a Calendar Heat Map in Google Sheets
A calendar heat map is used to visualize time series data displayed over a conventional calendar year.
Here’s how to create one;
- Highlight Data
In Google Sheets, input the calendar details with accurate date, and highlight.
- Click on Format
After clicking format, select conditional formatting and click “add another rule”.
- Choose Colour Scale
Tap the color scale and choose the default preview to set the gradient. However, you can select the color to your preference, and tap “Done”.
That’s all, your calendar heat map is ready.
Sometimes, you might want to make the numbers inside the calendar invincible. To do that;
Highlight All Values > Format > Number > Custom Number Format. Then type in three semicolons and click Apply.
You should have something like the screenshot below once you select “Apply”
How to Create a Geospatial Heat Map in Google Sheets
The geographical heat map uses color to show how values differ according to location. For illustration, we’ll use a country and its GDP.
Note that the figures are not the actual GDP of mentioned countries.
Here’s how to create one;
- The trick is using a geo chart to create a heat map. So, select all the values and click Insert. The first column should contain the geographical location e.g. country, or region codes, and the second column should contain the numeric data.
- Click on the “Chart” and you’ll be directed to the chart editor.
- Click on the Chart editor sidebar, and you’ll see a list of options because a Geo chart will not be selected by default. Under the map section, select “Geo Chart”.
- Select Customise and click on Geo.
- Choose your preferred color. Note that you can also use one single color and create a gradient from it.
To make a Geo heat map with markers, follow the same steps. But, when selecting “Geo chart” under the map selection, select “Geo chart with markers” instead. It might take a few seconds to load fully, so be patient.
You’ll get something similar to the screenshot below;
Limitations of Using Google Sheets Heat Maps
Yes, heat maps provide a visual cue on how to interpret complex number data, but there are some restrictions attached to using it;
- If the viewers are color-blind, it may present a problem, as the data representation will not be understood.
- The resulting information is sometimes not grouped in a way that clearly shows patterns. And this might lead to inaccurate conclusions or clustering of data.
- A heat map is all about colors, so without proper attention to detail, you might make a mistake in the color scheme.
Conclusion
A heat map is just a graphical or visual representation of data with colors. The different colors represent values that clearly show if your data goals are met.
The heat map is dynamic, meaning a change in any number of cells automatically reflects in the color.
The fun thing about heat maps is that they can be made either with one color or a mixture of different colors, and whatever choice you make, your heat map will still reveal the extremities of your data.