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.
Figure 758 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.
Figure 759 Starting in Excel 2007, the Field List includes drop zones.
In previous versions of Excel, 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.
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.
Figure 760 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.
Figure 761 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."