Secondary Axis going to 0

[Nike6106]

Hi

I have two series of data, and my second series is plotted on the secondary axis. For some reason, the plot goes to zero on this chart, and on many other similar charts. I read a lot about axis not being aligned, but have not been able to find anything to work. Using excel 2010.

secondary_example.xlsx

[MrShorty]

Is the problem that the axis goes to 0, or is the problem that the data series is dropping to 0?

If you want the axis minimum to be something other than 0, simply go into the axis formatting dialog and change the axis minimum to the desired value.

If the problem is with the data, I note that the last two entries in column E have a little green triangle indicating an error with those values. The error is "number stored as text". Charts treat text (including numbers stored as text) as 0. Solution will be to make sure these numbers are really numbers and not text. You might look at the source of these data and the process you are using to import them into Excel to see if there is something you can do at that stage to make sure numbers import correctly. If you want to deal with these after import, you can use the Text to Columns command. Or use the "enter 1, copy to clipboard, paste special, multiply" or variants. Or any of the other strategies for converting numbers stored as text to numbers.

[Nike6106]

Thanks. Even if I change the x axis to numbers (see attached) the problem still exists. The problem is that the last data point in series 2 is 1.21 yet on the chart the line shows 0. (the red line goes down when the data doesn't)

secondary_example.xlsx

[MrShorty]

You misunderstood. I was not referring to the axis type for your horizontal axis, I was referring to the actual data in column E in the spreadsheet.

If you have "numbers stored as text" checked in your error checking options (Excel Options -> formulas https://support.office.com/en-us/art...7-75F4B90045F8 ) and scroll down to the last two entries in the table, you should see little green triangles in those last two entries. The indicated "error" is number stored as text.

If you don't have and don't want to turn on these error checking options, then put =ISTEXT(E5) into column F and copy down. You will see that this returns FALSE for all entries except the last two, which return TRUE -- indicating that these numbers are being stored as text strings.

The solution to your problem is to convert those numbers stored as text to real numbers.