Analyzing Data with PivotTable Reports

Understanding the terminology and basic layout is only one third of mastering PivotTable report skills. The next third is creating a PivotTable report, and the final third includes using features such as the PivotTable field list, the PivotTable toolbar, the PivotTable report shortcut menu, and filtering PivotTable report data.

To get started, a convenient way to create PivotTable reports and PivotChart reports in Excel is by using the PivotTable And PivotChart Report wizard. To start the wizard, click PivotTable And PivotChart Report on the Data menu. On the wizard’s first screen, shown in Figure 4-10, you need to identify the source or location of the data you want to analyze and select the option for which type of report to create. Select PivotChart Report (With PivotTable Report) if you want to create a PivotTable report with a PivotChart report that uses the PivotTable report as its data source.

Figure 4-10: The PivotTable And PivotChart Wizard - Step 1 Of 3.

The options for data sources include the following:

• Microsoft Excel List Or Database You can use this option for data that’s stored in the active workbook or in another Excel file. If you select this option, you’ll see the dialog box shown in Figure 4 11. Use the Range box to identify the group of cells you want to include in the PivotTable report.

  
  4 11: The PivotTable And PivotChart Wizard - Step 2 Of 3 page for an Excel list or database.

If the data is stored in another Excel file, you should have that file open at the same time you run the PivotTable And PivotChart Wizard. Switch to the other Excel file and select the cells you want to use as the basis of the pivot report. Then click Next. (You can also click the Browse button, select an Excel worksheet, and then specify the cell range at the end of the file name in the Range box. Having the external worksheet open is the easier operation.)

• External Data Source If you select External Data Source, you’ll see the dialog box shown in Figure 4-12. The Get Data button opens the Choose Data Source dialog box. Click the appropriate tab and select your data source, or double-click New Data Source to specify a new data source.

  
  Figure 4-12: The PivotTable And PivotChart Wizard - Step 2 Of 3 page for an external data source.

• Multiple Consolidation Ranges Use this option if the data is in more than one group of cells in one or more Excel files. If you select Multiple Consolidation Ranges, you’ll see a series of dialog boxes. In step 2a of the wizard, shown in Figure 4-13, select the number of page fields to create. The wizard creates a single page field for you, or you can create the page fields yourself before the PivotTable is displayed. For example, you might want to group pages not only by time but also by, say, geographic regions. So, one page could show only West region sales figures for the year 2000, another page could show only East region sales figures for the year 2001, a third page could show only North region sales for the year 2002, and so on.

  
  Figure 4-13: When basing a PivotTable or PivotChart on more than one cell range, you can designate one or more page fields.

If you select the Create A Single Page For Me option, you’ll see the dialog box shown in Figure 4-14. Click the Range box, and then select a group of cells that you want to include in your PivotTable report. Click Add to add the group of cells, and then designate the other groups of cells that you want to include in your PivotTable report.

Figure 4-14: This PivotTable And PivotChart Wizard - Step 2b Of 3 page is displayed if you choose to have the wizard create a single page field.

If you choose to create your own page fields, you’ll see the dialog box shown in Figure 4-15. Click the Range box, select a group of cells that you want to include in your PivotTable report, and then click Add. Follow this step for each group of cells that you want to include in your PivotTable report. Click the number of page fields that you want, and give each item a label.

Figure 4-15: This PivotTable And PivotChart Wizard - Step 2b Of 3 page lets you create your own page fields.

Note

If the data is stored in other Excel files, you should have those files open at the same time you run the PivotTable And PivotChart Wizard. Switch over to the other open Excel files, and, file by file, select the cell groups that you want to use as the basis of the pivot reports. After you select each individual cell group, click Add. (You can also use the Browse button and add the cell reference to the sheet selected in the Range box.)

• Another PivotTable Report Or PivotChart Report Select this option if the data source is an existing PivotTable report or PivotChart report in the same workbook. In the dialog box shown in Figure 4-16, click a PivotTable report name.

  
  Figure 4-16: The PivotTable And PivotChart Wizard - Step 2 Of 3 page, which you use to select another PivotTable report or PivotChart report as a data source.

Step 3 of the PivotTable And PivotChart Wizard appears in Figure 4-17. In this step you have the option to have Excel create a new worksheet and place the PivotTable report on it, or have Excel place the PivotTable report on an existing worksheet. If you click the Existing Worksheet option, click the accompanying box and then click the cell on the worksheet where you want the PivotTable report to start.

