+ Reply to Thread
Results 1 to 11 of 11

How to change a series chart type from Line to XY-Scatter

  1. #1
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    How to change a series chart type from Line to XY-Scatter

    HI-

    I have a bar chart, and one series I believe is just a normal Line series, and then I have another series that is a secondary horizontal axis. How can I add 2 more series, but change theirs to an XY-Scatter?

    Thanks

  2. #2
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: How to change a series chart type from Line to XY-Scatter

    Chart question3.xlsxIf this helps, the attached has already been done, I just can't figure how to replicate. The name of the 2 series (total realized/unrealized gain), and (Total realized/unrealized loss)

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to change a series chart type from Line to XY-Scatter

    I think you'd either have to change the Line chart to an XY scatter too, or plot it on the primary axis.
    Remember what the dormouse said
    Feed your head

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to change a series chart type from Line to XY-Scatter

    Your example has the following.

    Series "P&L Gain" - Column on Primary
    Series "P&L Loss" - Column on Primary
    Series "Dummy" - Column on Primary (but choice disabled)
    Series Series "Total Realized/Unrealized Gain - XY-scatter on secondary
    Series "Total Realized/Unrealized Loss - XY-scatter on secondary

    If you create a chart with the frist 3 series as described you should see that "Dummy" is on secondary and choice of axis is not disabled.
    Use Select Data dialog to add a new series.
    Select the new series, which should already be on the secondary axis, and change it's chart type to xy-scatter.
    Go back to select data dialog and select x and y ranges.

    You will now see Dummy series axis choice is primary and disabled.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: How to change a series chart type from Line to XY-Scatter

    Chart Question 4.xlsx

    Thank you that helps a lot. I was able to mostly replicate in a new sheet. I have two small issues. I am not sure what I did wrong. 1) My error bars are moving together when I am adjusting factors, instead of separately. 2) In my Gain error bar there is a little bar in the star, and not sure how to get rid of it. See attadched.

    Thanks

  6. #6
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: How to change a series chart type from Line to XY-Scatter

    I figured out both. It's because the legend entry P&L loss was missing. I deleted it on bar graph, because it was populating a bar next to P&L gain. How do I eliminate the extra bar?

  7. #7
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: How to change a series chart type from Line to XY-Scatter

    I see. You changed to primary, then set it to overlap? That slightly distorts the Red color. I guess there is no way to put it back to regular red?

  8. #8
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: How to change a series chart type from Line to XY-Scatter

    Chart Question 4.xlsx

    sorry for all the replies. See attached. I believe I have fully replicated your original sheet. The only issue I am seeing, and not sure how to resolve, is that when I change the number in error bar, and I hit save, all the data labels under the bar chart move. Is there a way to fix that? Also, not sure on that color issue when you do an overlap.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to change a series chart type from Line to XY-Scatter

    I have change position of xy-scatter series data point to be left side of bracket. The error bar value for Plus is read from cell whilst negative is static at ={0}. Hopefully now the positioning will be easier to understand as it is not trying to center the data point.

    You can remove the data marker from the series by setting it to none.

    You can format the series used for losses to be any shade of red you want by formatting the fill colour.

    I don't see any problems with label alignment.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: How to change a series chart type from Line to XY-Scatter

    Andy-

    What do you mean when you say you changed position of xy-scatter series data point to be left side of bracket. How do you do that? Also, when adjusting the error bars, I may have been doing it wrong, by adjusting the x-axis which shifted secondary axis.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to change a series chart type from Line to XY-Scatter

    Previous the single data point was in the middle of the span with error bars going minus (left) and plus(right).
    To make it easier I changed the X value of the data point to the Left side and the error bar is now only a plus(right) for the complete span distance. There is still a minus error bar but the value is zero. The minus is needed to give the little cap on the left side of the span.

    The only changes needed where to the x value and error bar value on the sheet. The error bars reference for the minus part needs to be changed to ={0} rather than use a cell reference. This is done via the format dialog for the error bar using the Set Custom button.

    In theory you should not need to alter the secondary horizontal axis information.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Exclude one data series from scatter chart legend (but not from the chart)
    By JayUSA in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-29-2012, 05:24 PM
  2. Replies: 1
    Last Post: 09-02-2011, 08:57 AM
  3. how to change line color and transparancy in XY scatter chart
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2010, 09:54 AM
  4. Need to change series in a bar line chart
    By e602043 in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 06-17-2009, 10:38 PM
  5. [SOLVED] Adding a line Chart Type to a stacked-clustered Chart Type
    By Debbie in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-05-2005, 08:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1