Overview of PivotTable and PivotChart reports

Use a PivotTable report to summarize, analyze, explore, and present summary data. Use a PivotChart report to visualize that summary data in a PivotTable report, and to 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. The following sections provide an overview of PivotTable reports and PivotChart reports.

About PivotTable reports

A PivotTable report is an interactive way to quickly summarize large amounts of data. Use a PivotTable report to analyze numerical 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 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 you want to compare several facts about each figure. In the PivotTable report illustrated below, you can easily see how the third-quarter golf sales in cell F3 compare to sales for another sport, or quarter, or to the total sales.

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

In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. In the preceding example , the Sport column becomes the Sport field, and each record for Golf is summarized in a single Golf item.

A value field, such as Sum of Sales, provides the values to be summarized. Cell F3 in the preceding report 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, and text values use the COUNT function.

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

For more information, see Create a PivotTable to analyze data, Create a PivotChart, and Use the Field List to arrange fields in a PivotTable.

Ways to work with a PivotTable report

After you create the initial PivotTable report by defining the data source, arranging fields in the PivotTable field List, and choosing an initial layout, you can perform the following tasks as you work with a PivotTable report:

Explore the data     by doing the following:

• Expand and collapse data, and show the underlying details that pertain to the values.

• Sort, filter, and group fields and items.

• Change summary functions, and add custom calculations and formulas.

Change the form layout and field arrangement      by doing the following:

• Change the PivotTable report form: compact, outline, or tabular.

• Add, rearrange, and remove fields.

• Change the order of fields or items.

Change the layout of columns, rows, and subtotals      by doing the following:

• Turn column and row field headers on or off, or display or hide blank lines.

• Display subtotals above or below their rows.

• Adjust column widths on refresh.

• Move a column field to the row area or a row field to the column area.

• Merge or unmerge cells for outer row and column items.

Change the display of blanks and errors      by doing the following:

• Change how errors and empty cells are displayed.

• Change how items and labels without data are shown.

• Display or hide blank lines

Change the format      by doing the following:

• Manually and conditionally format cells and ranges.

• Change the overall PivotTable format style.

• Change the number format for fields.

• Include OLAP Server formatting.

For more information, see Design the layout and format of a PivotTable report.

About PivotChart reports

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 also interactive. When you create a PivotChart report, the PivotChart Filter Pane appears. You can use this filter pane to sort and filter the underlying data of the PivotChart report. Changes that you make to the layout and data in the associated PivotTable report are immediately reflected in the layout and data in the PivotChart report.

A PivotChart report displays data series, 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, the chart location, and so on.

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.

For more information, see Create a PivotTable to analyze data, Create a PivotChart, and Use the Field List to arrange fields in a PivotTable.

Comparing a PivotTable report and a PivotChart report

When you create a PivotChart report from a PivotTable report, the layout of the PivotChart report, that is, the position of its fields, is determined initially by the layout of the PivotTable report. When you create the PivotChart report first, you determine the chart layout by dragging fields from the PivotTable Field List to specific areas on the chart sheet.

Note: The Totals and Subtotals of an associated PivotTable report are ignored in a PivotChart report.

The following PivotTable and PivotChart reports of sales data illustrate the relationship between the two.

1. A row label corresponds to a category 2. A column label corresponds to a data series

Differences between PivotChart reports and standard charts

If you are familiar with standard charts, you will find that most operations are the same in PivotChart reports. However, there are some differences:

Row/Column orientation    Unlike a standard chart, you cannot switch the row/column orientation of a PivotChart report by using the Select Data Source dialog box. However, you can pivot the Row and Column labels of the associated PivotTable report to achieve the same effect.

Chart types     You can change a PivotChart report to any chart type except an xy (scatter), stock, or bubble chart.

Source data     Standard charts are linked directly to worksheet cells. PivotChart reports are based on the data source of the associated PivotTable report. Unlike a standard chart, you cannot change the chart data range in the Select Data Source dialog box of a PivotChart report.

Formatting     Most formatting—including chart elements that you add, layout, and style—is preserved when you refresh a PivotChart report. However, trendlines, data labels, error bars, and other changes to data sets are not preserved. Standard charts do not lose this formatting once it is applied.

