Bubble Chart with Text inside the bubble

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

1. Open a new excel workbook, 
2. Press Alt F11. This takes you to Microsoft Visual Basic Editor
3. Copy the below VBA code from "Public Sub... End Sub" and paste it into the above opened window
4. Close the VBA editor.
5. In the workbook, go to Insert -> Shapes -> Select a Rectangle. Draw a rectangle on your sheet. 
6. 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. 
7. 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.
8. 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