The Excel message Data source reference is not valid means the PivotTable cannot use the range, table, named range, or external source currently specified. In Microsoft Excel for Microsoft 365 on Windows, select a valid, rectangular data range with a header in every column, or convert the data to an Excel Table and create the PivotTable from that table.
What the error means
A PivotTable needs a usable source: a set of records organized in rows and columns, with one field per column. Excel stores a reference to that source when the PivotTable is created. If the reference points to a missing sheet, an invalid range, an inaccessible file, or a name that no longer refers to usable data, Excel cannot build or update the PivotTable and may show this error.
The message can appear when you first choose Insert > PivotTable, when you change the source through PivotTable Analyze > Change Data Source, or when you work with a PivotTable based on an existing named range or external connection. The wording is broad: it does not identify the precise fault, so the quickest fix is to check the source itself and then the reference Excel is using.
A related but different message is PivotTable field name is not valid. That often points to a blank or unusable column heading. Both errors can arise from poorly prepared source data, but the reference error more directly indicates that Excel cannot resolve the source location or object.
Common causes to check first
Start with the most common problems before rebuilding the PivotTable. A small change to the source layout, worksheet name, or file location may be enough to invalidate a reference.
- The selected range is not valid. The selection may be empty, may include only part of the intended data, or may not be one continuous rectangle. A PivotTable source should not be several separated blocks selected together.
- A worksheet or workbook reference has changed. A source sheet may have been renamed or deleted, or an external workbook may have been moved, renamed, or become unavailable.
- A named range is broken. A name used as the source may refer to a deleted sheet or to a range that no longer exists. A name that refers to a formula or an unsuitable multi-area selection may also fail as a PivotTable source.
- The header row is incomplete. Every source column needs a heading. A blank heading, merged header cells, or a header row included incorrectly can prevent Excel from interpreting the fields.
- The data was added outside the original source. If new rows sit below a fixed source range, they are not automatically included. This usually causes missing records rather than the exact reference error, but it is worth checking when a source has been edited.
- The range was typed incorrectly. A misspelled sheet name, incorrect cell address, or malformed external reference can make the source unresolvable.
How to diagnose the source
First find out what object the PivotTable is meant to use. Click inside the PivotTable and open PivotTable Analyze > Change Data Source. Review the Table/Range box. If it shows a worksheet range, check that the sheet exists and that the address covers the intended data. If it shows a table or named range, verify that object instead of assuming the displayed text is a cell address.
Next, inspect the source sheet. Confirm that the records form a single block, that the first row contains a distinct heading for each column, and that there are no merged cells in the heading row. Check that the selected range begins at the header row and includes at least one row of data. Blank rows within a dataset can complicate selection and may cause Excel to treat the data as separate blocks, so remove unnecessary gaps or select the full range explicitly.
If the source uses a name, open Formulas > Name Manager. Select the relevant name and examine its Refers to entry. A reference containing #REF! is broken. Update the reference to a valid range, or replace the name with a proper Excel Table as appropriate. If the PivotTable uses an external workbook, make sure the file still exists in the expected location and that you can open it.
Fix the error with a worksheet range
For a PivotTable based on cells in the current workbook, repair the source reference rather than starting over if the existing PivotTable is otherwise useful.
- Go to the source worksheet and confirm the complete data block, including its header row.
- Click inside the PivotTable and select PivotTable Analyze > Change Data Source.
- In the Table/Range box, select or enter the correct contiguous range, including all intended columns and rows. Check the sheet name and endpoints carefully.
- Select OK, then refresh the PivotTable using PivotTable Analyze > Refresh.
If you are creating a new PivotTable, select a cell inside the source data first and choose Insert > PivotTable. Check the detected range in the dialog before confirming it. If Excel does not identify the full block correctly, select the source range directly in the worksheet or enter its address in the dialog. Do not include unrelated notes or totals below the data unless they are intended to become records.
When the source is on another worksheet, it is often easier to select the cells using the dialog’s range-selection control than to type a reference from memory. This reduces mistakes in sheet names and punctuation. If a sheet name contains spaces, Excel’s generated reference normally encloses the sheet name in apostrophes; selecting the cells lets Excel format that reference for you.
Use an Excel Table for a changing source
An Excel Table is usually the better source when rows will be added over time. Click a cell in the data and use Insert > Table, confirm that the selected range is correct, and make sure My table has headers is selected. On Windows, Ctrl+T opens the Create Table dialog; on Mac, use Command+T. Give the table a clear name from Table Design > Table Name.
Create the PivotTable using the table as its source. Unlike a fixed cell range, a Table generally expands when data is appended directly beneath it, so the new rows can be included after the PivotTable is refreshed. Refreshing is still required; a Table does not update the displayed PivotTable results instantly. If data is pasted well outside the Table or separated from it by blank rows, check that the Table actually expanded before refreshing.
Tables also make the source easier to identify in the Change Data Source dialog. If a named range has become unreliable, converting a clean rectangular dataset to a Table and building a PivotTable from it can be a durable alternative. Avoid putting subtotals or grand totals inside the Table: PivotTables calculate their own summaries, and source totals can be counted as if they were ordinary records.
Named ranges and external workbook sources
Named ranges can be convenient, but they require maintenance. If a worksheet is deleted, renamed, or reorganized, a name may stop pointing to the intended cells. In Name Manager, edit the broken reference to point to the current source, or create a new name that refers to a single contiguous range with a header row. Then use that valid source when creating or repairing the PivotTable.
For a source in a different workbook, check both the reference and access to the file. A path may no longer work after a file is moved, a shared-folder location changes, or a drive is unavailable. Open the source workbook if possible, confirm that the relevant worksheet and data still exist, and use Excel’s source-selection controls rather than manually editing a long external reference. If the PivotTable is connected to an external database or another data connection rather than a worksheet range, inspect the connection settings and credentials; changing a cell range will not repair a broken connection.
When a file is shared with colleagues, consider whether everyone can access the same source location. A reference that works on one computer may fail for someone whose drive mapping or permissions differ. A workbook stored in a shared cloud location can also be more dependable than a reference to a personal local folder, provided users have permission to access it.
Prevent the problem and avoid misleading fixes
Keep source data in a simple tabular layout: one header row, one record per row, one field per column, and no merged headings. Use consistent column names and avoid blank rows or columns inside the data. When practical, use an Excel Table so the source can grow more safely. Before renaming or deleting source sheets, check whether PivotTables, formulas, or connections depend on them.
Do not assume that clearing the PivotTable or pressing Refresh will fix an invalid source reference. Refresh can update results only when Excel can still reach a valid source. Likewise, changing the report layout does not repair a broken range. Find and correct the source object first; then refresh. If the source is valid but the error persists, save a copy of the workbook, close and reopen it, and check whether the PivotTable is based on a connection or cache that differs from the worksheet range you expected.
One common misconception is that every missing newly added row causes this exact error. If a PivotTable was built from a fixed range and the source simply grew beyond that range, the PivotTable may still work but omit those rows. Update the range or use a Table, then refresh. The reference error is more likely when Excel cannot resolve the specified object at all.
Frequently Asked Questions
Why does Excel say the PivotTable data source reference is not valid?
Excel cannot resolve the selected source as a valid range, table, name, or connection. Check the source address, worksheet and workbook availability, named-range definition, and header row.
Can blank column headings cause this error?
They can stop Excel from recognizing the source fields and may produce the related field-name error. Add a heading to every column, unmerge header cells, and select the complete data block again.
Why is my new data missing from the PivotTable?
A PivotTable based on a fixed cell range does not necessarily include rows added beyond that range. Extend the source through Change Data Source, or use an Excel Table as the source, and then refresh the PivotTable.
Will Refresh fix an invalid source reference?
Not by itself. Refresh updates the PivotTable only if its source is still accessible and valid. Repair the range, name, or connection first, then refresh.
How do I fix a broken named range used by a PivotTable?
Open Formulas > Name Manager, locate the name, and correct its Refers to entry so it points to a valid source. If the name is no longer needed, use a valid cell range or Excel Table instead.
Related
- Combine Excel Files into One: Methods and Best Practices
- How to Combine Excel Files: Methods and Common Issues
- Combine Multiple XML Files Into One Excel Workbook
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Manual Sort Pivot Chart in Excel: Control Category OrderTo manually sort a PivotChart in Excel for Microsoft 365 on Windows, reorder the chart’s categories in its…
- Google Sheets Pivot Tables: Create, Customize, and RefreshYes, you can create a pivot table in Google Sheets using Insert > Pivot table. Select a range…
- Excel Pivot Table Errors: Causes and FixesMost Excel PivotTable errors come from a blank or invalid source-data header, a changed data source, values that…
- Radar Chart in Excel: How to Create and Format OneTo make a radar chart in Excel for Microsoft 365 on Windows, arrange categories in the first column…
- Excel KPI Dashboard Templates: How to Choose and UseExcel KPI dashboard templates are prebuilt workbooks that turn performance data into a compact view of key measures,…
- Gantt Chart Template Excel for Tasks: How to Build OneFor a simple Gantt chart template in Excel, list each task with its start and finish dates, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.