Step 2: Create a PivotTable Report

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

To create a PivotTable report, you first use the PivotTable and PivotChart Wizard to specify the data you want to use and to create the report framework. You can then use the PivotTable toolbar to arrange the data within that framework.

For example, you can choose the categories of data to include and the corresponding data values to summarize.

Then, tell Microsoft^® Excel how to arrange the data categories in the rows and columns of the PivotTable report. When Excel creates the finished report, it automatically summarizes and totals the data values.

Start by using the PivotTable and PivotChart wizard

1.      Open the workbook where you want to create the PivotTable report.

If you're basing the report on an Excel list or database, click a cell in the list or database.

2.      On the Data menu, click PivotTable and PivotChart Report.

3.      In step 1 of the PivotTable and PivotChart Wizard, follow the instructions, and click PivotTable under What kind of report do you want to create?

4.      Follow the instructions in step 2 of the wizard.

If you're retrieving external data for the PivotTable report, you can get more information about using Microsoft Query or the Query Wizard. Just type ways to retrieve data from an external database in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

5.      In step 3 of the wizard, click Finish.

Lay out the PivotTable report

1.      On the PivotTable toolbar, drag field buttons to the labeled areas on the PivotTable diagram.

Note   To see a demonstration of how to drag the field buttons, click the Start button:

2.      If you want to rearrange the fields, drag them from one area to another. To remove a field, drag it outside the PivotTable report.

Notes

·         In some cases, you might not want to lay out the PivotTable report directly on a worksheet. For example, if your data source is a large external database, worksheet layout may be too time consuming. In this case, you can click Layout in step 3 of the PivotTable and PivotChart Wizard to lay out the PivotTable report. (For more information about laying out a PivotTable report, type PivotTable Wizard Layout dialog box in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.)

·         For details on laying out a PivotTable report that uses OLAP source data, type lay out a PivotTable in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Anatomy of a PivotTable Report

You may have noticed that Excel uses specific terms to identify the parts of a PivotTable report. If you're not familiar with these terms, read this section for a quick primer.

Row field   A field from the source data that you assign to a row orientation in a PivotTable report. For example, Region and Salesperson are row fields.

Column field   A field from the source data that you assign to a column orientation in a PivotTable report. For example, Type is a column field.

Page field   A field from the source data that you assign to a page (or filter) orientation in a PivotTable report. For example, Year is a page field. You can use the Year field to display summarized data for only 1997, 1998, and so on.

Item   A subcategory of a row, column, or page field. For example, the Type field contains the following items: Beverages, Dairy, Meat, and Produce; the Salesperson field contains the following items: Buchanan, Davolio, Dodsworth, and Suyama.

Data field   A field from the source data that contains data to be summarized. For example, Sum of Sales is a data field.

Note   If you're using non-OLAP source data, you can choose how to summarize data (for example, by sum, average, or count). A data field usually summarizes numeric data, but it can also summarize text. For example, you can count the number of times a specific text entry (such as Yes or No) appears in a field.

Data area   The cells in a PivotTable report that contain summary data. For example, the value in cell C5 summarizes Buchanan's beverage sales for the East region in 1997. In other words, it's a summary of the sales figures for every row in the source data that contains the items Buchanan, Beverage, East, and 1997.

For more information, type elements of a PivotTable report in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.