Excel Pivot Table Data Source Not Valid? How to Fix

Charts & pivot tables · Updated November 21, 2026

If Excel says “Data source reference is not valid” when you create or change a PivotTable, check that the source is one valid, contiguous range with a header in every column, then choose it again in the PivotTable dialog. Also check that the workbook is saved under a normal file name and that any named range you selected still exists.

What the error means

A PivotTable needs a source Excel can identify and read: typically a rectangular block of cells on a worksheet, an Excel Table, or a supported connection. The error means Excel could not use the reference supplied for that source. It does not necessarily mean the records themselves are damaged, and it is different from an error that appears after a PivotTable has been created.

Excel has several related messages. “Data source reference is not valid” points toward the source reference or how Excel interprets it. “PivotTable field name is not valid” more specifically suggests a problem with the column headings, often a blank heading. If a PivotTable already exists but shows old results, the likely issue is that it needs refreshing or its source range does not include the new rows. Identify the exact message and when it appears before changing the data.

In Microsoft 365 for Windows, create a PivotTable from Insert > PivotTable. For an existing PivotTable, use PivotTable Analyze > Change Data Source. The names or placement of controls can differ slightly in other Excel versions, but the source still needs to be a valid range, table, or connection.

Check the source range first

For a worksheet range, select the data cells directly rather than selecting whole rows, unrelated columns, or multiple separate blocks. A typical source might run from A1 to D250, with one record per row and one field per column. The header row belongs at the top of the selection. A PivotTable cannot use a collection of scattered ranges as one ordinary worksheet source.

To reset the selection while creating a PivotTable:

  1. Click a cell inside the source data, or select the complete rectangular range.
  2. Choose Insert > PivotTable.
  3. In the Create PivotTable dialog, inspect the Table/Range box. Confirm that the sheet name and cell addresses match the intended source.
  4. Choose whether the PivotTable should go on a new worksheet or an existing worksheet, then select OK.

If you typed a reference yourself, remove extra punctuation, spaces in the wrong place, or an outdated sheet name. A worksheet name containing spaces must be enclosed in single quotation marks in a manually entered reference, as in ‘Quarterly Data’!A1:D250. Selecting the cells with the mouse is usually less error-prone than typing the reference.

Keep the source rectangular and consistent. For example, if columns A through D contain Date, Region, Product, and Sales, every data row should use those same four fields. Remove title lines above the actual headings and avoid subtotals embedded between records. Blank rows do not always cause this exact error, but they can confuse automatic range detection or make the source incomplete, so remove unnecessary gaps.

Repair blank or malformed headings

Every source column needs a heading that identifies its field. A blank cell in the header row can prevent Excel from building the PivotTable, even when the rest of the range looks correct. Give each column a clear label such as Date, Region, Product, or Sales, then select the source again. Avoid merged cells in the heading row: a merged heading may appear centered over several columns while only one cell actually contains text.

Make sure the selected first row really is the header row. If the range starts one row too high, Excel may treat a report title or blank line as the field names. If it starts too low, the first record may become the heading and the PivotTable fields will be misleading. Use one row of labels followed by records, without a second heading row or a decorative title inside the selected block.

Distinct labels are also good practice. Although Excel may adjust some repeated field names in the PivotTable field list, repeated or nearly identical headings make it harder to identify fields and can cause confusion when the source is changed or imported. Rename them deliberately, for example to Net Sales and Units Sold rather than using Sales for both columns.

Use an Excel Table to make the source more reliable

Converting a clean source range into an Excel Table is often the simplest lasting fix. A Table has a name and expands when you add records immediately below it, so the PivotTable source does not depend on remembering to extend a cell address from A1:D250 to A1:D300.

  1. Click a cell in the source data.
  2. Choose Insert > Table, or press Ctrl+T on Windows. On a Mac, the shortcut is usually Command+T.
  3. Check the displayed range and select My table has headers if the first row contains field names.
  4. After checking the headings, select OK. You can change the Table name on the Table Design tab.
  5. Choose a cell in the Table and create the PivotTable with Insert > PivotTable. Confirm that the Table name, rather than an unintended range, is shown as the source.

When you add records directly below an Excel Table, the Table generally expands to include them. Refresh the PivotTable afterward using PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. A Table does not remove the need to refresh the PivotTable; it makes the source boundary easier to maintain.

