Auto-adjust chart "max" issue

Bret1

Board Regular
Joined
Jun 14, 2013
Messages
129
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
125
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 14, 2013
Messages
129
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

Board Regular
Joined
Sep 22, 2019
Messages
125
Office Version
  1. 365
Platform
  1. Windows
That is weird and is not something I've seen before, do you have the same versions of Excel on both devices?
 

Bret1

Board Regular
Joined
Jun 14, 2013
Messages
129

ADVERTISEMENT

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.
 

Attachments

  • Excel chart compare gif.gif
    Excel chart compare gif.gif
    204.9 KB · Views: 4

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
125
Office Version
  1. 365
Platform
  1. Windows
I reckon it's something to do with the version of Excel you're using, short of that I'm as lost as you :unsure:
 

Bret1

Board Regular
Joined
Jun 14, 2013
Messages
129

ADVERTISEMENT

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"
 

Attachments

  • Excel line with markers chart.gif
    Excel line with markers chart.gif
    231.3 KB · Views: 2

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Do you need the 500 in there ?, if you made it text with a apostrophe in front, does that fix it
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,439
Members
410,684
Latest member
LakTik
Top