Site icon Professor Excel

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?
  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.

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.

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.

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.

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:

Image by Hans Braxmeier from Pixabay

Exit mobile version