Useful under-utilized Excel Features



3D Analysis in Excel

Excel has always been a very popular and powerful business tool.

One of the features of Excel that is easy to use and is frequently under-utilized is the Excel Bubble chart.

The power of the Bubble chart is that it allows us to present data analysis in three dimensions and can be significantly more informative than visually trying to aggregate multiple two-dimensional charts in the mind’s eye.

By way of example, we are all familiar with customer surveys where we ask our customers to rate our performance under a number of headings such as quality, customer service, etc. The results are usually informative and useful.

We can add a second dimension by asking to customer to also rate the factors in terms of their importance to him/her. By plotting the data in two dimensions we can now see how our performance is rated for the factors that are most important, are therefore most significant, to the customer.

However, customers’ approach to rating can vary from those who give everybody a 5 out of 5 to those who are much conservative and only rate above 3 out of 5 in exceptional circumstances. “If I gave somebody a 5 there would be no room for improvement”

Asking the customer to rate you on how you compare to competitors and other suppliers provides an additional insight into how to interpret the ratings he/she had given you.

For most of us the goal would be to be rated very highly for those factors that are most important to the customer and to be perceived to be much better than our competitors.

An Excel Bubble chart allows you to analyse the results in 3D; the X-axis being used for the rating you receive for the factor, Y-axis for the rating of the importance of the factor and the size of the Bubble indicating how you compare with the competition.

A low X value, a high Y value and a small Bubble spells trouble and indicates that you have a lot of work to do on this factor as you are rating as being poor for a factor that the customer considers important and you are perceived to be much worse that your competitors.

A “picture being worth a 1,000 words” the chart allows us to quickly hone in on the factors where we need to make improvement most urgently.

There are many other examples where a Bubble chart can prove useful.

In the case of a SWOT analysis the X-axis might indicate the size of the SWOT factor (Strength, Weakness, etc.) the Y-axis indicating the immediate or short-term importance/impact of the factor and the Bubble size indicating the more long-term strategic importance/impact of the factor.

The SWOT analysis example can be seen on the Business Tools Store together with other examples for Ansoff matrix, Boston Consulting Group (BCG) chart and GE/McKinsey 9-cell matrix.

18th Oct 2013 Patrick Divilly, The Business Tools Store

Recent Posts