Make a Pivot from Another Pivot Table in Excel

Recently, a client shared data with me that is clearly a pivot table and wanted me to make another pivot from it using Excel. This is a common and annoying problem we all face when working with Excel. Today, let me share my approaches for creating a pivot from another pivot report using Excel.

Option 1: If you have access to “original” data

Ha, I know, but we can dream eh? So, if you do have access to the original data from which the pivot is generated, just use that data and make the new pivot as you want.

If you need help creating a pivot report in the first place, learn the process here.

---

Option 2: Making a Pivot from Another Pivot when you don’t have access to original data

Tbh, this is the real scenario for most of us. We have a pivot and don’t have access to the data that was used to make it. Now we need to make another pivot. In this case, follow the below steps.

Pivot from Another Pivot – FREE Excel Template

I created a free Excel template to guide you thru the process with sample data. Download it here and use the sample data to understand the process better.

Step 1: Select and name your pivot range

1. Select the entire pivot table (including any headers) in Excel.
2. Go to the name box (next to formula bar on the left)
3. Type the name “pivot_range”
4. Pro tip: If you have multiple pivots, you can use names like “pivot_range1”, “pivot_range2”

See this illustration for the step.

Step 2: Go to Data Ribbon and load up the “pivot” to Power Query

1. Keep the pivot table selected
2. Go to Data Ribbon
3. Click on “From Table/Range” option in the Get & Transform Data area

This will load the Power Query Editor with your Pivot Table Data.

Step 3: Let’s “unpivot” the Pivot Table with Power Query

Now that our “pivot table” is in Power Query, we can “unpivot” it and create a regular table. This can be used to make our new pivot table.

Here is a snapshot of how the Power Query editor looks with the pivot_range data.

[optional step] Promote headers if needed

Depending on how your source Pivot is setup, you may need to adjust the column headings in Power Query. For example, in my case, I need to promote the headers. To do this, click on “Use First Row as Headers” button in the Home ribbon of Power Query editor.

See below illustration.

Step 4: Replace “null” with value from above

In my sample pivot, you can see that Rep name is not printed in all rows, just the first row. This shows up as null in the Power Query editor for rest of the rows. We just need to fill these down based on the top value.

• Select the column(s) with this problem
• Go to “Transform” ribbon in Power Query Editor
• Click on “Fill” and select Down to fill down all the nulls with the value from above

Step 5: Remove rows with “totals” & “sub-totals”

We don’t need totals or sub-totals any more. We will calculate them in the new pivot as needed. For now, let’s remove all the rows and columns that have totals.

1. Select the first column that has “total” labels
2. Click on “filter” button
3. Uncheck any total labels.
4. Repeat the steps for any other rows that need this clean-up step.
5. Pro tip: Use Text Filters > Does not contain to filter out all rows with “total” word in them.

Step 6: Remove Grand total / Sub-total columns (if any)

Let’s also remove any “grand total” columns and “sub-total” columns from our pivot report. Right click on the column with totals and select “remove” to take this column out.

Step 7: Unpivot the data

Finally, our pivot report is ready to be unpivoted.

1. Select the column(s) with row labels. In the above example, I selected “representative” and “day of week” columns
2. Pro Tip: Hold SHIFT or CTRL to select multiple columns in one go.
3. Right click on the column headings of either column.
4. Select “unpivot other columns”
5. This should reshape the pivoted data to unpivoted format.
6. See this quick demo (GIF):

Step 8: Rename the new “attribute” & “value” columns

Double click on the newly added “attribute” and “value column headers to rename them to appropriate labels. In my case, I named them – Gender & Calls.

Step 9: Load the data back to Excel so we can make the pivot

Ok. We are done. Just load the data back to Excel. To do this, go to “Home” ribbon and click on “Close & Load” button.

Step 10: Create the Pivot from the loaded data

Once the data is in Excel, just select any cell in the data, go to Insert > Pivot Table (shortcut: ALT N V T) and set up the pivot as per your needs. In my case, I needed the pivot report with number of calls by Day of Week & Representative. So here is how I made it (see the quick video demo).

---

Things to keep in mind:

The Power Query based approach to create pivot from another pivot is great, but you need to keep a few things in mind.

• Doesn’t work for averages: If your original pivot table has “averages” instead of “sums”, the new pivot will not be correct. This is because you will make the mistake of “averaging averages”. This technique works great for sums & counts only. Any other measures like average / median / min /max, you need “actual” data to make the new pivot.
• Power Query steps can get complicated: If your original pivot has a very complex, nested layout, then the PQ steps needed to “transform” data can be complex (but not impossible). I suggest learning how to use Power Query to solve such issues. Refer to this article or video to start your PQ journey.
• Needs refresh for data changes: If your original Pivot table changes (new values or new rows / columns), you need to update the “pivot_range” named range and refresh the power query data.
  • To update the named range: Go to Formula ribbon in Excel and click on “Name Manager”. Select the name “pivot_range” and edit it. Adjust the cell references as per your newly updated pivot.
  • To Refresh Power Query: Right click on the Power Query data you have loaded in Step 9. Select “Refresh’ to update the loaded data with new changes. Now go to the pivot you made (in step 10) and refresh that too (you guessed it right! Right click and Refresh).

What to do if you get an error (in Power Query):

Errors can happen either during the initial process (steps 3 to 9) or when you refresh the power query connection. Solving the error depends on your exact pivot table layout and what changes were made. But here are the most likely reasons for the error.

• Column names have changed: You will get error if your columns (in the original pivot) were changed between updates. Adjust the names in the original pivot or go to Power Query editor, locate the step where the error is happening and adjust the names there.
• Data type issues: If for some reason, your original pivot’s values are read by Power Query as “text”, it can create issues. Right click on the columns with numbers and explicitly convert them to numbers in PQ.
• Layout changes: If your pivot layout changes (say, instead of 2 columns, it now has 3 columns of row labels), then your refresh will fail. You need to select one more column before unpivoting (step 7).
• Other issues: Leave a comment with the issue / error you are facing so I can help.

Pivot from Another Pivot – FREE Excel Template

I created a free Excel template to guide you thru the process with sample data. Download it here and understand the process better.

In conclusion:

Power Query in Excel offers an elegant, simple and easy way to deal with the annoying issue of using “pivot tables” as data source. I had plenty of success with this method and I hope will too. If you do have any questions or face issues during the process, leave a comment.