pivot table data

Reverse engineering an Excel PivotTable / Flatten Excel PivotTable

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.33 out of 5)
Loading...

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

Scroll to botton right cell of the Pivot

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

Double click on the Pivot cell

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!