| Dynamic ranges for chart data | Alphabetical Index | Exporting Chart Images |

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

excel 2010

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.

Source data for an Excel chart and calculated axis values

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:

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.

charts with alternative minimum and maximum axis values
[Chart A] X Axis ranges set to calculated worksheet cells [Chart B] X Axis ranges set to Automatic values

adding VBA code to a worksheet

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.

Creating an Event linked to worksheet changes

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.
Document is in PDF format Click here for details about
obtaining this file

file: ExcelChartAxis.htm Page last updated: Feb14 2015 CMIDX S7 P9 N