Free Microsoft Excel 2013 Quick Reference

Add labels to a bubble chart with multiple series

New Bubble Graph v5.zip

Hi, would appreciate help with this,

I'm creating a sales pipeline / bubble chart for business development and having trouble with the labels.

I found the code to attach labels to a scatter graph / bubble chart on the microsoft website and have managed to do this on a single series but do not know how to do the same for multiple series.

As you will see on the 'Funnel' tab I have, 2 charts on top of each other. 1 of these charts has all of the data in 1 series (from the 'Data' tab), and I have made these bubbles transparent so that it's only the labels which show once the macro is run. The other chart has a series for each stage (from the 'Graph Feed') tab. This was so I could preset the colours of the bubbles

I would like, as I have done on the 'Funnel by BDM' tab, to be able to select the Business Development Manager (BDM) from the drop down box and show only these bubbles.... which works, but the labels were going out of place.

So I would like to just have the one chart and run the macro to show the labels.

note

- the number of opportunities (bubbles) will change each month
- i have a 'scale' bubble which is to reduce the size of the other bubbles, I don't need a label for this but at worst I can just remove it individually
- if possible i'd like the font/size of the labels to be included within the macro code so i can change it (currently appears calibri each time macro is run which is ok but may want to change it and the size)

thanks!!


I have created a bubble chart based on following data-table
Score 1 Score 2 Score 3
Customer A 1 4 5
Customer B 3 3 2
Customer C 5 1 1

Score 1 = X-axis / Score 2 = Y-axis / Score 3 = bubble size

I cannot get excel to add the customer names as labels to individual
bubbles. When I try to add names, it adds all names to all bubbles. Off
course I can manually change the names, however isn't there an easier way?

Hi all,
Im having a hard time trying to add labels to my bubble chart. Does anyone know how to do it the right, and easy, way?

THANKS in advance,
Brites.

I am trying to create a bar chart with multiple series in Excel 2003. I have
x and y data, but the x-axis has to be the same for all series or it takes
the x-values of the last series created. My problem is not the scale. It is
getting two series to appear independent of each other. Using bar charts
forces a comparison of the two series, but I simply need to plot data for
each series. Should I be using a different chart type? If so, what is it?
-- Thanks,
Patrick

Hello,
I am trying to add data labels to a pie chart. But I get an error when I put '.HasDataLabels = True' as a function of the chart object. How can I add labels? I'd like to add the data labels (as specified by part of the range, K3:K10) and the corresponding percentage (both in bold).

I'd greatly appreciate any help.


	VB:
	
 ActiveSheet.ChartObjects.Add _ 
    (Left:=440, Width:=310, Top:=475, Height:=200) 
    With .Chart 
        .SetSourceData Source:=ActiveSheet.Range("K3:L10") 
        .ChartType = xl3DPie 
        .HasTitle = True 
         '########   Error here:   ############
        .HasDataLabels = True 
         '##############################
        .ChartTitle.Characters.Text = "title" 'title
        .ChartTitle.Characters.Font.Size = 10 
        .ChartTitle.Characters.Font.Bold = True 
        .HasLegend = False 
        .RightAngleAxes = True 
        With .ChartArea 
            With .Border 
                .Weight = 2 
                .LineStyle = 0 
            End With 
            With .Interior 
                .ColorIndex = 39 'background: make same as plot area color
                .PatternColorIndex = 1 
                .Pattern = 1 
            End With 
        End With 
        With .PlotArea 
            With .Border 
                .Weight = 2 
                .LineStyle = 0 
            End With 
            With .Interior 
                .ColorIndex = 39 'background: make same as chart area color
                .PatternColorIndex = 1 
                .Pattern = 1 
            End With 
        End With 
    End With 
     
     '######### Problems here:  ###########
    With .SeriesCollection(1).DataLabels 
         '?????  what goes here?
         '?????
        .Font.Bold = True 
    End With 
     '################################
    .RoundedCorners = True 
    .Shadow = False 
     
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


How do I add labels to an Excel bubble chart that are NOT the X, Y or Z
values?

Instead, I want to add display name labels (e.g., Bob, Tom, Sally) to the
bubbles.

That seems rather basic, but I don't see how to do it (in Excel 2007).

In Excel 2003, is it possible to change the color of part of a line on a
chart with multiple data series? I can do it on a single series chart.

I need to prepare a chart including below information:

Overdue size of 10 customers
Overdue % of AR of 10 customers
AR Outstanding days of 10 customers
Target of Overdue % of AR
Target of AR Outstanding days

I am thinking of use a Bubble chart to demonstrate information of 10
customers and use a rectangle shadow to show the target. But I don't know how
to add the rectangle shadow to a Bubble chart. Am I thinking in the right way
and how to get that if yes? If I am not, is there any solution for the
problem? Thanks.

Christine

hi does anyone know how to add names on a bubble chart?

I need to automate the process of adding one bubble to a bubble chart. Most
of the code works fine, however, I cannot define the size of the bubble
correctly as it seems that I must use R1-style notation. Any assistance would
be greatly appreciated.

Sub AddBubble()
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Variant

Set rng = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng2 = ActiveCell
ActiveCell.Offset(0, 2).Range("A1").Select
Set rng3 = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng4 = ActiveCell

ActiveSheet.ChartObjects("BubbleChart").Activate
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = rng2
ActiveChart.SeriesCollection(2).Values = rng3
ActiveChart.SeriesCollection(2).Name = rng
ActiveChart.SeriesCollection(2).BubbleSizes = rng4
ActiveChart.ChartType = xlBubble
End Sub

I am trying to create a bubble chart with conditional formatting. I used the help and example provided in this older thread.

http://www.ozgrid.com/forum/showthread.php?t=87636

It's working but I need to add the data labels to each bubble. So, using the example provided, I need A-J to appear in each bubble.

Any help would be greatly appreciated.

Thanks,

Steve

Hi all,

I've got data that looks like this:

Business Unit Interest Influence/Authority Role
Cards & Payments 7 9 5
Internet 10 8 2
Cards & Payments10 5 2

I'm trying to create a Bubble chart (in Excel 2007) that has:
1. Interest as the X-axis
2. Influence/Authority as the Y-axis
3. Role as the bubble size
4. Business Unit as the Series name

If there is only once instance of a particular business unit, then
this doesn't appear to be a problem. It's a bit manual, but generally
works. But when I need to create another row for an existing Business
Unit, then I have to manually open that Series, and add in the cell
reference to the new X, Y, and Bubble size.

What I would LOVE Excel to do is to do a sort of dynamic array
creation/lookup type function. So that I specify the Series name and
it selects all X-values (Interest), Y-values (Influence/Authority) and
Bubble size where the Business Unit = the name that I specify.

I appreciate that this is nearly impossible for me to explain well,
but I hope that you can make sense of it. Basically what I'm doing is
a stakeholder analysis, and there are going to be multiple responses
per Business Unit, and I want to map all of these out, with each
Business Unit as a series with a different colour so at a glance I can
see the distribution, which Business Units I should focus on, which
outliers I need to target etc.

Because new people come and go all the time, I really don't want to
have to manually open each Series and manually point Excel to a new
row everytime. Nor do I want Cards & Payments to appear as two
separate series when they the two rows belong, logically in my head,
to the same series as defined by the series name.

I'm wondering if I need to layout my data differently - which is fine
as I'm just trying to get the shell together before I start populating
it. Any ideas would be gratefully received! I don't mind installing
add-ins and what not, I just can't seem to get Excel to do this
automatically for me!

Thanks so much for any assistance.

Cheers,
K

I would like to add a twist to a bubble chart. That is, make the bubbles a mini pie chart such that the bubble size shows total population and a slice in the bubble shows % of a sub-population.

Has someone figured this trick out yet? I took a stab at it, but only have been able to come up with very manual approaches.

I see the question has been asked here a few times, but no one has yet answered it.

Thanks.

Is it possible when creating a bubble chart to add a fourth set of values so that the bubbles are coloured accoring to that fourth value e.g I have a set of data that has date on the x axis, margin on the y axis and value as the size of the bubble I want the bubbles to be coloured by type

I have used named ranges to make a chart automatically adjust to a changing list of products in one column, but I do not know how to do this with multiple series. If I want to add products to this table, how can I make my chart dynamically adjust multiple series?

So the chart range is currently E5:H10. But I would need it to go beyond row 10 when I add new items.

Any ideas? Thanks.

Sheet1

*EFGH5*Week 1Week 2Week 36Product 11,234,123 1,258,805 1,283,982 7Product 21,234,223 1,258,907 1,284,086 8Product 33,423,321 3,491,787 3,561,623 9Product 4342,121 348,963 355,943 10Product 52,343,424 2,390,292 2,438,098

Excel tables to the web >> Excel Jeanie HTML 4

I WANT TO CREATE A BUBBLE CHART THAT COULD SHOW WHICH BUBBLE IS FROM WHICH
ROW OR COMPANY IN MY CASE. EACH BUBBLE COULD BE OF A DIFFERENT COLOR.

I have an Excel Spreadsheet that has cells with multiple line feeds. I would like to know if anyone has any idea how to combine 2 cells (or more) with multiple line feeds into a single cell with multiple line feeds. An example of what needs to be accomplished is shown below

