Auto-adjust chart "max" issue

[Bret1]

I copied an Excel file that has several graph charts to my new laptop. The laptop file is reading the "max" chart values differently.
The charts have 2 rows of data. One row is the date across the top, the other is number data across, below the date.

There is one large (reference) number down a column in the middle of the number data row. The "date" cell for that column is blank so the number doesn't register on the chart.
The average number data across is 25. The (reference) number is about 500. The reference number is not charted, but the max chart value is greatly increased.
On my old computer, the auto-adjust max number works as I want, with a max about 40, but the chart on new laptop gives a max of 600 (which compresses the chart lines very much).

I know I can set the chart to have a "FIXED" max number, but I would prefer it to "AUTO-ADJUST", but not count the one large number.
Any suggestions? Thanks!

data example...
9/1__9/2__9/3__9/4_________9/5__9/6__9/7
20___28___25___27___500____23__28___21

 

[denzo36]

Its hard to see without seeing a sample of your data but you could use a helper column that excludes the large value and then plot that range on your graph rather than the range contain the large number

Excel should then adjust the values on your axis to the numbers in your data, excluding the large value

 

[Bret1]

I found some very interesting information. Keep in mind, I copied the same file from my computer to my new laptop.
If I create a new chart on my computer with the data as shown, it "compresses" everything with "600" as the max value. But, if I then right click the date axis across the bottom, and select "Format Axis", and change "Position Axis" to "On Tick Marks", the chart immediately adjusts with the max value just above the max input number (which is what I want).
The weird thing is, if I do the exact thing on my laptop, it just repositions the dates to be on the tick marks. It does NOT effect the max chart value. Very Weird.
Could it be a "global" chart setting somewhere that is different on my computer vs laptop?

 

[denzo36]

That is weird and is not something I've seen before, do you have the same versions of Excel on both devices?

 

[Bret1]

Attached is a screenshot example of what I'm talking about. The 2 charts are identical, except for the one one on the right, I changed the date axis to position the axis "On Tick Marks". It immediately reduced the max value on the chart. This is what I want to happen, but it does not affect the max value on my laptop the same.

 

[denzo36]

I reckon it's something to do with the version of Excel you're using, short of that I'm as lost as you

 

[Bret1]

Can anyone else out there try this simple test (like the attached pic) to see if you can re-create the chart layout as shown on the right above?
I used "Line With Markers" chart, and all Axis options are set to "Auto"

 

[mole999]

Do you need the 500 in there ?, if you made it text with a apostrophe in front, does that fix it

 

[Bret1]

Do you need the 500 in there ?, if you made it text with a apostrophe in front, does that fix it

SUCCESS! The apostrophe worked! Thank you very much for the suggestion!

 

[Fluff]

In future please do not duplicate your threads, all clarifications, follow-ups, and bumps should be posted back to the original thread.

I have merged both threads.