Chart Range

Chart Name

The name of the chart to apply the function to. The browse button (…) opens a list of every configured chart within the workbook to select from. The list is in the format:

Chart Name – Sheet!Range of the chart

If a chart is selected in the list, the worksheet for the chart is activated and the range of the chart is selected to highlight the chart itself.

The chart name is not visible and is typically not set by the user. When a chart is inserted into a worksheet it is given a default name (Chart X where X is a number that starts at 1). However, if the worksheet is copied to another sheet, if the default name is left it can be changed on the new worksheet. To combat this, charts in the template are renamed to a fixed name (xlrX where X is a number that starts at 1) so that chart names are consistent between worksheets.

Note that only charts configured to worksheets are listed. Charts configured on a Chart Sheet cannot be used with this function.

Add/Remove Series

If set to Yes, when the function is executed, it will ensure that the number of series in the chart match the number of columns (or rows depending on Direction) in the Source range.

If there are more series configured than there are columns (or rows) in the Source range, the extra series are removed.

If there are less series configures than there are columns (or rows) in the Source range, those series are added to chart. The series properties like color, weight, thickness, etc. are determined by the defaults of the Design Studio.

The rule of thumb here is that if you want total control over the formatting of every series set up the chart for the worst case and let this function remove unused series. Otherwise just set up the first series and let this function add the others as needed.

X-Axis Ticks

The number of tick marks and labels displayed on the X-axis of the chart. Set this to 0 to automatically determine the number of tick marks and labels.

Adjust Y-Axis

The scaling of the Y-axis of the chart. Set to None to use the default scaling set for the chart, Automatic to determine the minimum and maximum as the minimum/maximum of the values of the series plus +/- 1% or Custom to use cell values for the minimum and maximum.

Y-Axis Minimum

If Adjust Y-Axis is set to Custom this setting must be set to a cell reference containing the minimum scale value for the Y-Axis.

If the chart has a secondary axis, the minimum scale value for it can be specified as a range of two cells either horizontally or vertically. E.g., $B$4:$B$5 means that the minimum for the primary axis comes from cell $B$4 and the minimum for the secondary axis comes from cell $B$5. Conversely a range of $B$4:$C$4 means that the minimum for the primary axis comes from cell $B$4 and for the secondary comes from cell $C$4.

If the cell is not on the Active By sheet, the sheet must be specified, e.g., Sheet1!$B$5.

Y-Axis Maximum

If Adjust Y-Axis is set to Custom this setting must be set to a cell reference containing the maximum scale value for the Y-Axis.

If the chart has a secondary axis, the maximum scale value for it can be specified as a range of two cells either horizontally or vertically. E.g., $C$4:$C$5 means that the maximum for the primary axis comes from cell $C$4 and the maximum for the secondary axis comes from cell $C$5. Conversely a range of $C$4:$D$4 means that the maximum for the primary axis comes from cell $C$4 and for the secondary comes from cell $D$4.

If the cell is not on the Active By sheet, the sheet must be specified, e.g., Sheet1!$B$5.