Excel > PivotTable reports and PivotChart reports > Basics

Overview of PivotTable reports and PivotChart reports

Excel 2007

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

Ways to work with a PivotTable report

About PivotChart reports

Comparing a PivotTable report and a PivotChart report

Differences between PivotChart reports and standard charts

Working with source data

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.

 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:

Interaction   With standard charts, you create one chart for each view of the data that you want to see, but they are not interactive. With PivotChart reports, you can create a single chart and view the data in different ways by changing the report layout or the details that are displayed. You interact with the data.

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

Source data   Standard charts are linked directly to worksheet cells. PivotChart reports can be based on several different types of data in 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.).

Formatting   Most formatting, including elements, 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.

 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

See Also

• Connect to (import) an OLAP database
• Create and change the field layout in a PivotTable or PivotChart report
• Create or delete a PivotTable or PivotChart report
• Design the layout and format of a PivotTable report
• Select data in a PivotTable report
• Work with offline cube files

Excel > PivotTable reports and PivotChart reports > Basics

Create or delete a PivotTable or PivotChart report

Excel 2007

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

Create a PivotChart report from an existing PivotTable report

Convert a PivotChart report to a static chart

Create a static chart from the data in a PivotTable report

Delete a PivotTable or PivotChart report

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.

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

3. Select a data source by doing one of the following:

Choose the data that you want to analyze

• Click Select a table or range.
• 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 to temporarily hide the dialog box, select the range on the worksheet, and then press Expand Dialog .

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

• Click Use an external data source.
• Click Choose Connection.

Excel displays the Existing Connections dialog box.

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

4. 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 to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .

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.

For more information, see 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 accompanying PivotTable report must always be in the same workbook.

For more information, see Overview of charting, Available chart types, and Create a chart.

 Top of Page

Create a PivotChart report from an existing PivotTable report

1. Click the PivotTable report of interest.
2. On the Insert tab, in the Charts group, click a chart type.

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

For more information, see Overview of charting, Available chart types, and Create a chart.

 Top of Page

Convert a PivotChart report to a static 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.
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

Create a static chart from the data in a PivotTable report

This procedure creates a standard, noninteractive chart instead of 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.).

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.

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

For more information on charts, see Overview of charting, Available chart types, and Create a chart.

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

See Also

• Overview of Online Analytical Processing (OLAP)
• Overview of PivotTable reports and PivotChart reports
• Print a PivotTable report

Excel > PivotTable reports and PivotChart reports > Basics

Working with different PivotTable formats in Office Excel

Because of the new features added to PivotTable and PivotChart reports in Microsoft Office Excel 2007, there are now three PivotTable formats that you can work with: version 2007, version 2002-2003, and version 2000. Furthermore, you can work with each PivotTable format in Microsoft Office Excel 2000, 2002, 2003, and 2007. It's important to understand the dynamics of working with the different program versions and the different PivotTable format versions to decide whether you should upgrade right away to the new PivotTable format version 2007.

In this article

Working with different Excel program versions, Excel file format versions, and PivotTable versions

Feature differences between different PivotTable formats versions 2000 and 2002-2003 and version 2007

Important considerations when deciding whether to upgrade a PivotTable format from versions 2000 and 2002-2003 to version 2007

Working with different Excel program versions, Excel file format versions, and PivotTable versions

As you might expect, if you create a new PivotTable report in Office Excel 2007, its PivotTable format is version 2007. In Excel 2007, when you open an Excel file format version 97-2003 that contains a PivotTable format version 2000 or 2002-2003, which puts you in Compatibility Mode, you can interact with the PivotTable without upgrading it right away to PivotTable format version 2007.

You can also open an earlier version of the file format by opening an Excel 97-2003 file format in Excel version 2007, by saving it back to the Excel 97-2003 file format, and then by opening it again in Excel version 2007 (also called roundtripping), and still preserve each PivotTable format of version 2000, 2002-2003, or 2007. However, if you save a PivotTable format version 2007 in Excel file format 97-2003, the PivotTable is read-only, and you will not be able to create a PivotChart from this read-only PivotTable report.

When you use Excel version 2007 to upgrade PivotTable format version 2000 and 2002-2003 to version 2007, you refresh the PivotTable report in Excel 2007 (On the PivotTable Options tab, in the Data group, click Refresh), and then save the file to PivotTable format version 2007.

As you can see, the PivotTable format that you are actually working with depends on a number of factors, such as the current Excel file format, if you are in Compatibility Mode, and if you refresh the PivotTable. To summarize the most important points:

• In Excel 2007, you can have fully interactive PivotTables in all formats of versions 2000, 2002-2003, and 2007.
• In versions of Excel earlier than Excel 2007, you can have a fully interactive PivotTable in version 2000 and 2002-2003 format, and a read-only PivotTable in version 2007 format.

The following tables list the possible interactions.

Using Excel 2007  

Using versions of Excel earlier than Excel 2007  

 Note    After you convert a PivotTable format from version 2000 and 2002-2003 to version 2007, you cannot convert it back to version 2000 and 2002-2003 format.

 Top of Page

Feature differences between different PivotTable formats versions 2000 and 2002-2003, and version 2007

When you use a PivotTable format version 2007 in Excel 2007, you can use all of the features of Excel 2007. When you use a PivotTable format for versions 2000 and 2002-2003 in Excel 2007, you can use most of the features of Excel 2007, but there are some features that are not supported, including the following:

• Filtering labels and values (except by using a Top 10 filter, which are supported).
• Filtering fields not in the current PivotTable report layout from the PivotTable Field List.
• Hiding or unhiding intermediate levels of hierarchies in Online Analytical Processing (OLAP) data sources.
• Using manual, inclusive filtering (by clearing the Include new items in manual filter field check box in the Subtotals & Filters tab of the Field Settings dialog box).
• Exceeding new PivotTable limits, such as the new unique items per field limit of 1,048,576.

Although you can attempt to use these features, you may receive an alert asking you to refresh the PivotTable so that the PivotTable can be upgraded to PivotTable format version 2007.

 Top of Page

Important considerations when deciding whether to upgrade a PivotTable format from versions 2000 and 2002-2003 to version 2007

Keep in mind the following important considerations when deciding whether to upgrade a PivotTable format from versions 2000 and 2002-2003 to version 2007.

Sharing refreshable PivotTable reports   PivotTable reports in format version 2007 will be read-only in earlier versions of Excel 2007. If your users need to share the refreshable PivotTable reports that are connected to the same data source, you probably want to have those users consistently use Excel 2007.

Using PivotTable reports in a mixed version environment   If you need to create a PivotTable that can be used in versions of Excel earlier than Excel 2007, then do not to save the workbook to an Excel version 2007 file format. This will ensure PivotTable compatibility across different versions of Excel with full interactivity.

Filter results may vary   There are changes in the way that filtering works in a PivotTable format version 2007. Filter criteria are additive, which means that each successive filter filters the current applied filter. In addition, the top filter items included in a Top 10 filter are now selected by dynamically evaluating their values in the context of where the field is placed in the PivotTable. In both cases, depending on the filter and data, you may see different filter results between PivotTable reports in format version 2007 and versions 2000 and 2002-2003.

 Top of Page

See Also

• Office Excel 2007 features that are not supported in earlier versions of Excel
• Use Office Excel 2007 with earlier versions of Excel

Excel > PivotTable reports and PivotChart reports > Layout and format

Create and change the field layout in a PivotTable or PivotChart report

After you create a PivotTable or PivotChart report, use the PivotTable Field List to add fields. If you want to change a PivotTable or PivotChart report, use the Field List to rearrange and remove fields. By default, the PivotTable Field List displays two sections: a field section at the top for adding and removing fields, and a layout section at the bottom for rearranging and repositioning fields. You can dock the PivotTable Field List to either side of the window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally.

 Notes 

• If you don't see the PivotTable Field List, make sure that you click the PivotTable or PivotChart report. If you still don't see the PivotTable Field List, for a PivotTable report, on the Options tab, in the Show/Hide group, click Field List, and for a PivotChart report, on the Analyze tab, in the Data group, click Field List.
• If you don't see the fields in the Field List that you want to use, refresh the PivotTable or PivotChart report to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.

What do you want to do?

Learn about the PivotTable Field List

Add fields

Copy fields

Rearrange fields

Remove fields

Change the PivotTable Field List view

Filter data before adding it to the report

Switch between automatic or manual updating of the report layout

Learn about the PivotTable Field List

It's important to understand how the PivotTable field List works and the ways that you can arrange different types of fields so that you can achieve the results that you want when you lay out a PivotTable or PivotChart report.

How the PivotTable Field List works

An external data source contains structured data organized as one or more fields (also called columns) that are displayed in the Field List.

Move a field to the Report Filter area in the Field List, which simultaneously moves the field to the Report Filter area in the PivotTable report.

Move a field to the Column Label area in the Field List, which simultaneously moves the field to the Column Label area in the PivotTable report.

Move a field to the Row Label area in the Field List, which simultaneously moves the field to the Row Label area in the PivotTable report.

Move a field to the Values area in the Field List, which simultaneously moves the field to the Values area in the PivotTable report.

Guidelines for moving fields to the four report areas

The following are guidelines for moving Value, Name, and Dimension fields from the field section to the the four areas in the layout section.

Value fields  

• If you just select a check box for a numeric field, the default area it is moved to is the Values area.

Row and Column fields  

• If a field is already in a Row Labels, Column Labels, or Report Filters area, and it is added to one of those areas again, it changes position when it is moved to the same area, but changes orientation when it is moved to a different area.

Measures  

• In an Online Analytical Processing (OLAP) data source, there are often many fields (or measures) organized in a hierarchy of different dimensions, hierarchies, and levels. Click the expand and collapse buttons until you find the fields that you want.
• You can only move hierarchies, attributes, and named sets to the Row Labels, Column Labels, and Report Filters areas.
• You can only move measures, calculated measures, and Key Performance Indicators (KPIs) to the Values area.

 Top of Page

Add fields

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

 Top of Page

Copy fields

In a PivotTable report that is based on data in an Excel worksheet or external data from a non-OLAP data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.

1. Click and hold a field name in the field section, and then drag the field to the Values area in the layout section.
2. Repeat step 1 as many times as you want to copy the field.
3. In each copied field, change the summary function or custom calculation the way you want.

 Notes 