Cell A1 (Input)
A
B
C

Cell B1 (Input)
1
2
3

Cell C1 (Desired Output)
A1
B2
C3

For spreadsheet purposes the output to Cell C1 could be A1B2C3 but it would present better for checking purposes if the output matched the line feed arrangement in the 2 input cells.

Using the concatenate function "Concatenate(A1,B1)" produces ABC123.

The number of line feeds in different rows varies from 1 single data set to 6 line feeds produced by using "ALT+ENTER". The number of line feeds in the cells to be combined is always the same. Breaking the data into multiple rows is not a manageable option. Either a formulaic solution or VB Code would be great!

Thanks

Hi:
I'm trying to create a bubble chart in which the size of the bubbles can
represent different variables in the same chart. I've seen people do this,
but I don't know how.
In the following example, I want to have "Business Potential" on the X axis,
"Channels Capacity" in Y, and I want to be able to change the size of the
bubble in the same chart so that they can stand for "Revenue", "GDP" or "GDP
PPP", by just clicking on the name of the variable.

Czech Russia Brazil Hungary
Revenue 100 650 300 150
GDP 200 1300 600 250
GDP PPP 220 1200 750 280
Channels Capacity 4 3 2 4
Business Potential 5 5 3 2

Could someone please let me know how to do this?
Thanks,
D.

Does anyone know how to make a bar chart with multiple data ranges?

For example, you have figures for Actual and Budgeted expenses for 2005 and 2006, and want to project them on the same chart.

One of our accountants was asking me, and I didn't know how either. Please help!

Hi

I like to think I am fairly good with excel but I need some help in understanding how to add % values to a bar chart that has absolute values (as opposed to %'s) on the y-axis.

All I am trying to do is graph a bar chart with absolutes on the y-axis, elect to display values, and have the values show as %'s on the bars, but this seems impossible and the only way to do it is overtype the values with the right % amounts (which is not very helpful if the numbers change later)!

Any help gratefully appreciated.

Thanks

I have a line chart with multiple series. In the data, each series has blank cells so I end up with non-continuous lines. Because I have multiple series (each row a different series), I can't hide columns as the blanks are not all in the same column. Anyway to get Excel to connect the dots within a series even if there are blanks between them? Thanks

I have a filled radar chart with two series that partly overlap. Unfortunately, one series covers the other and some information is lost. Is there a way to make the area covered by one of the series transparent instead of solid color? Thanks for the help.

Hi guys

I'm creating a line chart with multiple series as per the attached example, but I can't figure out how to remove the zero values and/or stop them from being plotted.

Thanks in advance.

Hi everyone,

I know this has been answered many times before, but I'm looking for a slight tweak to the answer. I would like to have data labels added to a bubble chart (Excel 2007), but I don't want to use the add-in I see recommended often ("XY Labeler"?) because I will eventually need to turn over my Excel file to my client and I'm not sure if s/he will be able to download an add-in.

So, I'd rather have some VB code (with which I very little experience).

Thanks in advance!

Kelly

I have an XY chart with multiple series. Each series has 96 data points and the location of the maximum value varies within each series (eg it might be point #34 in Series #1, point #76 in Series #2, etc...). I have some code that adds the series name as a data label to the 96th point in each series which works fine. What I would like to do is to be able to add this same series name data label to whatever the maximum value point is for each individual series. This will stop me from having to move all of the labels manually so they sit on top of each data series.

Code snippet below:

	VB:
	
 LastPointLabel() 
    Dim mySrs As Series 
     'Dim nPts As Long
    For Each mySrs In ActiveChart.SeriesCollection 
        With mySrs 
             'nPts = .Points.Count
            mySrs.Points(96).ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False 
            mySrs.Points(96).DataLabel.Text = mySrs.Name 
        End With 
        Selection.AutoScaleFont = False 
        With Selection.Font 
            .Name = "Arial" 
            .FontStyle = "Regular" 
            .Size = 8 
            .Strikethrough = False 
            .Superscript = False 
            .Subscript = False 
            .OutlineFont = False 
            .Shadow = False 
            .Underline = xlUnderlineStyleNone 
            .ColorIndex = xlAutomatic 
            .Background = xlAutomatic 
        End With 
    Next 
    For i = 1 To 32 
        With ActiveChart.SeriesCollection(i) 
            With .Points(96) 
                With .DataLabel 
                    .HorizontalAlignment = xlCenter 
                    .VerticalAlignment = xlCenter 
                    .Position = xlLabelPositionAbove 
                    .Orientation = xlHorizontal 
                End With 
            End With 
        End With 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any thoughts?