Figure 4-17: The PivotTable And PivotChart Wizard - Step 3 Of 3 page.

Tip

Click the Layout button to construct the PivotTable report within the wizard instead of constructing the PivotTable report on the worksheet itself. This option is especially helpful if your source data is from a large external database, in which case the worksheet layout may be too time-consuming.

Tip

Click the Options button to set additional PivotTable report options; for example, whether grand totals are automatically displayed for columns and rows, what to display for empty rows (such as “N/A”), and how often to automatically refresh the report’s data.

After you have created a PivotTable report, there are several additional options and features available to you, including the PivotTable field list, the PivotTable toolbar, and the PivotTable report shortcut menu. You can use these options and features to refine the ways in which pivot reports present and analyze your data.

Using the PivotTable Field List

The PivotTable Field List window, included in Excel 2002, is shown in Figure 4 18. The window has two main areas. The field list itself contains the fields in the data source. As you saw in the exercise earlier in this chapter, to include a field in a PivotTable report, you drag the field from the list to the area where it’s required on the PivotTable report.

Figure 4-18: The PivotTable field list.

At the bottom of the PivotTable field list is the drop zone list, which provides an alternative way of including fields in a PivotTable report, especially for users who have difficulty dragging fields to precise areas in a PivotTable report. You can use the drop zone list to add fields to a PivotTable more quickly than by dragging fields. To use the drop zone list, click a field in the field list, click an entry in the drop zone list below the field list, and then click the Add To button.

To remove a field from a PivotTable report, drag a field off the PivotTable report to an unused portion of the Excel worksheet. You don’t have to drag the field back to the PivotTable field list, and you don’t have to worry about the field disappearing from the PivotTable field list either.

Tip

To make the best use of screen real estate, when you begin adding fields to a PivotTable report, add the field with the fewest number of items to the page area, the field with the next fewest number of items to the column area, and then other fields to either the page or row areas.

Putting It Together

Imagine you are a real estate sales manager and you want to quickly create and analyze a PivotTable report from last year’s sales figures for the United States. In this exercise, you will create a PivotTable report, dragging items from the PivotTable field list to the PivotTable report and using the drop zone list to include items in the PivotTable report. You will switch perspectives in the PivotTable report to analyze the data and create a second PivotTable report based on the first PivotTable report while leaving the first PivotTable report’s layout unchanged.

1. Start Excel, and open the HomeSale.xls file in the Chap04 folder.

2. On the United States worksheet, click cell A1.

3. On the Data menu, click PivotTable And PivotChart Report and then click Finish in step 1 of the wizard.

4. From the PivotTable field list, drag the Month icon to the Drop Row Fields Here area.

5. In the PivotTable field list, click the Gross Sales icon, click Data Area in the drop zone list, and click Add To.

6. In the PivotTable field list, click the Region icon, click Column Area in the drop zone list, and click Add To. Compare your results to Figure 4-19.

   
   Figure 4-19: The sum of gross sales across regions by month.

7. Experiment with switching perspectives by dragging the Month field to the column area, dragging the Region field to the row area, and dragging either the Month field or the Region field to the page area.

8. Now create a second PivotTable report that gets its data from the first PivotTable report. Click any cell outside the PivotTable report, and then click PivotTable And PivotChart Report on the Data menu.

9. Select the option Another PivotTable Report Or PivotChart Report, and then click Finish.

10. As you experiment with the new PivotTable report, notice that the first PivotTable report’s layout doesn’t change. Creating a second report is helpful when you want to present two perspectives of the same data for side-by-side analysis.

Using the PivotTable Toolbar and Shortcut Menu

The PivotTable toolbar, shown in Figure 4-20, provides commands that you use to format the data in your report and to organize the data for analysis.

Figure 4-20: The PivotTable toolbar and PivotTable menu.

From left to right across the toolbar, the commands include

• The PivotTable menu. I’ll cover these menu commands in the next section.

• The Format Report button, which displays the AutoFormat dialog box. The AutoFormat dialog box provides options with which you can format a PivotTable report’s text and colors.

• The Chart Wizard button, which automatically creates a PivotChart report based on the PivotTable report’s data.

