+ Reply to Thread
Results 1 to 6 of 6

When I plot secondary axis, my horizontal axis changes and secondary axis data dont match

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    30

    When I plot secondary axis, my horizontal axis changes and secondary axis data dont match

    Hi all,

    I have a line and clustered column chart with many details.
    The columns representing $$ coming in and out, and the line showing balance.
    The difference between "Balance" and the $$ coming in and out is too big, hence I wanna add a secondary axis for "Balance".

    But when I choose secondary axis, the horizontal axis details changed and the data for secondary data doesn't match.

    The excel sheet shows an example and the picture shows my actual project difference.
    Attached Images Attached Images
    Attached Files Attached Files

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

    Re: When I plot secondary axis, my horizontal axis changes and secondary axis data dont ma

    In your example the number of cells covered by income and savings is of different lengths.
    The income series has no category data specified. This is okay whilst both series share the same category axis.

    When you move to secondary axis they have there own categories, which will be different in length.
    One will be from cells the other a sequential count of data points

    Sort out the data used in both series
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    10-23-2019
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    30

    Re: When I plot secondary axis, my horizontal axis changes and secondary axis data dont ma

    Sorry about that.

    I edited the formulas and chart re-uploaded.

    1) How come when I added secondary axis, the horizontal axis starts from 1 instead of Current Age 21?
    How can I change that?

    2) As for the picture, How can I adjust both vertical axis to the start off from the same horizontal axis?

    Left is before adding secondary axis, Right is after adding.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by WalaVita; 05-02-2020 at 01:12 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: When I plot secondary axis, my horizontal axis changes and secondary axis data dont ma

    Sounds to me like you need to manually set the scale for both axis so that that match?
    You can akso define where the y-axis starts from, too
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    10-23-2019
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    30

    Re: When I plot secondary axis, my horizontal axis changes and secondary axis data dont ma

    Quote Originally Posted by FDibbins View Post
    Sounds to me like you need to manually set the scale for both axis so that that match?
    You can akso define where the y-axis starts from, too
    Becos the chart auto adjust according to the horizontal axis "Age" and the vertical axis "Balance" can vary by a lot, so I can't manually set the scale everytime.

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

    Re: When I plot secondary axis, my horizontal axis changes and secondary axis data dont ma

    You have not specified a range for the category labels for the income series

    =SERIES("Income",,Sheet1!$E$2:$E$27,2)

    Where as the Saving series does

    =SERIES("Savings",Sheet1!$C$2:$C$27,Sheet1!$D$2:$D$27,1)

    The zeros will not automatically align on the 2 vertical axis. You will need to adjust the min/max values to do that.
    https://peltiertech.com/Excel/Charts/AlignXon2Ys.html

+ 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. Scale on horizontal axis on a graph with secondary vertical axis
    By selsaid in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-06-2019, 07:54 AM
  2. Replies: 1
    Last Post: 11-06-2019, 07:30 AM
  3. Replies: 2
    Last Post: 04-15-2015, 01:10 AM
  4. Replies: 2
    Last Post: 03-11-2011, 09:02 AM
  5. Replies: 6
    Last Post: 12-07-2010, 02:54 PM
  6. Replies: 0
    Last Post: 06-13-2007, 09:41 AM
  7. [SOLVED] Secondary Axis Match Primary Axis Gridlines
    By [email protected] in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-31-2005, 05:05 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