| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display hidden bubbles
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display hidden bubbles
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display hidden bubbles
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display hidden bubbles
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display hidden bubbles
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display hidden bubbles
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display hidden bubbles
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 |
|
#8
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display bubble chart labels instead of legend "series" label(s)
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" 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display bubble chart labels instead of legend "series" label(s)
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/200...e-elses-macro/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gretchen Gordon" wrote in message ... 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" 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display bubble chart labels instead of legend "series" label(s
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" wrote: 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/200...e-elses-macro/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gretchen Gordon" wrote in message ... 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" 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display bubble chart labels instead of legend "series" label(s
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gretchen Gordon" wrote in message ... 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" wrote: 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/200...e-elses-macro/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gretchen Gordon" wrote in message ... 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I don't want to display any controls in excel. | Excel Discussion (Misc queries) | |||
| Buttons to link to a hidden sheet | Excel Discussion (Misc queries) | |||
| Report that display percentage below certain level | Excel Discussion (Misc queries) | |||
| Look Up and Display set data | Excel Discussion (Misc queries) | |||
| How do you display Greek and Russian languages in excel? | Excel Discussion (Misc queries) | |||
Linear Mode
