Fix an Excel Chart That Does Not Recognize a Dynamic Named Range
Question details
The user needs to determine why an Excel chart does not recognize an OFFSET-based dynamic named range.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A chart uses a dynamic named range, while a similar date range works in another chart.
- Observed behavior
- The chart does not recognize or use the intended dynamic range.
Identify the chart series and the named range it should use. Locate the cells or names that control the OFFSET formula, including chtlen and Progress!$AI$1, and check what values they contain.
Check the OFFSET Formula and Its Input Values
Use this check when the named range may point to the wrong cells or return an invalid range.
In Excel, select Formulas > Name Manager, choose the range used by the chart, and inspect its Refers to formula.
Check the formula against OFFSET(reference, rows, columns, height, width). Confirm that row and column offsets point to the intended starting cell and that height and width match the data orientation.
On the Progress sheet, inspect cell AI1, and use Formulas > Name Manager to inspect chtlen. Confirm that the values used in the calculation are numeric and that the resulting height and width are positive and within the worksheet.
Select the name in Formulas > Name Manager and inspect its Refers to range. In empty cells, enter =ROWS(YourRangeName) and =COLUMNS(YourRangeName), replacing YourRangeName with the actual name, to check the returned dimensions.

Correct the Chart Series Reference to the Named Range
Use this solution when the named range works in the worksheet but the chart series does not use it correctly.
Work With Spreadsheet Charts in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files to work with data and charts while moving between office suites; it does not repair Excel-specific chart issues.

Frequently Asked Questions
What do the arguments in OFFSET mean?
OFFSET uses the order OFFSET(reference, rows, columns, height, width). Rows and columns move from the reference; height and width determine the size of the returned range.
How can I tell whether my named range is vertical or horizontal?
Enter =ROWS(YourRangeName) and =COLUMNS(YourRangeName) in empty cells, replacing YourRangeName with the actual name. A vertical range typically has multiple rows and one column; a horizontal range has one row and multiple columns.
How do I add a named range to an Excel chart series?
Right-click the chart, choose Select Data, select the series, and click Edit. In Series values, enter a workbook-scoped reference such as ='Workbook.xlsx'!ChartValues, using your actual workbook and range names.
Why might AI1 or chtlen cause the OFFSET range to fail?
If either value is nonnumeric, produces an invalid offset, or makes the range height or width zero or negative, the formula may not return a valid range. Check AI1 on the Progress sheet and inspect chtlen in Formulas > Name Manager.




