Linking a minimum or maximum chart axis value to a cell on a worksheet

When you create a chart in Excel, by default the range of axis values and gridline settings are set automatically. Typically the minimum value will be zero. This might be logical and may result in a fair representation of the data. However, in some circumstances where there are several similarly high values, this can result in line points or columns which are difficult to distinguish from one another. It is possible to edit this setting and replace the auto generated figures with specific fixed values.

If a chart is linked to a table which might get updated to contain widely differing ranges of values, it is not possible to set useful fixed minimum or maximum values. It might be helpful to link these settings to calculated cells on a worksheet.

In the following example, the user selects (in C1) values from site 1, 2 or 3. The values are automatically read in a table (B4:C8) which is the source data for a chart.

Cells D11:D12 contain the actual MINumum and MAXimum values for the current values of clinic attendances. Cells C11:C12 use calculated values for the minimum and maximum axis values which are required for this particular chart. The formulas used in this instance are:

• [C11] =INT((D11-1)/(10^(LEN(D11)-1)))*(10^(LEN(D11)-1))
• [C12] =INT((D12+(10^(LEN(D12)-1)))/(10^(LEN(D12)-1)))*(10^(LEN(D12)-1))

The purpose of the repeated component of the formulas ((10^(LEN(D11)-1)) is to identify that the values are 10's, 100's, 1000's or whatever. This is used to appropriately round the values.

[Chart A] X Axis ranges set to calculated worksheet cells	[Chart B] X Axis ranges set to Automatic values

It is necessary to use a small amount of visual basic code to make this happen. Right click on the sheet name label and then select View Code. In the visual basic module, change the two top dropdown options to Worksheet and Change. This instructs the program to run the macro and reset the Axis values each time a change is made to the worksheet.

Note that you need to know the name (assigned by Excel) of the Chart for the visual basic code. This can be identified by recording a temporary macro in which the chart is selected. The macro code will reveal the name.

ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue).MinimumScale = Range("C11").Value
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue).MaximumScale = Range("C12").Value

Get information from this website as a document accompanied by Excel worksheets. This page is NOT currently included.

Click here for details about obtaining this file