Chart & Visualization Issues

How to Fix Excel Dynamic Chart Named Range Formula Errors

Maira Mehtab Sep 20, 2026 869 views

Question details

The user encounters an error message when attempting to use a named range containing an INDIRECT formula for axis labels or custom data in a dynamic Excel chart.

Product: Excel
Device & OS: not provided
Scenario: Assigning dynamic named ranges constructed with the INDIRECT function to an Excel chart data series or axis labels.
Observed behavior: Excel rejects the named range and reports that the formula contains an error, preventing the chart from updating dynamically.

Before you start

Before modifying your chart settings, verify that your workbook calculation options are set to Automatic and familiarize yourself with the Name Manager in the Formulas tab.

Solution 1Recommended

Verify Named Range Syntax and Add Sheet References

Test the INDIRECT formula in standard worksheet cells to confirm it evaluates properly, then apply it to the chart using an explicit sheet reference.

Excel charts are exceptionally strict regarding formula structures. If an INDIRECT function returns a #REF! or #VALUE! error, the chart will entirely reject the named range.

Furthermore, unlike standard cell formulas, Excel charts require that any named range be prefixed with the specific worksheet name or the workbook name in the Select Data dialog.

Test formula validity in a cell

Select an empty cell and use a function like =SUM(YourNamedRange) or highlight the INDIRECT formula text and press F9. Ensure it calculates a value instead of returning an error.

Review the Name Manager configuration

Navigate to Formulas > Name Manager, select your dynamic named range, and click inside the 'Refers to' box at the bottom. Verify that the marching ants outline the correct dynamic data area.

Apply explicit sheet referencing

Right-click the chart and choose 'Select Data'. Edit your series or axis labels, and enter the named range strictly in the format '=SheetName!NamedRange' (e.g., =Sheet1!DynamicData).

Handling Spaces in Sheet Names: If your worksheet name contains spaces, you must enclose it in single quotation marks within the chart reference, such as =‘Sales Data’!NamedRange.

Create Dynamic Charts Flawlessly in WPS Spreadsheets

Avoid frustrating formula rejections by building your dynamic dashboards in WPS Spreadsheets. It offers an intuitive Name Manager and fully supports complex INDIRECT and OFFSET formulas for automated chart updates.

1. Open your dataset: Launch WPS Spreadsheets and open the workbook containing your raw chart data.
2. Define the dynamic range: Navigate to the Formulas tab, click Name Manager, and define a new name utilizing your INDIRECT or OFFSET logic.
3. Insert the visualization: Go to the Insert tab, select your preferred chart type, right-click the blank chart area, and click 'Select Data'.
4. Link the named range: In the series values or axis labels input field, type your sheet name followed by an exclamation mark and the named range (e.g., =Sheet1!MyDynamicRange), then click OK.

100% format compatibility with Microsoft Excel (.xlsx) files and dynamic array formulas.Intuitive Name Manager interface for quickly defining and auditing complex dynamic ranges.Lightweight architecture ensuring fast performance even with data-heavy visualizations.Free to use with comprehensive chart customization and professional templates.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel reject my named range in a chart even if it works perfectly in a cell?

Cell formulas can use named ranges without prefixes (e.g., =MyRange), but chart data sources require absolute context. You must include the worksheet or workbook name in the chart's formula input box, formatted as =Sheet1!MyRange or =Workbook.xlsx!MyRange.

Can I type the INDIRECT function directly into the chart data source?

No. Excel and most spreadsheet software do not allow volatile functions like INDIRECT or OFFSET to be typed directly into the chart's 'Select Data' dialog box. You must first wrap the formula inside a Named Range, and then assign that Named Range to the chart.

How do I troubleshoot a #REF! error inside my INDIRECT formula?

A #REF! error typically occurs if the text string constructed inside the INDIRECT function does not perfectly match an existing worksheet name or valid cell reference. Check your spelling and ensure that sheet names containing spaces are wrapped in single quotation marks within the formula.

Excel & Data Problems