• When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down within the Values area. You can even move the Values Column label to the Column labels area or Row labels areas. However, you can’t move the Values Column label to the Report Filters area.
• You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
• Another way to add the same field to the Values area is by using a formula (also called a calculated column) that uses that same field in the formula.
• You cannot add the same field more than once in a PivotTable report based on an OLAP data source.

 Top of Page

Rearrange fields

You can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

• To rearrange fields, click the field name in one of the areas, and then select one of the following commands:

Tip  You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas.

 Top of Page

Remove fields

• To remove a field, click the field name in one of the layout areas, and then click Remove Field, or clear the check box next to each field name in the field section.

Tip  You can also click and hold a field name in the layout section, and then drag it outside the PivotTable Field List.

 Note    Clearing a check box in the field section removes all instances of the field from the report.

 Top of Page

Change the PivotTable Field List view

The PivotTable Field List has five different views that are designed and optimized for different types of PivotTable tasks.

• To change the view, click the View button at the top of the PivotTable Field List and select one of the following:

Tip  In the Fields Section and Areas Section Stackedand Fields Section and Areas Section Side-By-Side views, you can adjust the width and height of each section by hovering over the section divider until the pointer changes to a vertical double arrow or horizontal double arrow , by dragging the double arrow up or down or left or right to where you want it, and then either by clicking the double arrow or by pressing ENTER.

 Top of Page

Filter data before adding it to the report

If the PivotTable report is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report.

• Hover over the field name in the field section, and then click the filter drop-down arrow next to the field name.

The Filter menu is displayed. For more information, see Filter data in a PivotTable report or PivotChart report.

 Top of Page

Switch between automatic or manual updating of the report layout

By default, each time that you make a change in the PivotTable Field List, the report layout is automatically updated. To improve performance when you are accessing a large amount of external data, you can switch to manual updating. When you switch to manual updating, you cannot use the report until you switch back to automatic updating. However, you can quickly add, move, and remove fields from the field section to the layout section, and then switch back to automatic updating to see your results.

 Caution    If you set the report layout to manual updating, closing the PivotTable Field List, changing to Fields only view, or exiting Excel discards all layout changes that you have made to the PivotTable report without confirmation.

• To enable or disable manual updating of the PivotTable report, select or clear the Defer layout update check box at the bottom of the PivotTable Field List.

When you finish changing the report layout in the Field List, click Update to see the layout in the PivotTable report.

 Note    A PivotTable report starts with automatic updating each time that you open the workbook.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Basics

Select data in a PivotTable report

To apply formatting, copy data, or make changes to a PivotTable report, you can select either individual cells or data regions by using commands or by using a mouse. The mouse pointer changes depending on what you are trying to select:

• To select cells, use the regular Microsoft Office Excel mouse pointer .
• To select data regions, position the mouse pointer at the top or left sides of fields and labels so that the mouse pointer changes to a down arrow or a right arrow .

 Note    If you don't see the pointer change shape, on the Options tab, in the Actions group, click Select, and then make sure that Enable Selection is selected.

What do you want to do?

Select individual cells

Select an entire report

Select all items in a field

Select item labels, data, or both

Select all instances of a single item

Select one instance of an item

Select multiple items

Select subtotals and totals

Select individual cells

1. Point to the first cell that you want to select, making sure the mouse pointer is .
2. Click to select a single cell, or click and drag to select multiple cells.

 Top of Page

Select an entire report

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

 Top of Page

Select all items in a field

Point to the top edge of the field until the mouse pointer changes to a down arrow , and then click.

For example, to select the East and West items, point to the top of Region, and then click once.

 Top of Page

Select item labels, data, or both

1. Select the items.
2. on the Options tab, in the Actions group, click Select, and then click what you want to select:

Labels and Values to select both.

Values to select only the values for the items.

Labels to select only the item labels.

 Top of Page

Select all instances of a single item

Point to the top edge of a column field item until the mouse pointer changes to a down arrow , or point to the left edge of a row field item until the mouse pointer changes to a right arrow , and then click.

For example, to select both rows for Golf, click the left edge of Golf.

 Top of Page

Select one instance of an item

1. Point to the top edge of a column field item until the mouse pointer changes to a down arrow , or point to the left edge of a row field item until the mouse pointer changes to a right arrow .
2. Click once, and then click additional times, waiting between clicks so that you do not double-click.

For example, to select only the row for Qtr1 Golf, click the left edge of Golf, then click again.

If a PivotTable report has several row labels, click repeatedly until you've selected only the items that you want. For example, to select a single instance of an item in the innermost of four row labels, you would click four times.

 Top of Page

Select multiple items

1. Point to the top edge of a column field item until the mouse pointer changes to a down arrow , or point to the left edge of a row field item until the mouse pointer changes to a right arrow .
2. Click as many times as necessary to select one of the items that you want. For more information, see Select one instance of an item.
3. Hold down SHIFT and click, or hold down CTRL and click to select additional items within the same field.
4. To cancel selection of an item, hold down CTRL and click the item.

 Top of Page

Select subtotals and totals

1. Point to the top edge of a subtotal or total in a column label until the mouse pointer changes to a down arrow , or point to the left edge of a subtotal or total in a row label until the pointer changes to a right arrow .
2. Click once to select all totals for the label, and then click again to select only the current subtotal or total.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Basics

Print a PivotTable report

Excel 2007

Printing a PivotTable report the way that you want often requires using a combination of print features for worksheets and PivotTable reports. To use these features, you must have only one report on the worksheet, or you must set a print area (print area: One or more ranges of cells that you designate to print when you don't want to print the entire worksheet. If a worksheet includes a print area, only the print area is printed.) that includes only one report.

Tip  For the best results, follow these sections in sequence.

In this article

Set a print area

Fine-tune the page layout in Page Layout view

Repeat row and column labels on each page as print titles

Set page breaks after each outer row label

Adjust page breaks throughout the report

Decide whether to print the expand and collapse buttons

Check your final layout in Print Preview and then print the report

Set a print area

If you have more than one 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.) on the worksheet, set a print area (print area: One or more ranges of cells that you designate to print when you don't want to print the entire worksheet. If a worksheet includes a print area, only the print area is printed.) that includes only the report that you want to print.

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

3. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.

 Top of Page

Fine-tune the page layout in Page Layout view

Page Layout view is useful for getting your data ready for printing. For example, you can use the rulers to measure the width and height of the data, change the page orientation, add or change page headers and footers, set margins for printing, and hide or display row and column headers.

1. On the View tab, in the Workbook Views group, click Page Layout.

Tip  You can also click Page Layout View on the status bar.

2. Make adjustments to the page layout.

For more information, see Use Page Layout view to fine-tune pages before printing.

 Top of Page

Repeat row and column labels on each page as print titles

You can repeat the row labels and column labels of a report on every printed page as print titles (print titles: Row or column labels that are printed at the top of or on the left side of every page on a printed worksheet.). When you change the layout of the report so that the labels are shown in different worksheet rows and columns, the new label rows and columns are repeated automatically the next time you print the report.

1. On the Page Layout tab, in the Page Setup group, click Print Titles.

2. In the Page Setup dialog box, make sure that the Rows to repeat at top and Columns to repeat at left check boxes are cleared.
3. Close the Page Setup dialog box.
4. On the Options tab, in the PivotTable group, click Options.

5. In the PivotTable Options dialog box, click the Printing tab, and then select the Set print titles check box.
6. If your report has more than one row label and you also want to repeat the outer row label items on each page, select the Repeat row labels on each printed page check box.

 Top of Page

Set page breaks after each outer row label

When your report has multiple row labels and a page break falls within a group of row label items, you can set the report to automatically repeat the item labels for the outer labels at the top of the next page. In a report with two or more row labels, all but the rightmost label are outer row labels. In the following example, the report is set so that the items East and Qtr2 from the outer row labels Region and Qtr are printed on the next page after the page break.

Page break within an item group.

Item labels from the outer row labels are repeated on page 2.

1. Right-click the outer row label that has the items that you want to print on separate pages, and then click Field Settings on the shortcut menu.
2. In the Field Settings dialog box, click the Layout & Print tab.
3. Select the Insert page break after each item check box.

 Top of Page

Adjust page breaks throughout the report

It is a good idea to review the entire PivotTable report in Page Break Preview, in case you need to make additional adjustments of the page breaks.

• On the View tab, in the Workbook Views group, click Page Break Preview.

Tip  You can also click Page Break Preview on the status bar.

You can insert new manual page breaks and move and delete automatic page breaks. For more information, see Add, delete, or move page breaks.

 Top of Page

Decide whether to print the expand and collapse buttons

You may or may not want to print the expand and collapse buttons. To decide whether you want to print the expand and collapse buttons, do the following:

• To show or hide the expand and collapse buttons on the worksheet, on the Options tab, in the Show/Hide group, click +/- Buttons.

• To show or hide the expand and collapse buttons on the printed report, do the following:
1. On the Options tab, in the PivotTable group, click Options.

2. Click the Printing tab, and then select or clear the Print expand/collapse buttons when displayed on PivotTable check box.

 Note    This check box requires the +/- Buttons command in the Show/Hide group on the Options tab to be turned on.

 Top of Page

Check your final layout in Print Preview and then print the report

1. To check your final print layout, click the Microsoft Office Button , click the arrow next to Print, and then click Print Preview.

Keyboard shortcut  You can also press CTRL+F2.

Make final adjustments as necessary. For more information, see Preview worksheet pages before printing.

2. When the preview looks correct, click Print.

 Top of Page

See Also

• Prepare a worksheet for printing
• Print a worksheet or workbook

Excel > PivotTable reports and PivotChart reports > Basics

Select different source data for a PivotTable report

Excel 2007

1. Click the 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.).
2. On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source.

The Change PivotTable Data source dialog box is displayed.

3. Do one of the following:
• To use a different Microsoft Office Excel table or cell range, click Select a table or range, and then enter the first cell in the Table/Range text box.

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

4. To use a different connection, click select a Use an external data source, and then click Choose Connection.

The Existing Connections dialog box is displayed.

5. In the Show drop-down list 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).
6. 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 category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.

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

7. Click OK.

Excel > PivotTable reports and PivotChart reports > Basics

