Thanks for you clarification.
--
Richard


"Jon Peltier" wrote:

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
_______


"Richard" wrote in message
...
I wanted the 'category' column to determine bubble color.
--
Richard


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Richard" wrote in message
...
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?
--
Richard


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Richard" wrote in message
...
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.

--
Richard