Putting Data into a Pivot Table Report

In the Download section for this book on the Apress web site, find the file named PivotTable01.xlsx and open it.

Remember our fictitious band "VBA" from Chapter 1? Well, they've been out touring and their manager wants to see what's selling and what's not, and where items are selling best. PivotTable01.xlsx contains sales data from the first quarter of their tour, as shown in Figure 6-1.

Figure 6-1. Tour sales data

A good way for the manager to look at this data is via an Excel 2007 PivotTable report. We're going to record a macro while we create a PivotTable. Then we'll take a look at some of the properties and methods available to us.

1. Start the Macro Recorder (Developer ribbon > Record Macro).

2. Name the new macro MakePivotTable.

3. Put the cursor anywhere inside the sales data.

4. Choose Insert Ribbon > Tables > PivotTable. The Create PivotTable dialog box will be displayed, as shown in Figure 6-2.

Figure 6-2. Create PivotTable dialog box

The Create PivotTable dialog box contains two sections. The first section is where you can choose a data source. This can be a table or range within an Excel workbook or data from an external source. External data is accessed through a connection file, such as an Office Data Connection (ODC) file (.odc) or a Universal Data Connection (UDC) file (.udcx).

The second section lets you dictate where you would like the PivotTable report to be placed.

5. For now, just accept the defaults and click OK. A blank PivotTable report will be inserted on Sheet4, as shown in Figure 6-3.

Figure 6-3. Excel 2007PivotTable report default view

The new PivotTable report has a revamped interface that allows for easy manipulation of pivot data. All fields in the table are listed in the PivotTable Field List pane, which you can see on the right side of Figure 6-3. Check boxes are provided for users to choose the fields they want to include in the report. Text fields will by default place themselves in the Row Labels list and numeric fields will default to the Values list.

An easier way to create a report is to drag the field from the selection section at the top of the PivotTable Field List pane to the correct list below (shown in Figure 6-4).

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

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?

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.

Figure 6-9. Excel 2003 PivotTable report default view
0 0

Post a comment