Categorized | excel chart

Square Bubble Heatmap Chart

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).


The corresponding series look something like this.

Making the Square Bubble Heatmap Chart


color-codesNow 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.


And here’s how it looks when all are done.

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 experiment with other color combinations as well.

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.)

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Fabrice wrote:

    In my version of S.B.H.C, I aligned the squares in the bottom left corner of a cell as their diagonal is longer than a side. Circles however are centered in the cell as the have identical radius in all directions

    Bonavista had a related post on their blog.

  2. Ajay wrote:

    @Fabrice – Cool. They look neat. Since bubbles are centered by default and this approach uses pseudo bubbles, the centering problem doesn’t arise here.

  3. Ajay wrote:

    @Fabrice – I tried creating a horizontal bar chart and got “An error occurred : 13 – Type mismatch” error. I also got the Application-defined or object defined error when I tried the vertical bar chart. What could I be doing wrong?

  4. mickey wrote:

    You cannot imagine how much I love your blog and suggestions. I was so sick of the usual charts and this is just awesome!

  5. Ajay wrote:

    @mickey – Thanks. My thoughts exactly !

  6. David wrote:

    This is so spectacular. It makes my boring data look like a Piet Mondrian art piece!

  7. Dan wrote:

    Too bad this doesn’t seem to work in ’07

  8. Dan wrote:

    Hey, after I posted my message I found this for Excel 07 and later users.

  9. PR wrote:

    Thank you for all your efforts – Make a life a lot more easy and I love the results and yes, everytime someone compliments me, I refer them to you!


  10. Daniel wrote:

    Dear all,

    superb chart, I like the squares best.

    2 questions to apply/enhance:

    1. is there a workaround for changing the shape of bubbles in Excel 2007+ yet?
    Any pictures for the filling in round buttons get distorted. :(

    2. is there a way to format colours according to continuous color scales, like e.g. the preset ones in conditional cell-formatting?
    I think I saw this in surface charts, were high values were intense green and low values were intense red. Maybe these weren’t made in excel …

    Thank you for any answers and best regards


  11. Pete wrote:

    Bit ridiculous copy/pasting actual graphic shapes onto the chart. Makes the whole thing so manual, you might as well just draw the graph manually anyway, no real need for the graph object.

  1. An Excel Chart’s Journey Across Versions | Excel & VBA - da Tab Is On wrote:

    […] to something like 409 pixels in Excel 2010). (Apparently the same treatment is meted out to a few other charts as […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel