Bubble chart is a concise and crisp way of presenting data with 3 dimensions. However the main challenge of creating a bubble chart is; 'to have the text (names/ regions/ countries/ products) within the bubble'
Assuming we have 4 people data of Age, Weight and Height. The data is as below
| Age | Weight | Height |
| John | 28 | 70 | 7 |
| Michael | 50 | 34 | 6 |
| Vicky | 34 | 60 | 8 |
| Neil | 15 | 27 | 10 |
If we create a bubble chart by selecting this data we get something like below
 |
| Basic bubble chart with data labels |
It is very difficult to determine which bubble belongs to which person. And to enter the persons name into the bubble charts, there is no automatic way in excel. Depending on the number of rows, this task can be very tedious and time consuming.
Thankfully, the bubble chart VBA code (provided at the end) can do this in a click. Follow the below steps
- Open a new excel workbook,
- Press Alt F11. This takes you to Microsoft Visual Basic Editor
- Copy the below VBA code from "Public Sub... End Sub" and paste it into the above opened window
- Close the VBA editor.
- In the workbook, go to Insert -> Shapes -> Select a Rectangle. Draw a rectangle on your sheet.
- Right click the rectangle, and click Assign Macro. From the list, select a row which ends with CreateBubbleChart2. This makes the rectangle shape a button. Try clicking it. It should say 'Selection must have 4 columns and at least 2 rows'. If you see this message, Bingo !! its working.
- To test this, copy paste the table provided above. Select the data (4 columns and 5 rows) and click the rectangle. This should create a bubble chart as below in a new sheet.
- When you close, make sure you Save As 'Excel Macro-Enabled Workbook' (this selection can be found just below the space where you enter file name.
 |
| Bubble chart created using the VBA code below |
Hope this helps.
-Ramada
PS: please feel free to drop you excel/ word/ power point queries to mad.exceltips@gmail.com. I will try and respond as soon as I can. The more curious your question, the faster I respond.
Public Sub CreateBubbleChart2()
If (Selection.Columns.Count <> 4 Or Selection.Rows.Count < 3) Then
MsgBox "Selection must have 4 columns and at least 2 rows"
Exit Sub
End If
Dim SelectedRange As Range
Set SelectedRange = Selection
Sheets.Add After:=Sheets(Sheets.Count)
Dim bubbleChart As ChartObject
Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=1, Width:=600, Top:=1, Height:=400)
bubbleChart.Chart.ChartType = xlBubble
Dim r As Integer
For r = 2 To SelectedRange.Rows.Count
With bubbleChart.Chart.SeriesCollection.NewSeries
.Name = "=" & SelectedRange.Cells(r, 1).Address(External:=True)
.XValues = SelectedRange.Cells(r, 2).Address(External:=True)
.Values = SelectedRange.Cells(r, 3).Address(External:=True)
.BubbleSizes = SelectedRange.Cells(r, 4).Address(External:=True)
End With
Next
bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & SelectedRange.Cells(1, 2).Address(External:=True)
bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & SelectedRange.Cells(1, 3).Address(External:=True)
bubbleChart.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
bubbleChart.Chart.Axes(xlCategory).MinimumScale = 0
ActiveSheet.ChartObjects(1).Activate
Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
srs.ApplyDataLabels AutoText:=True, LegendKey:=False, _
ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
srs.DataLabels.Position = xlLabelPositionCenter
Next srs
ActiveChart.SetElement (msoElementLegendNone)
End Sub
