How to Restore All Raw Source Data From PivotTable

You have received an Excel file with a PivotTable in it. Unfortunately, the file does not have the raw source data of the PivotTable. Or has it? Here are the steps for easily restoring the raw source data of the PivotTable.

Step 1: Check, if the source data is really not included

As the first step, make sure that the source data is really not included in your Excel file. If you are sure, proceed with step 2 below.

Why should you check this first? The reason is that seeing the source data has the advantage (compared to “restored” PivotTable data as we will see in Steps 2 and 3 below) that formulas, functions and formatting is intact.

How to check the source data of a PivotTable?

Check where the source data is located first. Maybe on a hidden or very hidden worksheet in your file?
Check where the source data is located first. Maybe on a hidden or very hidden worksheet in your file?
  1. Select a cell within your PivotTable.
  2. On the PivotTable Analyze ribbon, click on “Change Data Source”.
  3. If the Table/Range field refers to a source within your current file, navigate to it. It’s possible that the sheet is hidden or even very hidden – in this case, unhide it.

Step 2: Prepare the PivotTable

Let’s start with preparing the PivotTable so that we can restore all underlying data.

Step 2a) Remove all filters from the PivotTable

First, remove all filters. You can see all the filters in the PivotTable Fields list as highlighted below. You could also go directly to the PivotTable to remove the filters, but there is a chance that you could miss filters.

Go to the PivotTable Analyze ribbon, click on the small arrow under the “Clear” button and then on “Clear Filters”:

Remove all Filters in the PivotTable first.
Remove all Filters in the PivotTable first.

Step 2b) Add Grand Totals to the PivotTable

Next, show all Grand Totals.

  1. Click on any cell within the PivotTable.
  2. Go to the Design ribbon.
  3. Click on Grand Totals and then on “On for Rows and Columns”.
Add Grand Totals for restoring the source data.
Add Grand Totals for restoring the source data.

That’s it, we are done with the preparations! Let’s restore the data now.


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


Step 3: Restore the source data of the PivotTable

Your PivotTable now has all the Filters cleared and Grand Totals. For restoring all the data, simply double-click on the cell with Grand Totals for both rows and columns.

Double click on the Grand Totals cell in order to see all the raw source data.
Double click on the Grand Totals cell in order to see all the raw source data.

Excel now creates a new worksheet containing all data. It looks similar to this and is formatted as an Excel table.

By double-clicking on the Grand Totals, Excel inserts a new worksheet with the source data.
By double-clicking on the Grand Totals, Excel inserts a new worksheet with the source data.

Restrictions

PivotTable copy – plainly speaking – all the data into a cache. When you copy or move a PivotTable, the worksheet with the containing the data might not be available – but the PivotTable cache is. From this cache, we restore the data. There are, however, a few restrictions:

  • It doesn’t work if the data is not saved with the PivotTable. Right-click on any cell in the PivotTable, then click on “PivotTable Options” and go to the Data tab as shown on the screenshot on the right. If the checkmark of “Save source data with file” is not set, you cannot restore the data.
  • On the same Data tab in the PivotTable options: If the checkmark of “Enable show details” is missing, click on it in ordert to check it.
  • It must be a real PivotTable. If you – for example – use the Break Links feature of Professor Excel Tools, the source data can not be restored.
  • It usually also doesn’t work if the worksheet with the PivotTable on it is protected.

Image by Hans Braxmeier from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *