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:

- Extract the series formula of the first series of the active chart (if no chart is selected, the program aborts).
- Determine the range containing the Y values.
- 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.
- Ask the user how large the largest bubble should be (the default is 40).
- 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
```