• The MapPoint button, which, if you have Microsoft MapPoint installed, creates a map based on the PivotTable report’s data. (Not included in Microsoft Excel 2000.)

• The Hide Detail button, which reduces the amount of detail displayed in the PivotTable report.

• The Show Detail button, which increases the amount of detail displayed in the PivotTable report.

To see the effects of using the Hide Detail and Show Detail buttons, click a field label in a PivotTable report’s row area or column area, click Show Detail, click a field containing the detail you want to see, and then click OK. The PivotTable report’s layout changes to show detailed data for just that item. Click Hide Detail to remove the detailed data. To return to the original report layout, click Undo on the Edit menu until you return to the layout you want.

• The Refresh Data button, which retrieves the latest data from the PivotTable report’s data source.

• The Include Hidden Items In Totals button, which contributes items that are hidden to visible totals in a PivotTable report. (Not included in Microsoft Excel 2000.)

• The Always Display Items button, which, when selected, makes customizing PivotTable report layouts faster by hiding individual items until you add a data field.. (Not included in Microsoft Excel 2000.)

• The Field Settings button, which allows you to view different data field summarizations (such as Sum, Average, and Max) and set data display formats (such as currency and dates) and visual layout options (such as indented, grouped, or subtotaled). I’ll cover field settings in more detail in the next section.

• The Hide Field List button, which hides the PivotTable field list (or, if you are using Excel 2000, hides the available fields in the PivotTable toolbar). If the PivotTable field list is hidden, clicking the Hide Field List button will display the list of available fields. (If you’re using Excel 2000, the available fields are displayed on the PivotTable toolbar.)

The PivotTable menu, also shown in Figure 4-20, contains the following commands:

• The Format Report, PivotChart, Refresh Data, and Field Settings commands are identical to the corresponding buttons on the PivotTable toolbar.

• The Wizard command displays the PivotTable and PivotChart Wizard.

• The Offline OLAP command creates an OLAP cube from the PivotTable report and makes it available offline. (Not included in Microsoft Excel 2000.) Using OLAP cubes with PivotTable reports is covered in Chapter 8.

• The Hide command hides the selected data in the PivotTable report. (Not included in Excel 2000.)

• The Select command allows you to quickly select different parts of the PivotTable report.

• The Group And Show Detail command groups or ungroups selected data, or increases or decreases data details, in the PivotTable report. (In Excel 2000, this command is named Group And Outline and is available only on the PivotTable shortcut menu.)

• The Formulas command allows you to create your own additional data fields and data summarizations, called calculated fields and calculated items. You will learn how to create calculated fields and calculated items later in this chapter.

• The Order command allows you to reorder PivotTable report items. (In Excel 2000, this command is available only on the PivotTable shortcut menu.)

• The Subtotals command allows you to display subtotals in the PivotTable report. (Not included in Excel 2000.)

• The Sort And Top 10 command displays the PivotTable Sort And Top 10 dialog box, which allows you to sort PivotTable report items as well as display only the top number of items in a PivotTable report. (Not included in Excel 2000.)

• The Property Fields command displays property fields associated with an OLAP cube. (Not included in Excel 2000.)

• The Table Options command displays the PivotTable Options dialog box, which allows you to specify options such as what to display for error values in cells, empty cell values, and more.

• The Show Pages command displays one worksheet per page field item in the PivotTable report.

Additionally, when you right-click in any PivotTable report area, a shortcut menu appears. Many of the shortcut menu commands are identical to their PivotTable toolbar counterparts. The following commands are available only on the PivotTable report shortcut menu:

• The Format Cells command displays the Format Cells dialog box, which allows you to specify cell properties for text, alignment, color, borders, patterns, and protection.

• The Hide PivotTable Toolbar command hides the PivotTable toolbar. (Not included in Excel 2000.) If the PivotTable toolbar is hidden, the command reads Show PivotTable Toolbar.

Your Turn

In this exercise, you will use some of the commands on the PivotTable toolbar to format and display a PivotTable report.

1. Start Excel, and open the HomeSale.xls file in the Chap04 folder. If it is already open, close the file (do not save it) and open the file again.

2. Click cell A1.

3. On the Data menu, click PivotTable And PivotChart Report and then click Finish in the wizard.

