Problem: I have many rows of sales data. I want to produce a summary report that shows sales by region and product.
- 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:
- 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" on page 279.)
- Select a single cell in the database. Select Insert, Pivot Table.
- Excel's IntelliSense will guess the range of your data. Ensure the range is correct and click OK.
- 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 Field List dialog.
The PivotTable Field List includes a list of the fields at the top and four drop zones at the bottom of the dialog.
Note: The Field List is usually docked to the right side of the screen. For this book, I've undocked the Field List so I can show the Field List next to the pivot table. Gotcha: It is difficult to redock the PivotTable Field List 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.
- Starting in Excel 2007, the Field List includes drop zones.
- 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.
- Click a text field, and Excel moves it to the Row area.
- 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.
- 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.
- 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."