If Excel reports that a PivotTable data source reference is not valid, check that the source is a real, contiguous range or Excel table with a nonblank header in every column. Then create the PivotTable again or, for an existing one, use PivotTable Analyze > Change Data Source to select the corrected source.
What the reference error means
Excel needs a usable source before it can build a PivotTable: a defined range of cells, an Excel table, or a supported external connection. The reference error means Excel cannot interpret or access the source you supplied. It does not necessarily mean the underlying records are damaged. Often the issue is a mistyped range, a missing worksheet, or an incomplete selection.
Excel may show wording such as “The PivotTable field name is not valid” rather than “data source reference is not valid.” These messages often point to the same first checks: confirm that the selected source exists and that its first row contains a heading for every column. Other PivotTable errors can have different causes, so note the full message before changing the workbook.
A PivotTable summarizes records by fields, so Excel expects a consistent, tabular layout. For example, a sales list with Date, Region, Product, and Amount headings is a suitable source when each record occupies one row. A decorative title above the headings, merged heading cells, or a completely blank column splitting the list can make the intended source harder to identify.
Common reasons Excel rejects a source
- The range is mistyped or no longer exists. The source may refer to the wrong sheet, omit the exclamation mark between a sheet name and cell range, or include an invalid address.
- A worksheet was renamed or deleted. A reference to an old sheet name can become invalid, particularly when a source was entered manually or is stored as a defined name.
- One or more column headings are blank. Each source column needs a header. A blank cell in the header row is a frequent reason for the field-name error.
- The selection includes an unsuitable layout. Merged cells, multiple header rows, subtotal rows mixed into the records, or separated blocks of data may prevent Excel from treating the selection as one table.
- A defined name is broken. A named range may point to a deleted sheet or display a #REF! error in Name Manager.
- The source is in another workbook or connection. The workbook may have moved, the external source may be unavailable, or a connection may need attention. This differs from a simple range-selection problem.
Not every blank row or column is automatically fatal, but empty separators can cause Excel to select only part of a list or to interpret the source differently than you intended. Check the actual range shown in the PivotTable dialog rather than assuming Excel has found every record.
Check the source before rebuilding
Start with the source worksheet, not the PivotTable layout. Find the row that contains the field names and confirm that the records below it use the same columns. The source should be a single rectangular block: one header row followed by data rows, with no merged cells in that block. Give every column a meaningful, nonblank heading; distinct names also make fields easier to identify in the PivotTable Fields list.
Next, click a cell inside the data and look at the range Excel identifies. If the records are meant to run from A1 to F500, but the dialog shows only A1:D300, select the intended cells or convert the list to a table. If you type a range, verify the sheet name and address. A sheet named Sales Data, for example, needs the sheet name quoted in a worksheet reference because it contains a space. Selecting the cells with the mouse is often safer than typing a long reference.
If the source uses a defined name, choose Formulas > Name Manager and inspect the name’s Refers to entry. A reference containing #REF! is broken. Correct the reference to the current source cells, or use a table or direct range instead. If the name is used elsewhere in the workbook, check those dependencies before replacing or deleting it.
Create a PivotTable from a corrected range
- Fix the source headings and layout. Remove merged cells from the data block, and make sure each column has a heading.
- Click a cell within the source data. If the source is a normal range, select the full block when Excel does not identify it correctly.
- Choose Insert > PivotTable. In the Create PivotTable dialog, review the Table/Range field and correct it if necessary.
- Choose whether to place the PivotTable on a New Worksheet or an Existing Worksheet, then select OK.
- In the PivotTable Fields list, drag fields into Rows, Columns, Values, or Filters as needed.
If the source is not on the active sheet, use the dialog’s range selector to choose it. Avoid including a report title or notes above the actual column headings. For a source with 500 data rows and a header row in row 1, the intended selection would include the header and all records, not just the records without their field names.
On Excel for Mac, the main workflow is also Insert > PivotTable, although the dialog and ribbon labels can differ slightly by release. Excel for the web has a different feature set and interface; if you cannot edit the source or find the required command there, open the workbook in the desktop app for Microsoft 365 on Windows or Mac.
Change the source for an existing PivotTable
When the PivotTable already exists, select a cell inside it. On Windows, open PivotTable Analyze > Change Data Source, review the Table/Range box, enter or select the corrected range, and confirm. Then refresh the PivotTable using PivotTable Analyze > Refresh, or use Data > Refresh All when you need to update multiple connections and PivotTables.
Changing the source range does not always solve the problem if the source itself is malformed. Correct blank headings or broken references first. Also check that the new range includes the header row and every record, and that it is on the intended worksheet. If the source is an Excel table, use the table’s name in the source field when available rather than guessing its current cell address.
If Change Data Source is unavailable or the PivotTable uses an external connection or the workbook Data Model, the source may not be an ordinary worksheet range. In that situation, inspect the connection or model settings rather than forcing a cell range into the dialog. For a workbook created by someone else, ask where its source data is stored and whether you have access to it. A refresh can fail because an external file or database is unavailable even when the PivotTable itself is intact.
Why an Excel table can prevent repeat errors
For a regularly updated list, converting the records to an Excel table is usually more reliable than keeping a fixed range such as A1:F500. Select a cell in the list, choose Insert > Table, confirm the range, and ensure My table has headers is selected when appropriate. You can also use Home > Format as Table. Excel tables have names and expand when new rows are added directly below them, making them a convenient PivotTable source.
When you create a PivotTable from a table, Excel can use the table name as its source. Adding records to the table then expands the source automatically. The PivotTable still needs to be refreshed to show newly added data; expanding the source and updating the summary are separate actions. If new rows are pasted outside the table rather than into it or directly below it, confirm that the table expanded before refreshing.
A table does not repair bad data structure by itself. It still needs a header for each column, and each record should use the same set of fields. Avoid placing totals inside the list: PivotTables calculate summaries, and a total row included as an ordinary record can be counted again. Keep notes and separate reports outside the table.
Less obvious cases and useful distinctions
A filter, hidden row, or sorted list is not usually a broken reference. These affect which records are visible or their order, not whether a cell range exists. Likewise, a missing field in some records is a data-quality issue rather than necessarily a reference error. Focus first on the source address, headings, and layout.
Be careful not to confuse a source-reference problem with a PivotTable that simply shows old results. If the source is valid but its records have changed, refresh the PivotTable. If the source range has moved or grown, change the source as well as refreshing. Refreshing alone will not necessarily extend a fixed range to include records added below its last row.
If the error appears while opening or refreshing a workbook that uses an external file, confirm the file path and access permissions. If it uses a database connection, confirm that the connection is still configured and that the data is available. Do not replace an external source with a worksheet range unless you know that is the intended design; doing so can change how the report is maintained.
For a protected worksheet or workbook, you may need permission to change the source or edit the PivotTable. Protection is not the same as an invalid reference, though it can prevent the repair steps. If the workbook is shared, make a copy before making structural changes, especially when other reports or formulas may depend on the same source.
Prevent the error in future workbooks
- Keep one header row with a distinct, nonblank label for every field.
- Store records in one continuous table, without merged cells or explanatory text inside the data.
- Use an Excel table for lists that gain rows over time, and refresh the PivotTable after updates.
- When renaming or deleting a source sheet, check defined names and PivotTables that may refer to it.
- For external sources, document the file location or connection owner so the source can be restored if it moves.
These habits make the workbook easier to maintain, but they do not guarantee that a moved external file or changed database connection will remain available. A clear source layout and a quick check of the Table/Range field make it much easier to identify what changed.
Frequently Asked Questions
Why does Excel say the PivotTable field name is not valid?
Most often, one or more columns in the selected source have a blank heading, or Excel is treating the wrong row as the header. Check that the first row of the selected source contains a label in every column, then select the correct range again.
How do I fix a PivotTable source after adding rows?
If the source is a fixed cell range, select the PivotTable and use PivotTable Analyze > Change Data Source to include the new rows, then refresh. If the source is an Excel table, confirm the added rows are inside the table, then refresh the PivotTable.
Will refreshing fix an invalid PivotTable reference?
Not usually. Refresh updates a PivotTable from its existing source; it does not correct a misspelled sheet name, broken defined name, or incomplete fixed range. Repair the source first, then refresh.
Can a PivotTable use data from another worksheet?
Yes. The source can be on another sheet in the same workbook. In the Create PivotTable or Change Data Source dialog, select the intended range on that sheet and verify that the selected block includes its headings and records.
Why is Change Data Source unavailable?
The PivotTable may be based on an external connection or the Data Model rather than a normal worksheet range, or you may lack permission to edit it. Check the workbook’s connection and protection settings, and use the desktop Excel app if you are working in Excel for the web.
Related
- Excel Keyboard Shortcut for Next Sheet: Ctrl+Page Down
- Excel Keyboard Shortcuts to Find the End of Your Data
- Excel Fill Color Shortcut: Apply Color Quickly
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Sparklines Formula: How to Create and Customize ThemExcel does not have a worksheet formula called SPARKLINE: a sparkline is a small chart inserted into a…
- Tree Chart in Excel: Create and Format a TreemapIn Excel, a “tree chart” usually means a Treemap: a chart that displays hierarchical categories as nested rectangles…
- Excel Chart Overlay: How to Plot Two Data Sets TogetherYes. In Excel for Microsoft 365 on Windows, you can overlay two data sets by adding both as…
- Pareto Chart in Excel: Use Word Labels and CountsYes—an Excel Pareto chart can use words such as “Late delivery” or “Damaged item” as category labels, with…
- Excel Pivot Table Chart Maker: Create PivotChartsTo make a chart from an Excel PivotTable in Microsoft 365 for Windows, click inside the PivotTable and…
- Excel Trendline Formula in a Cell: Methods and ExamplesYes. To calculate a linear trendline estimate in an Excel cell, use FORECAST.LINEAR or calculate the slope and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.