Create or delete a PivotTable or PivotChart report

To analyze numeric data in depth and to answer unanticipated questions about your worksheet data or an external data source, you can create a PivotTable report or PivotChart report.

Important In Microsoft Excel 2010, the PivotTable and PivotChart Wizard that was available in earlier versions of Excel has been replaced with PivotTable and PivotChart commands on the Insert tab in the Tables group. The PivotTable and PivotChart Wizard is still available, and you can use it as needed by adding it to the Quick Access Toolbar, or by pressing ALT, D, P to start it.

Learn about PivotTable and PivotChart reports

A PivotTable report is useful to summarize, analyze, explore, and present summary data. A PivotChart report can help you visualize PivotTable report summary data so that you can easily see comparisons, patterns, and trends. Both a PivotTable report and a PivotChart report enable you to make informed decisions about critical data in your enterprise.

What is a PivotTable report?

A PivotTable report is an interactive way to quickly summarize large amounts of data. Use a PivotTable report to analyze numeric data in detail and to answer unanticipated questions about your data. A PivotTable report is especially designed for:

You often use a PivotTable report when you want to analyze related totals, especially 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. In the following PivotTable report example, you can easily see 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.

1. Source data, in this case, from a worksheet

2. The source values for Qtr3 Golf summary in the PivotTable report

3. The entire PivotTable report

4. The summary of the source values in C2 and C8 from the source data

An example of source data and the resulting PivotTable report

In a PivotTable report, each column or field in the source data becomes a PivotTable field that summarizes multiple rows of information. In the previous example, the Sport column becomes the Sport field, and each record (a collection of information about a field) for Golf is summarized in a single Golfitem.

A value field, such as Sum of Sales, contains the values to be summarized. In the previous report, the Qtr3 Golf summary contains the sum of the Sales value from every row in the source data for which the Sport column contains Golf and the Quarter column contains Qtr3. By default, data in the Values area summarize the underlying source data in the PivotChart report in the following way: numeric values use the SUM function to add values, and text values use the COUNT function to count the number of values.

To create a PivotTable report, you must define its source data, specify a location in the workbook, and lay out the fields.

What is a PivotChart report?

A PivotChart report provides a graphical representation of the data in a PivotTable report, which in this case is called the associated PivotTable report. A PivotChart report is interactive, which means that you can sort and filter it to show subsets of the PivotTable data. When you create a PivotChart report, PivotChart report filters are displayed in the chart area so that you can sort and filter the underlying data of the PivotChart report. Changes that you make to the field layout and data in the associated PivotTable report are immediately reflected in the PivotChart report.

A PivotChart report displays TE000126913, categories, data markers, and axes just as standard charts do. You can also change the chart type and other options such as the titles, the legend placement, the data labels, and the chart location.

A PivotChart report of sport sales by quarter

You can automatically create a PivotChart report when you first create a PivotTable report, or you can create a PivotChart report from an existing PivotTable report.

Create a PivotTable from worksheet data

When you create a PivotTable report from worksheet data, that data becomes the source data for the PivotTable report.

Do one of the following:

To use worksheet data as the data source, click a cell in the range of cells that contains the data.

To use data in an 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.

On the Insert tab, in the Tables group, click PivotTable, or click the arrow below PivotTable, and then click PivotTable.

Excel displays the Create PivotTable dialog box.

Tip To create a PivotTable and PivotChart report at the same time, on the Insert tab, in the Tables group, click the arrow below PivotTable, and then click PivotChart. Excel displays the Create PivotTable with PivotChart dialog box.

Under Choose the data that you want to analyze, make sure that Select a table or range is selected, and then in the Table/Range box, verify the range of cells that you want to use as the underlying data.

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 more information about defining names, see Define and use names in formulas.

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.

Under Choose where you want the PivotTable report to be placed, specify a location by doing 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 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.

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 create a PivotChart report at the same time that you create a PivotTable report, Excel displays the chart on top of the associated PivotTable report. A PivotChart report and its associated PivotTable report must always be in the same workbook.

To add fields to the report, 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) date and time hierarchies are added to the Column Labels 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.

Changes that you make to the source data after you create the PivotTable report are reflected in the report when you refresh the selected PivotTable report (PivotTable Tools, Options tab, Data group, Refresh button).

If you add rows to the range of source data, you can include those rows in the PivotTable report by changing the source data (PivotTable Tools, Options tab, Data group, Change Source Data button). If the source data is in an Excel table, additional rows are automatically displayed when you refresh the PivotTable report.

Create a PivotTable from an external data source

When you create a PivotTable report from external data, you are creating a connection to source data that is stored outside Excel, for example in a database program (such as Microsoft Access or Microsoft SQL Server) or an Online Analytical Processing (OLAP) cube.

Click a cell on the worksheet.

On the Insert tab, in the Tables group, click PivotTable, or click the arrow below PivotTable, and then click PivotTable.

Excel displays the Create PivotTable dialog box.

Tip To create a PivotTable and PivotChart report at the same time, on the Insert tab, in the Tables group, click the arrow below PivotTable, and then click PivotChart. Excel displays the Create PivotTable with PivotChart dialog box.

Under Choose the data that you want to analyze, click Use an external data source.

Click Choose Connection.

In the Show box at the top of the Existing Connections dialog box, select the category of connections for which you want to specify a connection or select All Connections (which is the default).

Under Select a Connection, select a connection, and then click Open.

Note If you select a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you select a connection from the Connection files on the network or Connection files on this computer categories, Excel copies the connection file into the workbook as a new workbook connection, and then uses that file (an .odc file) as the new connection for the PivotTable report.

Under Choose where you want the PivotTable report to be placed, specify a location by doing 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 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.

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 create a PivotChart report at the same time that you create a PivotTable report, Excel displays the chart on top of the associated PivotTable report. A PivotChart report and its associated PivotTable report must always be in the same workbook.

To add fields to the report, do one or more of the following:

Select the check box next to each field name in the field section. The field is placed in a default area of the layout section, but you can rearrange the fields if you want.

By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the Column Labels area.

Right-click the field name and then select the appropriate command, Add to Report Filter, Add to Column Label, Add to Row Label, and Add to Values, to place the field in a specific area of the layout section.

Tip You can also click and hold a field name, and then drag the field between the field section and an area in the layout section.

Convert a PivotChart report to a standard chart

You can convert a PivotChart report to a standard chart by deleting its associated PivotTable report. If you have multiple PivotTable reports and PivotChart reports in your workbook, you may first have to find the associated PivotTable report that has the same name as the PivotChart report.

Do one of the following:

If you know which PivotTable report is associated with the PivotChart report that you want to change, click that PivotTable report.

If you do not know which PivotTable report is associated with the PivotChart report that you want to change, do the following:

Click the PivotChart report that you want to change.

This displays the PivotChart Tools, adding the Design, Layout, Format, and Analyze tab.

To find the name of the associated PivotTable report, do the following:

On the Design tab, in the Data group, click Select Data.

In the Select Data Source dialog box, in the Chart data range box, note the associated PivotTable name, which is the text that follows the (!) exclamation point, and then click OK.

To find the associated PivotTable report, do the following:

Click a PivotTable report in the workbook.

On the Options tab, in the PivotTable group, click Options, and then click Options.

In the Name box, note whether the name matches the name of the associated PivotTable report.

Repeat steps 1 through 3 of this procedure for each PivotTable report in the workbook until you find the same name in the Name box.

Click OK, and then select the associated PivotTable report that has the same name as the PivotChart report that you want to change.

This displays the PivotTable Tools, adding the Options and Design tabs.

On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.