Clear a PivotTable report or PivotChart report

To remove all report filters, labels, values, and formatting from a PivotTable report, and to start designing the layout all over again, use the Clear All command. This command effectively resets the PivotTable report, but it does not delete it. The data connection, placement of the PivotTable report, and PivotTable cache remain. If there is a PivotChart associated with the PivotTable report, the Clear All command also removes related PivotChart fields, chart customizations, and formatting.

 Caution    If you are sharing a data connection or if you are using the same data between two or more PivotTable reports, then using the Clear All command on one PivotTable could also remove the grouping, calculated fields or items, and custom items in the other shared PivotTable reports. However, you are warned before Microsoft Office Excel attempts to remove items in the other shared PivotTable reports, and you can cancel the operation.

PivotTable report

1. Click the PivotTable report.
2. On the Options tab, in the Actions group, click Clear, and then click Clear All.

PivotChart report

1. Click the PivotChart report.
2. On the Analyze tab, in the Data group, click Clear, and then click Clear All.

 Note    The Clear All command is not visible when the worksheet that contains the PivotTable report is protected. The Clear All command does not work if you protect the worksheet and then select the Use PivotTable Reports check box in the Protect Sheet dialog box because the Clear All command requires a refresh operation.

Excel > PivotTable reports and PivotChart reports > Basics

Move a PivotTable report

You might want to move a PivotTable report so you can insert worksheet cells, rows, or columns at the current location of the report.

1. Click the PivotTable report.
2. On the Options tab, in the Actions group, click Move PivotTable.

The Move PivotTable dialog box is displayed.

3. Under Choose where you want the PivotTable to be placed, do one of the following:
• To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.
• To place the PivotTable in an existing worksheet, select Existing worksheet, and then type the first cell in the range of cells where you want to locate the PivotTable report.

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

Excel > PivotTable reports and PivotChart reports > Basics

Rename a PivotTable or PivotChart report

When you create a PivotTable or PivotChart report, Microsoft Office Excel assigns default names to each of these objects by using the following naming conventions: PivotTable1, PivotTable2, and so on; and Chart 1, Chart 2, and so on. However, you can change the name of each object to make it more meaningful to you.

PivotTable report

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click the PivotTable Name text box.
3. Type a new name.
4. Press ENTER.

PivotChart report

1. Click the PivotChart report.
2. On the Layout tab, in the Properties group, click the Chart Name text box.
3. Type a new name.
4. Press ENTER.

Excel > PivotTable reports and PivotChart reports > Basics

Rename a field or item in a PivotTable or PivotChart report

You can quickly change the Custom Name of a field or item without displaying the Field Settings or Value Field Settings dialog boxes. The following procedures do not change the Source Name of the field or item.

PivotTable report

1. Click the field or item that you want to rename.
2. On the Options tab, in the Active Field group, click the Active Field text box.
3. Type a new name.
4. Press ENTER.

 Note    Renaming a numeric item changes it to text, which sorts separately from numeric values and can't be grouped with numeric items.

PivotChart report

1. Click the object in the chart (such as a bar, line, or column) that corresponds to the field or item that you want to rename.
2. On the Analyze tab, in the Active Field group, click the Active Field text box.
3. Type a new name.
4. Press ENTER.

Excel > PivotTable reports and PivotChart reports > Basics

PivotTable options

Excel 2007

Use the PivotTable Options dialog box to control various settings for a PivotTable report.

Name  Displays the PivotTable name. To change the name, click the text in the box and edit the name.

Layout & Format

Totals & Filters

Display

Printing

Data

• Automatic   The default number of unique items for each field.
• None   No unique items for each field.
• Max   The maximum number of unique items for each field. You can specify up to 1,048,576 items.

Excel > PivotTable reports and PivotChart reports > Basics

Field settings

Excel 2007

Use the Field Settings dialog box to control various format, print, subtotal, and filter settings for a field in a PivotTable report.

Source Name  Displays the name of the field in the data source.

Custom Name   Displays the current field name in the PivotTable report, or the source name if there is no custom name. To change the Custom Name, click the text in the box and edit the name.

Layout & Print

Subtotals & Filters

• Automatic Uses the default function for the field.
• None Displays the field without a subtotal.
• Custom Enables selecting one of the following functions as a subtotal.

•  Note    You cannot use a custom function with an OLAP data source.

Excel > PivotTable reports and PivotChart reports > Basics

Use a PivotTable report to make external table data available in Excel Services

Excel 2007

If you want to work with table data based on an external data connection in Excel Services, you can't use an external data range (also called a query table). You must create a PivotTable report that flattens multidimensional, hierarchical data into a table, or two dimensions. Here is how to do this.

Convert a multidimensional PivotTable report …

…to a two-dimensional PivotTable report…

…so that you can see flattened table data in Excel Services.

What do you want to do?

Learn how Excel Services supports connections to external data sources

Learn about differences between PivotTable reports that are connected to external data and external data ranges

Make a two-dimensional, tabular PivotTable report

Create the PivotTable report and connect it to the external data source

Add, lay out, and arrange the fields in the Row Labels area

Change PivotTable options and field settings

Lay out the PivotTable report as a two-dimensional table

Customize the design of the PivotTable report

Publish the workbook to Excel Services

Learn how Excel Services supports connections to external data sources

PivotTable reports are designed to aggregate a lot of numerical data into sums and totals, and to work with multidimensional data that is organized into a hierarchy. On the other hand, external data ranges are two-dimensional tables structured as rows and columns, that display nonaggregated records of source data.

When you use the Data Connection Wizard or Microsoft Query to connect to external data, you usually create an external data range. The only exception to this is when you create a PivotTable report that is connected to external data. A PivotTable report does not create an external data range. This difference in the way that connections are created is important to understand when you publish a workbook to Excel Services, because Excel Services only supports external data connections based on PivotTable reports and does not support external data ranges.

 Top of Page

Learn about differences between PivotTable reports that are connected to external data and external data ranges

When you work with a PivotTable report that is connected to external data, there are differences between the behavior of a PivotTable report and an external data range that you should be aware of, including the following:

PivotTable reports group and subgroup data in a left-to-right order

In general, grouped data is based on a sort of the data by one or more columns. For example, if you want to see all employees grouped by their departments, you can perform a primary sort of the data by departments and a secondary sort by employees. You can also nest groups, such as Product Line, Category, and Product, or Geography, Country/Region, State/Province, and City. By default, PivotTable reports are automatically sorted into groups and subgroups in a left-to-right order to make it easy to see related data, to display heading and summary information, and to expand or collapse related items.

Data grouped by Product Line, and subgrouped within each Product Line by Category

By putting a column with unique values in the Row Labels area on the left, you can automatically flatten the data.

The same Product Line data flattened by adding the ID-Num column

If you don't want to display the column, after adding the column to the PivotTable report, you can hide the column. (Select the column, and then on the Home tab, in the Cells group, click the arrow next to Format, point to Hide & Unhide, and then click Hide Columns.)

Important  If a PivotTable report detects two or more duplicate rows of data from the data source, the PivotTable report displays only one row. If you want all rows, even duplicate rows, displayed in a PivotTable report, you must add a column that contains unique values to the table, query, or view that you import from the data source. If the data source does not have a unique column, you could add one at the data source. For example, you could add a column that has the AutoNum data type to a Microsoft Office Access table, or a column that has the Identity data type to a Microsoft SQL Server table, or a calculated column that displays a unique number for each record to a database query or view.

Editing cell data and adding calculated columns

Before you publish the workbook to Excel Services you can edit the data in an external data range, but PivotTable data is always read-only.

If you double-click a cell in an external data range, you enter edit mode, and you can change the value or even enter a formula. However, note that you are not changing the original value at the data source, and the next time that you refresh the data, the new value might be overwritten, depending on the setting of the external data range property If the number of rows changes upon data refresh.

If you double-click a Row Label cell in a PivotTable report, the cell expands or collapses the items in that Row Label. When you flatten a PivotTable report into a two-dimensional table, the flattening has the effect of making all of the row values in a group to the left of the cell disappear or reappear.

You can add calculated columns anywhere in a Microsoft Office Excel table that is based on an external data range, or you can insert columns and fill down formulas in an external data range. You cannot insert calculated columns inside of a PivotTable report (although you can insert calculated fields). However, you can add a column that fills down data or formulas to the immediate left or right of the PivotTable report, and inside a filled-down formula, you can use cell references within the PivotTable report.

Creating charts

If you want to create a chart based on the data in the PivotTable report, you can create a PivotChart report, which behaves similarly to standard charts, but there are some differences, including the following:

Interaction   With standard charts, you create one chart for each view of the data that you want to see, but the views are not interactive. With PivotChart reports, you can create a single chart and interactively view the data in different ways by changing the report layout or the detail that is displayed. In Excel Services, both a standard chart and PivotChart report can be updated based on user interaction with the workbook, but both charts are displayed as static images.

Chart types   The default chart type for a standard chart is a clustered column chart, which compares values across categories. The default chart type for a PivotChart report is a stacked column chart, which compares the contribution of each value to a total across categories. You can change a PivotChart report to any chart type except xy (scatter), stock, or bubble.

Chart elements   PivotChart reports contain the same elements as standard charts, but they also contain fields and items that can be added to, rotated, or removed, to display different views of your data. Categories, series, and data in standard charts are category fields, series fields, and value fields in PivotChart reports. PivotChart reports can also contain report filters. Each of these fields contains items, which in standard charts are displayed as category labels or series names in legends (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.).

Formatting   Most formatting, including elements, 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 series are not preserved. Standard charts do not lose this formatting once applied.

Moving or resizing items   In a PivotChart report, you cannot move or resize the plot area (plot area: In a 2-D chart, the area bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including the data series, category names, tick-mark labels, and axis titles.), legend, chart titles, or axis titles, although you can select one of several preset positions for the legend, and you can change the font size of titles. In a standard chart, you can move and resize all of these elements.

After you create a PivotChart report, if you prefer, you can convert it to a standard chart. For more information, see the article Create or delete a PivotTable or PivotChart report.

Comparing external data range properties and PivotTable options

External data ranges and PivotTable reports have properties that you can use to control the data formatting and layout, and the refresh behavior of the data.

Both external data ranges and PivotTable reports enable you to adjust column width, preserve column sorting, filtering, cell formatting, and the layout of the data if the external data is refreshed.

