How does Excel choose the lower bound for chart axes?

[JoeMerchant02]

Hi,

I've got a spreadsheet connected to a tabular model running on SQL Server Analysis Services. I am creating pivot tables / pivot charts with the row field as months. I've defined two measures - billed orders per day and quoted orders per day. Both measures are similar in magnitude (i.e. billed per day averages about 1,900 per day and quoted per day averages about 2,600 per day). There are no significant high or low outliers in the summarized data in the pivot table.

When I create a pivot chart from the pivot tables, the y axis range for the billed/day chart is from 1,650 to 2,150. The y axis range for the quoted/day chart is from 0 to 3,000.

Obviously, I can manually define the y axis range for the quoted/day chart, but I want to connect slicers to allow the users to view the data by location, product, etc. If I manually define a range, it won't be valid for all slicer combinations.

Does anyone know how Excel determine the range for chart axes or how I can force Excel to choose a more appropriate range rather than 0 to ... ? I know I can do this with VBA, just wondering if there is some rhyme or reason to this strange behavior on Excel's part.

Thanks!

 

[thisoldman]

Jon Peltier has written an explanation, How Excel Calculates Automatic Chart Axis Limits:

http://peltiertech.com/how-excel-calculates-automatic-chart-axis-limits/