Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 15
Default Bubble Chart Legend

Is it possible to create a bubble chart with the standard X, Y, and Bubble
Size values and then use text in another column as the legend, such as
Company A as the legend with the values in Growth Rate, Margin, and Bubble
Size?

Thanks for your assistance.
--
I Teach
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1,180
Default Bubble Chart Legend

Excel 2007
Add text bubble labels.
http://www.mediafire.com/file/clnnk0mmzzj/09_22_09.xlsm
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 15
Default Bubble Chart Legend

Thanks for the information, but what I was looking for was how to create
those labels as a legend. Thanks again.
--
I Teach


"Herbert Seidenberg" wrote:

Excel 2007
Add text bubble labels.
http://www.mediafire.com/file/clnnk0mmzzj/09_22_09.xlsm
.

  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Bubble Chart Legend

Hi,

The legend contains the Names of each series. So you would need to create a
series for each Company.
There is a limit of 255 series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Is it possible to create a bubble chart with the standard X, Y, and Bubble
Size values and then use text in another column as the legend, such as
Company A as the legend with the values in Growth Rate, Margin, and Bubble
Size?

Thanks for your assistance.
--
I Teach


  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 15
Default Bubble Chart Legend

Thanks, Andy. Please tell me how to do that in Excel 2007.
--
I Teach


"Andy Pope" wrote:

Hi,

The legend contains the Names of each series. So you would need to create a
series for each Company.
There is a limit of 255 series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Is it possible to create a bubble chart with the standard X, Y, and Bubble
Size values and then use text in another column as the legend, such as
Company A as the legend with the values in Growth Rate, Margin, and Bubble
Size?

Thanks for your assistance.
--
I Teach


.



  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Bubble Chart Legend

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #7   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 15
Default Bubble Chart Legend

Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to each
bubble. Could you please help me to change the macro so that a legend is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.

  #8   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Bubble Chart Legend

Based on Herbert's data try this, which will create a new chart and populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


  #9   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 15
Default Bubble Chart Legend

This worked perfectly - I cannot thank you enough. My only question is that
it only works when the data is formatted as a table. When I convert it to a
range, I get an error message "Subscript out of range" and in debug, it
points to the line "Set rngData = ActiveSheet.ListObjects(1).Range". Could
this macro work without the data being formatted as a table. If not, that's
okay too.

Please let me know.
--
I Teach


"Andy Pope" wrote:

Based on Herbert's data try this, which will create a new chart and populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


.

  #10   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Bubble Chart Legend

If the data is not in a table then you can use something like

Set rngData = ActiveSheet.Range("A1").currentregion


Of course you would need to amend the row/column indexes so you got a
reference to the correct data.

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
This worked perfectly - I cannot thank you enough. My only question is
that
it only works when the data is formatted as a table. When I convert it to
a
range, I get an error message "Subscript out of range" and in debug, it
points to the line "Set rngData = ActiveSheet.ListObjects(1).Range". Could
this macro work without the data being formatted as a table. If not,
that's
okay too.

Please let me know.
--
I Teach


"Andy Pope" wrote:

Based on Herbert's data try this, which will create a new chart and
populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend
is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


.




  #11   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 15
Default Bubble Chart Legend

Andy, thanks for all your assistance. Now I have two ways to display the
chart, whether the data is in a table or not.

I appreciate all your help and everything worked well.
--
I Teach


"Andy Pope" wrote:

If the data is not in a table then you can use something like

Set rngData = ActiveSheet.Range("A1").currentregion


Of course you would need to amend the row/column indexes so you got a
reference to the correct data.

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
This worked perfectly - I cannot thank you enough. My only question is
that
it only works when the data is formatted as a table. When I convert it to
a
range, I get an error message "Subscript out of range" and in debug, it
points to the line "Set rngData = ActiveSheet.ListObjects(1).Range". Could
this macro work without the data being formatted as a table. If not,
that's
okay too.

Please let me know.
--
I Teach


"Andy Pope" wrote:

Based on Herbert's data try this, which will create a new chart and
populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend
is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


.


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 'bubble' chart where colors are used instead of bubble size paulmichael Charts and Charting in Excel 3 December 5th 07 04:33 PM
How can I change the color of negative bubble in bubble chart Jon Peltier Charts and Charting in Excel 0 July 4th 07 03:29 PM
How can I change the color of negative bubble in bubble chart Daniel Charts and Charting in Excel 1 July 4th 07 03:25 PM
Problem with BUBBLE CHART scale and legend keesberbee Charts and Charting in Excel 6 July 2nd 06 09:11 PM
ho to change in the bubble chart the bubble position and size laszlo Charts and Charting in Excel 0 March 25th 05 05:45 PM


All times are GMT +1. The time now is 10:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"