Excel Pivot Table Error Message Reference Not Valid

Tuesday morning, I opened an Excel file that I’d been working on the day before. Immediately, an error message appeared, “Reference isn’t valid”. I’ve seen that error with Excel charts before, but this turned out to be a pivot table problem.

Fooled by a Chart

When the message appeared, there was a greyed-out chart behind it.

That chart must be the problem! So, I clicked OK, then deleted the chart, and figured I’d rebuild it later.

Unfortunately, a few seconds later, the error message appeared again.

That chart was not the problem!

Check the Pivot Tables

What else could have a reference problem? Well, there were two pivot tables in the workbook, so I checked them next.

If I tried to refresh either pivot table, the error message appeared.
Both pivot tables were based on the same data source
I checked the data source for one of the pivot tables
As expected, it showed a named Excel table, Table1, as the source.

It was strange though – the pivot table was still showing in the background.

Usually, Excel finds the source data range, and highlights it with “marching ants”.

Check Source Data Table

Next, I checked the data sheet, and there was no nicely formatted Excel table. Mysteriously, the table had converted back to a simple list, with all of the table structure removed.

How did that happen?

I work alone, so there were no co-workers to blame.
Then I remembered – Excel had crashed the day before, while I worked on that file
Excel repaired the file, and re-opened it, with a “Repaired” message that I didn’t read (oops!)

Apparently the Excel table had some corruption, so Excel removed it, and I didn’t notice.

Don’t be like me – Read those messages!

Reformat the Excel Table

Fortunately, the broken reference problem was easy to fix.

First, I formatted the list as an Excel table again.
- Excel used the same name – Table1 – but you can rename it, if needed
After that, I checked the pivot table’s data source again, and everything was working correctly.

In the screen shot below, you can see the dashed line (marching ants), around the table’s border

Happy Ending

Good news! I’m happy to report that the error message, “Reference isn’t valid”, hasn’t appeared since I rebuilt that table.

Maybe I should check the rest of the workbook though, to see if anything else was affected by that “repair”.

✅ And remember – read those “Repaired” messages!

Find Problems with Pivot Tables

All kinds of things can go wrong, if you’re working with Excel pivot tables.

Luckily, my pivot table problem was easy to find and fix, but in a big workbook, with lots of pivot tables, and different data sources, it can be tricky to pinpoint the problem.

For details on all pivot tables in a workbook, you can use my “List All Pivot Table – Details” macro
That macro lists each pivot table in the file, with details on its location, size, and source data.

If source data is a table in the same Excel workbook, macro shows source data details

Video: Fix Pivot Table Refresh Errors

This video shows a couple of other pivot table problems that you might run into, and ways to fix them.

You can get the macro and workbook from the Pivot Table List Macros page, to follow along, and there are written steps on that page too.

_________________________