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

Use a Pivot Table to Summarize Detailed Data

November 17, 2022 - by

Use a Pivot Table to Summarize Detailed Data

Problem: I have many rows of sales data. I want to produce a summary report that shows sales by region and product.

Columns in the data set include Region, Product, Date, Customer, Quantity, Revenue, COGS, and Profit.
Figure 819. Summarize this data set.

Strategy: To solve this problem, you can use a pivot table. As Excel’s most powerful feature, pivot tables are well suited to this type of analysis.

Creating a summary of revenue by region and product requires four mouse clicks and one mouse drag:

  • 1. Ensure that your data is in list format and that every heading is unique. (For a refresher on list format, see How to Set up Your Data for Easy Sorting and Subtotals.)

  • 2. Select a single cell in the database. Select Insert, Pivot Table.

  • 3. Excel’s IntelliSense will guess the range of your data. Ensure the range is correct and click OK.

The Create PivotTable dialog offers a range of A1:H564. You have a choice if the pivot table should be on a new worksheet or a location on an Existing Worksheet.
Figure 820. Make sure that Excel guessed the correct range.

You will now see an empty pivot table icon, two new PivotTable Tools tabs on the ribbon, and the PivotTable Fields dialog.

The PivotTable Fields dialog includes a list of the fields at the top and four drop zones at the bottom of the dialog.

Note: The dialog is usually docked to the right side of the screen. For this book, I’ve undocked it so I can show the Pivot Table Fields next to the pivot table. 

Gotcha: It is difficult to redock the PivotTable Fields dialog. You have to grab the left side of the title bar and drag it 90% off the right edge of the Excel window.

Way back in Excel 2003, you would drag fields from the Field List dialog to the pivot table. This process was frustrating for people new to pivot tables. Now, you drag fields from the top of the Field List dialog to the proper drop zone at the bottom of the Field List dialog. In many cases, clicking the field in the Field List dialog will move it to the correct drop zone. In this case, you want to have products going down the side of the report and regions going across the top.

The PivotTable Fields pane. The Fields box has checkboxes next to fields like Region, Product, Date. At the bottom are four drop zones, labeled Filters, Columns, Rows, and Values.
Figure 821. Drag fields from the top to the drop zones at the bottom.

  • 4. Click the Product check box in the top of the Field List dialog. Excel automatically moves it to the Row Labels drop zone. The pivot table shows a list of unique products in column A.

The fastest way to get a unique list of products is to create a pivot table and put only Product in the Rows area.
Figure 822. Click a text field, and Excel moves it to the Row area.

  • 5. Click the Revenue check box in the top of the Field List dialog. Because this field is numeric, Excel will add it to the Values section of the pivot table.

  • 6. If you click the Region check box, Excel will add it to the row area of the pivot table. Because you want regions to go across the top of your pivot table, drag the Region field from the top of the Field List dialog and drop it in the Column Labels drop zone at the bottom of the Field List dialog.

Drag Region from the top of the Fields pane and drop it in Columns. Checkmark Product and Values. This builds a report with Products down column A, regions across row 3, and revenue totals at the intersection of each product and region.
Figure 823. Checkmark Revenue, drag Region.

Excel will summarize the data by product and region, as shown above.

Additional Details: Pivot tables offer many powerful options. This topic describes the steps to create your first pivot table; you should read the next several topics to learn more about pivot tables.

Gotcha: If you were a pivot table pro in previous Excel versions, you can quickly adapt to the new pivot tables. The drop zones have been renamed. The Row Area drop zone is now Row Labels. The Column Area drop zone is now Column Labels. The Page Field drop zone is now Report Filter. The Data Area drop zone is now ∑ Values (although I will call it the Values drop zone, leaving off the ∑ symbol).

Gotcha: A dropdown at the top of the PivotTable Field List dialog offers five different views of the dialog. Three of those views omit either the fields or the drop zones. If your dialog box is missing one section, use the dropdown to return it to Fields Section and Areas Section Stacked. There are also views where the sections are side by side. Throughout the next pages, I will refer to the drop zones at the bottom of the dialog. If you have moved them to be side by side, then mentally change those instructions to read “the drop zones on the right side of the dialog.”

This article is an excerpt from Power Excel With MrExcel

Title photo by Kelly Sikkema on Unsplash