In an external data range, you also have the option of including row numbers as the first column of data. In a PivotTable report, there is no similar option. See the section, PivotTable reports group and subgroup data in a left-to-right order, for a discussion about adding a unique column at the data source.

When you refresh an external data range, you can control what happens to existing data, if the number of rows in the data range changes, by setting one of the following options:

• Insert cells for new data, delete unused cells (the default)
• Insert entire rows for new data, clear unused cells
• Overwrite existing cells with new data, clear unused cells

When you refresh a PivotTable report, it just brings in new data. However, you may be prompted and asked to decide whether to overwrite existing cells below the current report.

Filtering data

Filtering data in an external data range and PivotTable report are very similar, but there are some differences, including the following:

• External data ranges use the Text Filter, Number Filter, and Date Filter commands, whereas PivotTable reports use the Label Filter, Value Filter, and Date Filter commands.
• The Filter menu of an external data range always removes items that are no longer valid, and the Filter menu never includes new items with an applied filter after a refresh operation, but a PivotTable report has options to control this behavior. For more information, see the section Change PivotTable options and field settings.
• A PivotTable report has a report filter feature (not available to an external data range) that enables users to work with a subset of data in the PivotTable report.

Sorting data

In an external data range, you can perform a multicolumn sort by up to 64 columns, you can choose any column as the primary sort, and you can choose any other columns as secondary sorts. However, the processes of sorting data in a PivotTable report and sorting an external data range are different. Unlike an external data range, in a PivotTable report you can only perform a primary sort on the left-most column. All other columns perform secondary sorts based on the column immediately to the left. In short, you can only perform a primary and secondary sort in a left-to-right order.

Furthermore, when you place a column with unique values as the first column on the left, you can only sort by that column, because, in effect, you are creating a primary group for each row. Depending on the needs of your users, you may want to presort the data in Excel, and after publishing the workbook to Excel Services, turn off sorting by clearing the Sorting check box in the Microsoft Office Excel Web Access Web Part tool pane.

Using hyperlinks

Unlike external data ranges, active hyperlinks are not supported in PivotTable cells. The hyperlink is treated as text, but you cannot follow the hyperlink to a Web page or document, in Excel or Excel Services.

 Top of Page

Make a two-dimensional, tabular PivotTable report

There are a number of steps that are required to flatten multidimensional data into a two-dimensional, tabular PivotTable report. For best results, do the following procedures in sequence.

 Top of Page

Create the PivotTable report and connect it to the external data source

1. Select a blank cell.
2. On the Insert tab, in the Tables group, click PivotTable, and then click PivotTable.

The Create PivotTable dialog box is displayed.

3. Click Use an external data source.
4. Click Choose Connection.

The Existing Connections dialog box is displayed.

5. In the Show list 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).
6. Select a connection from the Select a Connection list, and then click Open.
7. Enter a location. Do 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 type the first cell of the range of cells where you want to place the PivotTable report.

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

8. Click OK.

An empty PivotTable report, with the PivotTable Field List displayed, is added to the location that you entered.

 Top of Page

Add, lay out, and arrange the fields in the Row Labels area

Use the PivotTable Field List to add, lay out, and arrange the fields, and to make sure that all fields are added to the Row Labels area.

1. Click the PivotTable report.
2. Do one or more of the following:
• 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 in the Field List. Each field is placed in a default area of the layout section, but you can rearrange the fields.

By default, non-numeric fields are added to the Row Labels area, numeric fields are added to the Values area, and time/date fields are added to the Column Labels area.

• To move a field into the Row Labels area, right-click the field name, and then select Add to Row Labels on the shortcut menu.

Tip  You can also click and hold a field name, and then drag the field from the field section to an area in the layout section.

• To rearrange fields, click the field name in one of the areas, and then select one of the following commands:

• To remove a field, click the field name in one of the layout areas, and then click Remove Field, or clear the check box next to each field name that you want to remove, in the field section.

Tip  You can also click and hold a field name in the layout section, and then drag the field name outside of the PivotTable Field List.

 Note    Clearing a check box in the field section removes all instances of the field from the report.

 Top of Page

Change PivotTable options and field settings

To ensure that a PivotTable report behaves similarly to an external range, do the following:

Change PivotTable options  

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.

The PivotTable Options dialog box is displayed.

3. To make a column width automatically adjust after a refresh operation, click the Layout & Format tab, and then under the Display section, select the Autofit column widths on update check box.
4. To keep formatting every time that the data is refreshed, click the Layout & Format tab, and then under the Display section, select the Preserve cell formatting on update check box.
5. To make sure that the Filter menu removes items that are no longer valid, after a refresh operation, click the Data tab, and in the Number of items to retain per field box, select None.

Change field settings  

1. Click the PivotTable report.
2. To include new items in a PivotTable report with an applied filter, for each field, right-click a cell, click Field Settings on the shortcut menu, click the Subtotals & Filters tab, and then under the Filters section, select the Include new items in manual filter check box.

 Top of Page

Lay out the PivotTable report as a two-dimensional table

To lay out the PivotTable report as a two-dimensional table, do the following:

1. Click the PivotTable report.
2. On the Design tab, in the Layout group, click Subtotals, and then click Do Not Show Subtotals.
3. On the Design tab, in the Layout group, click Grand Totals, and then click Off for Rows and Columns.
4. On the Design tab, in the Layout group, click Report Layout, and then click Show in Tabular Form.
5. On the Options tab, in the Show/Hide group, click Field Headers.

 Top of Page

Customize the design of the PivotTable report

The built-in PivotTable styles and style options are designed for multidimensional data, not a two-dimensional table. When you flatten the report into a two-dimensional table, these styles do not display cell borders, and the Banded Rows and Banded Columns check boxes in the PivotTable Style Options group of the Design tab do not affect the format of cells. However, you can customize a PivotTable style so that each cell has a cell border and the PivotTable report uses conditional formatting to display banded rows or columns.

Customize the PivotTable style  

1. Click the PivotTable report.
2. On the Design tab, in the PivotTable Styles group, select a style that you want, and then do the following:
1. Right-click a visible style, scroll through the gallery, or to see all of the available styles, click More at the bottom of the scroll bar.
2. Click Duplicate.

The Modify Pivot Table Quick Style dialog box is displayed.

3. Optionally, enter a new name in the Name box.
4. In the Table element box, select Whole Table, and then click Format.

The Format Cells dialog box is displayed.

5. Click the Border tab, and then create a cell border.
6. Optionally, click the Fill and Font tabs, and make other changes.
7. Click OK twice.
8. Click the More button at the bottom of the scroll bar, and then under the Custom category, click the PivotTable style that you just created.

Display banded rows or banded columns by using conditional formatting  

1. Select all of the columns in the PivotTable report.
2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3. To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

4. Under Select a Rule Type, click Use a formula to determine which cells to format.
1. Under Edit the Rule Description, in the Format values where this formula is true box, enter a formula.
2. Do one of the following:
• To band rows, enter the following formula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A))

• To band columns, enter the following formula:

=AND(MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

• To band rows and columns, enter the following formula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A),MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

3. Click Format to display the Format Cells dialog box.
4. Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK.

You can choose more than one format. The formats that you select are displayed in the Preview box.

 Top of Page

Publish the workbook to Excel Services

1. Click the Microsoft Office Button , click the arrow next to Publish, and then click Excel Services under Distribute the document to other people.
2. In the File name box, enter the path to the server, and accept the suggested name for the workbook or type a new name if needed.

On a computer that is running Windows Vista  

• In the Address bar, select a network location that is accessible to the intended users, and then click Save.

On a computer that is running Microsoft Windows XP  

• In the Save in box, select a network location that is accessible to the intended users, and then click Save.

For example, type http://server/site/file name

 Note    Excel can publish a workbook to the server in only the Microsoft Office Excel 2007 XML-based file format (.xlsx) or Office Excel 2007 Binary file format (.xlsb).

3. Click OK.
4. To verify that the viewable areas of the workbook are displayed correctly in the browser, select the Open this workbook in my browser after I save check box.
5. Click Save.

For more information, see the article Publish a workbook to Excel Services.

 Top of Page

See Also

• Create or delete a PivotTable or PivotChart report
• Design the layout and format of a PivotTable report
• Get Help about SQL Server 2005 Analysis Services

Excel > PivotTable reports and PivotChart reports > Basics

What happened to the PivotTable and PivotChart Report command?

In earlier versions of Microsoft Office Excel, the PivotTable and PivotChart Report command was located on the Data menu and it started the PivotTable and PivotChart Wizard. In Microsoft Office Excel 2007, the PivotTable and PivotChart Report command has been separated into the following two commands:

• The PivotTable command, which displays the Create PivotTable dialog box.
• The PivotChart command, which displays the Create PivotTable with PivotChart dialog box.

Both commands are available from the PivotTable in-group command in the Tables group on the Insert tab. Both dialog boxes have similar user interfaces, and are much simpler to use than the wizard in the earlier version. The difference is that the Create PivotTable dialog box only creates a PivotTable report, and the Create PivotTable with PivotChart dialog box creates a PivotChart report with an associated PivotTable report.

See Also

• Consolidate multiple worksheets into one PivotTable report
• Optimize memory in a PivotTable report
• Retrieve PivotTable data one report filter item at a time
• Unshare a data cache between PivotTable reports

Excel > PivotTable reports and PivotChart reports > Layout and format

Design the layout and format of a PivotTable report

Excel 2007

After you create a PivotTable report and have added the fields that you want, you often want to enhance the layout and format of the report to improve readability and to make it more attractive. This article discusses various ways you can work with the layout and format of a report.

Important  Refer to the See Also section for useful demos and training that provide important background information about PivotTable reports, in addition to other ways to change the format of a report.

What do you want to do?

Change the form layout and field arrangement

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

Add, copy, rearrange, and remove fields

Change the layout of columns, rows, and subtotals

Turn column and row field headers on or off

Display subtotals above or below their rows

Change the order of row or column items

Adjust column widths on refresh

Move a column to the row labels area or a row to the column labels area

Merge or unmerge cells for outer row and column items

Change how blank cells, blank lines, and errors are displayed

Change how errors and empty cells are displayed

Display or hide blank lines

