Bubble Charts

Someone in the Excel Reddit was asking about making a combination chart by adding a scatter chart series to a bubble chart. He wanted to draw a customized trendline, and you can’t connect points with lines in a bubble chart. You can add a trendline to a bubble chart, but you are limited to the standard options.

But you can create a “scatter bubble chart” if you adjust the size of the markers in a scatter chart. It can be a bit tedious to do this manually: you need to figure out how large to make each marker (and as Barbie once famously said, “Math is hard”) and then you have to format each one.

Fortunately, it takes a fairly simple VBA procedure to turn a scatter chart into a bubble chart. The code at the end of this post does the following tasks:

1. Extract the series formula of the first series of the active chart (if no chart is selected, the program aborts).
2. Determine the range containing the Y values.
3. Find the range containing the bubble values. If Y values are in a column, the program uses the next column; if Y values are in a row, it uses the next row.
4. Ask the user how large the largest bubble should be (the default is 40).
5. Loop through the points in the series, calculating the size of the marker. Markers are scaled based on the square root of the bubble values, so their areas are proportional.

Here is an Excel bubble chart made with a simple three-column data set. the first column contains the X values, the second contains the Y values, and the third contains the Z values that set the bubble size.

Here is an Excel scatter chart made using the first two columns of the range.

Press Alt+F8 to open the Macro dialog, select the macro ScatterBubble, and click Run.

The macro asks how large the largest bubble should be.

Finally, here is the scatter chart with resized markers.

Nearly indistinguishable from the bubble chart shown above. I used a largest bubble size of 37 to make it match so closely.

Here is the VBA procedure ScatterBubble which created the Scatter Bubble Chart above.

Sub ScatterBubble()
  ' convert a scatter chart into a scatter bubble chart
  Dim sFmla As String, sYvals As String
  Dim vFmla As Variant
  Dim rYvals As Range
  Dim rBubbles As Range
  Dim iPt As Long
  Dim Zmax As Double, Zmin As Double, Bmax As Variant
  
  Const BubbleMaxDefault As Double = 40
      
  If ActiveChart Is Nothing Then
    Exit Sub
  End If
      
  ' extract Y value range from series formula
  sFmla = ActiveChart.SeriesCollection(1).Formula
  vFmla = Split(sFmla, ",")
  sYvals = vFmla(LBound(vFmla) + 2)
  Set rYvals = Range(sYvals)
  
  ' determine bubble size data range
  If rYvals.Rows.Count > 1 Then
    ' multiple rows, must be one column -> use next column
    Set rBubbles = rYvals.Offset(, 1)
  ElseIf rYvals.Columns.Count > 1 Then
    ' multiple columns, must be one row -> use next row
    Set rBubbles = rYvals.Offset(1)
  Else
    ' one cell only -> indeterminate
    MsgBox "Cannot determine the bubble size data range.", _
      vbOKOnly + vbCritical
    GoTo ExitSub
  End If
  
  ' validate
  If WorksheetFunction.Count(rBubbles) <> _
    WorksheetFunction.Count(rYvals) Then
    ' inconsistent data
    MsgBox "The bubble size data is not consistent with the Y values data.", _
      vbOKOnly + vbCritical
    GoTo ExitSub
  End If
        
  Zmax = WorksheetFunction.Max(rBubbles)
  Zmin = WorksheetFunction.Min(rBubbles)
  If Zmin <= 0 Then
    ' can't have negative diameter bubbles
    MsgBox "The bubble size data range contains negative values. " _
      & "Bubble sizes cannot be negative.", vbOKOnly + vbCritical
    GoTo ExitSub
  End If
  
  ' ask user for largest bubble size
  Bmax = Application.InputBox("What is the largest bubble size (max = 72 points)?", _
    "Bubble Size", BubbleMaxDefault, , , , , 1)
  
  If TypeName(Bmax) = "Boolean" Then
    ' user canceled
    GoTo ExitSub
  End If
  
  Bmax = Val(Bmax) ' make sure it's numeric
  If Bmax < 0 Then
    ' can't have negative diameter bubbles
    MsgBox "The maximum bubble size must be positive.", vbOKOnly + vbCritical
    GoTo ExitSub
  ElseIf Bmax > 72 Then
    MsgBox "The maximum bubble size is 72 points.", vbOKOnly + vbInformation
    Bmax = 72
  End If
  
  ' loop through and resize markers
  For iPt = 1 To ActiveChart.SeriesCollection(1).Points.Count
    ' scale area of bubble to area of largest bubble
    ActiveChart.SeriesCollection(1).Points(iPt).MarkerSize = _
      Sqr(rBubbles.Cells(iPt) / Zmax) * Bmax
  Next iPt
      
ExitSub:
End Sub