x
Solved

# Bubble Chart or Scatter Plot

Posted on 2013-11-08
Medium Priority
442 Views
I am trying to create a graph that would show revenue on the x axis and time spent on the Y axis and then the dots would grow in size based on revenue and I also want the color of the dot to change based on satisfaction level rating.  Here is the some sample date

Customer      Revenue         time Hr             satisfaction
Cust A      25000            10                       5
Cust B      17500            5.5                       3
Cust C      7000             8                       2
Cust D      12000             9                       4

The dot would be red if it was 1 or 2, Yellow if it was 3 and Green if it is 4 or 5.

Thanks,
Montrof
0
Question by:montrof
• 3
• 2

LVL 25

Assisted Solution

Ejgil Hedegaard earned 1000 total points
ID: 39634740
The values must be arranged different for a bubble chart.
3 values each series, X-axis (Revenue), Y-axis (Time) and Bubble size (set to Revenue/1000).
Changing chart series colours (Satisfaction) is done with VBA.
Try attached sheet, change some values, and press the button to set the colours.
Bubble-Chart-colours.xlsm
0

LVL 81

Expert Comment

ID: 39635155
It seems odd to use Revenue for both the X-axis and the size of the bubble.

It would be much more normal to put the Customer names on the Category axis, with Time on the Y-axis, Revenue for bubble size and color for satisfaction.

If you don't care about customer names, then you could put Revenue on the X-axis, Time on the Y-axis and Satisfaction as bubble size.
0

LVL 25

Expert Comment

ID: 39635755
Is it possible to use names on the Category axis?

To my knowledge, a bubble chart is like an X-Y scatter chart, and only values can be used for X, Y and bubble size.
0

LVL 81

Accepted Solution

byundt earned 1000 total points
ID: 39637794
While the native bubble chart may be like an X-Y scatter chart, you can make a pretty convincing fake using a line chart along with some VBA.

The suggested code traps changes to two named ranges: Revenue and Satisfaction. If one of those values are changed, then the Worksheet_Change sub calls the sub SetBubbleSizeAndColor. That sub in turn scales the marker to one point per \$1000 revenue, and sets the color of each marker according to the requested satisfaction scale.
``````'This sub goes in the code pane for the worksheet with the chart. It won't work at all if installed anywhere else.

Where hgholt had a different series for each customer, I decided to have a single series for all customers. Doing so made for a more compact data table for the chart. That difference, however, meant that I had to set bubble size and color for each point within a single series rather than for the series collection item. The Select Case block of code used to set marker color came from hgholt's sub.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgRevenue As Range, rgSatisfaction As Range
Set rgRevenue = Range("Revenue")
Set rgSatisfaction = Range("Satisfaction")
If Not Intersect(Target, Union(rgRevenue, rgSatisfaction)) Is Nothing Then SetBubbleSizeAndColor
End Sub

'This sub goes in a regular module sheet
Sub SetBubbleSizeAndColor()
Dim ws As Worksheet
Dim rgRevenue As Range, rgSatisfaction As Range
Dim iSatisfactionValue As Integer
Dim i As Long, n As Long
Dim dColorValue As Double
Dim pt As Point
Dim dRevenueScale As Double

Set ws = ActiveSheet
Set rgRevenue = ws.Range("Revenue")
Set rgSatisfaction = ws.Range("Satisfaction")
dRevenueScale = 1000  '\$1000 revenue = 1 point

With ws.ChartObjects(1).Chart.SeriesCollection(1)
n = .Points.Count
For i = 1 To n
.Points(i).MarkerSize = CInt(rgRevenue.Cells(i).Value / dRevenueScale)
Select Case rgSatisfaction.Cells(i).Value
Case 1 To 2
dColorValue = 255 'Red
Case 3
dColorValue = 65535 'Yellow
Case 4 To 5
dColorValue = 5296274 'Green
End Select
.Points(i).MarkerBackgroundColor = dColorValue
.Points(i).MarkerForegroundColor = dColorValue
Next
End With
End Sub
``````
Bubble-Chart-Q28289384.xlsm
0

LVL 25

Expert Comment

ID: 39640017
Smart solution to make a line chart look like a bubble chart.
I save it for future use.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.