A what!!! A Square Bubble Heatmap Chart !!! What has the Bison been eating lately. Frankly .. I don’t really have a name for this chart. It is a bubble chart. The bubbles are square. And it’s a heatmap. So I’ll call it a Square Bubble Heatmap Chart or better still, S.B.H.C. for short 😎 .
The Basic Data Set For the Bubble Chart
Now let us assume for a moment that you are the owner of a multi-store food chain. You had four stores and all were doing rather well under your able leadership lately. The employees are well fed and are quite happy with the generous ESOP’s and the year-end bonus that you gave them. However, being ‘the management’, you would love to track the performance of key products across the stores. Here’s how the performance looked like at the end of the year.
You would like to classify the stores and products in to three categories –
1. The ones with a growth rate of less than 3% per year (Low Growth).
2. The second ones with a growth rate of greater than 3% but less than or equal to 7% per year (Average Growth).
3. And finally the steller performers with growth rates in excess of 7% (High Growth).
Preparing the data for the Chart
Let’s convert the data set into the three categories as mentioned above.
If you look at the above table, you will see that the last three columns contain values from the ‘Size Column’ based on the growth rate. If the growth rate is less than the first threshold (3%) then the first of the last three columns would contain the size and the other two would remain zero. The middle column would contain the size value when the growth rate is more than 3% but less than or equal to 7%. The last column would contain a values only if the growth rate is more than the threshold (7%) in this case.
So Let’s begin by making a Bubble Chart
You could pick up the bar charts and the lines to represent this data but then they have been used so often (and so mercilessly) that nearly half of the office (and the German Shepherd at the gate) could do it by now. So you decide to blow bubbles. Here’s the first bubble (chart).
Making the Square Bubble Heatmap Chart
Now in order to make the bubbles into squares, all you need to do is draw a few auto shapes and color them. I’ve picked up squares but there are a ton of other shapes available for use. Select the ‘Insert’ tab from the ribbon (or the ‘Autoshapes’ button from the Drawing toolbar) and create your own custom shapes. Once you have the custom shapes ready, copy them one by one and paste over the corresponding series. In our case, we pasted the Red ones over the ‘Low’ growth series, the Grey ones over the ‘Averages’ and the Green ones over the ‘High’. here’s how the chart looks like now.
Adding the Labels to the Bubble Chart
The first thing to do when you are tying to add labels to the chart is to ensure that they line up with the bubbles correctly. The store names should be aligned to the X-Axis and above each column and the product names along the Y-axis and to the left of each series. Here’s the table that does the trick for us.
Now one by one add each of these data points to the bubble chart shown above. Shown below is the chart when the first three data points have been added to it.
Turn On the Labels and Fix the Chart Axis
Let’s turn on the chart labels. Right click the points and select ‘Format Data Series’ -> ‘Data Labels’ and check ‘Series Name’. The labels for the horizontal series are aligned towards left and the ones for the vertical series are aligned towards top.
Once this is done, adjust the scales so that they start from 0 and end at the last category. This is more like sizing the chart so that the plot area shows only the intended categories and nothing else.
Now remove the extra entries (the ones that we used for the labels) from the legend by placing two slow clicks on each one and pressing the delete key.
The final step is to make the additional (label) plot markers disappear. Adjust the plot area so that the labels do not overlap any of the bubbles or the grid lines. Here’s the final “S.B.H.C.”
You can download a sample worksheet containing the square bubble heatmap chart here or click on the button below:
(Note: This doesn’t seem to work too well in Excel 2007 and later.)