Chart & Visualization Issues

Fix Excel Named Range Cannot Be Used as a Chart Data Source

Muhammad Talha Sep 28, 2026 869 views

Question details

The user is unable to use a named range as a data source for an Excel chart due to an invalid reference error.

How to Fix Excel Named Range Cannot Be Used as a Chart Data Source

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating or updating an Excel chart using a defined named range as the data source.
Observed behavior: Excel displays an invalid reference error and refuses to accept the named range as the chart's data source, often due to unresolved references or #REF! errors.

Before you start

Ensure the workbook containing the named range is currently open and saved. Note the exact spelling of the named range you are trying to use, as exact syntax is required for chart data inputs.

Solution 1Recommended

Check and Repair Name Manager References

Identify and resolve any broken links or #REF! errors in your defined names.

A named range must resolve to a valid range in an open worksheet before it can be used by a chart. If cells were deleted or moved, the named range might be broken.

Open Name Manager

Go to the 'Formulas' tab on the Excel ribbon and click 'Name Manager'.

Inspect the Refers To Field

Locate the named range you want to use for your chart and check the 'Refers to' column at the bottom of the dialog box.

Fix Broken References

Look for #REF! errors or unresolved external workbook links. Delete the error text and re-select the correct cell range on your worksheet.

Save Changes

Click the checkmark icon next to the input box or click 'Close', then confirm saving the updated named range formula.

Check and Repair Name Manager References

Workbook-Qualified Syntax: When entering the named range in the chart data source, you must include the workbook or sheet name, such as ='MyWorkbook.xlsx'!MyNamedRange.

Solution 2

Delete and Recreate the Named Range

If repairing the existing name fails, creating a fresh named range ensures a clean data connection without hidden errors.

Free Spreadsheet Software

Use WPS Spreadsheet for Seamless Charting and Data Management

WPS Spreadsheet makes managing named ranges and creating dynamic charts effortless. It provides a robust, easy-to-use Name Manager and is fully compatible with Microsoft Excel file formats.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook.
2. Define Your Range: Select your chart data, go to the 'Formulas' tab, and click 'Name Manager' to create a new defined name.
3. Insert Chart: Navigate to the 'Insert' tab, choose your preferred chart type, and select your named range as the data source.

Highly compatible with Microsoft Excel (.xlsx, .xls) files and chart dataIntuitive Name Manager to easily define and track data rangesFree, lightweight, and fast alternative to Microsoft OfficeRich library of customizable chart templates

microsoft office alternative - wps office

Frequently Asked Questions

Why does my named range work in formulas but not in charts?

Charts require strict referencing. Even if a formula accepts a globally defined name, an Excel chart requires the worksheet or workbook name to precede the named range (e.g., ='WorkbookName.xlsx'!NamedRange).

Can I use dynamic named ranges for Excel charts?

Yes, you can use OFFSET or INDEX functions within the Name Manager to create a dynamic named range that updates automatically as you add new data. However, ensure the formula resolves correctly to an actual range without returning errors.

What happens if the named range refers to a closed workbook?

Excel charts generally cannot read data dynamically from a closed workbook using a named range. The source workbook must remain open for the chart data to resolve and update correctly.

How do I fix a #REF! error in Name Manager?

Open the Name Manager from the Formulas tab, select the defined name containing the error, and modify the 'Refers to' field by manually highlighting the correct, existing cells on your current worksheet.

Excel & Data Problems