4. From the PivotTable field list, drag the Month icon to the Drop Row Fields Here area. Drag the Region icon to the Drop Column Fields Here area, and drag the Gross Sales icon to the Drop Data Items Here area.

5. On the PivotTable toolbar, click the Format Report button.

6. Click the Report 2 picture, and then click OK.

7. Click the Chart Wizard button. A linked PivotChart is created.

8. On the worksheet labeled Sheet1, click cell C5.

9. On the PivotTable toolbar, click Field Settings.

10. Click the Number button, and then click Currency in the Format Cells dialog box.

11. In the Decimal Places box, type 0.

12. Click OK, and then click OK again. The sales figures are formatted as currency.

13. From the PivotTable field list, drag the Region icon to the Drop Page Fields Here area.

14. On the PivotTable toolbar, click the PivotTable menu and then click Show Pages.

15. Click OK. Excel creates worksheets labeled East, North, South, and West, showing views of the different regions.

Field Settings

The Field Settings button on the PivotTable toolbar opens the PivotTable Field dialog box. This dialog box provides a variety of options; the options displayed in the dialog box depend on the sort of field or item you select in the PivotTable. The options provide the means for changing the way data is displayed in a PivotTable, including summarization options and a set of custom calculations that you can use to compare the items in your data.

Clicking any PivotTable report field and then clicking the Field Settings button always displays the following two options:

• The Name box, which allows you to change the field’s display name in the PivotTable report (but not in the field list).

• The Number button, which allows you change the display format for a field’s value, provided the display format makes sense for the specified values (for example, displaying text as currency has no effect).

Clicking a page, row, or column field and then clicking the Field Settings button gives you the following options:

• The Subtotals options and list, which allow you to display, where applicable, function subtotals for a field’s items, such as Sum, Max, Min, and so on.

• The Advanced button, which allows you to display a field’s items in ascending, descending, or a custom order, as well as display the top or bottom values in a field.

If your PivotTable report includes more than one row field, clicking an outer row field (any row that isn’t the one closest to the data) and then clicking the Field Settings button adds the Layout button. The PivotTable Field Layout dialog box provides options for you to display the field’s items in tabular or outline form and to insert page breaks between items for printing purposes.

Clicking a data field and then clicking the Field Settings button provides the following options:

• The Summarize By list, which allows you to display data items’ function results as Sum, Count, Min, Max, and so on.

• The Show Data As list (viewed by clicking the Options button), which allows you to show custom calculations—values based on other items in the data area. For example, you can display values in the Sum of Sales data field as a percentage of June’s sales or as a running total of the items in a Month field. To produce these calculations, you pick from the list of calculations and then pick a base field and a base item that serve as starting points for the calculations and comparisons. These custom calculations include the following:

  • Difference From Displays data as the difference from the value you select in the Base Item list for the base field.

  • % Of Displays data as a percentage of the value of the item you select in the Base Item list for the base field.

  • % Difference From Displays data as the percentage difference from the value of the item you select in the Base Item list for the base field.

  • Running Total In Displays the data as a running total for the field you select in the Base Field list.

  • % Of Row Displays the data in each column in a row or category as a percentage of the total for the row or category.

  • % Of Column Displays the data in each row in a column or series as a percentage of the total for the column or series.

  • % Of Total Displays data as a percentage of the grand total of all the data or data points in the report.

  • Index Calculates data using the following formula: ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

Filtering PivotTable Report Fields

So far, you have learned how to create, format, and rearrange fields in a PivotTable report. You can also filter PivotTable report fields to match specific criteria.

To filter PivotTable report data, with the PivotTable report active, click an arrow in the PivotTable report associated with a row field, column field, or data field, or the list next to a page field. You’ll see a menu such as the one shown in Figure 4-21.

Figure 4-21: List of items for the Agent ID field; you can filter the data to show records for all agents, one agent, or a particular combination.

For row fields, column fields, and data fields, select or clear check boxes to display or hide fields. For page fields, click the item in the list associated with the page of data that you want to display.

Putting It Together

In this exercise, you will find the top five sales months for the East region’s home sales, sorted in order with the highest sales month at the top of the list. You will accomplish this by filtering a PivotTable report to show only East region sales figures and then use the Sort And Top 10 dialog box to show only the top five sales months, sorted in descending order.

1. Start Excel, and open the HomeSale.xls file in the Chap04 folder. If the file is already open, close it (do not save the file) and reopen the file.

