Posted to microsoft.public.excel.misc
|
|
Display hidden bubbles
"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
|