Change how items and labels with no data are shown

Change or remove formatting

Change the PivotTable format style

Conditionally format data in a PivotTable report

Change the number format for a field

Include OLAP server formatting

Preserve or discard formatting

Remove all formatting from a report

Change the form layout and field arrangement

To make substantial changes to the layout and format of the report, you can organize the entire report into three forms — compact, outline, or tabular. You can also add, rearrange, and remove fields to get the final results that you want.

 Top of Page

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

You can change the form, whether compact, outline or tabular, for a PivotTable report and any of its various fields.

PivotTable report  

1. Click the PivotTable report.
2. On the Design tab, in the Layout group, click Report Layout, and then do one of the following:
• Show in Compact Form  Use to keep related data from spreading horizontally off of the screen and to help minimize scrolling. Beginning fields on the side are contained in one column and are indented to show the nested column relationship.
• Show in Outline Form  Use to outline the data in the classic PivotTable style.
• Show in Tabular Form  Use to see all data in a traditional table format and to easily copy cells to another worksheet.

Fields  

• Select a row field, and then on the Options tab, in the Active Field group, click Field Settings.

The Field Settings dialog box opens.

Tip  You can also double-click the row field in outline or tabular form.

• Click the Layout & Print tab, and then under the Layout section, do one of the following:
• To show field items in outline form, click Show item labels in outline form.
• To display or hide labels from the next field in the same column in compact form, click Show item labels in outline form, and then select Display labels from the next field in the same column (compact form).
• To show field items in table-like form, click Show item labels in tabular form.

 Top of Page

Add, copy, rearrange, and remove fields

You use the PivotTable Field List to add, copy, rearrange, and remove fields.

For more information about working with the PivotTable Field List, see Create and change the field layout in a PivotTable or PivotChart report.

 Notes 

• If you don't see the PivotTable Field List, make sure that the PivotTable or PivotChart report is selected. If you still don't see the PivotTable Field List, for a PivotTable report, on the Options tab, in the Show/Hide group, click Field List, and for a PivotChart report, on the Analyze tab, in the Data group, click Field List.
• If you don't see the fields in the Field List that you want to use, refresh the PivotTable or PivotChart report to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.

Add fields

• 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, non-numeric fields are added to the Row Labels area, numeric fields are added to the Values area, and 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, or 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.

 Top of Page

Copy fields

In a PivotTable report that is based on data in an Excel worksheet or external data from a non-OLAP data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.

1. Click and hold a field name in the field section, and then drag the field to the Values area in the layout section.
2. Repeat step 1 as many times as you want to copy the field.
3. In each copied field, change the summary function or custom calculation the way you want.

 Notes 

• When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down within the Values area. You can even move the Values Column label to the Column labels area or Row labels areas. However, you can’t move the Values Column label to the Report Filters area.
• You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example to the Row Labels and the Column labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
• Another way to add the same field to the Values area is by using a formula (also called a calculated column) that uses that same field in the formula.
• You cannot add the same field more than once in a PivotTable report that is based on an OLAP data source.

 Top of Page

Rearrange fields

You can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

• To rearrange fields, click the field name in one of the areas, and then select one of the following commands:

Tip  You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas.

 Top of Page

Remove fields

• To remove a field, click the field name in one of the layout areas, and then click Remove Field, or clear the check box next to each field name in the field section.

Tip  You can also click and hold a field name in the layout section, and then drag it outside the PivotTable Field List.

 Note    Clearing a check box in the field section removes all instances of the field from the report.

1. Click the PivotTable report.
2. If necessary, on the Options tab, in the Show/Hide group, click Field List, and for a PivotChart report, on the Analyze tab, in the Show/Hide group, click Field List.

 Top of Page

Change the layout of columns, rows, and subtotals

To further refine the layout of the report, you can make changes that affect the layout of columns, rows, and subtotals, such as displaying subtotals above rows or turning column headers off. You can also rearrange individual items within a row or column.

Turn column and row field headers on or off

1. Click the PivotTable report.
2. To switch between showing and hiding field headers, on the Options tab, in the Show/Hide group, click Field Headers.

 Top of Page

Display subtotals above or below their rows

1. Select the row field, and then on the Options tab, in the Active Field group, click Field Settings.

The Field Settings dialog box opens.

Tip  You can also double-click the row field in outline or tabular form.

2. If subtotals are not turned on (the option None is selected), click the Subtotals & Filters tab, and then under the Subtotals & Filters section, click Automatic or Custom.
3. Click the Layout & Print tab, and then under the Layout section, click Show item labels in outline form.
4. Do one of the following:
• To display subtotals above the subtotaled rows, select the Display subtotals at the top of each group check box.
• To display subtotals below the subtotaled rows , clear the Display subtotals at the top of each group check box.

 Top of Page

Change the order of row or column items

• Right-click the row and column label or item in a label, point to Move, and then use one of the commands on the Move menu to move the item.

You can also select the row or column label item, and then point to the bottom border of the cell. When the pointer becomes an arrow, drag the item to a new position. The following illustration shows how to select a row item.

 Top of Page

Adjust column widths on refresh

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.

The PivotTable Options dialog box appears.

3. Click the Layout & Format tab, and then under the Display section, do one of the following:
• To adjust the PivotTable report columns to automatically fit to the size of the widest text or number value, select Autofit column widths on update.
• To keep the current PivotTable report column width, clear Autofit column widths on update.

 Top of Page

Move a column to the row labels area or a row to the column labels area

You might want to move a column field to the row labels area or a row field to the column labels area to optimize the layout and readability of the PivotTable report. When you move a column to a row or a row to a column, you are transposing the vertical or horizontal orientation of the field. This operation is also called "pivoting" a row or column.

• Right-click the row field, point to Move <field name>, and then click Move <field name> To Columns, or right-click the column field, and then click Move <field name> to Rows.

You can also drag a field. The following illustration shows how to move a column field to the row labels area.

Click a column field

Drag it to the row area

Sport becomes a row field like Region

 Top of Page

Merge or unmerge cells for outer row and column items

You can merge cells for row and column items in order to center the items horizontally and vertically, or to unmerge cells in order to left-justify items in the outer row and column fields at the top of the item group.

1. Click the PivotTable report.
2. the Options tab, the PivotTable group, click Options.

The PivotTable Options dialog box appears.

3. To merge or unmerge cells for outer row and column items , click the Layout & Format tab, and then under the Layout section, select or clear the Merge and center cells with labels check box.

 Note     You cannot use the Merge Cells check box under the Alignment tab in a PivotTable report.

 Top of Page

Change how blank cells, blank lines, and errors are displayed

There may be times when your data contains blank cells, blank lines, or errors, and you want to adjust the default behavior of a report.

Change how errors and empty cells are displayed

1. Click the PivotTable report.
2. the Options tab, in the PivotTable group, click Options.

The PivotTable Options dialog box appears.

3. Click the Layout & Format tab, and then under the Format section, do one or more of the following:

Change error display  Select the For error values show check box. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.

Change empty cell display  Select the For empty cells show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

 Top of Page

Display or hide blank lines

You can display or hide blank lines after a row or item.

Rows  

1. Select the row field, and then on the Options tab, in the Active Field group, click Field Settings.

The Field Settings dialog box opens.

Tip  You can also double-click the row field in outline or tabular form.

2. To add or remove the blank rows, click the Layout & Print tab, and then under the Layout section, select or clear Insert blank line after each item label.

Items  

1. Select the item in a PivotTable report.
2. On the Design tab, in the Layout group, click Blank Rows, and then select Insert Blank Line after Each Item Label or Remove Blank Line after Each Item Label.

 Note    You can apply character and cell formatting to the blank lines, but you cannot enter data in them.

 Top of Page

Change how items and labels with no data are shown

1. Click the PivotTable report.
2. Click the Display tab, and then under the Display section, do one or more of the following:
• Show items with no data on rows   Select or clear to display or hide row items that have no values.

 Note    This setting is only available for 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.) data source.

• Show items with no data on columns  Select or clear to display or hide column items that have no values.

 Note    This setting is only available for an OLAP data source.

• Display item labels when no fields are in the values area  Select or clear to display or hide item labels when there are no fields in the value area.

 Note    This check box only applies to PivotTable reports that were created by using versions of Microsoft Office Excel earlier than Office Excel 2007.

 Top of Page

Change or remove formatting

You can choose from a wide variety of styles in the gallery. In addition, you can control the banding behavior of a report. Changing the number format of a field is a quick way to apply a consistent format throughout a report.

Change the PivotTable report format style

You can easily change the style of a PivotTable report by using a gallery of styles. Office Excel 2007 provides numerous predefined table styles (or quick styles) that you can use to quickly format a PivotTable report. You can also add or remove banding (alternating a darker and lighter background) of rows and columns. Banding can make it easier to read and scan data.

Apply styles

1. Click the PivotTable report.
2. On the Design tab, in the PivotTable Styles group, do the following:
• Click a visible style, scroll through the gallery, or to see all of the available styles, click the More button at the bottom of the scroll bar.
• Optionally, if you have displayed all of the available styles and you want to create your own custom PivotTable report style, click New PivotTable Style at the bottom of the gallery to display the New PivotTable Style dialog box.

 Note     Although you can delete only a custom PivotTable report style, you can remove any PivotTable report style so that it is no longer applied to the data.

Apply banding

1. Click the PivotTable report.
2. On the Design tab, in the PivotTable Style Options group, do one of the following:
• To alternate each row with a lighter and darker color format, click Banded Rows.
• To alternate each column with a lighter and darker color format, click Banded Columns.
• To include row headers in the banding style, click Row Headers.
• To include column headers in the banding style, click Column Headers.

 Top of Page

Conditionally format data in a PivotTable report

Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. Conditional formatting helps you answer specific questions about your data. There are important differences to understand when you use conditional formatting on a PivotTable report:

• If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.
• The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.

 Note    In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.

• There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

For more information, see Add, change, or clear conditional formats.

 Top of Page

Change the number format for a field

1. In the PivotTable report, select the field of interest.
2. On the Options tab in the Active Field group, click Field Settings.

The Field Settings dialog box displays labels and report filters; the Values Field Settings dialog box displays values.

3. Click Number Format at the bottom of the dialog box.

The Format Cells dialog box opens.

