Heat Map In Google Sheets

What Is Heat Map In Google Sheets?

Heat Map in Google Sheets is a graphical representation of the data that analyzes its traffic in a particular time frame and where a range of values are represented by different or gradient colors. When we say different colors, the scale is from dark to light such as red, yellow, and green.

The Google Sheets Heat Map is a typical visualization technique vastly used by market analysts, scientists, etc. Some of the typical Heat Map representations are:

1. The day-wise temperature of a particular city.
2. Seasonal rainfall of a particular region.
3. Monthly Sales data of a particular region.

For example, the following image is a prime example of a Heat Map Google Sheets.

As we can see, we have different color shades that help us to compare the value of cells quickly. Visually we can notice that the highest-value cells are in green color with a range of dark to light, and the lowest-value cells are filled with red color and the cells with middle value are in yellow color.

Explained – Heat Map In Google Sheets

Let us understand when and why to use a Google Sheets Heat Map.

#When to use a Heat Map?

We will consider a couple of analysis where the Heat Map is necessary.

• Cricket Analysis –

For instance, a cricket analyst’s responsibility is to showcase the team about the opposition team’s strengths and weaknesses through data analysis. When analyzing the opposition’s impact and the bowler’s strength, he must understand what length and line that particular bowler is pitching the ball to cause problems for the batsmen.

The following Heat Map image shows the bowler’s consistent line and length.

When we look at the above Heat Map image, a bowler consistently bowled in the fuller length ball (indicated in red) and rarely in the short length.

• Population Analysis –

When we analyze the country-wise population, a Heat Map will convey the message to the end-users with simple colors. The following image is an example of a country-wide population.

#Why we must use a Heat Map?

1. To understand the patterns of consumer interest in a specific region or area.
2. By understanding the peak hours of the sales companies, we can plan their marketing activity and roll out offers to the consumers during non-peak hours.
3. Communication of key areas of sales volume and making the territory managers understand the trend in their respective regions.
4. Capitalizing on seasonal sales trends to make most of the opportunities available in the market.

How To Create A Heat Map In Google Sheets?

In Google Sheets or Microsoft Excel, we do not have a built-in Heat Map. However, by using formatting techniques, we can create a Heat Map in Google Sheets.

The best way to create a Google Sheets Heat Map is by using conditional formatting, as follows:

First, choose the dataset à select the “Format” tab à click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.

Next, select the “Color Scale” tab and under the “Format rules” section,

1. First, under the Minpoint section, click the “Min value” drop-down and select the required option, enter the values in the field beside it and select the desired color.
2. Next, under the Midpoint section, click the “None” drop-down and select the required option, enter the values in the field beside it and select the desired color.
3. Next, under the Maxpoint section, click the “Max value” drop-down and select the required option, enter the values in the field beside it and select the desired color.
4. Finally, click the “Done” option to save the settings, as shown below.

Examples

Let us consider some examples to understand Heat Map in Google Sheets.

Example #1 – Existing Color Scale –

We will create a Heat Map for the following dataset that consists of the city-wise monthly sales data using the already existing color scale default variants.

The steps to create a Heat Map using conditional formatting are as follows.

Step 1: Select the cell range B2:G5 – select the “Format” tab – click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.

Step 2: Select the “Color Scale” tab. Under the “Format rules”, click the “Choose Default Format” option under the “Preview” section, as shown below.

Step 3: Select the “Green to yellow to red” or the “Green – Yellow – Red” format, from the available pre-defined color scale and click the “Done” button, as shown below.

Automatically, we get the final output, i.e. the generated Heat Map with existing color scale options along with the default value selections, as shown below.

As we see, all the lowest values, (>40) are in green color (dark to light green), the middle values are in yellow color (49 to 40) and all the highest values are in red color (77 to 50).

Example #2 – Create Heat Map With Custom Color Scale –

We will create a Heat Map using the Custom Color Scale for the following table showing the temperature data for a city in the previous year.

The steps to create Heat Map for the above temperature data are as follows:

Step 1: Select the cell range B2:M4 – select the “Format” tab – click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.

Step 2: Here, we will choose the color scale of “Red – Yellow – Green”. Because the higher the temperature, the more dangerous it is. So, we must color the highest in red, middle values in yellow, and lowest values in green.

Therefore, Select the “Color Scale” tab and under the “Format rules” section,

1. First, under the Minpoint section, keep the “Min value” selected in the drop-down and select “Green” color.
2. Next, under the Midpoint section, click the “None” drop-down and select the “Percentile” option, enter the value 50 in the field beside it and select “Yellow” color.
3. Next, under the Maxpoint section, keep the “Max value” selected in the drop-down and select “Red” color.
4. Finally, click the “Done” option to save the settings, as shown below.

We will get the default Heat Map like the following one.

However, this isn’t the best Heat Map yet. Because anything greater than 27 should be in dark red rather than shading red and anything below 20 should be in dark green rather than shading green.

Step 3: Therefore, click the existing set format rule to enter new values with specific conditions, instead of the default ones. In other words, let us customize as per our requirements.

Step 4: Update the data values as follows:

1. First, under the Minpoint section, click the “Min value” drop-down and select the “Number” option, enter the value as 20 in the field beside it and leave the color selection as “Green” itself.
2. Next, under the Midpoint section, do not do any changes.
3. Next, under the Maxpoint section, click the “Max value” drop-down and select the “Number” option, enter the value as 27 in the field beside it and leave the color selection as “Red” itself.
4. Finally, click the “Done” option to save the settings, as shown below.

We get the updated Heat Map, as shown below.

The difference between a default Heat Map and a customized Heat Map is that the temperature of anything above 27 is filled with dark red color, while the default one was raining the red from dark to light red. Similarly, the dark green denotes a temperature of less than 20.

Example #3

The following data shows the customers visiting the retail shop on an hourly basis throughout the week. We will create a Heat Map to check when are the peak hours when we can expect a huge crowd.

The steps to create a Heat Map to check for the peak shopping hours are as follows:

Step 1: Select the cell range B2:H14 – select the “Format” tab – click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.

Step 2: Select the “Color Scale” tab and under the “Format rules” section,

1. First, under the Minpoint section, click the “Min value” drop-down and select the “Number” option, enter the value as 100 in the field beside it and select “Green” color.
2. Next, under the Midpoint section, click the “None” drop-down and select the “Number” option, enter the value as 500 in the field beside it and select “Yellow” color.
3. Next, under the Maxpoint section, click the “Max value” drop-down and select the “Number” option, enter the value as 501 in the field beside it and select “Red” color.
4. Finally, click the “Done” option to save the settings, as shown below.

The generated Heat Map is shown below.

When we view the map, we can immediately understand that the peak hours of shopping on all the days of the week, is around 11:00am to 7:00pm. We can expect least customers in the early hours of 9:00am to 10:00am and in the late hours of 8:00pm to 9:00pm.

Important Things To Note

1. A Heat Map is not a built-in chart in Google Sheets.
2. Conditional formatting will be removed if we copy and paste the non-conditional formatting cells onto the already formatted cells.
3. Conditional formatting is volatile in nature. Hence, it will not be applicable for any additional rows or columns to the data table. Therefore, whenever there is a change in the worksheet, we must recalculate.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Heat Map in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to Heat Map in Google Sheets. We create a map to display high-low values of data & display in gradient colors with examples and a work template. You can learn more from the following articles –

Logical Test In Google Sheets

Tables In Google Sheets

SUMIF Not Blank In Google Sheets