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.
Source data, in this case, from a worksheet
The source values for Qtr3 Golf summary in the PivotTable report
The entire PivotTable report
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.
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.
Create the PivotTable report
- 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.
- 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.
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 to temporarily hide the dialog box, select the range on the worksheet, and then click Expand Dialog .
- 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.
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.
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.
The field section contains the field names of the fields you can add to the Layout section
The layout section contains the Report Filter
area, the Column Labels
area, the Row Labels
area, and the Values
- 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.
Top of Page