Excel Pivot Table Data Source Not Valid? Fix It

Charts & pivot tables · Updated November 19, 2026

The Excel message “The data source reference is not valid” usually means Excel cannot resolve the range, table, or connection selected for the PivotTable. Check that the source is a valid, contiguous range with a header in every column, then select the data again or convert it to an Excel Table before creating or repairing the PivotTable.

What the error means

A PivotTable summarizes records from a source, such as a worksheet range, an Excel Table, or an external connection. Excel needs a usable reference to that source when you create the PivotTable and when you change its source later. If the reference is missing, malformed, inaccessible, or points to data that does not meet the expected layout, Excel may show this error.

This message is not the same as an error in the values being summarized. A text value in a sales column might affect how Excel summarizes that field, for example, but it does not by itself make the source reference invalid. Likewise, a PivotTable that opens but shows old results may have a refresh problem rather than an invalid source. Identifying whether the error occurs during creation, source changes, or refresh narrows down what to inspect.

Check the source data layout

Start by looking at the source range in the worksheet. A typical PivotTable source is one rectangular block: one row of field names followed by records, with each column representing one field. For example, a sales list might have Order Date, Region, Product, and Amount as headers, followed by one order per row.

Pay particular attention to the first row. Every column should have a nonblank heading, and headings should be distinct and descriptive. A blank heading is a frequent cause of PivotTable problems; duplicate names can also cause confusion or be adjusted by Excel, depending on how the source is created. Avoid merged cells in the header row. If the data has a report title above its actual column headings, select the data starting at the headings, not at the title.

Check that the records form a coherent block. A completely blank row or column can interfere with Excel’s automatic detection of the data area, even if a manually specified range can include blank cells. Remove unnecessary blank lines inside the list, or explicitly select the full intended range. Keep notes, totals, and unrelated information outside that block when possible.

  • Use one header row, not multiple tiers of headings.
  • Do not merge cells in the source list.
  • Make sure the selected range includes all intended columns and records.
  • Keep unrelated tables on a different part of the sheet or on another sheet.

Create the PivotTable from a clear selection

In Excel for Microsoft 365 on Windows, click a cell inside the source data and choose Insert > PivotTable. In the dialog, check the Table/Range field and confirm that it shows the correct worksheet and range. Choose whether to place the PivotTable on a new worksheet or an existing one, then select OK. If Excel has selected only part of the list, edit the range in the dialog or cancel and select the whole block first.

When selecting manually, use a reference such as Sheet1!$A$1:$D$250, adjusted for the actual sheet name and last row. If a sheet name contains spaces, Excel normally encloses it in single quotation marks in a reference. Do not type a range that extends beyond the intended table just to include future rows; an Excel Table is generally a more dependable way to handle growth.

If the data is on another worksheet, the source reference should point to that worksheet and its range. Make sure the sheet still exists and has not been renamed or deleted since the reference was made. If you copy a PivotTable or workbook from another file, its source may still refer to a location that is no longer available.

Use an Excel Table for a source that grows

For a list that gains new records regularly, convert it to an Excel Table before building the PivotTable. Click a cell in the data and choose Insert > Table, confirm that the selected range is correct, and check My table has headers if the first row contains the field names. You can also use Ctrl+T on Windows. On Mac, Command+T is the common shortcut for creating a table, though ribbon labels can vary slightly by Excel build.

Give the table a useful name from the Table Design tab, such as SalesData. Then create the PivotTable from a cell in that table. Excel uses the table as the source instead of a fixed set of cell addresses, so rows added directly beneath the table are incorporated into its range. The PivotTable still needs refreshing to display newly added records; making a source dynamic does not necessarily refresh the summary immediately.

A Table also gives the source consistent headers and expands formulas and formatting as records are added. It does not correct every data issue: blank or unsuitable field names, inconsistent records, and values entered outside the Table can still cause trouble. Confirm that new records have become part of the Table before assuming the PivotTable source is at fault.

Repair an existing PivotTable’s source

If the PivotTable already exists and the error appears when you change its source, click inside the PivotTable and choose PivotTable Analyze > Change Data Source on Windows. In the dialog, enter or select the valid range or table, then confirm. The PivotTable Analyze tab appears when a PivotTable is selected. If the source was an Excel Table, use its table name rather than an old fixed range when appropriate.

For example, suppose a PivotTable originally used Sheet1!$A$1:$D$100, but the records now extend to row 250. Change the source to the full range or to the Excel Table containing the records. If the source sheet was renamed, choose the current range from the sheet rather than relying on a stale reference. After changing the source, use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh, to update its results.