4. In the Category list, click the format category of interest.
5. Select the options that you prefer, and then click OK twice.

Tip  You can also right-click a value field and click Number Format.

 Top of Page

Include OLAP server formatting

If you are connected to a Microsoft SQL Server Analysis Services OLAP database, you can specify what OLAP server formats to retrieve and display with the data.

1. Click the PivotTable report.
2. On the Options tab, in the Data group, click Change Data Source, and then click Connection Properties.

The Connection Properties dialog box appears.

3. Click the Usage tab, and then under the OLAP Server Formatting section, do one of the following:
• Number Format Select or clear to enable or disable number formatting, such as currency, dates, and times.
• Font Style Select or clear this check box to enable or disable font styles, such as bold, italics, underline, and strikethrough.
• Fill Color Select or clear this check box to enable or disable fill colors.
• Text Color Select or clear this check box to enable or disable text colors.

 Top of Page

Preserve or discard formatting

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.

The PivotTable Options dialog box appears.

3. Click the Layout & Format tab, and then under the Format section, do one of the following:
• To save the PivotTable report layout and format so that it is used each time that you perform an operation on the PivotTable report, select the Preserve cell formatting on update check box.
• To discard the PivotTable report layout and format and resort to the default layout and format each time that you perform an operation on the PivotTable, clear the Preserve cell formatting on update check box.

 Note    While this option also affects the PivotChart report formatting, 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 specific data series are not preserved.

 Top of Page

Remove all formatting from a report

1. Click the PivotTable report.
2. On the Design tab, in the PivotTable Styles group, click the More button at the bottom of the scroll bar to see all of the available styles, and then click Clear at the bottom of the gallery.

 Top of Page

See Also

• Add, change, or clear conditional formats
• Change the summary function or custom calculation for a field in a PivotTable report
• Create and change the field layout in a PivotTable or PivotChart report
• Create, edit, or delete a PivotTable or PivotChart formula
• Overview of PivotTable reports and PivotChart reports
• Overview of values, custom calculations, and formulas in a PivotTable or PivotChart report

Excel > PivotTable reports and PivotChart reports > Layout and format

Group items in a PivotTable report

Excel 2007

In the row and column label areas of a PivotTable report, you can group the items in a field in a custom way. Grouping the data can help you to isolate a subset of data that satisfies your specific needs, and that cannot be easily grouped in other ways, such as sorting and filtering.

What do you want to do?

Learn about grouping items

Group numeric items

Group dates or times

Group selected items

Rename a default group name

Ungroup items

Learn about grouping items

To isolate a subset of items for more refined analysis of your data, you can group numeric, date, time, and even a selection of specific items. The following are common examples of grouping items:

Example of grouping by date items  

Before grouping, the orders for each salesperson are listed by date.

After grouping, the orders are grouped and summarized by month for easier analysis and display.

Example of grouping by numeric items  

Before grouping, the orders for each salesperson are listed by the order number.

After grouping, the orders are grouped and summarized by every 100 orders for easier analysis and display.

Example of grouping by selected items  

Before grouping, the salespersons in each country are listed.

After grouping, the salespersons are now grouped by the top two in sales and the bottom in sales in each country for easier analysis and display.

When you group items by selection, you create a new field based on the field whose items you are grouping. For example when you group the SalesPerson field by specific names, you create a new field SalesPerson1 that is added to the PivotTable Field List. You can work with this new field in many ways; for example, move it to different areas of the PivotTable report, rearrange it with other fields in an area, rename the field by using the Field Settings dialog box, and filter the field it is based on by using the new group name and values.

 Notes 

• You cannot group items for Online Analytical Processing (OLAP) 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.) that does not support the CREATE SESSION CUBE statement.
• You cannot add a calculated item to a grouped field. You must first ungroup the items, add the calculated item, and then regroup the items.
• You cannot use the Convert to Formulas command (On the Options tab, in the Tools group, click OLAP tools) when you have one or more grouped items. You must first remove the grouped items before using this command.

 Top of Page

Group numeric items

1. Select the numeric field in the PivotTable report that you want to group.
2. On the Options tab, in the Group group, click Group Field.
3. In the Starting at box, enter the first item to group.
4. In the Ending at box, enter the last item to group.
5. In the By box, type a number that represents the interval included in each group.

 Top of Page

Group dates or times

1. Select the date or time field in the PivotTable report that you want to group.
2. On the Options tab, in the Group group, click Group Field.
3. Enter the first date or time to group in the Starting at box, and enter the last date or time to group in the Ending at box.
4. In the By box, click one or more time periods for the groups.

To group items by weeks, click Days in the By box, make sure that Days is the only time period selected, and then click 7 in the Number of days box. You can then click additional time periods to group by, such as Month.

 Top of Page

Group selected items

1. Select two or more items in the PivotTable report that you want to group, either by clicking and dragging, or by holding down CTRL or SHIFT while you click.
2. On the Options tab, in the Group group, click Group Selection.

Tip  You might find it easier to either create another group for all the other items in the field or remove the field the group is based on so that you can create a more compact report.

 Note    For fields organized in levels, you can only group items that all have the same next-level item. For example, if the field has levels Country and City, you can't group cities from different countries.

 Top of Page

Rename a default group name

When you group items, Excel 2007 provides a default group name such as Group1 for selected items, or Apr for dates in the month of April. To rename the group to something more meaningful, do the following:

1. Select the group name.
2. Press F2.
3. Enter a new group name.

 Top of Page

Ungroup items

1. Select the group of items that you want to ungroup.
2. On the Options tab, in the Group group, click Ungroup.

For a numeric or date and time field, all groups for the field are ungrouped. For a group selection, only the selected items are ungrouped, and the group field is not removed from the PivotTable Field List until all groups for the field are removed.

 Top of Page

See Also

• Convert PivotTable cells to worksheet formulas
• Create or delete a PivotTable or PivotChart report
• Overview of PivotTable reports and PivotChart reports

Excel > PivotTable reports and PivotChart reports > Layout and format

Create and change the field layout in a PivotTable or PivotChart report

After you create a PivotTable or PivotChart report, use the PivotTable Field List to add fields. If you want to change a PivotTable or PivotChart report, use the Field List to rearrange and remove fields. By default, the PivotTable Field List displays two sections: a field section at the top for adding and removing fields, and a layout section at the bottom for rearranging and repositioning fields. You can dock the PivotTable Field List to either side of the window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally.

 Notes 

• If you don't see the PivotTable Field List, make sure that you click the PivotTable or PivotChart report. If you still don't see the PivotTable Field List, for a PivotTable report, on the Options tab, in the Show/Hide group, click Field List, and for a PivotChart report, on the Analyze tab, in the Data group, click Field List.
• If you don't see the fields in the Field List that you want to use, refresh the PivotTable or PivotChart report to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.

What do you want to do?

Learn about the PivotTable Field List

Add fields

Copy fields

Rearrange fields

Remove fields

Change the PivotTable Field List view

Filter data before adding it to the report

Switch between automatic or manual updating of the report layout

Learn about the PivotTable Field List

It's important to understand how the PivotTable field List works and the ways that you can arrange different types of fields so that you can achieve the results that you want when you lay out a PivotTable or PivotChart report.

How the PivotTable Field List works

An external data source contains structured data organized as one or more fields (also called columns) that are displayed in the Field List.

Move a field to the Report Filter area in the Field List, which simultaneously moves the field to the Report Filter area in the PivotTable report.

Move a field to the Column Label area in the Field List, which simultaneously moves the field to the Column Label area in the PivotTable report.

Move a field to the Row Label area in the Field List, which simultaneously moves the field to the Row Label area in the PivotTable report.

Move a field to the Values area in the Field List, which simultaneously moves the field to the Values area in the PivotTable report.

Guidelines for moving fields to the four report areas

The following are guidelines for moving Value, Name, and Dimension fields from the field section to the the four areas in the layout section.

Value fields  

• If you just select a check box for a numeric field, the default area it is moved to is the Values area.

Row and Column fields  

• If a field is already in a Row Labels, Column Labels, or Report Filters area, and it is added to one of those areas again, it changes position when it is moved to the same area, but changes orientation when it is moved to a different area.

Measures  

• In an Online Analytical Processing (OLAP) data source, there are often many fields (or measures) organized in a hierarchy of different dimensions, hierarchies, and levels. Click the expand and collapse buttons until you find the fields that you want.
• You can only move hierarchies, attributes, and named sets to the Row Labels, Column Labels, and Report Filters areas.
• You can only move measures, calculated measures, and Key Performance Indicators (KPIs) to the Values area.

 Top of Page

Add fields

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

 Top of Page

Copy fields

In a PivotTable report that is based on data in an Excel worksheet or external data from a non-OLAP data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.

1. Click and hold a field name in the field section, and then drag the field to the Values area in the layout section.
2. Repeat step 1 as many times as you want to copy the field.
3. In each copied field, change the summary function or custom calculation the way you want.

 Notes 

• When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down within the Values area. You can even move the Values Column label to the Column labels area or Row labels areas. However, you can’t move the Values Column label to the Report Filters area.
• You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
• Another way to add the same field to the Values area is by using a formula (also called a calculated column) that uses that same field in the formula.
• You cannot add the same field more than once in a PivotTable report based on an OLAP data source.

 Top of Page

Rearrange fields

You can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

• To rearrange fields, click the field name in one of the areas, and then select one of the following commands:

Tip  You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas.

 Top of Page

Remove fields

• To remove a field, click the field name in one of the layout areas, and then click Remove Field, or clear the check box next to each field name in the field section.

Tip  You can also click and hold a field name in the layout section, and then drag it outside the PivotTable Field List.

 Note    Clearing a check box in the field section removes all instances of the field from the report.

 Top of Page

Change the PivotTable Field List view

The PivotTable Field List has five different views that are designed and optimized for different types of PivotTable tasks.

• To change the view, click the View button at the top of the PivotTable Field List and select one of the following:

Tip  In the Fields Section and Areas Section Stackedand Fields Section and Areas Section Side-By-Side views, you can adjust the width and height of each section by hovering over the section divider until the pointer changes to a vertical double arrow or horizontal double arrow , by dragging the double arrow up or down or left or right to where you want it, and then either by clicking the double arrow or by pressing ENTER.

 Top of Page

Filter data before adding it to the report