Check workbook names and named ranges

A less obvious cause can be the workbook file name. Square brackets have a special role in Excel references to other workbooks. In some PivotTable workflows, a workbook file name containing [ or ] can be misread as part of an external reference and lead to a source-reference error. If the file name includes brackets, save a copy with a simpler name that uses letters, numbers, spaces, hyphens, or underscores, then try again. This is a targeted check, not a claim that every unusual file name causes the error.

If you are using a named range rather than selecting cells, confirm that the name exists and points to the intended cells. In Excel for Windows, open Formulas > Name Manager. Look for a name that has been deleted, misspelled, or defined with a reference to a sheet or range that no longer exists. Correct the reference or choose a valid source range instead. A name that refers to a formula or a dynamic range may behave differently depending on how it was defined; using a straightforward cell range or Excel Table is a useful diagnostic test.

Also confirm that the workbook is saved in a standard Excel workbook format such as .xlsx if you need to retain PivotTables and workbook features. A CSV file stores plain text data, not the PivotTable and workbook structure, so it is not a suitable final format for a workbook that must preserve the PivotTable.

Fix an existing PivotTable or refresh problem

If the PivotTable already exists, first distinguish a creation error from a refresh problem. Click inside the PivotTable and choose PivotTable Analyze > Change Data Source. Review the Table/Range entry, select the correct source, and confirm the change. If the source is an Excel Table, use its name where Excel offers that option. Then refresh the PivotTable.

A PivotTable built from a fixed range will not automatically include records added below that range. For example, a source of A1:D250 will not include a new record in row 251 unless you extend the source or replace it with an expanding Table. This often appears to users as missing data rather than an invalid-source error, but checking the boundary is important when repairing a PivotTable.

If the source is an external connection, a database, or the workbook Data Model, do not assume that Change Data Source works like it does for a simple worksheet range. The source may depend on a connection, permissions, a file path, or a data model. Check the connection and its availability, and use the relevant connection or model settings. For a quick diagnosis, try creating a separate PivotTable from a small, clean range in the same workbook. If that works, the original source type or reference is the likely issue.

A practical troubleshooting order

Work from the simplest checks toward less common ones. Make a copy of the workbook before substantial edits, especially if it contains formulas, queries, or other PivotTables. Then use this order:

  • Read the exact error and note whether it appears during creation, a source change, or refresh.
  • Confirm the source is a single rectangular range or a valid Excel Table, not a multi-selection.
  • Check that the selected range includes the intended header row and that no heading is blank or merged.
  • Remove report titles, stray blank rows, and subtotal rows from the source block where possible.
  • Re-select the source through Insert > PivotTable or PivotTable Analyze > Change Data Source rather than relying on an old typed reference.
  • Check the Table name, named range, worksheet name, and workbook file name if the direct range checks do not help.
  • For an external source, verify the connection or path. For a simple test, create a PivotTable from clean worksheet data in the same workbook.

Changing one thing at a time makes the cause easier to identify. For example, if a corrected header fixes the error, there is no need to rename the workbook or rebuild unrelated connections. If the same clean range works in a new test PivotTable, the problem is more likely tied to the old PivotTable’s source definition than to the data values.

Frequently Asked Questions

Why does Excel say the PivotTable field name is not valid?
Usually, at least one column heading in the selected source is blank, or Excel is treating the wrong row as the header. Add a heading to every column, unmerge the heading cells, and select the range again.

Will converting the data to a Table fix the error?
It often helps when the issue is an unclear or outdated range, because a Table provides a named, expandable source. It will not fix blank headings, a broken external connection, or every invalid reference. Check the Table’s range and headers before creating the PivotTable.

Why are new rows missing from my PivotTable?
The PivotTable may use a fixed range that ends before the new rows. Extend the source with PivotTable Analyze > Change Data Source, or use an Excel Table as the source and refresh the PivotTable.

Can a workbook file name cause this error?
It can in some cases, particularly when the name contains square brackets, which Excel also uses in external-workbook references. Try saving a copy with a simpler file name and then select the source again.

Does refreshing a PivotTable fix an invalid source?
Refresh updates results from a source Excel can already access. It usually will not repair a malformed or missing source reference. Use Change Data Source to correct the reference first, then refresh.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.