Today a quick tip that helped me out of a tight spot during one of my projects. The issue was that I received an Excel file with multiple PivotTables, but no source tables on which these PivotTables were built (these were in separate worksheets that were not shared). I needed the source data to produce my own custom complex reports (for which I use MS Queries instead of PivotTables), and I needed it now. Fortunately, PivotTables contain embedded source tables and there is a quick and easy way to recover Pivot Table data. Read on to learn how… maybe it will come useful.
How to reverse engineer a PivotTable
Let’s assume we have the following Excel PivotTable below.
In order to recover the Pivot Table data associated with the PivotTable you need to: scroll down to the right-most, bottom-most Grand Total value in the PivotTable.
Now simply double-click on the Grand Total and the source table will appear on a separate Excel Worksheet as show below:
Easy right? Hope this helps you in similar situations – if yes, I appreciate a comment below!