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.
In this article
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.
Source data, in this case, from a worksheet
The source values for Qtr3 Golf summary in the PivotTable report
The entire PivotTable report
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 or delete a PivotTable or PivotChart report and Create and change the field layout in a PivotTable report.
Top of Page
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.
Top of Page
About PivotChart reports
A PivotChart report provides a graphical representation of the data in 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.), 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 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 (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.), 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 or delete a PivotTable or PivotChart report and Create and change the field layout in a PivotTable report.
Top of Page
About the PivotChart Filter Pane
Use the PivotChart Filter Pane to sort and filter the underlying data and change the appearance of the PivotChart report.
Axis fields (Categories) box This displays items from the Row label of the associated PivotTable report that form individual categories for which data points are charted. Categories appear on the horizontal (category) axis, also called the x-axis, of the PivotChart report.
Legend fields (Series) box This displays items from the Column label of the associated PivotTable report that form the individual data series. The names of the series appear in the legend of the PivotChart report.
The Report Filter box If the associated PivotTable report has one or more report filters, you can use the Report Filter box as a convenient way to summarize and quickly focus on a subset of data without modifying your series and category information. For example, you can click All in a Year Report Filter to show sales for all years, and then focus on specific years by clicking one year at a time. Each report filter page of your chart has the same category and series layout for different years, so the data for each year can be easily compared. Also, retrieving one report filter page at a time can save memory for a large external data source.
Tip To hide or show the PivotTable Field List, you can toggle the Field List button at the top of the PivotChart Filter Pane, so that you can see more of the PivotChart and PivotTable reports, or redisplay the Field List to rearrange the PivotTable layout and change the PivotChart appearance.
Top of Page
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 (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.).
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.
A row label corresponds to a category
A column label corresponds to a data series
Top of Page
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 (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.). 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 (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) a PivotChart report. However, trendlines (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.), 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.), error bars (error bars: Usually used in statistical or scientific data, error bars show potential error or degree of uncertainty relative to each data marker in a series.), 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.
Top of Page
Working with source data
When you 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 a 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.), you can use any of several different types of source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.).
Using worksheet data
You can use data from a Microsoft Office Excel worksheet as the basis for a report. The data should be in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) 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 (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.) names in the report.
Using a named range To make the report easier to update, name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) the source range, and use the name when you create the report. If the named range expands to include more data, you can refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) 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.
For more information, see Overview of Excel tables.
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 external data sources
You can retrieve data from a source that is external to Excel such as a database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.), an 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 (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) 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.
For more information, see Connect to (Import) external data, Create, edit, and manage connections to external data, and Connection properties.
OLAP source data When you retrieve source data from an 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.) database or a cube (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.) file, the data is returned to Excel only as a PivotTable report or a PivotTable report that has been converted to worksheet functions.
For more information, see Connect to (import) an OLAP database.
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
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 (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) 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 (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) 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 (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.) 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 an existing report's source data
Changes in the source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) 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 (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) 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.
For more information, see Select different source data for a PivotTable 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 (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) range that you specified. For reports based on external data, Excel retrieves new data that meets the criteria for the underlying query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) or data that becomes available in an OLAP cube (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.). You can view any new fields in the Field List and add the fields to the report.
For more information, see Refresh connected (imported) data.
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 (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) 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.
For more information, see Create an offline cube file from an OLAP server database.
Top of Page