Create or delete a PivotTable or PivotChart report

Try Office 2010 In Excel 2010, the new user interface and improved features make it easier to create a PivotTable.
Watch a video or try Office 2010!

To analyze numerical data in depth and to answer unanticipated questions about your data, use a PivotTable or PivotChart report.

For more information, demos, and training, refer to the See Also section.

What do you want to do?


Create a PivotTable or PivotChart report

To create a PivotTable or PivotChart report, you must 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.

  1. Select the type of report to generate by doing one of the following:
    • To create a PivotTable report, on the Insert tab, in the Tables group, click PivotTable, and then click PivotTable.
      Excel Ribbon Image
      Excel displays the Create PivotTable dialog box.
    • To create a PivotTable and PivotChart report, on the Insert tab, in the Tables group, click PivotTable, and then click PivotChart.

Excel displays the Create PivotTable with PivotChart dialog box.

  1. Select a data source by doing 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, Excel displays the range of cells or table name reference in the Table/Range box.

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

 Tip   Consider using a table name reference instead of a range of cells, because rows added to a table are automatically included in the PivotTable report when you refresh the data.

 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.

Excel displays the Existing Connections dialog box.

  1. In the Show drop-down list box 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).
  2. 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, Excel copies the connection file into the workbook as a new workbook connection, and then uses that file as the new connection for the PivotTable report.

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

  1. 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 specify the first cell in the range of cells where you want to position the PivotTable report.

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

  1. 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.

For more information, see Overview of PivotTable and PivotChart reports and Create and change the layout of fields in a PivotTable report.

If you create a PivotChart report, Excel creates an 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.) directly beneath the PivotChart report. A PivotChart report and its associated PivotTable report must always be in the same workbook.

For more information, see Overview of PivotTable and PivotChart reports and Charts.

Top of Page Top of Page

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.

Excel Ribbon Image

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

For more information, see Overview of PivotTable and PivotChart reports and Charts.

 Note   A PivotChart report and its associated PivotTable report must always be in the same workbook.

Top of Page Top of Page

Convert a PivotChart report to a standard chart

  1. Find 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.) that has the same name as the PivotChart report by doing the following:
    1. Click the PivotChart report.
      Clicking a PivotChart report displays the PivotChart Tools, adding the Design, Layout, Format, and Analyze tab.
    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.

Top of Page Top of Page

Create a standard chart from some or all data in a PivotTable report

  1. Select the data in the PivotTable report that you want to use in your chart. To include field buttons (field button: Button that identifies a field in a PivotTable or PivotChart report. You can drag the field buttons to change the layout of the report, or click the arrows next to the buttons to change the level of detail displayed in the report.) and data in the first row and column of the report, start dragging from the lower-right corner of the data that you select.

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

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

Top of Page Top of Page

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 (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.) for a PivotChart report creates a standard 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.

Top of Page Top of Page

 
 
Applies to:
Excel 2007