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

Summarize Pivot Table Data by Three Measures

November 22, 2022 - by

Summarize Pivot Table Data by Three Measures

Problem: I want to summarize data by region, product, and customer. How can I use a two-dimensional report to show three dimensions of data?

Strategy: Several views of the data are possible. Say that you are starting with products across the top and customers down the side. From the top of the PivotTable Field List dialog, you click the Region field. It is automatically added as the last row field. The view below shows the first customer and the purchases by region.

With Customer already in the Rows area, checkmark Region. It moves to the second row field. The report shows the first customer in row 5, then the three regions in rows 6, 7, and 8. It might make more sense to have Region as the first row field.
Figure 827. Regions within customer.

Another option is to drag the Region field heading above the Customer field heading in the bottom of the Field List dialog. Watch for the blue insertion bar.

If your mouse is not accurate enough to complete this drop, you can move the Product field to the Row Labels drop zone. Then you open the dropdown arrow at the right side of the Product field in the bottom of the Field List dialog and choose Move Up or Move to Beginning.

Open the drop-down menu on the Customer tile. Your choices are: Move Up Move Down Move to Beginning Move to End Move to Report Filter Move to Row Labels Move to Column Labels Move to Values Remove Field, and Field Settings. Choose Move to Beginning.
Figure 828. Drag fields, or use this dropdown menu.

Results: By changing the order of the fields in the row area, you now see the first region and all of the customers in that region.

After putting Customer after Region, you have a report with Central Region in Row 5 adn then the longer list of customers in A6, A7, A8, A9 and so on.
Figure 829. Customers within region.

You can also stack fields in the Column Labels drop zone.

The PivotTable Fields has Region and Product in Columns, Customer in Rows, and Revenue in Values. This time, you have two rows of headers across the top: Central in A4, products in row 5.
Figure 830. Two fields in the Column Labels drop zone.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tony Hand on Unsplash