See also

• Overview of PivotTable reports and PivotChart reports
• Design the layout and format of a PivotTable report
• Select different source data for a PivotTable report
• Group items in a PivotTable report
• Connect to (import) external data
• Connect to (import) an OLAP database
• PivotTable II: Filter PivotTable report data in Excel 2007

Prepare the source data

Here are recommendations for setting up your Microsoft® Office Excel® 2007 worksheet for an Excel 2007 PivotTable® report:

• Make sure each column has a heading. These column headings are used to name the fields in the PivotTable report (a field summarizes multiple rows of information from the source data).
• Put similar data in each column. For example, text items in one column, numbers in another column, and dates in another column.
• Remove any blank rows or columns.

Note    If you open in Excel 2007 an Excel workbook created in a version prior to Excel 2007, and you create an Excel 2007 PivotTable report, the layout area will be in Classic PivotTable layout instead of in the Excel 2007 layout. To avoid this, follow these steps:

1. Save the data as an Excel 2007 file.
2. Close the workbook.
3. Open it again.
4. Create the PivotTable report.

If you do this before you create the PivotTable report, the layout area will be in Excel 2007 layout.

Create a PivotTable report

1. Select a cell or range of cells.
2. Click the Insert tab. In the Tables group, click the arrow on PivotTable, and on the menu, click PivotTable.

   The Create PivotTable dialog box opens.

3. Select a table or range is already selected for you. The Table/Range box shows the range of the selected data.
4. New Worksheet is also selected for you as the place where the report will be placed (you can click Existing Worksheet if you don't want the report placed in a new worksheet). Click OK.

   On the left is the layout area ready for the PivotTable report, and on the right is the PivotTable Field List. This list shows the column titles from the source data: Each title is a field in the list.

5. In the field list, select the check boxes next to the fields that you want to add to the report.

   Fields are automatically added to the report. Fields that are non-numeric are added to the Row Labels area of the report. As you add more non-numeric fields, Excel places them on the inside of fields already on the PivotTable report, building a hierarchy. Numeric fields are added on the right.

   • To remove a field from the report, clear the check box next to the field name in the field list.
   • To remove all the fields from a report so that you can start over again, click the Options tab on the Ribbon under PivotTable Tools. In the Actions group, click the arrow on the Clear button, and then select Clear All.
   • To delete the entire report, click the Options tab. In the Actions group, click the arrow on Select. Click Entire Table. Then press DELETE.

Sort a field

• Right-click a cell in the field you want to sort. Point to Sort, and then click an option. For example, click Sort Largest to Smallest or More Sort Options.

Group a field

You can group data that contains dates or times by following these steps. To manually group selected items, see the Help topic about grouping under See also, above.

1. Right-click a cell in the field you want to group, and then click Group. In the Grouping dialog box, select an option, and then click OK.
2. To ungroup, click in the grouped field, right-click, and then select Ungroup.

Add a report filter

Use a report filter to focus on a subset of data in the report, often a product line, a time span, or a geographic region.

• In the PivotTable Field List, right-click a field name, and then select Add to Report Filter.

Pivot a report

When you pivot a PivotTable report, you transpose the vertical or horizontal view of a field, moving rows to the column area or moving columns to the row area.

• Right-click the field you want to pivot. Point to Move, and then select Move "field name" to Columns, or select Move "field name" to Rows.

Move a PivotTable report to another location

1. Click the PivotTable report.
2. Click the Options tab on the Ribbon under PivotTable Tools. In the Actions group, click Move PivotTable.

   The Move PivotTable dialog box opens.

3. Under Choose where you want the PivotTable report to be placed, either select New Worksheet, or in the Location box for Existing Worksheet, type the first cell in the range of cells where you want to locate the PivotTable report. Then click OK.

Print a PivotTable report

To set printing options, click in the PivotTable report. Click the Options tab on the Ribbon under PivotTable Tools. In the PivotTable group, click Options. In the PivotTable Options dialog box, on the Printing tab, select the options you want.