Forum Discussion
Named pivot table creates ghost pivot tables on show report filter pages
We've recently seen increased occurrence of overlapping pivot table errors which are inexplicable. Instability warranted rollback to prior monthly channel version: Microsoft Excel for Office 365 MSO (16.0.12130.20382) 64-bit and stopping all updates. (General support recommendation in other's similar situation suggested version rollback and channel change which is not permitted here.) Problem continues.
Noticed today that named pivot tables (ie: not pivottable1, pivottable2, etc etc) appears to cause/contribute to the issue.
A named pivot table (not automatic pivottable+number) with Report Filter creates GHOST pivot table(s) on Show Report Filter Page tabs.
Show Report Filter Pages throws overlapping pivot table message as each GHOST pivot table is created.
Attempts to save the file throw corruption error.
Repaired file removes ALL pivot tables.
(Choosing NOT to repair/save the file results in EXCEL shutting down and immediately restarting.)
An unnamed pivot table (automatic name = pivottable+number) does NOT create GHOST pivot tables on Show Report Filter Page.
I've also noticed that the first pivot table created in a workbook increments the automatic pivot table name - (pivottable+number) - based on ALL pivot tables in ALL open workbooks. Is that expected behavior? Or would you expect the first pivot table created in a workbook to be named pivottable1?
I've attached a list of the pivot tables in my test files for Named-Before/Named-After and Unnamed-Before/Unnamed-After (and the VBA to generate those lists.)
We have MANY MANY files with named pivot tables - frequently open at the same time with no issues for YEARS. Since mid-December, we've experienced catastrophic corruption of several files.
Is there a problem with pivot table functionality since about mid-December, 2019?
Any thoughts on a resolution to the GHOST pivot tables?
20200312
Updated for attachment with more visual example of ghost pivot tables in a test file. (see additional files attached in thread)
20200312_2
Created new test file on machine running Microsoft Excel 2016 MSO (16.0.12430.20112) 32bit. No ghosts. Transferred to offending ghost machine. Also no ghosts and no crash on inserting worksheet when pivot table tab active...BUT pivottable.name does appear to duplicate which I did not expect...?
Thanks.
15 Replies
Link to Ghost Pivot Table Video
I've since realized that the named table behaves no differently than unnamed. Took a step back and realized that I had many more pivot table objects in the workbooks than expected or visible.
Essentially multiple ghost tables are created in each pivot table tab created.
And, attempting to insert new worksheet when a pivot table tab is active also creates a ghost pivot table regardless of presence of Page Filters.
A test file is attached which includes a brief description of the test steps executed along with the account information requested. I'm also attaching a video of the overlapping message as the ghost tables are created. (Note, that the test data is the same size for each group. When size is different visible pivot table is inconsistent with ghost pivot table.)
Attempts to save this test file after removing personal information shut down EXCEL which immediately reopened. If that become relevant, I will recreate a new test file.
We began experiencing unacceptable instability in mid-December, but cannot confirm whether/if that correlates to any updates. There is one similar ghost pivot table discussion on the support boards with an official recommendation to change Office 365 channel from monthly to quarterly and rollback to Version 1902 (Build 11328.20492) . Our IT department was not comfortable making that change so rolled back on the monthly channel to Version 1910 (Build 12130.20410) and stopped all future updates.
Link to similar support discussion
- I can't repro all of your issues, but I do see some, like the overlapping PT error. I also had Excel crash on my when I tried to delete a couple of sheets. I think the file contains corruptions and needs to be rebuilt.
Thanks for your observations. I agree that the file has corruptions. Those corruptions appear to be related to standard pivot table functionality. More importantly, rebuilding years and years of work on complex models (that worked without issue and then in the last 3 months didn't) is not an option. Deleting worksheets with ghost pivot tables will crash EXCEL every single time. Repairing the files removes all pivot table objects. I suppose I could test for ghost objects and then specifically try to remove them vs deleting worksheets. If that worked, at least we might quasi-manage risk. That's not acceptable as a final solution though.
- A file which demonstrates the issue with instructions on how to repro might be nice.
