Creating Heat Maps In Excel

When it comes to understanding large amounts of information, visuals are considered a better option than textual data.

From making comparisons to using values for decision-making, visual images help professionals make sense of even the most complex situations.

Microsoft Excel offers an extensive range of these graphic elements that enable you to transform piles and piles of boring data into captivating images.

However, the majority of these illustrations are essentially diagrams, losing the essence of the original data.

Heat maps are an excellent solution in this case, distinguishing important figures through vibrant colours without surrendering their actual data structure.

If that’s what you’re looking for, here’s our guide to creating heat maps in Excel and their significance in data analysis.

Or you can have a look at our Excel training courses where you’ll learn about heat maps and much more with the help of our expert trainers.

Understanding Heat Maps

Heat maps are a visual representation of numeric data where different colours highlight extreme, low, or mid values from a data set.

Compared to a standard data analysis report where each block of information is presented similarly, heat maps make it much easier to visualise and understand the statistics.

Scientists, marketers, and data analysts are among the professionals who use heat maps to discover generic patterns or analyse preliminary data.

Here are a few real-world examples where an Excel heat map may be handy:

Air temperature heat map – Displays data concerning air temperature in a specific region.
Risk management heat map – Presents different risks and their side effects in a concise way.
Geographical heat map – Showcases numeric data for different regions of a geographical area with different shades.

Creating A Heat Map In Excel

Excel Heat Map With Conditional Formatting

The easiest way to create a heat map in Excel is through conditional formatting. To demonstrate how this works, we’ll use the following sheet displaying the sales figures of a florist from the different zones of the country in a single year.

To create an Excel heat map, follow the steps below:

- Select the numerical values you want to display in the heat map.

- Head to Home Tab > Styles > Conditional Formatting > Color Scales (There are six distinct colour scales representing the colour schemes for your heat map)

- After you select the colour palette of your choice, its shades will be applied to the cells you had selected according to their distinct values, and you’ll get a heat map as presented below:

- You can also create an Excel heat map without any numbers. To do this, select the heat map again and press Ctrl + 1. This will take you to the format cells dialogue box.
- From the Numer Tab, go to Custom, and under Type, enter “;;;” then hit OK.

- Your new heat map will present data like this:

Dynamic Heat Map In Excel

You may not always want the coloured cells in your sales spreadsheet. To solve this, Excel allows you to create a dynamic heat map where you can hide and show the heat map effects according to your preference.

Here’s how you can create a dynamic heat map:

- Start by adding a checkbox in Excel. Navigate to the Developer Tab > Insert > Form Controls > Checkbox.

- Select and drag the checkbox to your desired cell. Then right-click on it and go to Format Control > Control Tab. In the Cell Link space, add the cell address where you want the linked message of the checkbox to appear. In this case, we chose cell N2.

- Next, select the entire data set and head to Conditional Formatting > Color Scales > More Rules to create a custom colour scale for the figures.

- In the Formatting rule dialogue box, head to the Format Style section to unveil its drop-down list and choose the 3-Color Scale.

- From the Minimum, Midpoint, and Maximum drop-down lists, tap on Formula.

- Based on your data points, it’s time to add values in the Value bars so that the heatmap highlights them accurately. Here is an example:
  - Minimum: =IF($N$2=TRUE, MIN($B$2:$M$5), FALSE)
  - Midpoint: =IF($N$2=TRUE, AVERAGE($B$2:$M$5), FALSE)
  - Maximum: =IF($N$2=TRUE, MAX($B$2:$M$5), FALSE)

These formulas command Excel to highlight the lowest, middle, and highest values in your data chart (B2:M5) when the cell linked to your checkbox (N2) is TRUE with the help of MAXIMUM, MINIMUM, and AVERAGE functions.

This means that the moment you remove the check mark from the box and the message turns to FALSE, the different shades vanish, leaving behind the original structure of the data.

- Before hitting OK, select a custom colour scale from the Color drop-down list, and you’re good to go.

- Here’s a preview of what happens to a dynamic heat map when the linked cell (N2) changes from TRUE to FALSE.

The Value Of Heat Maps

Heat maps in Excel enable you to discern key insights effectively and deliver them to your team, stakeholders, or managers for prompt and growth-focused decision-making.

Whether it’s the performance of your marketing campaign that you’re analysing, or the trends in the sales data for the year that you’re identifying, heatmaps have the ability to convert mind-boggling data into colour-coded and easily digestible information.

Pros And Cons Of Heat Maps

Pros

- Quick data interpretation – Instead of drowning in a sea of numbers and figures, Excel heat maps compartmentalise your data so data analysts can interpret them conveniently.

- Observing trends and patterns – In their raw form, all figures look alike. With a heat map, you can see months that experienced high sales, indicating peak shopping periods or patterns that can help shape your marketing campaigns.

- Enhancing presentation – Heat maps in Excel turn dull reports into a visual representation of colours without losing their crux.

Cons

- Performance issues – If you’re working with an extremely large data set, understanding and working with heatmaps can get nerve-wracking, resulting in slower performance.

- Dependence on colour schemes – Colour schemes are at the core of a heatmap’s effectiveness in data analysis. This can be challenging for individuals with visual deficiencies.

Troubleshooting Common Heat Map Issues

Not all Microsoft Excel versions are the same, so it’s natural that our desktop looks different from yours. Additionally, trivial errors are also possible when so many instructions are involved.

Here, we’ll discuss a few issues we faced so you don’t get stuck if they arise for you, too.

Missing Developer Tab in Excel: If your Excel does not have a Developer Tab, simply go to File > Options > Customise Ribbon and click the Developer checkbox under Main Tabs.
Formula Error: While we would advise you to copy and paste the formula for minimum, maximum, and medium values provided above, if you wish to add them manually, remember to avoid unnecessary spaces.
Checkbox Position: To change the position of the checkbox in Excel, you have to first click on it to enable editing. Only then will you be able to drag it to your desired location.

Conclusion

Imagine looking at a data set revealing endless rows of numbers and being unable to differentiate between the highest or lowest values.

To do this, you start going row by row, highlighting cell values individually, only to find out it will take you forever to finish this project.

With heatmaps, we can help you complete it within seconds. Data analysis and complex decision-making are now easier and simpler than ever!