Tag Archives: reverse engineer

pivot table data

Reverse engineering an Excel PivotTable (recovering Pivot Table data)

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.00 out of 5)

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.

Pivot Table Data
The PivotTable

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.
Pivot Table data: Reverse engineering a PivotTable
Double Click on the Grand Total in the bottom right-most corner

Now simply double-click on the Grand Total and the source table will appear on a separate Excel Worksheet as show below:
Pivot Table data
The resulting source Pivot Table data

Easy right? Hope this helps you in similar situations – if yes, I appreciate a comment below!