Refreshing Charts From a Changing Data Range in Excel

The chart looked right on the day it was made. A month later the data has 31 more rows, and the line stops in March. Or the data refreshed from the database and the chart shows an axis with twelve blank slots at the end. Neither is a mystery. A chart in Excel is a list of references to cell ranges, and those references do not change on their own. This article explains why charts miss new data, and the four ways to keep them current: tables, dynamic names, generated ranges and careful refresh. It is the maintenance side of charts from data pulled out of databases and follows the build steps in sending charts from a query to an Excel sheet.

Why a chart misses new rows

Select a chart's series and the formula bar shows something like =SERIES(Data!$B$1, Data!$A$2:$A$13, Data!$B$2:$B$13, 1). The categories are cells A2 to A13 and the values are B2 to B13, fixed. Add a thirteenth month in row 14 and the chart does not know. Excel does extend a chart range automatically in one situation, when you type directly adjacent to a contiguous block that the chart covers entirely, and that heuristic fails with a total row, a blank row or an edit made by a script.

The reverse happens when data shrinks. A refresh that returns eight rows into a range sized for twelve leaves four empty slots, so the chart has a gap on the right or plots zeros.

Fix one: base the chart on an Excel table

An Excel table (Insert, Table, or Ctrl+T) is a named, structured range that grows and shrinks as rows are added or removed. A chart whose series refer to table columns follows the table.

To set it up: select the data, make it a table and name it (Table Design, Table Name), then create the chart from the table. The series references still show cell addresses, but Excel keeps them tied to the table's size. Add a row under the table and the table expands, the chart picks the row up and the formulas that refer to the table (Sales[Value]) include it.

From code, XlsxWriter can add a table with ws.add_table(first_row, first_col, last_row, last_col, {"name": "Sales", "columns": [...]}), and openpyxl has Table objects. Be aware that the chart's references written by a library are plain addresses; they follow the table when a person adds rows in Excel, but a script that rewrites the data with more rows must also rewrite the table range, or the chart will still stop short. The reliable version for scripts is the generated range below.

Fix two: a dynamic named range

For a data range that is not a table, define names that grow. In Formulas, Name Manager, create two names:

ChartCats  =Data!$A$2:INDEX(Data!$A:$A, COUNTA(Data!$A:$A))
ChartVals  =Data!$B$2:INDEX(Data!$B:$B, COUNTA(Data!$A:$A))

COUNTA counts the filled cells in column A (including the header), INDEX returns a reference to the last of them and the colon builds a range from A2 to that cell. Then change the chart's series to use the names: =SERIES(Data!$B$1, 'Book1.xlsx'!ChartCats, 'Book1.xlsx'!ChartVals, 1). The sheet-qualified name form is required in the series dialog.

This pattern avoids OFFSET, which also works but is volatile and recalculates on every change. INDEX is not volatile. Requirements: the column used for COUNTA must have no gaps, and nothing else may sit below the data in that column. If the data has gaps, count with MATCH on a large number instead. This works in every Excel version and survives a refresh that changes the row count.

Fix three: compute the range in the generator

If a script writes the workbook, make it write the chart's references from the result size:

n = len(df)          # data rows
chart.add_series({
    "name":       ["Data", 0, 1],
    "categories": ["Data", 1, 0, n, 0],
    "values":     ["Data", 1, 1, n, 1],
})

n follows the data. Every run produces a chart sized for its own result. In openpyxl: Reference(ws, min_col=2, min_row=1, max_row=len(df) + 1). This is the simplest and the most reliable approach for generated reports, and the one to choose when nobody edits the sheet by hand afterward.

Handle the zero-row case. A chart with an empty range may produce a file that Excel flags as corrupt, or an empty plot. If n == 0, skip the chart and write a note: "No data for this period."

If both a script and a person touch the data, combine approaches: the script writes the data as an Excel table (so a person adding rows extends it) and sets the table range from the result size.

Fix four: handle refreshes properly

When the data comes from a connection (Data, Get Data, or a database query), the refresh replaces the table's contents. Two things can go wrong. If the connection is set to preserve column sorting and filtering, or to insert cells for new data, it can push neighboring cells around. And if the chart points at a plain range outside a table, the refresh may delete and recreate the cells, leaving the chart with #REF! references.

Settings that help, in the connection properties of the query table:

• Turn Insert cells for new data, delete unused cells off, and prefer "Overwrite existing cells with new data, clear unused cells".

• Keep Preserve column sort/filter/layout off for generated data.

• Load the result into a table, not a plain range, so that the chart follows.

After setting up, test it: refresh with a query that returns fewer rows, then more, and watch the chart. A chart that survives only the happy path is a chart that breaks in a quarter.

For Power Query, load to a table and base the chart on it (refreshing Power Query data on a schedule covers the refresh side).

Gaps and zeros that look like range bugs

Some problems that look like range errors are not.

Empty cells. In Chart Tools, Select Data, Hidden and Empty Cells, you choose whether empty cells show as gaps, as zero, or whether a line connects across them. The default depends on the chart type. For a line chart, "gaps" breaks the line at a missing value; "connect data points with line" bridges it. Choose deliberately: a bridge hides missing data, a zero lies about it.

**Formulas that return "".** A cell holding an empty string is text, not empty, so a chart plots it as zero. To make a point disappear, return NA() and let the chart skip it; #N/A is ignored by line charts.

Hidden rows. By default, charts omit data in hidden rows and columns. If a filter hides rows, the chart shrinks with it. The same dialog has "Show data in hidden rows and columns" if you want it all.

Text in numeric columns. A stray "n/a" turns that column into text for the chart, and the points are treated as zero. Clean the data first.

A total row. A summary row directly under the data, included in the range by accident, gives a giant last bar. Keep totals out of chart ranges or put them above the table.

Pivot charts

A pivot chart follows its pivot table and refreshes with it, so it handles changing sizes without a named range. The costs are the pivot cache inside the file, the loss of some formatting on refresh (enable "Preserve cell formatting on update" in the pivot options) and the need for Excel to refresh it, which a script cannot do without Excel. They fit interactive use. For generated reports, a regular chart over a computed range is simpler.

Check that it works

Make the range test part of the build. After saving, reopen the workbook with openpyxl and assert that each chart's reference size matches the data: len(chart.series[0].val.numRef.f) holds the formula string, from which you can parse the last row. Alternatively, keep the test at the data level: assert that the sheet contains n + 1 rows and that the code computed n from the same frame. Then perform the manual refresh test once whenever you change the layout (testing a generated Excel report lists the other checks).

When a chart range is not the cause

If a chart is wrong with the range correct, look elsewhere. The query may be returning the wrong rows, a date column may be text so the axis shows labels rather than a time scale, or the axis may be fixed at a maximum that clips the data (check Format Axis, Bounds). A chart that shows stale numbers may be reading a sheet whose calculation mode is manual. Those are covered in avoiding misleading axes in Excel charts and charts from data pulled out of databases. Microsoft's guide to creating a table and the XlsxWriter tables documentation are the two references to keep.