If the PivotTable report is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report.

• Hover over the field name in the field section, and then click the filter drop-down arrow next to the field name.

The Filter menu is displayed. For more information, see Filter data in a PivotTable report or PivotChart report.

 Top of Page

Switch between automatic or manual updating of the report layout

By default, each time that you make a change in the PivotTable Field List, the report layout is automatically updated. To improve performance when you are accessing a large amount of external data, you can switch to manual updating. When you switch to manual updating, you cannot use the report until you switch back to automatic updating. However, you can quickly add, move, and remove fields from the field section to the layout section, and then switch back to automatic updating to see your results.

 Caution    If you set the report layout to manual updating, closing the PivotTable Field List, changing to Fields only view, or exiting Excel discards all layout changes that you have made to the PivotTable report without confirmation.

• To enable or disable manual updating of the PivotTable report, select or clear the Defer layout update check box at the bottom of the PivotTable Field List.

When you finish changing the report layout in the Field List, click Update to see the layout in the PivotTable report.

 Note    A PivotTable report starts with automatic updating each time that you open the workbook.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Layout and format

Display or hide items or levels in a PivotTable report

Excel 2007

To further refine the layout of a PivotTable report, you can display or hide items or levels in a row label or column label.

What do you want to do?

Display or hide items

Display or hide levels

Display or hide items

You can hide specific items in a row label or column label to help you eliminate unnecessary information from your PivotTable report. Hiding an item in a row label or column label removes the item from the report, but the item still appears in the drop-down list for the label.

1. Select one or more of the items that you want to display or hide.
2. Right-click the selection, point to Filter, and then click Hide Selected Items or Keep Only Selected Items.
3. If items are hidden and you want to display those items, right-click a related item, point to Filter, and then click Clear Filter.

 Top of Page

Display or hide levels

You can also hide levels that you don't want to view in your report. For example, you may want to hide a state/province level because the country/region that you are analyzing has no data for states or provinces, and you want to make the report more readable. You can hide levels between the highest and lowest levels of 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.) hierarchy. Any level of a hierarchy can be hidden at any time unless it is the only level available (a one-level hierarchy) or unless it is the only level that isn't hidden.

 Note    This procedure applies only to a PivotTable report that is connected to an OLAP data source.

1. Select a field in a dimension hierarchy for which you want to display or hide a level.
2. Right-click a field in a dimension hierarchy in the PivotTable report, point to Show/Hide Fields, and then click Show All Fields, Hide All Fields, or a specific level in the hierarchy.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Layout and format

Expand, collapse, or show details in a PivotTable report PivotChart report

In a PivotTable report or PivotChart report, you can expand or collapse to any level of data detail, and even for all levels of detail in one operation. You can also expand or collapse to a level of detail beyond the next level. For example, starting at a country/region level, you can expand to a city level which expands both the state/province and city level. This can be a time-saving operation when you work with many levels of detail. In addition, you can expand or collapse all members for each field in an OLAP data source.

You can also see the details that are used to aggregate the value in a value field, or see the details of a category in a PivotChart report.

What do you want to do?

Show the expand and collapse buttons in a PivotTable report

Expand or collapse levels of detail in a PivotTable report

Expand or collapse levels of detail in a PivotChart report

Display or hide detail for a value field in a PivotTable report

Show or hide the expand and collapse buttons in a PivotTable report

Although the expand and collapse buttons are turned on by default, you may have turned off the expand and collapse buttons because you printed a report. You must turn them on to be able to use these buttons to expand or collapse levels of detail in the report.

• To show or hide the expand and collapse buttons, on the Options tab in the Show/Hide group, click +/- Buttons.

 Top of Page

Expand or collapse levels of detail in a PivotTable report

1. On the item in the column label of a PivotTable report or series in a PivotChart report, do one of the following:
• Click the expand or collapse button.

Tip   You can also double-click the item.

• Right-click the item, select Expand/Collapse, and then do one of the following:
• To see the details for the current item, click Expand.
• To hide the details for the current item, click Collapse.
• To see the details for all items in field, click Expand Entire Field.
• To hide the details for the current item, click Collapse Entire Field.
• To see a level of detail beyond the next level, click Expand To "<Field name>".
• To hide to a level of detail beyond the next level, click Collapse To "<Field name>".

 Top of Page

Expand or collapse levels of detail in a PivotTable report

1. Right-click the category label or the data series (For example, the slice in a pie chart or ring in a doughnut chart) for which you want to show or hide level details.
2. On the shortcut menu, click the level you want to expand or collapse, and then click Show detail category name or Hide detail category name.

 Top of Page

Display or hide detail for a value field in a PivotTable report

1. Right-click a cell in the values area, and then select Show Details.

Tip  You can also double-click the field.

If prompted, double-click the field that has the detail that you want to show.

2. The detail data that the value field is based on is placed on a new worksheet.
3. To hide the detail data, delete the new worksheet.

 Notes 

• To enable or disable access to this detail in a non-OLAP data source, set or clear the Enable show details check box in the PivotTable Options dialog box. For more information, see PivotTable options.
• This setting is not available for an OLAP data source.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Layout and format

Display or hide ScreenTips and properties in a PivotTable report

Excel 2007

A ScreenTip provides contextual or supplementary information, such as member properties in 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, about a field in a PivotTable report.

What do you want to do?

Display or hide a contextual ScreenTip

Display or hide member properties in a ScreenTip

Display member properties as data in a PivotTable report

Display or hide a contextual ScreenTip

A contextual ScreenTip is summary information about the value, row, or column for a field. For example, if you move the pointer over a cell in the values area, a contextual ScreenTip displays the current value and the row and column location. This contextual ScreenTip is especially useful when you are working with a large report and do not want to change the layout or scroll through the report to find or verify this information.

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.
3. In PivotTable Options dialog box, click the Display tab and then, under Display, select the Show contextual tooltips check box.

 Top of Page

Display or hide member properties in a ScreenTip

If your PivotTable report is connected to a Microsoft SQL Server 2005 Analysis Services OLAP cube and the cube administrator has enabled the display of member property information, you can display the property values in a ScreenTip when you move the pointer over a field. For example, a product can contain additional property information, such as a product key, a dealer price, and a stock level. You can also control which specific property information is displayed in the ScreenTip.

 Note    This procedure requires a field to have properties defined and enabled on the OLAP cube. To find out if properties are available, right-click the field, and then point to Show properties in Report on the shortcut menu. If the shortcut menu displays (No Properties Defined), no properties are available to be displayed.

1. Make sure that member properties are displayed for the PivotTable report by doing the following:
1. On the Options tab, in the PivotTable group, click Options.
2. In PivotTable Options dialog box, click the Display tab and then, under Display, select the Show properties in tooltips check box.
2. Right-click a field, point to Show Properties in Tooltips on the shortcut menu, and then click Show All Properties, Hide All Properties, or an individual property caption.

 Top of Page

Display member properties as data in a PivotTable report

You can display member property information as data in the PivotTable report. For example, you may want to sort, filter, or print the property information.

When you filter member property data, the member property caption is displayed along with the field name. For example, if color is a member property of the Product field, you can filter products by the color silver.

 Note    This procedure requires that a field have properties defined and enabled on the OLAP cube. To find out whether properties are available, right-click the field, and then point to Show properties in Report on the shortcut menu. If the shortcut menu displays (No Properties Defined), no properties are available to be displayed.

1. Click the PivotTable report.
2. Make sure that member properties are displayed for the PivotTable report by doing the following:
1. On the Options tab, in the PivotTable group, click Options.
2. In PivotTable Options dialog box, click the Display tab and then, under Display, select the Show properties in tooltips check box.
3. Click the field in the dimension hierarchy for which you want to display property fields.

If the levels for which you selected property fields aren't displayed in the report, click the field and then click the Expand button .

4. On the Options tab, in the Tools group, click OLAP tools, and then click Property Fields.

The Choose Property Fields for Dimension dialog box appears.

5. In the Choose properties from level list, click each level for which you want to display property fields, and then double-click the property fields that you want to see.
6. In the Property fields to display box, use the and buttons to arrange the property fields in the order you want them to appear in the report.
7. Make sure that the Show fields for this dimension in outline form check box is selected, and then click OK.

 Note    In column fields, property fields are displayed only for items in the lowest level of detail displayed in the report. To display property fields for other levels, move the field to the row label area.

Tip  You can also right-click the label field, point to Show Properties in Report on the shortcut menu, and then click Show All Properties, Hide All Properties, or an individual property caption.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Layout and format

Use a report filter in a PivotTable report or PivotChart report

Excel 2007

Use a report filter to conveniently display a subset of data in a PivotTable report or PivotChart report. A report filter helps to manage the display of large amounts of data, and to focus on a subset of data in the report, such as a product line, a time span, or a geographic region.

What do you want to do?

Display a different set of values in a report based on a report filter item

Display each set of values in a report based on a report filter item on a separate worksheet

Arrange multiple report filters

Display a different set of values in a report based on a report filter item

1. Click the arrow in the cell with the item  currently displayed in the report filter displayed on the PivotTable report or PivotChart report.
2. Do one of the following:

Select a single item   Click the item that you want. In fields organized in levels, click beside a level to display the lower-level items, or click to display higher-level items until the item that you want appears in the list.

Select multiple items   Click the Select multiple items check box, and then click the items that you want.

Display the summary of all items   Click (All).

 Note    If your OLAP data source is Microsoft SQL Server Analysis Services version 2005, you can only select a calculated member if it is a single item, you cannot select multiple items when one or more of those items are calculated members.

 Top of Page

Display each set of values in a report based on a report filter item on a separate worksheet

You can display each report filter page, which is the set of values that match the selected report filter item, on a separate worksheet.

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click the arrow next to Options, and then click Show Report Filter Pages.
3. In the Show Report Filter Pages dialog box, select a report filter field, and then click OK.

 Top of Page

Arrange multiple report filters

If you have multiple report filters, you can arrange them on the PivotTable report or PivotChart report by doing the following:

