Quick start: Create a PivotTable report

By using a PivotTable report, you can summarize, analyze, explore, and present a summary of your worksheet data or an external data (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) source. A PivotTable report is especially useful when you have a long list of figures to sum, and aggregated data or subtotals would help you look at the data from different perspectives and compare figures of similar data.

For example, the following PivotTable report shows how the total third-quarter Golf department sales in cell F3 compare to sales for another sport, or quarter, or to the total sales of all departments.

Example of souce data and resulting PivotTable report
Callout 1 Source data, in this case, from a worksheet
Callout 2 The source values for Qtr3 Golf summary in the PivotTable report
Callout 3 The entire PivotTable report
Callout 4 The summary of the source values in C2 and C8 from the source data

 Important   Although you can also create a PivotTable report for an external data source, this quick start article describes how to create one for worksheet data.

How?

Icon image

Define the data source for the PivotTable report

  • To use worksheet data as the data source, click a cell in the range of cells that contains the data.
  • To use data in a Microsoft Excel table as the data source, click a cell inside the Excel table.

Note    Make sure that the range has column headings or that headers are displayed in the table, and that there are no blank rows in the range or table.

Icon image

Create the PivotTable report

  1. On the Insert tab, in the Tables group, click PivotTable.

 Tip   To create a PivotChart that is based on the PivotTable report at the same time, click the arrow below PivotTable, and then click PivotChart.

Excel Ribbon Image

  1. In the Create PivotTable dialog box, make sure that Select a table or range is selected, and then in the Table/Range box, verify the range of cells.

Create PivotTable dialog box

Excel automatically determines the range for the PivotTable report, but you can replace it by typing a different range or a name that you defined for the range.

For data in another worksheet or workbook, include the workbook and worksheet name by using the following syntax [workbookname]sheetname!range.

Tip    You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the range on the worksheet, and then click Expand Dialog Button image.

  1. Do one of the following:
    • To place the PivotTable report in a new worksheet starting at cell A1, click New Worksheet.
    • To place the PivotTable report at a specific location in an existing worksheet, select Existing Worksheet, and then in the Location box, specify the first cell in the range of cells where you want to position the PivotTable report.
  2. Click OK.

Excel adds an empty PivotTable report to the specified location and displays the PivotTable Field List so that you can add fields, create a layout, and customize the PivotTable report.

Note    If you created a PivotChart at the same time, it is displayed on top of the associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.). A PivotChart and its associated PivotTable report must always be in the same workbook.

Icon image

Add fields to the PivotTable report

In the PivotTable Field List, do one or more of the following:

  • To place a field in the default area of the layout section, select the check box next to the field name in the field section.

By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) date and time hierarchies are added to the Column Labels area.

PivotTable Field List
Callout 1 The field section contains the field names of the fields you can add to the Layout section
Callout 2 The layout section contains the Report Filter area, the Column Labels area, the Row Labels area, and the Values area.
  • To place a field in a specific area of the layout section, right-click the field name in the field section, and then select Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values.
  • To drag a field to the area that you want, click and hold the field name in the field section, and then drag it to an area in the layout section.

Tip    You can use the PivotTable Field List to rearrange the fields later as needed by right-clicking the fields in the layout section, and then selecting the area that you want, or by dragging the fields between the areas in the layout section.

Next steps

Top of Page Top of Page

 
 
Applies to:
Excel 2010