Bubble Charts are a great way to visualise data that has three numerical values for each point. Two of the values are plotted on the X and Y axis, while the other is represented via the diameter of the bubble.
Excel also supports Bubble Charts with more than one data series – this is useful if you want to label each bubble. However I’ve found that the Excel team’s collective brain operates in an entirely different way to mine, resulting in some very strange interpretations of my data.
For example, here’s a simple table with 4 data series, each containing 3 values:
To me, this looks like a perfect range of data from which to create a Bubble Chart. However if you select the range and choose Insert > Charts > Other Charts > Bubble (Excel 2010 menus, may differ in other versions) you’ll get this monstrosity:
It’s possible to get more sensible results by adding each series one-by-one, but this is time consuming and useless if the number of series changes over time. There may be some way of structuring the source data to appease Excel, but I since I needed to create a bunch of charts and already had my data in this format I decided to brush off my VBA skills and teach Excel my way of doing things. The result from the same data is much more pleasing:
The VBA to achieve this is below. Note that the macro requires that you select a range with exactly 4 columns and at least 2 rows (a header row and at least one data row) before executing it. It’s a bit quick and dirty, and styles the graphs according to my personal requirements, but hopefully it will be of use to others too.
Public Sub CreateMultiSeriesBubbleChart()
If (selection.Columns.Count <> 4 Or selection.Rows.Count < 3) Then
MsgBox "Selection must have 4 columns and at least 2 rows"
Dim bubbleChart As ChartObject
Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=selection.Left, Width:=600, Top:=selection.Top, Height:=400)
bubbleChart.chart.ChartType = xlBubble
Dim r As Integer
For r = 2 To selection.Rows.Count
.Name = "=" & selection.Cells(r, 1).Address(External:=True)
.XValues = selection.Cells(r, 2).Address(External:=True)
.Values = selection.Cells(r, 3).Address(External:=True)
.BubbleSizes = selection.Cells(r, 4).Address(External:=True)
bubbleChart.chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & selection.Cells(1, 2).Address(External:=True)
bubbleChart.chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & selection.Cells(1, 3).Address(External:=True)
bubbleChart.chart.Axes(xlCategory).MinimumScale = 0
I can see that you provided with an excellent representation chart, dedicated for a very vital factor in project management (Risks).
Actually I expected to face couple of issues, but it worked wonderfully.
Next, I'll share this article with Excel Lovers Group (if you don't mind of course).
Thanks Ahmad - share away!
Many thanks for your generous gift of this code. A quick question: How would I amend the code if I have multiple data points that share the same name? For example, let's say multiple data points that correspond to a smaller number of regions in a country.