- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Richard" <Ric...@discussions.microsoft.com> wrote in message
news:D85FAFF7-8836-4932...@microsoft.com...
>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
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
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" <Ric...@discussions.microsoft.com> wrote in message
news:67F37C93-E090-40BC...@microsoft.com...
"Jon Peltier" wrote:
> ..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)
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" <Ric...@discussions.microsoft.com> wrote in message
news:00E8DC06-9B86-4845...@microsoft.com...
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
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, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Gretchen Gordon" <Gretche...@discussions.microsoft.com> wrote in
message news:CDEC0AB1-AF86-4F9C...@microsoft.com...
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Gretchen Gordon" <Gretchen Gor...@discussions.microsoft.com> wrote in
message news:643D8CED-85DE-4718...@microsoft.com...
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
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Gretchen Gordon" <Gretche...@discussions.microsoft.com> wrote in
message news:201E448D-323A-44AC...@microsoft.com...