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

1. ## 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.

2. ## 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

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

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?

4. ## 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

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

Originally Posted by FDibbins
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. ## 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

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

#### 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