Although you cannot directly resize the data labels in a PivotChart report, you can increase the font size of the text to effectively resize the labels.

Working with the source data of a PivotTable or PivotChart report

When you create a PivotTable or a PivotChart report, you can use any of several different types of source data.

Creating a PivotTable or PivotChart from worksheet data

You can use data from a Microsoft Excel worksheet as the basis for a report. The data should be in list format, with column labels in the first row. Each cell in subsequent rows should contain data appropriate to its column heading. There should not be any blank rows or columns within the data of interest. Excel uses your column labels for the field names in the report.

Using a named range    To make the report easier to update, name the source range, and use the name when you create the report. If the named range expands to include more data, you can refresh the report to include the new data.

Excel tables    Excel tables are already in list format and are good candidates for PivotTable source data. When you refresh the PivotTable report, new and updated data from the Excel table is automatically included in the refresh operation.

Including totals    Excel automatically creates subtotals and grand totals in a PivotTable report. If the source data contains automatic subtotals and grand totals that you created by using the Subtotals command in the Outline group on the Data tab, use that same command to remove the subtotals and grand totals before you create the report.

Using an external data source to create a PivotTable or PivotChart

You can retrieve data from a source that is external to Excel such as a database, an Online Analytical Processing (OLAP) cube, or a text file. For example, you might maintain a database of sales records you want to summarize and analyze.

Office Data Connection files    If you use an Office Data Connection (ODC) file (.odc) to retrieve external data for your report, you can input the data directly into a PivotTable report. We recommend that you retrieve external data for your reports by using ODC files.

OLAP source data    When you retrieve source data from an OLAP database or a cube file, the data is returned to Excel only as a PivotTable report or a PivotTable report that has been converted to worksheet functions.

Non-OLAP source data    This is the underlying data for a PivotTable report or a PivotChart report that comes from a source other than an OLAP database. For example, data from relational databases or text files.

Using another PivotTable report as the data source

The PivotTable cache    Each time that you create a new PivotTable report or PivotChart report, Excel stores a copy of the data for the report in memory, and saves this storage area as part of the workbook file. Thus, each new report requires additional memory and disk space. However, when you use an existing PivotTable report as the source for a new report in the same workbook, both reports share the same copy of the data. Because you reuse the same storage area, the size of the workbook file is reduced and less data is kept in memory.

Location requirements    To use a PivotTable report as the source for another report, both reports must be in the same workbook. If the source PivotTable report is in a different workbook, copy the source report to the workbook location where you want the new report to appear. PivotTable reports and PivotChart reports in different workbooks are separate, each with its own copy of the data in memory and in the workbook files.

Changes affect both reports    When you refresh the data in the new report, Excel also updates the data in the source report, and vice versa. When you group or ungroup items in one report, both reports are affected. When you create calculated fields or calculated items in one report, both reports are affected.

PivotChart reports     You can base a new PivotTable report or PivotChart report on another PivotTable report, but you cannot base it directly on another PivotChart report. However, Excel creates an associated PivotTable report from the same data whenever you create a PivotChart report; therefore, you can base a new report on the associated report. Changes to a PivotChart report affect the associated PivotTable report, and vice versa.

Changing the source data of an existing PivotTable report

Changes in the source data can result in different data being available for analysis. For example, you may want to conveniently switch from a test database to a production database. You can update a PivotTable report or a PivotChart report with new data that is similar to the original data connection information by refreshing the report.

To include additional data or different data, you can redefine the source data for the report. If the data is substantially different with many new or additional fields, it may be easier to create a new report.

Displaying new data brought in by refresh     Refreshing a report can also change the data that is available for display. For reports based on worksheet lists, Excel retrieves new fields within the source range or named range that you specified. For reports based on external data, Excel retrieves new data that meets the criteria for the underlying query or data that becomes available in an OLAP cube. You can view any new fields in the Field List and add the fields to the report.

Changing OLAP cubes that you create     Reports based on OLAP data always have access to all of the data in the cube. If you created an offline cube file that contains a subset of the data in a server cube, you can use the Offline OLAP command to modify your cube file so that it contains different data from the server.

Top of Page

See Also

PivotTable options