2. On the worksheet labeled United States, click cell A1.

3. On the Data menu, click PivotTable And PivotChart Report and then click Finish in the wizard.

4. From the PivotTable field list, drag the Month icon to the Drop Row Fields Here area. Drag the Region icon to the Drop Page Fields Here area, and drag the Gross Sales icon to the Drop Data Items Here area.

5. In cell B1, click the arrow, click East, and then click OK. Compare your results to Figure 4-22. Only figures for the East region are displayed.

   
   Figure 4-22: Filtering data for the East region in the Region field.

6. Click cell A4. Open the PivotTable menu, and then click Sort And Top 10.

7. Click the Descending option. In the Using Field list directly below, select Sum of Gross Sales.

8. Click the On option. In the box next to the Top box, replace the number 10 with the number 5. In the Using Field list directly below, select Sum of Gross Sales.

9. Click OK, and compare your results to Figure 4-23. July (month number 7) had the highest sales month for the East region.

   
   Figure 4-23: Top five sales months, sorted in descending order, for the East region.

Creating and Using Calculated Fields and Calculated Items

The fields and items that you’ve analyzed so far correspond to specific values in a group of data records. You can also create fields and items, known as calculated fields and calculated items, that are calculated on the basis of specific values. The calculated values can then be used as if they were fields and items that already exist in the group of data records. For example, you might want to calculate sales ratios or merge geographical locations to enhance the level of your PivotTable report’s summarizations.

Note

You cannot create calculated fields or calculated items in reports based on online analytical processing (OLAP) data. For more information about OLAP, see Chapter 7.

To create a calculated field, first click a field in an active PivotTable report. Click the PivotTable menu, point to Formulas, and then click Calculated Field. The Insert Calculated Field dialog box appears, as shown in Figure 4-24. Type a name and formula to use for the field, click Add to place the field in the PivotTable field list, and then click OK.

Figure 4-24: The Insert Calculated Field dialog box.

Note

Calculated fields always use only the SUM summary function.

To create a calculated item, you first click an item in an active PivotTable report. On the PivotTable toolbar, click the PivotTable menu, point to Formulas, and click Calculated Item. The Insert Calculated Item dialog box appears, as shown in Figure 4-25. Type a name and formula to use for the item, click the Add button to add the item to the PivotTable report, and then click OK.

Tip

Click a field in the Fields list if you do not want to type field names manually into the Formula box. For calculated items, click an item in the Items list if you do not want to type item names into the Formula box. Click the Add button to add more calculated items or calculated fields without closing the Insert Calculated Item or Insert Calculated Field dialog box.

Figure 4-25: The Insert Calculated Item dialog box.

Your Turn

In this exercise, you will create a field that calculates a sales agent’s commission and create a calculated item that represents a sales agent’s overhead proceeds for sales in Washington state.

1. Start Excel, and open the HomeSale.xls file in the Chap04 folder. If the file is already open, close it (do not save the file) and then reopen the file.

2. On the worksheet labeled West Region, click cell A1.

3. On the Data menu, click PivotTable And PivotChart Report and then click Finish in the wizard.

4. Build the PivotTable report by dragging the Month icon from the PivotTable field list to the Drop Page Fields Here area, the Agent ID icon to the Drop Row Fields Here area, the State icon to the Drop Column Fields Here area, and the Sale Price icon to the Drop Data Items Here area.

5. Click cell A5.

6. On the PivotTable toolbar, click the PivotTable menu, point to Formulas, and then click Calculated Field.

7. In the Name list, type Commission.

8. In the Formula box, type = 'Sale Price' * .0035.

9. Click Add. Compare your results to Figure 4-26, and then click OK. The Commission field is added to the PivotTable field list and the PivotTable report.

   
   Figure 4-26: The insert calculated field dialog box for the Commission field.

10. Click cell C4. On the PivotTable toolbar, click the PivotTable menu, point to Formulas, and click Calculated Item.

11. In the Name list, type WA Overhead Proceeds.

12. In the Formula box, type = WA * 0.00175.

13. Click Add. Compare your results to Figure 4-27, and then click OK. The calculated item is added to the PivotTable report.

    
    Figure 4-27: The Insert Calculated Item dialog box for the WA Overhead Proceeds field.