Display hidden bubbles

[Richard]

I have some hidden points on a bubble chart.

How do I prevent bigger bubbles from covering up small bubbles on a bubble
chart?

It would be fine if the smaller bubble partially covered the bigger bubble.

 

[Jon Peltier]

Order the data in each series so the large bubbles are plotted first (higher
in the data list) and small bubbles last. Then order the series so the one
with larger bubbles is plotted first, although this is not as likely to
prevent obscuring of bubbles on the first series.

- Jon

 

[Richard]

Your suggestion works great on showing 'hidden' bubbles.

Could you tell me the best way to order the data to simplify graphing?
Right now I have
Column 1: x-values
Column 2: y-values
Column 3: bubble size
Column 4: category

When I manually graph the data, Excel's default is to use Column 1 and
Column 2 headers to get category. Is there a 'standard' way of ordering the
data so the default graph settings pick up the 'category' data for bubble
color and ledgend?

 

[Jon Peltier]

A bubble chart requires three columns: X, Y, and Bubble size. If you've
chosen the Vary Colors by Point option, the legend shows the X values. In
general, the series name will by default be the cell atop the Y values, but
bubble charts seem a bit stupider than most chart types; even the old
standby of leaving the top left cell blank does not cause Excel to use the
top row for series names.

If you want each point to be its own series with its own legend entry and
format, you will need to use a macro. Here's one I posted four years ago
which seems like it will do what you want:

Sub OneRowPerBubbleSeries()

'' Takes 4-column range and constructs Bubble chart

'' Uses one series per row: Columns in order: Name, X, Y, Z

'' Select the range and run this macro

Dim wks As Worksheet

Dim cht As Chart

Dim srs As Series

Dim rng As Range

Dim rng1 As Range

Dim rownum As Integer

Dim bFirstRow As Boolean

Set wks = ActiveSheet

Set rng = Selection

Set cht = wks.ChartObjects.Add(100, 100, 350, 225).Chart

bFirstRow = True

For rownum = 1 To rng.Rows.Count

Set rng1 = rng.Cells(rownum, 2).Resize(1, 3)

If IsNumeric(rng1.Cells(1, 1).Value) And _

IsNumeric(rng1.Cells(1, 2).Value) And _

IsNumeric(rng1.Cells(1, 3).Value) Then

'' First time: need to do it differently

If bFirstRow Then

cht.SetSourceData Source:=rng1, _

PlotBy:=xlColumns

cht.ChartType = xlBubble

bFirstRow = False

'' Remove spurious second series

cht.SeriesCollection(2).Delete

Else

Set srs = cht.SeriesCollection.NewSeries

End If

With cht.SeriesCollection(cht.SeriesCollection.Count)

..Values = rng1.Cells(1, 2)

..XValues = rng1.Cells(1, 1)

..BubbleSizes = "=" & rng1.Cells(1, 3).Address _

(ReferenceStyle:=xlR1C1, external:=True)

..Name = rng.Cells(rownum, 1)

End With

End If

Next

End Sub


- Jon

 

[Richard]

I wanted the 'category' column to determine bubble color.

 

[Jon Peltier]

You may notice the columns in my macro's assumed data region are labeled

Name, X, Y, Z (Bubble Size)

Yours are labeled

x-values, y-values, bubble size, category

The difference being that I put my series names first and you put yours
last. You could move your fourth column in front of the other three and use
my macro, or you could rewrite my macro to process the data in your column
order.

- Jon

 

[Richard]

Thanks for you clarification.
--
Richard

You may notice the columns in my macro's assumed data region are labeled

Name, X, Y, Z (Bubble Size)

Yours are labeled

x-values, y-values, bubble size, category

The difference being that I put my series names first and you put yours
last. You could move your fourth column in front of the other three and use
my macro, or you could rewrite my macro to process the data in your column
order.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

 

[Gretchen Gordon]

You may notice the columns in my macro's assumed data region are labeled

Name, X, Y, Z (Bubble Size)

Yours are labeled

x-values, y-values, bubble size, category

The difference being that I put my series names first and you put yours
last. You could move your fourth column in front of the other three and use
my macro, or you could rewrite my macro to process the data in your column
order.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

 

[Gretchen Gordon]

Jon,

I read your answers to Richard and I think I need to follow your solution,
however, I am plotting an array of data with three series and four categories
(X, Y and size values for each). In all, 12 bubbles. I want each of my
bubbles to display the category name (just like Richard's question, I think)
but I'm not completely sure from reading your answer that your macro would
work in my case. To make matters worse, I'm really a complete macro novice.
Any advice?

very sincerely,
Gretchen Gordon - FedEx Latin America, Marketing Analysis


I read this discussion thread

 

[Jon Peltier]

If you arrange the data columns as I described, then my procedure should
work to apply the appropriate labels to the points.

This blog post describes how to use a macro someone has provided to you:

http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

- Jon

 

[Jon Peltier]

I don't see a question. If the problem is that small bubbles are obscured by
larger ones, you could sort the data so that small bubbles are drawn last,
in front of the larger ones.

- Jon

 

[Gretchen Gordon]

Jon,

thanks ! I will go check out your blog posting. In the mean time a very
resourceful intern in our office found an add-in to Excel that does what I'm
asking about - the add-in is called Power Utility Pack (v5) published (?) by
JWalk & Associates www.j-walk.com

warm regards!
Gretchen Gordon

 

[Jon Peltier]

Actually, here are two free Excel chart labeling add-ins:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

The J-Walk one is probably just a small subset of what's in PUP, but it
handles the chart labels just fine.

- Jon