I recently described how How Excel Calculates Automatic Chart Axis Limits. The problem with these automatic limits is that they often leave a little too much white space around the outside of the plotted data, especially between the lowest data point and zero. So in Calculate Nice Axis Scales in Excel VBA I presented code that takes high and low series values and computes “nice” axis scaling parameters. This code could be called from other VBA procedures, or as a user defined function from the worksheet.

What if you want to get your axis scale parameters in the worksheet, but for some reason want to avoid using VBA? In this tutorial I show how to use boring old worksheet formulas to do just that.

## Calculate Axis Scales in the Worksheet

The following table shows how to set up worksheet calculations of your axis limits. The minimum and maximum of your data are entered into the blue cells B4:B5 (labeled Min and Max), either as constants or as calculated values. Below these values are some calculations.

B6 and B7 (Min’ and Max’) are adjustments to min and max, adding 1% of the difference between the data max and min to the max, and subtracting this amount from the min. If the values are zero or closer to zero than 1% of the difference, then zero is used. This prevents any values except for zero from being located on the edge of the plot area of the chart. (The formulas shown in column C are used in the adjacent cells in column B.)

B8 and B9 determine what the axis tick spacing (called “major unit” by Excel) should be. If this major unit were written in exponential notation, Factor is “like” the pre-exponential coefficient and Power is “like” the power of ten.

The axis tick spacing (Xmajor) is computed in B3, where Factor is used in the lookup table in A11:B15, and multiplied by a function of Power to determine a nicer tick spacing. The tick spacings you calculate may not be exactly right, do to different chart and font sizes or other factors. You can experiment with the values in the lookup table to try to improve them.

Xmin in B1 is calculated as the largest multiple of Xmajor which is less than Min’, and Xmax in B2 is the smallest multiple of Xmajor which is greater than Max’.

Use the values in the red cells to adjust the chart axis manually.

## Calculate Axis Scales Allowing User to Override Values

There are cases where you may want to allow the user to lock in one or more of the axis scale parameters. For example, if your data is for the value axis of a bar chart, you could override the calculated minimum to ensure that the axis scale starts at zero, regardless of the data minimum.

This greatly complicates everything, notably the formulas appearing in column C. The data min and max are in B7:B7 in the modified table below, while the user may enter preferred values into any of the cells B4:B6. Valid entries will be used in B1:B3, invalid entries or blanks will result in values being calculated.

The Min’ and Max’ modifications to the data Min and Max use the override min and max values if they are valid, otherwise use the same algorithm as in the previous case.

Power and Factor are calculated the same way as before.

The logic of the new calculations are as follows:

Xmajor: if there is a valid override entry for tick spacing, use it, otherwise, calculate it as before from Factor and Power.

Xmin: if there is a valid override entry for axis minimum, use it; otherwise, if there is a valid override entry for axis maximum, start counting down from the override maximum in increments of Xmajor, and use the largest value which is less than Min’; otherwise calculate as before.

Xmax: if there is a valid override entry for axis maximum, use it; otherwise, if there is a valid override entry for axis minimum, start counting up from the override minimum in increments of Xmajor, and use the smallest value which is greater than Max’; otherwise calculate as before.

This example with user overrides may be overkill. But I occasionally find it very useful. This post is my way of saving it to the cloud, so I can find it later in a search engine.

Janus Christensen says

Hi Jon,

Beatiful stuff. I have one question, though: why do you divide by log(10) in the power function? I’d say log(B7-B6) is sufficient because log(10) always yields a value of 1.

Cheers,

Janus

Jon Peltier says

Hi Janus –

I never even thought of that. It must be carried over from the VBA algorithm. If you don’t specify in VBA, Log is the natural logarithm.

Neil Wilson says

Hi Jon,

You just made my day with this post. It solves all my excel nightmares in one go. it fit perfectly to the problem I thought unsolveable. Can’t thank you enough.

Cheers,

Neil

Joan M. Claveria says

Nice job, Jon.

Is there a way for a chart in Excel 2010 to pick automatically the axis scale values calculated in the worksheet?

Cheers

Joan-Manel

Jon Peltier says

Joan –

There’s no automatic way to control axes like this, but I wrote a tutorial that shows how to Link Chart Axis Scale Parameters to Values in Cells.

joerg fricke says

I just want to say thank you for this very helpful tutorial. I was looking for an easy way to calculate a chart axis scale and ticks which works for various numbers (big, small, positive, negative). I’ll not use it in excel but in a self programmed app to show data in a chart. the chart needs to get programmed myself of course. Like in the old days.

Thank you very much.

Nicklas says

Hi Jon,

Thank you so much for the help! I just have one question towards your Macro, is there anyway you can automatically change the x and y values of the axis for quadrant graphs?

Thanks :)

Jon Peltier says

Nicklas –

I’m not sure what else is needed. What are you calling a quadrant graph?

James R says

Thanks for this.

I note though that there are some edge cases where this doesn’t work. Whilst this will work ok if all data values are 0 (albeit scaling from 0 to 1), it isn’t happy if all data values are non-zero, giving Min’ = Max’ and hence trying to find Log(Min’ – Max’) = Log(0), resulting in a #NUM error.

My suggested edit would be: Min’ =IF(B4=B5,B4-0.5,IF(B4=0,0,B4-(B5-B4)/100))

and Max’ =IF(B4=B5,B4+0.5,IF(B5=0,0,B5+(B5-B4)/100))

If all data is now 0, Min’; and Max’; are respectively -0.5 and 0.5, if all data is 1 then they are 0.5, 1.5 etc.

(Note: I wasn't wholly aware of the benefit of capping / flooring B4-(B5-B4)/100) at zero, hence removed the MAX / MIN from the formulae).

Jon Peltier says

James –

Good point. I think if all values are equal and positive, I might still work it so the minimum is zero, and if all are equal and negative, so the maximum is zero. Otherwise, this avoids the error in a sensible way.

P.S. I’ve incorporated your correction to Max’.