Microsoft Excel: Create A “Heat Map” in Excel Using Conditional Formatting

It’s Easier Than You Think…And It’s Compelling!

Overview

I’m working on a data analytics presentation using a dataset of the Dow Jones Index DAILY performance (this is a hot topic lately, so might as well grab the data for analysis…).

The common “process” for data analytics that I frequently see is “ELTR”, which stands for:

• Extract
• Transform
• Report

This post will focus on a simple “heat map” REPORT. Note that all the work of Extract, Transform and Load has been done before this. Reporting starts the fun part of data analytics. :-)

Excel “heat map” visualizations are popular for certain data sets. The performance of the DOW over a historical time period is one of those data “fits”. So, let’s explore how that visualization is created — fortunately Excel makes it pretty straightforward and simple.

What’s the Question?

The question? that this answers is “What is the volatility of the Dow Index performance over time?”

Here is what I calculated / summarized out of the data:

• Grouping data by decade (1970’s through 2010's),
• Using a pivot table, I showed the Standard Deviation (measure of volatility) of the Dow 30 % Change peformance for each month of each decade

It appears like this:

Let’s Do This

As you can see, it’s not too informative in this format, so I want to format it as a “heat map” so that outliers stand out with a quick glance of the report.

Apply Conditional formating

• Highlight the data range and select “Home → Conditional Formatting”
• Select the “Color Scales” option
• I want the format that shows the MOST volatile (highest standard deviation) to be highlighted RED. As you “hover” over the choice, the table formatting changes. I have the one I want!
• Select that option and here is your report view now.
• Click the drop-down menu that appears, and select the option for “All cells showing “StdDev of %Chg” values”.

The report now looks like this:

Remove the Numbers — Meaningless for this Report

Ok, we’re getting there, but there is one more formatting step. The % values are really meaningless as far as this visual is concerned, so let’s remove them.

• Highlight the range again, right-click the mouse and select format cells
• Select “Custom” format and type “;;;” (3 semi-colons) into the “Type” field. This will hide the values from the report.
• Final report — no numbers and the report “tells the story”.

Now, Can I Modify The Report?

How easy is it to modify the report? Easy!

Let’s say I want to look at the report, by YEAR (rather than decade), for the years 2000 forward…

• Insert a Pivot Table Slicer for “Decade?” — this allows me to easily select just the decades I want.
• Add Year as a Pivot Table row field.

Now it looks like this. And the user has the flexibility to quickly get a report on any decade they want.

Can you apply this approach to any of your reporting? Let me know in the comments!