Bubble Charts: How they are used

Hey Folks!!

Wishing you a very happy Sankranti. Hope you all had a good time in Dean's Lunch.

Bubble Charts! One of the most fascinating charts.

I guess Kanishk has already shared about what Bubble chart is all about and I believe that everyone has read it :-). So let me get straight into the action i.e how to use bubble chart in any business applications or in day to day life :-)

I would like to share few snapshots to show you all, how cool Bubble chart is all about.



Yeah the best GUI level graphs than can beat anything.

The easiest way to create bubble charts is to set them up in Excel, using the chart/bubble option :-).

One can then copy them directly into PowerPoint or a Word document.

In practice, creating a bubble chart is only slightly more complicated than creating a bar or line chart.

For example, Lets take anything in X-axis, lets take salary and the percent job growth in Y-axis i.e. into the second column, and the total employment size into the third column, and the location quotient into the fourth column.

 Then simply one needs to highlight the three first columns and subsequently plot the chart. 

If you want to design it or want to look it more flashy! Exactly to improve the GUI Level one can do it via macros in Excel. Various colors can be assigned to bubbles via macros. Animations can be added via macro.

One can add conditions. Like if the quotient exceed 1 or something, u can make it red, if less than that you can make it to different color through micros.

For additional details, analysts can also add text labels to highlight particular industries, if so desired.

Easy and Fascinating right?

Now lets see if  how to create multi series Bubble Charts in Excel


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:

Risk	Probability	Impact	Exposure
Foo	10	6	60
Bar	30	8	240
Baz	90	5	450
Spong	50	2	100

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"
        Exit Sub
    End If
    
    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
        With bubbleChart.chart.SeriesCollection.NewSeries
            .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)
        End With

    Next

    bubbleChart.chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    bubbleChart.chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & selection.Cells(1, 2).Address(External:=True)
    
    bubbleChart.chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    bubbleChart.chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & selection.Cells(1, 3).Address(External:=True)
    
    bubbleChart.chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
    bubbleChart.chart.Axes(xlCategory).MinimumScale = 0
End Sub

Links

http://en.wikipedia.org/wiki/Bubble_chart

http://code.google.com/apis/chart/interactive/docs/gallery/columnchart.html#Example

http://junkcharts.typepad.com/junk_charts/bubble_chart/

http://www.perceptualedge.com/articles/visual_business_intelligence/visualizing_change.pdf