MrExcel.com - Your One Stop for Excel Tips & Solutions

Create a Flattened Pivot Table for Reuse

December 02, 2022 - by

Create a Flattened Pivot Table for Reuse

Problem: Why would they put three different kinds of information in column A? Doesn’t this make pivot tables as silly as the person who created the bad data set back in “Add a Customer Number to Each Detail Record”?

The default Compact layout puts Region, Customer, and Product all in Column A.
Figure 842. Microsoft is mixing 3 fields in one column.

My goal is to use the pivot table to make a summary, then convert to values for use as a new data set. Having three different fields in column A is really bad form.

Note: I’ve met one person who likes compact view. He has 15 fields in the Row Area of his report. Compact layout allows that report to fit on a screen.

Strategy: It is very annoying that Microsoft made this new view be the default. Luckily, it is only a few clicks to go back to the proper view.

  • 1. Select one cell in the pivot table.

  • 2. Choose the Design tab of the ribbon.

  • 3. Open the Report Layout dropdown.

  • 4. Change from Compact Form to Tabular Form.

  • 5. Open the Report Layout dropdown again and choose Repeat All Item Labels.

On the Report Layout drop-down, choose Show in Tabular Form and then Repeat All Item Labels.
Figure 843. Eliminate blanks in the row area.

  • 6. For each field in the Row Area except the last field, open the dropdown in the Row Area dropdown and choose Field Settings.

  • 7. In the Field Settings dialog, choose None for Subtotals.

On the Field Settings for Customer, change Subtotals from Automatic to None.
Figure 844. Turn off the subtotals for the outer row fields.

  • 8. On the Design tab, open the Grand Total dropdown and choose Off for Rows and Columns.

The result is a flattened pivot table, perfect for re-use as a new consolidated data set. Copy the pivot table and paste as values to a new worksheet.

The three row fields are now Region, Customer, and Product in columns A, B, and C.
Figure 845. Copy and Paste Values this pivot table for re-use.

This article is an excerpt from Power Excel With MrExcel

Title photo by Roberto Huczek on Unsplash