Forum Discussion
Named pivot table creates ghost pivot tables on show report filter pages
TLM_MI Hi - I'm a program manager on the Excel product team and I'm asking our engineers to investigate. We have another report of a problem that sounds like it might be similar but it's hard to know for sure until the engineers take a look. In any event, we'll take a look and I'll let you know what we find. Thanks for posting this, and thanks also to Jan Karel Pieterse for calling the issue to our attention.
Thanks,
Howie Dickerman
With some new information, here are additional test observations.
Thanks for your comments which informed additional testing.
Macro security set to disable with notice.
Access to library disabled.
Created a completely new file with no other files open.
No VBA module or macro in the file.
Built worksheet tab with records.
Created pivot table sourced from those records with NO page filters.
Remained on the pivot table worksheet and inserted a new worksheet.
No ghost pivot table.
Moved a pivot table field to the page filter.
Remained on the pivot table worksheet and inserted a new worksheet.
Ghost pivot table created.
Moved to the data worksheet.
Inserted a new worksheet.
No ghost pivot table.
Saved the file.
Opened a blank worksheet.
Closed the test file.
Excel crashed, immediately relaunched and opened test file.
Document recovery message indicated original test file was recovered.
Book1.xlsx created prior to closing test file was not recovered.
(An Application Error and Windows Reporting was logged indicating APPCRASH associated with EXCEL)
This test suggests to me that pivot table page filters contribute to our ongoing instability.
While we can certainly not use page filters going forward, that does not mitigate risk associated with existing complex models already constructed with page filters.
With the macro variable removed, I'd appreciate you taking another look.
Thank you.
Howie Dickerman Fantastic news! Thank you for the thorough explanation - and this suggestion: ...copying PivotTables to new worksheets instead of copying the entire worksheet .... You have no idea how valuable those 11 words will be! Again, much appreciated.
Hi,
I have what I hope is good news about this issue. Our engineer checked in a fix for this issue on April 24. He discovered that there was a problem being introduced when making copies of worksheets that contained PivotTables. He has checked in the fix to what we call the "April fork" and also to the "March fork". He will wait a while to make sure we don't see any regressions, and then he plans to backport the fix to the "January fork" as well.
To explain these forks, each month, on the last Sunday of the month, we generate a fork of the code from our development branch. We test the build, and we slowly deploy the build to various rings of validation. First we ask people who work in Office to use it, then we roll it out to all Microsoft employees. We also deploy to a group of folks we call "Insiders" who try out early versions of the software. Along the way, we fix problems as they are discovered, and eventually, on the second Tuesday of the month ("patch Tuesday") we deploy builds to customers on the Monthly channel. The March fork will deploy to monthly production users on about May 12. The April fork will deploy to monthly users on about June 9. Customers in the Semi-Annual Targeted Channel will see this change in an update to "Semi-Annual Targeted" in a few more months, and the Semi-Annual channel will get this fix in September.
You can get the fix a bit sooner by choosing to sign up for "Insider" builds. Unfortunately, I don't know for sure if the monthly build that is being rolled out now (since 4/29, currently deployed to 25% of monthly users, build 16.0.12730.20236) has the fix or if it will be in the update that comes after that. But a fix is on the way. In the interim, to avoid creating additional worksheet corruption, I suggest copying PivotTables to new worksheets instead of copying the entire worksheet as a way to replicate a PivotTable.
Hope this helps,
-Howie
PS: For my own future reference, to help me find data about this issue, as our conversation progresses, the bug ID for this issue is 4021192.
Hello, Howie. Following up as the inability to reliably insert worksheets into workbooks with a pivot table is officially untenable. It's imperative we achieve stability while engineers investigate. Can you point me to a known prior stable version/channel of EXCEL? Or is moving to a perpetual license our only option? Thanks, in advance.
Howie Dickerman Again, cannot thank you enough. So appreciate your engagement.
TLM_MI Thanks for the additional information! This will help a lot, and I'm adding this info to the bug I've filed in our bug database for our engineers to investigate.
Thanks,
-Howie