Chart series extents and ranges of data can be inconsistent (Figure 1). Series might not contain all data as data are added annually. Alternatively, series can extend well beyond available data to capture incremental data additions, but workbook performance can be degraded. Noticeable and needless degradation of performance occurs where series are defined across greater than 100,000 rows to plot less than 1,000 rows of values.
Figure 1.— Example of discrepancies between series extent and rows of data on a worksheet.
Inconsistencies between series extents and data ranges can be rectified for a single chart, all charts in a workbook, or all charts in multiple workbooks with ExtendCHARTS macro. This macro identifies all charts in selected workbooks. Each series formula, SERIES(Name, Category-X, Values-Y, iOrder), in a chart is examined, where Category-X is the range of labels or ordinate values on the X axis and Values-Y is the range of values on the Y axis. Ranges in Category-X and Values-Y are fit to available data by selecting the first cell in a range and extending the range by either of two methods. The first method assumes a continuous range and searches downward to end of a contiguous data block. The second method searches upward from the bottom of a worksheet to first filled cell, which allows for empty cells in a charted range.
The ExtendChartMACRO.v?.xlsm workbook consists of a single control on one page that launces the Specify Workbooks form. The form allows selection of a workbook or multiple workbooks to be processed. Method of fitting series extent to data ranges can be changed between assuming continuous data or data with missing values in a series. All charts and series changed optionally can be summarized in a new workbook (Figure 2).
Figure 2.—Example workbook that summarizes series where series extent was paired with data range. Default view shows series where extents changed. Chart series that use data ranges in external workbooks are highlighted in red.
The file ExtendChartMACRO.zip contains,
- Macro for fitting series to data ranges in ExtendChartMACRO.v6.xlsm,
- Example files B2_ExtraFRAG.xlsx, B2_OtherWB.xlsx, B2_TestPAGES.xlsx, and ChartAutoXY.xlsm, and
- Explanatory PDF.
Zip file can be downloaded with the following link.
Revisions
September 22, 2023—Revisions through version 6 are initial release.
October 20, 2023—Corrected bug that stopped execution where charts were on independent pages and no charts were embedded on worksheets.