Changing the source can affect the available fields and the PivotTable layout. If the new source has different column headings, fields previously used in Rows, Columns, Values, or Filters may disappear or need to be added again. Before replacing a source, check that the new data has the same field structure if you want to preserve the existing report setup.

Check file names, named ranges, and connections

Sometimes the data itself is sound, but the reference Excel is trying to use is not. If the workbook was moved, renamed, or copied, inspect the source address and any connection settings. A defined name used as a source can also become invalid if it refers to deleted cells or contains a broken reference. In Formulas > Name Manager, review relevant names and correct or remove names whose Refers to entry no longer points to valid cells.

One unusual but documented compatibility trap is square brackets in a workbook’s file name. Brackets are used in Excel references to identify workbook names, and in some circumstances they can interfere with PivotTable source references. If the error persists despite a valid range, save a copy with a simpler file name that does not contain square brackets, close it, reopen it, and try again. This is a targeted troubleshooting step, not a reason to rename every workbook.

External-data PivotTables are different from PivotTables based on a local worksheet range. They depend on a connection, source file, database, or permissions being available. If the workbook uses an external connection, check Data > Queries & Connections and review whether the connection exists and can access its source. A connection failure may produce a different message, but a broken or unavailable source can prevent the PivotTable from working as expected. Avoid deleting a connection until you know whether other reports rely on it.

Distinguish a bad source from a refresh issue

If the PivotTable was created successfully but does not show recent rows, first confirm that those rows are actually inside the source range or Table. Then refresh it using PivotTable Analyze > Refresh, or choose Refresh All from the Data tab if several connections or PivotTables need updating. Refreshing updates cached results from the current source; it does not automatically fix a range that excludes the new records.

If Refresh produces the same invalid-reference error, return to Change Data Source and verify the reference. If the source address looks correct but the source worksheet is missing, restore or update the sheet reference. If the workbook was opened from a shared or network location, check that the file or connection is accessible and that you have permission to read it. Saving a local copy can help isolate an access problem, but it is not a substitute for repairing a broken source path.

There is also a common misconception that refreshing a PivotTable always expands its source. A fixed range normally remains fixed: if it ends at row 100, new records in row 101 and below are not included just because you click Refresh. An Excel Table is usually better for recurring additions, provided the new rows are added to the Table.

Prevent the error in future workbooks

A small amount of preparation makes PivotTables easier to maintain. Keep the source as a simple list, use stable field names, and avoid mixing subtotals or presentation formatting into the records. Store one type of information in each column—for example, dates in the date field and amounts in the amount field—so the PivotTable can recognize fields consistently.

For a source that changes over time, use an Excel Table and give it a clear name. Save the workbook in a normal Excel format such as .xlsx or .xlsm, and avoid renaming or deleting source sheets without checking dependent PivotTables. When sharing a workbook, include the source data or ensure that required external connections remain available to the recipient.

When a problem occurs, work from a copy if the workbook is important. Confirm the location and shape of the source, repair the reference, then refresh and verify a few totals against the original records. This check can reveal a source range that is technically valid but still omits data or includes unrelated rows.

Frequently Asked Questions

Why does Excel say the PivotTable data source is not valid?
Excel cannot use the selected source reference. Common causes include an incorrect or outdated range, a missing source sheet, blank column headings, a broken defined name, or an unavailable external connection. Check the source address and the source layout first.

Can blank rows cause the data source not valid error?
A blank row does not automatically invalidate every manually selected range, but it can make Excel detect only part of a list or split what should be one continuous data block. Remove unnecessary blank rows within the records or specify the intended range explicitly.

How do I change a PivotTable’s data source?
Select a cell in the PivotTable, choose PivotTable Analyze > Change Data Source, enter or select the correct range or Table, and confirm. Refresh the PivotTable afterward. The exact tab name or ribbon layout can differ slightly in Excel for Mac.

Will refreshing include new rows in the source?
Only if the rows are within the source the PivotTable uses. A fixed cell range may not include later rows. Using an Excel Table helps the source expand as records are added, but you still need to refresh the PivotTable to update its summary.

Why does the error happen when my range looks correct?
Check whether a source sheet was renamed, a named range is broken, the workbook name contains square brackets, or an external connection is unavailable. Also confirm that the reference points to the workbook and sheet you intend, rather than an old copy or location.

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.