Results 1 to 5 of 5
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Bubble Charts (2003)

    I have some data I want to plot on a bubble chart
    An Example of the style of data is shown below

    Region,Mkt Value,% Share, Size Factor
    London,22345,8o%.40000
    Cambridge, 12567, 6%, 12567
    Peterborough, 34622, 27%, 40000
    Huntingdon, 32886, 15%, 32886
    Northampton, 45321, 60%, 45321
    Corby, 23114, 25%, 23114
    Kettering, 23176, 2%, 23176
    WellBoro, 19887, 45%, 19887


    I need a Bubble for each town with the Bubble Label from the Region Name
    Market Value a the X Values
    % Share as the Y Value and
    Size Factor as the Bubble Width Parameter
    I have attached an example of a sheet with Example Chart.

    Maybe I am being foolish, BUT I cannot get it produce this from
    A single selection of the data (ignore Axis scale adjustments).
    I have to add the Data One series at a time to get the designated layout because the bubble
    label needs to be the Series Name.
    Am I being daft or can it be done as a selection
    Attached Files Attached Files
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bubble Charts (2003)

    As far as I know, that is not possible. You can create a bubble chart based on B310 (by columns). You'll get one series with column B as x values, column C as y values and column D as bubble size.
    Excel will give all bubbles the same color (since they belong to the same series), but you can specify that you want different colors in the Options tab of the Format | Selected Series dialog.

    You'll have to use Rob Bovey's free add-in XY Chart Labeler to add the data labels based on column A.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bubble Charts (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 23-Oct-07 18:31. Added Macro for datalabels)</P>You can do it if you rearrange your data or create a new "dataset" from your source in a different format. You still must select each one to set the datalabels, though if needed a lot you could create a macro.

    I have set up the extracted data on sheet2. Row 1, row2 and columnA are set formulas the rest of the table is the same formula copied down the rows and across the columns

    Steve
    PS An example of the alluded to macro
    <pre>Option Explicit
    Sub ApplyDatalabels()
    Dim SC As Series
    For Each SC In ActiveChart.SeriesCollection
    SC.ApplyDatalabels AutoText:=True, _
    LegendKey:=False, ShowSeriesName:=True, _
    ShowCategoryName:=False, ShowValue:=True, _
    ShowPercentage:=False, ShowBubbleSize:=False
    Next
    End Sub</pre>

    Attached Files Attached Files

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Bubble Charts (2003)

    Many thanks to all for response.
    I had a feeling it was not a simple one.
    All given solutions are OK.
    The XY Labeller is a good little Add-In (for other stuff as well).
    A bit of a hassle to have to re-arrange the data like that, shame that MS didn't cater for this option.
    I can live with manual version.I might just write a Macro to build the manual chart given an input range of data.
    Once written would save a lot of time so effort in worth it.

    All good stuff though.

    Maybe in Office 2009!
    Andrew

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bubble Charts (2003)

    MS caters to how it expects the data to be arranged. This is the way with all the charts.

    The formulas I post are generic and read your data directly. It is setup to prepare the data the way XL expects it to do what you want. It is only 4 formula and will cater to more columns and rows.

    A macro can be developed to create each series if desired, but I leave that to you...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •