Home > Pivot > Errors > Ref Pivot Table Error Reference Isn't Valid See how to troubleshoot and fix the Excel error message, "Reference isn't valid". I found this error message confusing, and it took a while to find the problem,

1) Reference Isn't Valid

Usually, things go smoothly when you when you try to create a pivot table. Occasionally though, you might see a pivot table error message, with clear details, or a vague message.

Recently, when I opened an Excel file, one of those "unhelpful" error messages appeared -- "Reference isn't valid".

1.1) Confusing Message

There was a chart on the worksheet, behind the message, and I (incorrectly) assumed that was the problem. However, the error turned out to be a pivot table problem - the source data table was broken!

I had to reformat the data as a named table, and that fixed things. See the details below, if you run into a similar problem.

2) Pivot Table Source Data Problem

In my workbook, a pivot table was causing the problem. The pivot table's source data was a table that no longer existed in the workbook. As soon as I fixed the table, the error message stopped appearing

2.1) Fixing the Problem - Details

To fix the problem I did these two things, and there are detailed steps below:

-- a) Located the pivot table's data source

-- b) Found and fixed the problem in the source data

2.2a) Locate Pivot Table Data Source

To find the data source of a pivot table, follow these steps:

1. Select any cell in the pivot table.
2. On the Ribbon, click the PivotTable Analyze tab
3. In the Data group, click the top section of the Change Data Source command.

The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. For my pivot table, it showed a table name - Table1

Usually, that table or range is highlighted in the background, but I could still see the pivot table in the background. So, I clicked the Cancel button, and went to the sheet where the data was stored.

2.2b) Find Problem in Source Data

When I checked the data sheet, there was no table - just a list on the worksheet. Nobody else uses the workbook, so how did the formatted Excel table go back to being just a list?

Table Removed During Repair

• Excel had crashed earlier in the day, and the workbook was automatically repaired.
• There must have been some corruption in that table, so Excel removed the table structure
• The "We repaired your workbook" message doesn't give many details, and I hadn't noticed the missing table.

2.3) Fix Problem in Source Data

To fix the Reference isn't valid error, I formatted the list as an Excel table again.

• Note: Excel automatically named the table as Table1 again, but if it hadn't, I could have renamed the new table, as Table1.

Next, I checked the Change PivotTable Data Source dialog box again, and the table was correctly highlighted in the background.

• In the screen shot below, you can see the "marching ants" border at the top right of the table data.

I clicked Cancel, and the error message didn't appear again, when I refreshed the pivot table.

3) Source Data Problem Example

In the sample file that you can download below, I created an example that leads to a Reference Isn't Valid error.

• Source data is on the SalesData sheet
• There is a named range, PivotData, that was based on the sales data
• The pivot table was based on the PivotData named range

Problem with Named Range

However, now the named range is broken, so you'll see errors:

• If you try to refresh the pivot table, long error message appears
• Next, if you check the pivot table data source, then try to close that dialog box, a Reference Isn't Valid error appears
• Click OK, then click Cancel in the Change PivotTable Data Source dialog box

Check the Named Range

To check the named range, follow the steps below:

• On the Excel Ribbon, go to the Formulas tab, and open the Name Manager
• Name Manager shows a #REF! error for PivotData named range, instead of a worksheet address

Fix the Named Range

To fix the named range, follow the steps below

• At the bottom of the Name Manager, click in the Refers to box
• Delete the error reference
• Next, go to the SalesData sheet, and select the sales data table (headings and data)
• At the left of the Refers To box, click the check mark, to confirm the change
• Click the Close button, to exit the Name Manager

Now, the pivot table should refresh correctly, with no error messages.

4) Get the Excel File ✅

• Ref Isn't Valid Error: To see the error message, download the Ref Isn't Valid Error sample file. The zipped file is in xlsx format, and does not contain any macros.
  Tip: See my notes about this file, in the Source Data Problem Examplesection above.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Pivot Table Tutorials

Error: Field Name Not Valid

Error: Pivot Table Overlap

Error: Couldn't Get Data

Error: Reference Isn't Valid

Problem: Pivot Table Duplicate Items

Hide Error Values in Pivot Table

Pivot Table Details List Macros

Clear Old Items in Pivot Table