Create or delete a PivotTable or PivotChart report

To analyze numerical data in depth and to answer unanticipated questions about your data, use a PivotTable or PivotChart report. For more background information, see Overview of PivotTable and PivotChart reports.What do you want to do?

Create a PivotTable or PivotChart report

Create a PivotChart report from an existing PivotTable report

Convert a PivotChart report to a static chart

Create a static chart from the data in a PivotTable report

Delete a PivotTable or PivotChart report

Create a PivotTable or PivotChart report

To create a PivotTable or PivotChart report, you need to connect to a data source and enter the report's location.

1. Select a cell in a range of cells, or put the insertion point inside of a Microsoft Office Excel table.

   Make sure that the range of cells has column headings.

2. Do one of the following:
   • To create a PivotTable report, on the Insert tab, in the Tables group, click PivotTable, and then click PivotTable.

     

     The Create PivotTable dialog box is displayed.

   • To create a PivotTable and PivotChart report, on the Insert tab, in the Tables group, click PivotTable, and then click PivotChart.

   • The Create PivotTable with PivotChart dialog box is displayed.

3. Select a data source. Do one of the following:

   Choose the data that you want to analyze

   1. Click Select a table or range.
   2. Type the range of cells or table name reference, such as =QuarterlyProfits, in the Table/Range box.

      If you selected a cell in a range of cells or if the insertion point was in a table before you started the wizard, the range of cells or table name reference is displayed in the Table/Range box.

      Alternatively, to select a range of cells or table, click Collapse Dialog to temporarily hide the dialog box, select the range on the worksheet, and then press Expand Dialog .

      Note If the range is in another worksheet in the same workbook or another workbook, type the workbook and worksheet name by using the following syntax: ([workbookname]sheetname!range).

   Use external data

   1. Click Use an external data source.
   2. Click Choose Connection.

      The Existing Connections dialog box is displayed.

   3. In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).
   4. Select a connection from the Select a Connection list box, and then click Open.

      Note If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer categories, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.

      For more information, see Manage connections to data in a workbook.

4. Enter a location. 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 in an existing worksheet, select Existing Worksheet, and then type the first cell in the range of cells where you want to locate the PivotTable report.

     Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .

5. Click OK.

   An empty PivotTable report is added to the location that you entered with the PivotTable Field List displayed so that you can start adding fields, creating a layout, and customizing the PivotTable report. For more information, see Create and change the layout of fields in a PivotTable report.

   If you are creating a PivotChart report, an associated PivotTable report is created directly underneath the PivotChart report for the location that you enter. This PivotTable report must be in the same workbook as the PivotChart report. If you specify a location in another workbook, the PivotChart report will also be created in that workbook. For more information, see Overview of charting, Available chart types, and Create a chart.

Create a PivotChart report from an existing PivotTable report

1. Click the PivotTable report.
2. On the Insert tab, in the Charts group, click a chart type.

   

   You can use any chart type except xy (scatter), bubble, or stock.

For more information, see Overview of charting, Available chart types, and Create a chart.

Convert a PivotChart report to a static chart

1. Find the associated PivotTable report that has the same name as the PivotChart report by doing the following:
   1. Click the PivotChart report.
   2. To find the associated PivotTable report name, on the Design tab, in the Data group, click Select Data to display the Edit Data Source dialog box, and then note the associated PivotTable name, which is the text that follows the (!) exclamation point, in the Chart data range text box and then click OK.
   3. To find the associated PivotTable report, click each PivotTable report in the workbook, and then on the Options tab, in the PivotTable group, click Options until you find the same name in the Name text box.
2. Click OK.
3. On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.
4. Press DELETE.

Create a static chart from the data in a PivotTable report

This procedure creates a regular, noninteractive chart rather than a PivotChart report .

1. Select the data in the PivotTable report that you want to use in your chart. To include field buttons and data in the first row and column of the report, start dragging from the lower-right corner of the data that you're selecting.

   For more information, see Select data in a PivotTable report.

2. On the Home tab, in the Clipboard group, click Copy .
3. Click a blank cell outside of the PivotTable report.
4. On the Home tab, in the Clipboard group, click the arrow next to Paste, and then click Paste Special.
5. Click Values, and then click OK.
6. On the Insert tab, in the Charts group, click a chart type.

   For more information on charts, see Overview of charting, Available chart types, and Create a chart.

Delete a PivotTable or PivotChart report

Delete a PivotTable report

1. Click the PivotTable report.
2. On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.
3. Press DELETE.

Note Deleting the associated PivotTable report for a PivotChart report creates a static chart that you can no longer change.

Delete a PivotChart report

1. Select the PivotChart report.
2. Press DELETE.

   Note Deleting the PivotChart report does not automatically delete the associated PivotTable report.