With the recent release of version 8 we’re going to blog about a number of the new features, starting with how to create a heat map in Excel.
Here’s a fairly large table showing sales for thirty six products across twenty six US states:
There’s a lot of data here but it’s not giving us any helpful information as the table is too large to see any pattern or comparison.
A heat map could be a useful way to give a quick visual picture of the spread of the sales volume. Let’s add a simple heat map, new in version 8 of XLCubed.
Select the data area in the table, and then from the XLCubed ribbon select the InCell-Chart group, and heat map:
As we have already selected the data area to be charted this prompt is already showing the correct cell locations.
Choose the formula destination (where the formula controlling the chart will be located), and the Chart destination (where the top left cell in the chart area will be located).
We can now define the look of the heat map in the Chart Format dialog:
We have set the low and high colours to define a blue colour gradient.
Outlying values could potentially skew the chart so you have the option to exclude these by setting minimum and maximum values. Select the icon to use, squares in our case, and the number of steps or bands to split the range of values into.
We have pre-arranged the Excel cell sizes to be squares, and this is the resulting heat map:
You can now quickly assimilate the spread of values in a glance, and note the higher sales volumes in Maine, Michigan and Missouri for Road, Touring and Mountain Bikes.
To alter the formatting of the chart simply double click on any one of the squares in the heat map, or on the chart formula to bring up the formatting dialog.
If you are not already a user of XLCubed you can get started with an evaluation of XLCubed by going to our registration page.