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.

What do you want to do?

Learn about PivotTable and PivotChart reports

What is a PivotTable report?

What is a PivotChart report?

Create a PivotTable from worksheet data

Create a PivotTable from an external data source

Create a PivotChart report from an existing PivotTable report

Convert a PivotChart report to a standard chart

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

Delete a PivotTable or PivotChart report

Delete a PivotTable report

Delete a PivotChart report

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:

  • Querying large amounts of data in many user-friendly ways.

  • Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.

  • Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas that are of interest to you.

  • Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.

  • Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want.

  • Presenting concise, attractive, and annotated online or printed reports.

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.

example of souce data and resulting pivottable report

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

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.

Top of Page

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.

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

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

    Excel Ribbon Image

    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.

  3. 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 Button image to temporarily hide the dialog box, select the range on the worksheet, and then click Expand Dialog Button image.

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

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

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

      Tips

  7. You can use the PivotTable Field List to rearrange the fields at any time 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. For more information about the Field List and how to rearrange the fields, see Overview of PivotTable and PivotChart reports and Pivot data in a PivotTable or PivotChart report on Office.com.

  8. 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).

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

Top of Page

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.

  1. Click a cell on the worksheet.

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

    Excel Ribbon Image

    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.

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

  4. Click Choose Connection.

  5. 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).

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

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

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

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

Tip    You can use the PivotTable Field List to rearrange the fields at any time 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. For more information about the Field List and how to rearrange the fields, see Overview of PivotTable and PivotChart reports and Pivot data in a PivotTable or PivotChart report on Office.com.

Top of Page

Create a PivotChart report from an existing PivotTable report

  1. Click the PivotTable report.

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

  2. On the Options tab, in the Tools group, click PivotChart.

    Excel Ribbon Image

  3. In the Insert Chart dialog box, click the chart type and chart subtype that you want. You can use any chart type except an xy (scatter), bubble, or stock chart.

    For more information about chart types, see Available chart types.

  4. Click OK.

    The PivotChart report that appears has PivotChart report filters that you can use to change the data that is displayed in the chart.

Notes    

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

  • If you insert a standard chart for the PivotTable data (Insert tab, Charts group), the chart will be a PivotChart that is associated with the PivotTable report.

Top of Page

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.

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

      1. Click the PivotChart report that you want to change.

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

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

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

      Excel Ribbon Image

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

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

      PivotTable group on the Options tab under PivotTable Tools

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

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

    Excel Ribbon Image

  3. Press DELETE.

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.

    Tip    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 select.

  2. On the Home tab, in the Clipboard group, click Copy.

    Excel Ribbon Image

    Keyboard shortcut  You can also press CTRL+C.

  3. Click a blank cell outside the PivotTable report.

  4. On the Home tab, in the Clipboard group, click the arrow below Paste, and then under Paste Values, click Values, Values & Number Formatting, or Values & Source Formatting.

    Excel Ribbon Image

  5. On the Insert tab, in the Charts group, click the chart type that you want, and then click a chart subtype.

    Excel Ribbon Image

    For more information about chart types, see Available chart types.

Top of Page

Delete a PivotTable or PivotChart report

Delete a PivotTable report

  1. Click anywhere in the PivotTable report that you want to delete.

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

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

    Excel Ribbon Image

  3. Press DELETE.

Note    Deleting a PivotTable report that is associated with a PivotChart report turns that PivotChart report into a standard chart that you can no longer pivot or update.

Delete a PivotChart report

  1. Click anywhere in the PivotChart that you want to delete.

  2. Press DELETE.

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

Top of Page

Applies To: Excel 2010



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language