1. Click the PivotTable report or the associated PivotTable report of a PivotChart report.
2. On the Options tab, in the PivotTable group, click Options.
3. In the PivotTable Options dialog box, click the Layout & Format tab.
4. Under Layout, in the Display fields in report filter area list box do one of the following:
• Select Down, Then Over to first display fields in the report filter area from the top to the bottom, as fields are added to it, before taking up another column.
• Select Over, Then Down to first display fields in the report filter area from left to right, as fields are added to it, before taking up another row.
5. In the Report filter fields per column or Report filter fields per row box, type or select the number of fields to display before taking up another column or row based on the setting of Display fields in report filter area.

 Top of Page

See Also

• Retrieve PivotTable data one report filter item at a time

Excel > PivotTable reports and PivotChart reports > Calculations and totals

Overview of values, custom calculations, and formulas in a PivotTable or PivotChart report

Excel 2007

PivotTable and PivotChart reports  provide ways to calculate data. Value fields use summary functions  to combine values from the underlying source data . You can also use custom calculations  to compare data values, or add your own formulas that use elements of the report or other worksheet data.

In this article

How PivotTable and PivotChart reports calculate data

How the type of source data affects calculations

Formula syntax

Using formulas in PivotChart reports

How PivotTable reports and PivotChart reports calculate data

There are three types of calculations that you can do in a PivotTable report or PivotChart report.

Values and functions

The data in the values area summarize the underlying source data in the PivotTable report as the following example shows. Values and calculations in a PivotChart report reflect those in the associated PivotTable report , and vice versa.

The following source data:

Produces the following PivotTable and PivotChart reports.

In the PivotTable report, the Month column field provides items March and April. The Region row field provides items North, South, East, and West. The value at the intersection of the April column and the North row is the total sales revenue from the records in the source data that have Month values of April and Region values of North.

In a PivotChart report, the Region field might be a category field that shows North, South, East, and West as categories. The Month field could be a series field that shows the items March, April, and May as series represented in the legend. A Values field called Sum of Sales could contain data markers  that represent the total revenue in each region for each month. For example, one data marker would represent, by its position on the value axis , the total sales for April in the North region.

Custom calculations

A custom calculation shows values based on other items or cells in the data area. For example, you could display values in the Sum of Sales data field as a percentage of March sales, or as a running total of the items in the Month field.

Formulas

If summary functions and custom calculations don't provide the results that you want, you can create your own formulas in calculated fields  and calculated items . For example, you could add a calculated item with the formula for the sales commission, which could be different for each region. The report would then automatically include the commission in the subtotals and grand totals.

 Top of Page

How the type of source data affects calculations

Calculations and options that are available in a report depend on whether the source data came from an OLAP  database or a non-OLAP source data.

OLAP source data

Calculations   For reports that are created from OLAP cubes , the summarized values are precalculated on the OLAP server before Microsoft Office Excel displays the results. Therefore, you cannot change how these values are calculated from within the report. You cannot change the summary function that is used to calculate data fields or subtotals, or add calculated fields  or calculated items . If the OLAP server provides calculated fields, known as calculated members, you will see these fields in the PivotTable Field List. You will also see any calculated fields and calculated items that are created by macros  that were written in Visual Basic for Applications  and stored in your workbook, but you won't be able to change these fields or items. If you need additional types of calculations, contact your OLAP database administrator.

Hidden items in totals   For OLAP source data, you can include or exclude the values for hidden items when calculating subtotals and grand totals.

Non-OLAP source data

Calculations   In reports based on other types of external data or on worksheet data, Excel uses the Sum summary function to calculate value fields that contain numeric data, and the Count summary function to calculate data fields that contain text. You can choose a different summary function, such as, Average, Max, or Min, to further analyze and customize your data. You can also create your own formulas that use elements of the report or other worksheet data by creating a calculated field or a calculated item within a field.

 Top of Page

Formula syntax

You can create formulas only in reports that are not based on OLAP  source data.

Formulas are available in PivotChart reports and use the same syntax as those in PivotTable reports. For best results when working in a PivotChart report, create and edit formulas in the associated PivotTable report , where you can see the individual values that make up your data, and then view the results in the PivotChart report.

Formula elements   In formulas that you create for calculated fields  and calculated items , you can use operators  and expressions as you do in other worksheet formulas. You can use constants  and refer to data from the report, but you cannot use cell references or defined names . You cannot use worksheet functions  that require cell references or defined names as arguments, and you cannot use array  functions.

Names in reports   Excel provides names to identify the elements of a report in your formulas. The names comprise field  and item  names. In the following example, the data in range C3:C9 is named Dairy.

In a PivotChart report, the field names are displayed in the PivotTable field list, and item names can be seen in each field drop-down list. Don't confuse these names with those you see in chart tips, which reflect series and data point names instead.

Examples   A calculated field named Forecast could forecast future orders with a formula such as the following:

=Sales * 1.2

A calculated item in the Type field that estimates sales for a new product based on Dairy sales could use a formula such as the following:

=Dairy * 115%

Formulas operate on sum totals, not individual records   Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula. For example, the formula =Sales * 1.2 multiplies the sum of the sales for each type and region by 1.2; it does not multiply each individual sale by 1.2 and then sum the multiplied amounts. Formulas for calculated items, however, operate on the individual records; the calculated item formula =Dairy *115% multiplies each individual sale of Dairy times 115%, after which the multiplied amounts are summarized together in the Values area.

Spaces, numbers, and symbols in names   In a name that includes more than one field, the fields can be in any order. In the example above, cells C6:D6 can be 'April North' or 'North April'. Use single quotation marks around names that are more than one word or that include numbers or symbols.

Totals   Formulas cannot refer to totals (such as, March Total, April Total, and Grand Total in the example).

Field names in item references   You can include the field name in a reference to an item. The item name must be in square brackets — for example, Region[North]. Use this format to avoid #NAME? errors when two items in two different fields in a report have the same name. For example, if a report has an item named Meat in the Type field and another item named Meat in the Category field, you can prevent #NAME? errors by referring to the items as Type[Meat] and Category[Meat].

Referring to items by position   You can refer to an item by its position in the report as currently sorted and displayed. Type[1] is Dairy, and Type[2] is Seafood. The item referred to in this way can change whenever the positions of items change or different items are displayed or hidden. Hidden items are not counted in this index.

You can use relative positions to refer to items. The positions are determined relative to the calculated item that contains the formula. If South is the current region, Region[-1] is North; if North is the current region, Region[+1] is South. For example, a calculated item could use the formula =Region[-1] * 3%. If the position that you give is before the first item or after the last item in the field, the formula results in a #REF! error.

 Top of Page

Using formulas in PivotChart reports

The methods and rules that are used for creating formulas in PivotChart reports are the same as the rules for PivotTable reports. When you create a calculated field  or calculated item  in a PivotChart report, the calculations are reflected in the associated PivotTable report , and vice versa. For best results, create formulas for a PivotChart report in the associated PivotTable report, where you can see the individual values that make up your data, and then view the results graphically in the PivotChart report.

For example, the following PivotChart report shows sales for each salesperson per region:

To see what sales would look like if they were increased by 10 percent, you could create a calculated field with the following formula:

=Sales * 110%

The result would be reflected in the chart like this:

To see a separate data marker  for sales in the North region minus a transportation cost of 8 percent, you could create a calculated item in the Region field with the following formula:

=North – (North * 8%)

The result would look like this:

A calculated item that is created in the Salesperson field, however, would appear as a series represented in the legend and appear in the chart as a data point in each category.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Calculations and totals

Change the summary function or custom calculation for a field in a PivotTable report

Excel 2007

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. However, you can change the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.). Optionally, you can also create a custom calculation (custom calculation: A method of summarizing values in the data area of a PivotTable report by using the values in other cells in the data area. Use the Show data as list on the PivotTable Field dialog for a data field to create custom calculations.).

1. Select a field in the Values area for which you want to change the summary function of the PivotTable report.
2. On the Options tab, in the Active Field group, click Active Field, and then click Field Settings.

The Value Field Settings dialog box is displayed.

The Source Name is the name of the field in the data source.

The Custom Name displays the current name in the PivotTable report, or the source name if there is no custom name. To change the Custom Name, click the text in the box and edit the name.

3. Click the Summarize by tab.
4. In the Summarize value field by box, click the summary function that you want to use.

Summary functions you can use

 Note    For some 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.), such as OLAP data and for 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.) and fields with 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.), you can't change the summary function.

5. Optionally, you can use a custom calculation by doing the following:
1. Click the Show values as tab.
2. Click the calculation that you want in the Show values as box.

Custom calculations that you can use

3. Select a Base field and Base item, if these options are available for the calculation that you chose.

Base field should not be the same field that you chose in step 1.

6. To change the way that numbers are formatted, click Number Format, and in the Number tab of the Format Cells dialog box, select a number format, and then click OK.
7. If the report has multiple value fields, repeat the previous steps for each one that you want to change.

 Note    To use more than one summary function for the same field, add the field again from the PivotTable Field List, and then repeat the steps by choosing the other function that you want.

Excel > PivotTable reports and PivotChart reports > Calculations and totals

Create, edit, or delete a PivotTable or PivotChart formula

Excel 2007

If summary functions and custom calculations don't provide the results that you want, you can create your own formulas in calculated fields  and calculated items . For example, you could add a calculated item with the formula for the sales commission, which could be different for each region. The PivotTable report would then automatically include the commission in the subtotals and grand totals.

 Notes 

• You cannot create formulas in a PivotTable or PivotChart report that is connected to an OLAP  source data.
• For best results in a PivotChart report , work in the associated PivotTable report  where you can see the individual data values that your formula calculates.

What do you want to do?

Create a formula

Display a list of formulas

Edit a formula

Delete a formula

Create a formula

1. Decide whether you want a calculated field  or a calculated item  within a field.
• Use a calculated field when you want to use the data from another field in your formula.
• Use a calculated item when you want your formula to use data from one or more specific items  within a field.
2. Do one of the following.

Add a calculated field  

• Click the PivotTable report.
• On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
• In the Name box, type a name for the field.
• In the Formula box, enter the formula for the field.

To use the data from another field in the formula, click the field in the Fields box, and then click Insert Field. For example, to calculate a 15% commission on each value in the Sales field, you could enter = Sales * 15%.

• Click Add.

Add a calculated item to a field  

• If items in the field are grouped, on the Options tab, in the Group group, click Ungroup.
• Click the field where you want to add the calculated item.
• On the Options tab, in the Tools group, click Formulas, and then click Calculated Item