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 (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.).

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

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
Callout 1 Source data, in this case, from a worksheet
Callout 2 The source values for Qtr3 Golf summary in the PivotTable report
Callout 3 The entire PivotTable report
Callout 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 (field: In a PivotTable or PivotChart report, a category of data that's derived from a field in the source data. PivotTable reports have row, column, page, and data fields. PivotChart reports have series, category, page, and data fields.) 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 Golf item (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.).

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 (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 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 (chart area: The entire chart and all its elements.) 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 data series (data series: Related data points that are plotted in a chart and originate from datasheet rows or columns. Each data series in a chart has a unique color or pattern. You can plot one or more data series in a chart. Pie charts have only one data series.), categories, data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.), and axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) just as standard charts do. You can also change the chart type and other options such as the titles (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.), the legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) placement, the data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.), 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 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.

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

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

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

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 (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 report and its associated PivotTable report must always be in the same workbook.

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

  • 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

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

Top of Page Top of Page

Create a PivotTable from an external data source

When you create a PivotTable report from 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.), 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) (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.) 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.

  1. Under Choose the data that you want to analyze, click Use an external data source.
  2. Click Choose Connection.
  3. 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).
  4. 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.

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

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 (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 report and its associated PivotTable report must always be in the same workbook.

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

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

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

Excel Ribbon Image

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

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

Top of Page 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 (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.). 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.

  1. To find the name of the associated PivotTable report, do the following:
  1. On the Design tab, in the Data group, click Select Data.

Excel Ribbon Image

  1. 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:
  1. Click a PivotTable report in the workbook.
  2. On the Options tab, in the PivotTable group, click Options, and then click Options.

Excel Ribbon Image

  1. In the Name box, note whether the name matches the name of the associated PivotTable report.
  2. 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.
  3. 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.

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

Excel Ribbon Image

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

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

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

Excel Ribbon Image

Keyboard shortcut  You can also press CTRL+C.

  1. Click a blank cell outside the PivotTable report.
  2. 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

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

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

Excel Ribbon Image

  1. 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 Top of Page

 
 
Applies to:
Excel 2010