• Excel Book Excerpt

Excel Summarize Pivot Table Data by Three Measures

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

LE10000794.jpg

Figure 765 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.

LE10000795.jpg

Figure 766 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.

LE10000796.jpg

Figure 767 Customers within region.

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

LE10000797.jpg

Figure 768 Two fields in the Column Labels drop zone.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy