CHAPTER 6 n PIVOTTABLES 225
Figure 6-4. Dragging the State field to the Row Labels section
Once you drop the field, the PivotTable updates to show the text or data (when available),
as shown in Figure 6-5.
Figure 6-5. State field added to the PivotTable
CHAPTER 6 n PIVOTTABLES 226
6. Drag the Product field to the Column Labels list.
7. Drag the Qty field to the Values list. The PivotTable Field List pane should look like
Figure 6-6.
Figure 6-6. PivotTable Field List with all fields added
The PivotTable report will look like Figure 6-7.
Figure 6-7. The completed PivotTable report
We see a sales summary by product line by state. But what if we also need to see sales by
city within each state?
CHAPTER 6 n PIVOTTABLES 227
8. Drag the City field to the Row Labels list and place it under the State field.
The finished report should now look like Figure 6-8.
Figure 6-8. City added to PivotTable report
9. Stop the Macro Recorder by clicking the Stop Recording command on the Developer
ribbon.
If you have had any experience with previous versions of Excel PivotTable reports, you
probably immediately noticed a change in the UI of the blank PivotTable.
The PivotTable Field List pane in Excel 2007 now does the work of all three components
shown in Figure 6-9. The user experience is much cleaner this way, and makes using
PivotTables much easier for users.
CHAPTER 6 n PIVOTTABLES 228
Figure 6-9. Excel 2003 PivotTable report default view
The Macro Code
Listing 6-1 shows the code the Macro Recorder generated for us.
Pages:
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222