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.
• In the Name box, type a name for the calculated item.
• In the Formula box, enter the formula for the item.

To use the data from an item in the formula, click the item in the Items list, and then click Insert Item (the item must be from the same field as the calculated item).

• Click Add.
• If you ungrouped items in step 1, regroup them if you want. For more information, see Group items in a PivotTable or PivotChart field
3. For calculated items, you can enter different formulas cell by cell.

For example, if a calculated item named OrangeCounty has a formula of =Oranges * .25 across all months, you can change the formula to =Oranges *.5 for June, July, and August.

Do the following:

• Click a cell for which you want to change the formula.

To change the formula for several cells, hold down CTRL and click the additional cells.

• In the formula bar , type the changes to the formula.
4. If you have multiple calculated items or formulas, adjust the order of calculation by doing the following:
• Click the PivotTable report.
• On the Options tab, in the Tools group, click Formulas, and then click Solve Order.
• Click a formula, and then click Move Up or Move Down.
• Continue until the formulas are in the order that you want them to be calculated.

 Top of Page

Display a list of formulas

To display a list of all the formulas used in the current PivotTable report, do the following:

1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click List Formulas.

 Top of Page

Edit a formula

1. Determine whether the formula is in a calculated field  or a calculated item . If the formula is in a calculated item, determine whether the formula is the only one for the calculated item by doing the following:
1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click List Formulas.
3. In the list of formulas, find the formula that you want to change listed under Calculated Field or Calculated Item.

When there are multiple formulas for a calculated item, the default formula that was entered when the item  was created has the calculated item name in column B. For additional formulas for a calculated item, column B contains both the calculated item name and the names of intersecting items.

For example, you might have a default formula for a calculated item named MyItem, and another formula for this item identified as MyItem January Sales. In the PivotTable report, you would find this formula in the Sales cell for the MyItem row and January column.

2. Do one of the following:

Edit a calculated field formula  

1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
3. In the Name box, select the calculated field for which you want to change the formula.
4. In the Formula box, edit the formula.
5. Click Modify.

Edit a single formula for a calculated item  

6. Click the field that contains the calculated item.
7. On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
8. In the Name box, select the calculated item.
9. In the Formula box, edit the formula.
10. Click Modify.

Edit individual formulas for specific cells of a calculated item  

For example, if a calculated item named OrangeCalc has a formula of =Oranges * .25 across all months, you can change the formula to =Oranges *.5 for June, July, and August.

11. Click a cell for which you want to change the formula.

To change the formula for several cells, hold down CTRL and click the additional cells.

12. In the formula bar , type the changes to the formula.
3. If you have multiple calculated items or formulas, adjust the order of calculation by doing the following:
1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click Solve Order.
3. Click a formula, and then click Move Up or Move Down.
4. Continue until the formulas are in the order that you want them to be calculated.

 Top of Page

Delete a formula

Tip  If you don't want to delete a formula permanently, you can hide the field or item. To hide a field, drag it out of the report.

1. Determine whether the formula is in a calculated field  or a calculated item .

Calculated fields appear in the PivotTable Field List. Calculated items appear as items  within other fields.

2. Do one of the following:

Delete a calculated field  

1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
3. In the Name box, select the field that you want to delete.
4. Click Delete.

Delete a calculated item  

5. Click the field with the item that you want to delete.
6. On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
7. In the Name box, select the item that you want to delete.
8. Click Delete.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Calculations and totals

Subtotal and total fields in a PivotTable report

Excel 2007

When working with a PivotTable report, you can display or hide subtotals for individual column and row fields, display or hide column and row grand totals for the entire report, and calculate the subtotals and grand totals with or without filtered items.

What do you want to do?

Subtotal column and row fields

Display or hide column and row grand totals for the entire report

Calculate the subtotals and grand totals with or without filtered items

Subtotal row and column fields

1. Select an item of a row or column field in a PivotTable report.
2. On the Options tab, in the Active Field group, click Field Settings.

The Field Settings dialog box is displayed.

3. Do one of the following:

Subtotal an outer row or column label  

1. To subtotal by the default summary function, under Subtotals, click Automatic.
2. Optionally, to use a different function or to display more than one type of subtotal, click Custom, and then select a function.

Functions that you can use as a subtotal

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

3. For outer row labels in compact or outline form, you can display subtotals above or below their items, or hide the subtotals, by doing the following:
1. On the Design tab, in the Layout group, click Subtotals.
2. Do one of the following:
• Select Do Not Show Subtotals.
• Select Show all Subtotals at Bottom of Group.
• Select Show all Subtotals at Top of Group.

Subtotal an inner row or column label  

4. To choose a function, under Subtotals, click Custom, if this option is available, and then select a function.

Functions that you can use as a subtotal

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

Remove subtotals  

5. Click None under Subtotals.

 Note    If a field contains a calculated item , you can't change the subtotal summary function.

4. Select or clear the Include new items in manual filter check box to include or exclude new items when applying a filter in which you have selected specific items in the Filter menu.

Tip  To quickly display or hide the current subtotal, right-click the item of the field, and then select Subtotal "<Label name>".

 Top of Page

Display or hide grand totals for the entire report

You can display or hide the totals for the current PivotTable report.

Display or hide grand totals  

1. Click the PivotTable report.
2. On the Design tab, in the Layout group, click Grand Totals, and then select one of the following:
• Off for Rows and Columns
• On for Rows and Columns
• Off for Rows Only
• On for Columns Only

Set the default behavior for displaying or hiding grand totals  

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

The PivotTable Options dialog box is displayed.

3. Click the Totals & Filters tab.
4. Do one of the following:

Display grand totals  

• Select the Show grand totals for columns check box, the Show grand totals for rows check box, or both.

Hide grand totals  

• Clear the Show grand totals for columns check box, the Show grand totals for rows check box, or both.

 Top of Page

Calculate the subtotals and grand totals with or without filtered items

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

The PivotTable Options dialog box is displayed.

3. Click the Total & Filters tab, and then do one of the following:

OLAP source data   Do one of the following:

• Select or clear the Subtotal filtered page items check box to include or exclude report filter items.

 Note    The OLAP data source must support the MDX expression subselect syntax.

• Select or clear the Mark totals with * check box to display or hide an asterisk next to totals. The asterisk indicates that the visible values that are displayed and that are used when Excel calculates the total are not the only values that are used in the calculation.

 Note    This option is only available if the OLAP data source does not support the MDX expression subselect syntax.

non-OLAP source data   Select or clear the Allow multiple filters per field check box to include or exclude filtered items in totals.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Calculations and totals

Summary functions available in PivotTable reports

Excel 2007

Summary functions are used in automatic subtotals and PivotTable (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.). In PivotTable, the following summary functions are available for all 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.) except 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.).

Excel > PivotTable reports and PivotChart reports > Calculations and totals

Custom calculations for PivotTable data fields

Excel 2007

The following functions are available for custom calculations (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.) in value fields.

Excel > PivotTable reports and PivotChart reports > OLAP

Overview of Online Analytical Processing (OLAP)

Excel 2007

Online Analytical Processing (OLAP) is a technology that is used to organize large business databases and support business intelligence. OLAP databases are divided into one or more cubes, and each cube is organized and designed by a cube administrator to fit the way that you retrieve and analyze data so that it is easier to create and use the PivotTable reports and PivotChart reports that you need.

In this article

What is business intelligence?

What is Online Analytical Processing (OLAP)?

OLAP features in Excel

Software components that you need to access OLAP data sources

Feature differences between OLAP and non-OLAP source data

What is business intelligence?

A business analyst often wants to get a big picture of the business, to see broader trends based on aggregated data, and to see these trends broken down by any number of variables. Business intelligence is the process of extracting data from an OLAP database and then analyzing that data for information that you can use to make informed business decisions and take action. For example, OLAP and business intelligence help answer the following types of questions about business data:

• How do the total sales of all products for 2007 compare with the total sales from 2006?
• How does our profitability to date compare with the same time period during the past five years?
• How much money did customers over the age of 35 spend last year, and how has that behavior changed over time?
• How many products were sold in two specific country/regions this month as opposed to the same month last year?
• For each customer age group, what is the breakdown of profitability (both margin percentage and total) by product category?
• Find top and bottom salespeople, distributors, vendors, clients, partners, or customers.

 Top of Page

What is Online Analytical Processing (OLAP)?

Online Analytical Processing (OLAP) databases facilitate business-intelligence queries. OLAP is a database technology that has been optimized for querying and reporting, instead of processing transactions. The source data for OLAP is Online Transactional Processing (OLTP) databases that are commonly stored in data warehouses. OLAP data is derived from this historical data, and aggregated into structures that permit sophisticated analysis. OLAP data is also organized hierarchically and stored in cubes instead of tables. It is a sophisticated technology that uses multidimensional structures to provide rapid access to data for analysis. This organization makes it easy for a PivotTable report or PivotChart report to display high-level summaries, such as sales totals across an entire country or region, and also display the details for sites where sales are particularly strong or weak.

OLAP databases are designed to speed up the retrieval of data. Because the OLAP server, rather than Microsoft Office Excel, computes the summarized values, less data needs to be sent to Excel when you create or change a report. This approach enables you to work with much larger amounts of source data than you could if the data were organized in a traditional database, where Excel retrieves all of the individual records and then calculates the summarized values.

OLAP databases contain two basic types of data: measures, which are numeric data, the quantities and averages that you use to make informed business decisions, and dimensions, which are the categories that you use to organize these measures. OLAP databases help organize data by many levels of detail, using the same categories that you are familiar with to analyze the data.

The following illustration shows how a cube is typically organized and what are the key components of a cube that you work with in a PivotTable report and PivotChart report.

A cube

The cube has three dimensions: Product, Geography, and Fiscal Year

These two dimensions are divided into hierarchies

Members are items in a dimension

A measure is the basic "building block" of a cube

The following sections describe each of these components in more detail:

Cube   A data structure that aggregates the measures by the levels and hierarchies of each of the dimensions that you want to analyze. Cubes combine several dimensions, such as time, geography, and product lines, with summarized data, such as sales or inventory figures. Cubes are not "cubes" in the strictly mathematical sense because they do not necessarily have equal sides. However, they are an apt metaphor for a complex concept.

Measure   A set of values in a cube that are based on a column in the cube's fact table and that are usually numeric values. Measures are the central values in the cube that are preprocessed, aggregated, and analyzed. Common examples include sales, profits, revenues, and costs.

Member    An item in a hierarchy representing one or more occurrences of data. A member can be either unique or nonunique. For example, 2007 and 2008 represent unique members in the year level of a time dimension, whereas January represents nonunique members in the month level because there can be more than one January in the time dimension if it contains data for more than one year.

Calculated member   A member of a dimension whose value is calculated at run time by using an expression. Calculated member values may be derived from other members' values. For example, a calculated member, Profit, can be determined by subtracting the value of the member, Costs, from the value of the member, Sales.

Dimension   A set of one or more organized hierarchies of levels in a cube that a user understands and uses as the base for data analysis. For example, a geography dimension might include levels for Country/Region, State/Province, and City. Or, a time dimension might include a hierarchy with levels for year, quarter, month, and day. In a PivotTable report or PivotChart report, each hierarchy becomes a set of fields that you can expand and collapse to reveal lower or higher levels.

Hierarchy   A logical tree structure that organizes the members of a dimension such that each member has one parent member and zero or more child members. A child is a member in the next lower level in a hierarchy that is directly related to the current member. For example, in a Time hierarchy containing the levels Quarter, Month, and Day, January is a child of Qtr1. A parent is a member in the next higher level in a hierarchy that is directly related to the current member. The parent value is usually a consolidation of the values of all of its children. For example, in a Time hierarchy that contains the levels Quarter, Month, and Day, Qtr1 is the parent of January.

Level   Within a hierarchy, data can be organized into lower and higher levels of detail, such as Year, Quarter, Month, and Day levels in a Time hierarchy.

 Top of Page

OLAP features in Excel

Retrieving OLAP data   You can connect to OLAP data sources (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) just as you do to other external data sources. You can work with databases that are created with Microsoft SQL Server OLAP Services version 7.0, Microsoft SQL Server Analysis Services version 2000, and Microsoft SQL Server Analysis Services version 2005, the Microsoft OLAP server products. Excel can also work with third-party OLAP products that are compatible with OLE-DB for OLAP.

You can display OLAP data only as a PivotTable report or PivotChart report or in a worksheet function converted from a PivotTable report, but not as an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.). You can save OLAP PivotTable reports and PivotChart reports in report templates (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the template.), and you can create Office Data Connection (ODC) files (.odc) to connect to OLAP databases for OLAP queries (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.). When you open an ODC file, Excel displays a blank PivotTable report, which is ready for you to lay out.

Creating cube files for offline use   You can create an offline cube file (.cub) with a subset of the data from an OLAP server database. Use offline cube files to work with OLAP data when you are not connected to your network. A cube enables you to work with larger amounts of data in a PivotTable report or PivotChart report than you could otherwise, and speeds retrieval of the data. You can create cube files only if you use an OLAP provider (OLAP provider: A set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database.), such as Microsoft SQL Analysis Services Server version 2005, which supports this feature.

Server Actions   A server action is an optional but useful feature that an OLAP cube administrator can define on a server that uses a cube member or measure as a parameter into a query to obtain details in the cube, or to start another application, such as a browser. Excel supports URL, Report, Rowset, Drill Through, and Expand to Detail server actions, but it does not support Proprietary, Statement, and Dataset. For more information, see Perform an OLAP server action in a PivotTable report.

KPIs    A KPI is a special calculated measure that is defined on the server that allows you to track "key performance indicators" including status (Does the current value meet a specific number?) and trend (what is the value over time?). When these are displayed, the Server can send related icons that are similar to the new Excel icon set to indicate above or below status levels (such as a Stop light icon) or whether a value is trending up or down (such as a directional arrow icon).

Server Formatting   Cube administrators can create measures and calculated members with color formatting, font formatting, and conditional formatting rules, that may be designated as a corporate standard business rule. For example, a server format for profit might be a number format of currency, a cell color of green if the value is greater than or equal to 30,000 and red if the value is less than 30,000, and a font style of bold if the value is less than 30,000 and regular if greater than or equal to 30,000. For more information, see Design the layout and format of a PivotTable report.

Office display language   A cube administrator can define translations for data and errors on the server for users who need to see PivotTable information in another language. This feature is defined as a file connection property and the user's computer country/regional setting must correspond to the display language.

 Top of Page

Software components that you need to access OLAP data sources

An OLAP provider   To set up OLAP data sources (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) for Excel, you need one of the following OLAP providers (OLAP provider: A set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database.):

• Microsoft OLAP provider   Excel includes the data source driver (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different driver.) and client software that you need to access databases created with Microsoft SQL Server OLAP Services version 7.0, Microsoft SQL Server OLAP Services version 2000 (8.0), and Microsoft SQL Server Analysis Services version 2005 (9.0).
• Third-party OLAP providers   For other OLAP products, you need to install additional drivers and client software. To use the Excel features for working with OLAP data, the third-party product must conform to the OLE-DB for OLAP standard and be Microsoft Office compatible. For information about installing and using a third-party OLAP provider, consult your system administrator or the vendor for your OLAP product.

Server databases and cube files   The Excel OLAP client software supports connections to two types of OLAP databases. If a database on an OLAP server is available on your network, you can retrieve source data from it directly. If you have 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 OLAP data or a cube definition (cube definition: Information, stored by the OLAP Cube Wizard in an .oqy file, that defines how to construct an OLAP cube in memory using data retrieved from a relational database.) file, you can connect to that file and retrieve source data from it.

Data sources   A data source gives you access to all of the data in the OLAP database or offline cube file. After you create an OLAP data source, you can base reports on it, and return the OLAP data to Excel in the form of a PivotTable report or PivotChart report, or in a worksheet function converted from a PivotTable report.

Microsoft Query   You can use Query to retrieve data from an external database such as Microsoft SQL or Microsoft Access. You do not need to use Query to retrieve data from an OLAP PivotTable that is connected to a cube file. For more information, see Use Microsoft Query to retrieve external data.

 Top of Page

Feature differences between OLAP and non-OLAP source data

If you work with PivotTable reports and PivotChart reports from both 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.) and other types of source data, you will notice some feature differences.

Data retrieval   An OLAP server returns new data to Excel every time that you change the layout of the report. With other types of external source data, you query for all the source data at once, or you can set options to query only when you display different report filter field items. You also have several other options for 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.

In reports based on OLAP source data, the report filter field settings are unavailable, background query is unavailable, and the optimize memory setting is not available.

 Note    The optimize memory setting is also not available for OLEDB data sources and for PivotTable reports baed on a cell range.

Field types  OLAP source data, dimension (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) fields can be used only as row (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) (series), column (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) (category), or page fields. Measure fields can be used only as value fields. For other types of source data, all fields can be used in any part of a report.

Access to detail data   For OLAP source data, the server determines what levels of detail are available and calculates summary values, so the detail records that make up summary values may not be available. The server may, however, provide property fields (property fields: Independent attributes associated with items, or members, in an OLAP cube. For example, if city items have size and population properties stored in the server cube, a PivotTable report can display the size and population of each city.) that you can display. Other types of source data don't have property fields, but you can display the underlying detail for data field values and for items, and you can show items with no data.

OLAP report filter fields may not have an All item, and the Show Report Filter Pages command is unavailable.

Initial sort order   For OLAP source data, items first appear in the order in which the OLAP server returns them. You can then sort or manually rearrange the items. For other types of source data, the items in a new report first appear sorted in ascending order by item name.

Calculations   OLAP servers provide summarized values directly for a report, so you cannot change the summary functions (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.) for value fields. For other types of source data, you can change the summary function for a value field and use multiple summary functions for the same value field. You cannot 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 reports with OLAP source data.

Subtotals   In reports with OLAP source data, you cannot change the summary function for subtotals. With other types of source data, you can change subtotal summary functions and show or hide subtotals for all row and column fields.

For OLAP source data, you can include or exclude hidden items when you calculate subtotals and grand totals. For other types of source data, you can include hidden report filter field items in subtotals, but hidden items in other fields are excluded by default.

 Top of Page

See Also

• Get Help about SQL Server 2005 Analysis Services

Excel > PivotTable reports and PivotChart reports > OLAP

Convert PivotTable cells to worksheet formulas

A PivotTable report has several layouts that provide a predefined structure to the report, but you cannot customize these layouts. If you need more flexibility in designing the layout of a PivotTable report, you can convert the cells to worksheet formulas, and then change the layout of these cells by taking full advantage of all of the features available in a worksheet. You can either convert the cells to formulas that use Cube functions or use the GETPIVOTDATA function. Converting cells to formulas greatly simplifies the process of creating, updating, and maintaining these customized PivotTable reports.

When you convert cells to formulas, these formulas access the same data as the PivotTable report and can be refreshed to see up-to-date results. However, with the possible exception of report filters, you no longer have access to the interactive features of a PivotTable report, such as filtering, sorting, or expanding and collapsing levels.

 Note     When you convert an Online Analytical Processing (OLAP) PivotTable report, you can continue to refresh the data to get up-to-date measure values, but you cannot update the actual members that are displayed in the report.

What do you want to do?

Learn about common scenarios for converting PivotTable reports to worksheet formulas

Convert cells to formulas that use Cube functions

Convert cells to formulas that use the GETPIVOTDATA function

Learn about common scenarios for converting PivotTable reports to worksheet formulas

The following are typical examples of what you can do after you convert PivotTable cells to worksheet formulas to customize the layout of the converted cells.

Rearrange and delete cells  

Let's say that you have a periodic report that you need to create each month for your staff. You only need a subset of the report information and you prefer to lay out the data in a customized way. You can just move and arrange cells in a design layout that you want, delete the cells that are not necessary for the monthly staff report, and then format the cells and worksheet to suit your preference.

Insert rows and columns  

Let's say that you want to show sales information for the previous two years broken down by region and product group, and that you want to insert extended commentary in additional rows. Just insert a row and enter the text. In addition, you want to add a column that shows sales by region and product group that is not in the original PivotTable report. Just insert a column, add a formula to get the results that you want, and then fill the column down to get the results for each row.

Use multiple data sources  

Let's say that you want to compare results between a production and test database to ensure that the test database is producing expected results. You can easily copy cell formulas and then change the connection argument to point to the test database to compare these two results.

Use cell references to vary user input  

Let's say that you want the entire report to change based on user input. You could change arguments to the Cube formulas to cell references on the worksheet, and then enter different values in those cells to derive different results.

Create a nonuniform row or column layout (also called asymmetric reporting)  

Let's say that you need to create a report that contains a 2008 column called Actual Sales, a 2009 column called Projected Sales, but you don't want any other columns. You can create a report that contains just those columns, unlike a PivotTable report, which requires symmetric reporting.

Create your own Cube formulas and MDX expressions  

Let's say that you want to create a report that shows sales for a particular product by three specific salespeople for the month of July. If you are knowledgeable about MDX expressions and OLAP queries, you can enter the Cube formulas yourself. Although these formulas can become quite elaborate, you can simplify the creation and improve the accuracy of these formulas by using Formula AutoComplete. For more information, see Use Formula AutoComplete.

 Top of Page

Convert cells to formulas that use Cube functions

 Note    You can only convert an Online Analytical Processing (OLAP) PivotTable report by using this procedure.

1. To save the PivotTable report for future use, we recommend that you make a copy of the workbook before you convert the PivotTable by using the Save As command on the Microsoft Office Button . For more information, see Save a file.
2. Prepare the PivotTable report so that you can minimize the rearrangement of the cells after conversion by doing the following:
• Change to a layout that most closely resembles the layout that you want.
• Interact with the report, such as filtering, sorting, and redesigning the report, to get the results that you want.
3. Click the PivotTable report.
4. On the Options tab, in the Tools group, click OLAP tools, and then click Convert to Formulas.

If there are no report filters, then the conversion operation completes. If there are one or more report filters, then the Convert to Formulas dialog box is displayed.

5. Decide how you want to convert the PivotTable report:

Convert the entire PivotTable report  

• Select the Convert Report Filters check box.

This converts all cells to worksheet formulas and deletes the entire PivotTable report.

Convert only the PivotTable row labels, column labels, and values area, but keep the Report Filters  

• Make sure that the Convert Report Filters check box is clear. (This is the default.)

This converts all row label, column label, and values area cells to worksheet formulas, and keeps the original PivotTable report, but with only the report filters so that you can continue to filter by using the report filters.

 Note     If the PivotTable format is version 2000-2003 or earlier, you can only convert the entire PivotTable report.

6. Click Convert.

The conversion operation first refreshes the PivotTable report to ensure that up-to-date data is used.

A message is displayed in the status bar while the conversion operation takes place. If the operation takes a long time and you prefer to convert at another time, press ESC to cancel the operation.

 Notes 

• You cannot convert cells with filters applied to levels that are hidden.
• You cannot convert cells in which fields have a custom calculation that were created through the Show Values As tab of the Values Field Settings dialog box. (On the Options tab, in the Active Field group, click Active Field, and then click Values Field Settings.)
• For cells that are converted, cell formatting is preserved, but PivotTable styles are removed because these styles can apply to PivotTables only.

 Top of Page

Convert cells by using the GETPIVOTDATA function

You can use the GETPIVOTDATA function in a formula to convert PivotTable report cells to worksheet formulas when you want to work with non-OLAP data sources, when you prefer not to upgrade to the new PivotTable version 2007 format right away, or when you want to avoid the complexity of using the Cube functions.

1. Make sure that the Generate GETPIVOTDATA command in the PivotTable group on the Options tab is turned on.

 Note    The Generate GETPIVOTDATA command sets or clears the Use GETPIVOTTABLE functions for PivotTable references option in Formulas category of the Working with Formulas section in the Excel Options dialog box.

2. In the PivotTable report, make sure the cell that you want to use in each formula is visible.
3. In a worksheet cell outside the PivotTable report, type the formula that you want up to the point where you want to include data from the report.
4. Click the cell in the PivotTable report that you want to use in your formula in the PivotTable report. A GETPIVOTDATA worksheet function is added to your formula that retrieves the data from the PivotTable report. This function continues to retrieve the correct data if the report layout changes or if you refresh the data.
5. Finish typing your formula and press ENTER.

 Note    If you remove any of the cells referenced in the GETPIVOTDATA formula from the report, the formula returns #REF!.

 Top of Page

See Also

• Cube functions
• Get Help about SQL Server 2005 Analysis Services
• GETPIVOTDATA function

Excel > PivotTable reports and PivotChart reports > OLAP

Perform an OLAP server action in a PivotTable report

If your PivotTable report is connected to a Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP) database, you can perform a server action by using Microsoft Office Excel. A server action is an auxiliary command that performs related tasks on the data in your report.

What do you want to do?

Learn more about server actions

Perform a server action

Learn more about server actions

A server action is an optional but useful feature that an OLAP cube administrator can define on a server that uses a cube member or measure as a parameter into a query to obtain details in the cube, or to start another application, such as a browser.

The following are common examples of using server actions in a PivotTable report:

• You are a sales representative who wants to prepare for an important customer visit. You use a server action to display a customer profile in a Web page directly from a standard PivotTable report of your sales area. The Web page contains links to additional information, such as maps and recommended restaurants.
• You are a business analyst for a hospital supplier and notice that a valued customer has reduced its orders by 35% in the last year. You want to find out why, so you use a server action to view the customer's order history on a separate worksheet.
• You are a district sales manager who wants to probe a product sales figure and see which customers have contributed most to those sales by "drilling through" to the details contained in a fact table on the cube, so you can plan sales targets and goals for the next fiscal quarter.

There are five types of server actions.

URL   This commonly-used server action displays a Uniform Resource Locator (URL) in a browser. For example, you can click a customer name and then display a customer profile on a Web page.

An OLAP cube administrator can define a URL server action on dimension members, hierarchies, hierarchy members, attribute members, measure cells, or the entire cube.

Report   This server action creates a Microsoft SQL Server Reporting Services report by building a report-based URL that takes the following syntax form:

http://<reporting server name>/<server directory>

An OLAP cube administrator can define a Report server action on dimension members, hierarchies, hierarchy members, attribute members, measures, or the entire cube.

Rowset   This action returns a rowset based on a condition specified in an MDX statement. This action is useful for linking a rowset to data in a different but related cube in the same OLAP database, such as store sales from several years ago. The data is displayed in a worksheet as an Excel table.

An OLAP cube administrator can define a Rowset server action on dimension members, hierarchies, hierarchy members, attribute members, measures, or the entire cube.

Drill Through   This action queries the cube based on a condition specified in an MDX statement and returns data from the fact table that is the source of the measure's aggregated values. The data is displayed in a worksheet as an Excel table. The MDX statement specifies which columns should be returned from the fact table and the maximum number of records to return, specified in the Maximum number of records to retrieve property under the OLAP Drill Through section on the Usage tab of the Connection Properties dialog box. For more information, see Connection properties.

An OLAP cube administrator can define a Drill Through server action only on measures.

Show Details   This action queries the cube based on a condition specified in an MDX statement and returns data from the fact table that is the source of the measure's aggregated values. This action is available by default but can be controlled by the Enable show details check box under the PivotTable Data section on the Data tab of the PivotTable Options dialog box. For more information, see PivotTable options.

An OLAP cube administrator can define a Show Details server action only on measures.

 Notes 

• The following server actions are not supported in Office Excel: Proprietary, Statement, and Dataset.
• Although an OLAP cube administrator can define a server action on a set, a server action on a set is not supported by Office Excel.
• Server Actions are not supported on a report filter.

 Top of Page

Perform a server action

• In a PivotTable report, right click a column or row label, click Additional Actions, and then select the command defined by the OLAP cube administrator.

For example:

• URL  A URL server action command, such as Customer Profile.
• Report   A Report server action command, such as Q1Summary.
• RowSet   A RowSet server action command, such as Product Details.
• Drill-Through   A Drill-through server action command, such as Sales Numbers.
• Show Details  

Tip  You can also double-click the cell that contains the measure.

If there are no server actions defined by the OLAP cube administrator, you see the menu entry (No actions defined).

 Top of Page

See Also

• Get Help about SQL Server 2005 Analysis Services

Excel > PivotTable reports and PivotChart reports > OLAP

Get Help about SQL Server 2005 Analysis Services

Excel 2007

When you use a workbook connected to a Microsoft SQL Server 2005 Analysis Services database, you may need additional information to answer specific product questions, such as reference information about multidimensional expressions (MDX), or configuration procedures 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.) server.

For more information about SQL Server 2005 Analysis Services, you can do the following:

• Download SQL Server 2005 Books Online from the Microsoft TechNet site.
• Read SQL Server 2005 Books Online in the SQL Server Developer Center on the Microsoft Developer Network (MSDN) site.

Excel > PivotTable reports and PivotChart reports > OLAP

Connect to (import) an OLAP database

Excel 2007

You can use an Office Data Connection (.odc) file to connect to an Online Analytical Processing (OLAP) database.

1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

The Data Connection Wizard is displayed. This wizard has three screens.

Screen 1: Connect to Database Server  

2. In step 1, type the name of the OLAP server in the Server name text box.

 Note    To specify 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.), type the complete file path, file name, and extension. For more information about creating offline cube files, see Create an offline cube file from an OLAP server database.

3. In step 2, under Log on credentials, do one of the following:
• To use your current Windows user name and password, click Use Windows Authentication.
• To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password text boxes.

Security  

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

Screen 2: Select Database and Table  

4. Under Select the database that contains the data you want, select a database.
5. To connect to a specific cube  in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

Screen 3: Save Data File and Connection  

6. Optionally, in the Filename text box, revise the default file name. Click Browse to change the default file location of My Data Sources, or check for existing file names.
7. Optionally, type a description of the file, a friendly name, and common search words in the Description, Friendly Name, and Search Keywords text boxes.
8. To ensure that the connection file is always used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data. This check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.
9. To specify how a PivotTable is accessed if the workbook is saved to Excel Services and is opened by using Excel Services, click Authentication Settings, and select one of the following options to log on to the data source:
• Windows Authentication  Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can impact performance when there are many users.
• SSO  Select this option to use Single Sign On, and then enter the appropriate identification string in the SSO ID text box. A site administrator can configure a SharePoint site to use a Single Sign On database where a user user name and password can be stored. This method can be the most efficient when there are many users.
• None  Select this option to save the username and password in the connection file.

Security  Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

10.  Note    The authentication setting is only used by Excel Services, and not by Microsoft Office Excel. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication setting in Excel is the same.
11. Click OK.
12. Click Finish to close the Data Connection Wizard.

The Import Data dialog box is displayed.

13. Under Select how you want to view this data in your workbook do one of the following:
• To create just a PivotTable report, click PivotTable Report.
• To create a PivotTable report and PivotChart report, click PivotChart and PivotTable Report.
• To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.
14. Under Where do you want to put the data?, do one of the following:
• To place the PivotTable or PivotChart report 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 .

15. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.
16. Optionally, you can change connection properties by clicking Properties, by making your changes in the Connection Properties dialog box, and then by clicking OK. For more information, see Connection properties.

See Also

• Get Help about SQL Server 2005 Analysis Services

Excel > PivotTable reports and PivotChart reports > OLAP

SQL Server 2005 Data Mining Add-ins for the 2007 Office release

The Microsoft SQL Server 2005 Data Mining Add-ins for the 2007 Microsoft Office system help you derive patterns and trends that exist in complex data, visualize those patterns in charts and interactive viewers, and generate rich, colorful summaries for presentation and for business analytics. You can use these add-ins to utilize the predictive analytics of SQL Server 2005 in Microsoft Office Excel 2007 and Microsoft Office Visio 2007.

This package includes the following two add-ins for Office Excel 2007 (Table Analysis Tools and Data Mining Client) and one add-in for Office Visio 2007 (Data Mining Templates):

Table Analysis Tools for Excel  This add-in provides easy-to-use tasks that leverage SQL Server 2005 Data Mining to perform powerful analytics on your worksheet data.

Data Mining Client for Excel  This add-in enables advanced users to go through the full development life cycle for the data mining model within Office Excel 2007 by using either worksheet data or external data from SQL Server 2005 Analysis Services.

Data Mining Templates for Visio  This add-in enables you to render and share your data mining models as the following annotatable Office Visio 2007 drawings:

• Decision Tree diagrams based on Microsoft Decision Trees, Microsoft Linear Regression, and Microsoft Logistical Regression algorithms
• Dependency Network diagrams based on the Microsoft Naive Bayes, Microsoft Decision Trees, and Microsoft Association Rules algorithms
• Cluster diagrams based on the Microsoft Clustering algorithm

To download the add-ins, see Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007.

For additional information about the add-ins, including training, see Product Information for Microsoft SQL Server Data Mining Add-ins for Office 2007.

See Also

• Get Help about SQL Server 2005 Analysis Services

Excel > PivotTable reports and PivotChart reports > OLAP

Work with offline cube files

Excel 2007

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.) (.cub) stores data in the form 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.) cube. This data may represent a portion of an OLAP database from an OLAP server or it may have been created independently of any OLAP database. Use an offline cube file to continue to work with PivotTable (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.) and PivotChart reports (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.) when the server is unavailable or when you are disconnected from the network.

Security   Be careful using or distributing an offline cube file that contains sensitive or private information. Instead of a cube file, consider keeping the data in the workbook so that you can use Rights Management to control access to the data.

For more information, see Information Rights Management in the 2007 Microsoft Office system.

 Note    The creation and usage of offline cube files from Microsoft SQL Server Analysis Services is based on the Microsoft OLAP OLE DB provider and subject to the term and licensing of your Microsoft SQL Server installation.

For more information, see SQL Server 2005 Licensing Frequently Asked Questions.

What do you want to do?

Learn more about offline cubes

Create an offline cube file from an OLAP server database

Reconnect an offline cube file to an OLAP server database

Refresh and recreate an offline cube file

Include different data in an offline cube file

Delete an offline cube file

Learn more about offline cubes

When you work with a PivotTable (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or PivotChart (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.) report that is based on source data from an OLAP server, you can use the Offline Cube Wizard to copy the source data to a separate offline cube file on your computer. To create these offline files, you must have the OLAP data provider that supports this capability, MSOLAP from Microsoft SQL Server Analysis Services, installed on your computer.

Using the Offline Cube Wizard

To create the offline cube file, you use the Offline Cube Wizard to select a subset of the data in the OLAP database and then save that subset. Your report doesn't have to include every field that you include in the file, and you can select from any of the dimensions (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) and data fields that are available in the OLAP database. To keep the size of your file to a minimum, you can include only the data that you want to be able to display in the report. You can omit entire dimensions and, for most types of dimensions, you can also omit lower-level detail and top-level items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) that you don't need to display. For any items that you include, the property fields (property fields: Independent attributes associated with items, or members, in an OLAP cube. For example, if city items have size and population properties stored in the server cube, a PivotTable report can display the size and population of each city.) that are available in the database for those items are also saved in your offline file.

Taking data offline and then bringing the data back online

To do this, you first create a PivotTable or PivotChart report that is based on the server database, and you then create the offline cube file from the report. You can then switch the report between the server database and the offline file whenever you want; for example, when you use a portable computer to take work home or on a trip, and later reconnect the computer to your network.

The following procedure provides the basic steps for taking data offline and then bringing the data back online.

1. Create or open a PivotTable (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) that is based on the 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 that you want to access offline.
2. Create 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.) on your computer. See the section Create an offline cube file from an OLAP server database.
3. Disconnect from your network and work with the offline cube file.
4. Reconnect to your network and reconnect the offiline cube file. See the section Reconnect an offline cube file to an OLAP server database.
5. Refresh the offline cube file with new data and then recreate the offline cube file. See the section Refresh and recreate an offline cube file.
6. Repeat this procedure, beginning with step 3.

 Top of Page

Create an offline cube file from an OLAP server database

 Note    If your OLAP database is large and you want the cube file to provide access to a large subset of the data, you will need to provide ample disk space, and you will find that saving the file may prove to be time consuming. To improve performance, consider creating the offline cube file by using an MDX script.

1. Click the PivotTable report for which you want to create an offline cube file — you can also click 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.
2. On the Options tab, in the Tools group, click OLAP tools, and then click Offline OLAP.

The Offline OLAP settings dialog box is displayed.

 Note    If your OLAP provider doesn't support offline cube files, the Offline OLAP command is unavailable. Contact the vendor for your OLAP provider for more information.

3. Click Create offline data file or, if an offline cube file already exists for the report, click Edit offline data file.

The Offline Cube Wizard is displayed.

4. In step 1 of the wizard, click Next.
5. In step 2 of the wizard, select each dimension (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) from your server cube that has data that you want to include in the offline cube file. Click the box next to each such dimension, and select the levels (level: A part of an OLAP dimension. Within a dimension, data is organized into lower and higher levels of detail, such as Year, Quarter, Month, and Day levels in a Time dimension.) that you want to include.

 Notes 

• You cannot skip intermediate levels within a dimension.
• To reduce the size of the cube file, omit lower levels that you don't need to view in the report.
• Be sure to include any dimensions where you have grouped items, so that Microsoft Office Excel can maintain these groupings when you switch between the server database and the offline file.
• Dimensions that do not have a box don't allow you to exclude levels. You can only include or exclude all of this type of dimension.
6. In step 3 of the wizard, click the box next to Measures, and select the fields that you want to use as data fields (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.) in the report. You must select at least one measure; otherwise the dimensions associated with the measure will contain no data. For each dimension listed under Measures, click the box next to the dimension, and then select the top-level items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) to include in the offline cube file.
• To limit the size of the cube file so that you don't run out of disk space and to reduce the amount of time required to save the file, select only the items that you need to view in the report. Any property fields (property fields: Independent attributes associated with items, or members, in an OLAP cube. For example, if city items have size and population properties stored in the server cube, a PivotTable report can display the size and population of each city.) that are available for the items that you select are automatically included in the cube.
• If items that you want to include are missing, you may not have included the dimension that contains them in the previous step. Click Back in the wizard and select the missing dimension in step 2, and then return to step 3.

 Note    In the OLAP Cube Wizard, the only summary functions available for data fields are Sum, Count, Min, and Max.

7. In step 4 of the wizard, enter a name and location for the .cub file, and then click Finish.

To cancel saving the file, click Stop in the Create Cube File – Progress dialog box.

8. After Excel has finished creating the offline cube file, click OK in the Offline OLAP Settings dialog box.

Issue: My computer ran out of disk space while saving a cube.

• Eliminate dimensions  In step 2 of the Offline Cube Wizard, select only the dimensions that are actually displayed as fields in your PivotTable or PivotChart report.
• Eliminate levels of detail   Click the box next to each selected dimension in step 2 of the wizard, and then clear the check boxes for levels lower than those displayed in your report.
• Eliminate data fields  In step 3 of the wizard, click the box next to Measures, and then select only the data fields (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.) you're using in the report.
• Eliminate items of data  Click the box next to each dimension in step 3, and then clear the check boxes for items that you don't need to see in the report.

 Top of Page

Reconnect an offline cube file to an OLAP server database

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.) or 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 (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.).
2. On the Options tab, in the Tools group, click OLAP tools, and then click Offline OLAP.

3. Click On-line OLAP, and then click OK.
4. If you are prompted to locate the data source, click Browse to find source, and then locate the OLAP server on your network.

 Top of Page

Refresh and recreate an offline cube file

Refreshing 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.), which re-creates it by using the most recent data from the server cube or new offline cube file, can be a time consuming process and require a lot of temporary disk space. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

1. Click the PivotTable report that is based on the offline cube file.
2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.

Issue: New data doesn't appear in my report when I refresh. (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.)

 Top of Page

Include different data in an offline cube file

Saving a revised offline cube file can be a time consuming process, and you cannot do other work in Excel while the file is being saved. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

1. Make sure that you are connected to your network and that you can access the original OLAP server database that supplied the data for the offline cube file.
2. Click a PivotTable report that is based on the offline cube file, or click the associated PivotTable report for a PivotChart report.
3. On the Options tab, in the Tools group, click OLAP tools, and then click Offline OLAP.
4. Click Offline OLAP, and then click Edit offline data file.
5. Follow the steps in the Offline Cube Wizard to select different data for the file. In the last step, specify the same name and location as the existing file that you are changing.

 Note    To cancel saving the file, click Stop in the Create Cube File – Progress dialog box.

 Top of Page

Delete an offline cube file

Warning  If you delete the offline cube file for a report, you can no longer use the report offline or create a new offline cube file for the report.

1. Close all workbooks containing reports that use the offline cube file, or make sure all such reports have been deleted.
2. In Windows, locate and delete the offline cube file (.cub).

 Top of Page

See Also

• Get Help about SQL Server 2005 Analysis Services

Excel > PivotTable reports and PivotChart reports > OLAP

Issue: Cannot Convert PivotTable cells to worksheet formulas

Symptoms

Microsoft Office Excel cannot convert the PivotTable to formulas.

Cause

One or more filtered fields are not completely visible. To convert a cell to a formula, all items that are used in the formula must be visible.

Resolution

To make the filtered fields completely visible, do one or more of the following:

• Expand the entire field for all fields in levels above the filtered fields.
• If grand totals are displayed, display the subtotals as well.

Then, try to convert the PivotTable cells to worksheet formulas again.

For more information, see Display or hide items in a PivotTable or PivotChart field and Subtotal and total row and column labels in a PivotTable report.

See Also

• Get Help about SQL Server 2005 Analysis Services

Excel > PivotTable reports and PivotChart reports > Filtering, sorting, and conditionally formatting data

Filter data in a PivotTable report or PivotChart report

Excel 2007

You filter data to quickly and easily find and work with a subset of data in a PivotTable report or PivotChart report.

What do you want to do?

Learn about filtering

Filter labels or text items within labels

Filter numbers in the values area

Filter dates or times

Filter for top or bottom numbers

Filter by selection

Remove filters

Learn about filtering

Filtered data displays only the subset of data that meet the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) that you specify and hides data that you do not want displayed. Unlike filtering a cell range or table, you do not need to reapply a filter. Filters are automatically reapplied every time the PivotTable is refreshed or updated.

In the PivotTable or PivotChart report, filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data. In a subset of data, you can create up to three types of filters at the same time: manual, label or date, and value, and they are evaluated in that order. You can control this behavior by selecting or clearing the Allow multiple filters per field check box in the Totals & Filters tab of the PivotTable Options dialog box. For more information, see PivotTable options.

 Notes 

• You can control whether the filter button is available by setting or clearing the Display field captions and filter drop downs check box in the Display tab of the PivotTable Options dialog box, or by clicking Field Headers in the Show/Hide group on the Options tab. For more information, see PivotTable options.
• You can also filter data in the PivotTable Field List. For more information, see Design the layout and format of a PivotTable report.
• You cannot filter by color, font color, or icon set in a PivotTable report or PivotChart report.
• You cannot filter by label, date or time, value, or top or bottom numbers if the PivotTable data source is an OLAP database that does not support the MDX expression subselect syntax.
• You can only do manual filtering in a report filter.

 Top of Page

Filter labels or text items within labels

Labels

1. In the PivotTable report or PivotChart report, click the arrow in the column area (series field) or row area (category field).

Tip  To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Do one of the following:

Select from a list of text values (manual filtering)  

• In the list of text values, select or clear one or more text values to filter by.

If the list is large, clear (Select All) at the top, and then select the specific text values to filter by.

Tip  To make the Filter menu wider or longer, click and drag the grip handle at the bottom.

Filter by a text value  

• Point to Label Filters and then click one of the comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) commands.

For example, to filter by text that begins with a specific character, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.

• In the Label Filter <Field name> dialog box, in the box on the right, enter text.

For example, to filter by text that begins with the letter "J", enter J , or to filter by text that has "bell" anywhere in the text, enter bell.

In a non-OLAP data source, if you need to find text that shares some characters but not others, use a wildcard character.

How to use wildcard characters

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Label Filter.
2. In the Label Filter <Field name> dialog box, do the following:
1. In the box on the left, click one of the comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) commands.

For example, to filter by text that begins with a specific character, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.

2. In the box on the right, enter text.

For example, to filter by text that begins with the letter "J", enter J, or to filter by text that has "bell" anywhere in the text, enter bell.

In a non-OLAP data source, if you need to find text that shares some characters but not others, use a wildcard character.

How to use wildcard characters

 Top of Page

Filter numbers in the values area

Labels

1. In the PivotTable or PivotChart report, click the arrow in the column label (series field) or row label (category field).

Tip  To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Do one of the following:

Select from a list of numbers (manual filtering)  

• In the list of numbers, select or clear one or more numbers to filter by.

If the list is large, clear (Select All) at the top, and then select the specific numbers to filter by.

Tip  To make the Filter menu wider or longer, click and drag the grip handle at the bottom.

Filter by a number value  

• Point to Value Filters and then click one of the comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) commands.

For example, to filter by a lower and upper number limit, select Between.

• In the Value Filter <Field Name> dialog box, in the box or boxes on the right, enter numbers.

For example, to filter by a lower number of 25 and an upper number of 50, enter 25 and 50.

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Value Filter.
2. In the Value Filter <Field name> dialog box, do the following:
1. In the box on the left, select a field from the drop-down list.
2. In the box on the middle, click one of the comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) commands.

For example, to filter by a lower and upper number limit, select Between.

3. Iin the box or boxes on the right, enter numbers.

For example, to filter by a lower number of 25 and an upper number of 50, enter 25 and 50.

 Top of Page

Filter dates or times

 Note    For an OLAP data source, date filters require the OLAP cube field hierarchy data type of time. If a date is entered as text in a text field, then the date filter is not visible.

Labels

1. In the PivotTable or PivotChart report, click the arrow in the column area (series field) or row area (category field).

Tip  To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Do one of the following:

Select from a list of dates or times (manual filtering)  

• In the list of dates or times, select or clear one or more dates or times to filter by.

If the list of values is large, clear (Select All) at the top, and then select the values to filter by.

Tip  To make the Filter menu wider or longer, click and drag the grip handle at the bottom.

Filter by a date or time value  

• Point to Date Filters and then do one of the following:

Common filter  

 Note    A common filter is one based on a comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.).

1. Click one of the comparison operator commands (Equals, Before, After, or Between), or click Custom Filter.
2. In the Date Filter dialog box, in the box on the left, select a comparison operator.

For example, to filter by a lower and upper date or time, select is between.

3. In the Date Filter dialog box, in the box or boxes on the right, enter a date or time, or click the Calendar button to find and enter a date.

For example, to filter by an earlier date of "3/1/2006" and a later date of "6/1/2006", enter 3/1/2006 and 6/1/2006. Or, to filter by an earlier time of "8:00 AM" and a later time of "12:00 PM", enter 8:00 AM and 12:00 PM.

Dynamic filter  

 Note    A dynamic filter is one where the criteria can change when you reapply the filter.

4. Click one of the predefined date commands.

For example, to filter all dates by the current date, select Today, or to filter by the following month, select Next Month.

5. Click OK.

 Notes 

• The commands under the All Dates in the Period menu, such as January or Quarter 2, filter by the period no matter what the year. This can be useful, for example, to compare sales by a period across several years.
• This Year and Year to Date are different in the way that future dates are handled. This Year can return dates in the future for the current year, whereas Year to Date only returns dates up to and including the current date.

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Date Filters.
2. In the Date Filter <Field name> dialog box, do the following:
1. In the box on the left, select a comparison operator.

For example, to filter by a lower and upper date or time, select is between.

2. In the box or boxes on the right, enter a date or time, or click the Calendar button to find and enter a date.

For example, to filter by an earlier date of "3/1/2006" and a later date of "6/1/2006", enter 3/1/2006 and 6/1/2006. Or, to filter by an earlier time of "8:00 AM" and a later time of "12:00 PM", enter 8:00 AM and 12:00 PM.

 Top of Page

Filter for top or bottom numbers

Labels

1. In the PivotTable or PivotChart report, click the arrow in the column label (series field) or row label (category field).

Tip  To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Point to Value Filters and then select Top 10.
3. In the Top 10 Filter <Field Name> dialog box, do the following.
1. In the box on the left, click Top or Bottom.
2. In the box on the middle left, enter a number.
3. In the box on the middle right, do one of the following:
• To filter by number of items, click Items.
• To filter by percentage, click Percent.
• To filter by sum, click Sum.
4. In the box on the right, select a field from the drop-down list.

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Top 10.
2. In the Top 10 <Field name> dialog box, do the following:
1. In the box on the left, click Top or Bottom.
2. In the box on the middle left, enter a number.
3. In the box on the middle right, do one of the following:
• To filter by number of items, click Items.
• To filter by percentage, click Percent.
4. In the box on the right, select a field from the drop-down list.

 Top of Page

Filter by selection

You can quickly filter data with criteria that is equal to the contents of the active field.

1. In a PivotTable report or PivotChart report, select one or more items in the field that you want to filter by selection.
2. Right-click an item in the selection.
3. Click Filter, and then click Keep Only Selected Items or Hide Selected Items.

 Top of Page

Remove filters

1. Do one of the following:
• To remove all filtering on the Options tab, in the Actions group (PivotTable report), or on the Analyze tab, in the Data group (PivotChart report), click Clear, and then click Clear Filters.
• To remove filtering for a specific field, click the arrow in the row or column label, and then click Clear Filter from <Field Name>.

Tip  To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

 Top of Page

Excel > PivotTable reports and PivotChart reports > Filtering, sorting, and conditionally formatting data

Sort data in a PivotTable report or a PivotChart report

Excel 2007

Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical order or compile a list of product inventory levels from highest to lowest. Sorting data enables you to quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

 Note    To find the top or bottom values in a set of data, such as top 10 grades or bottom 5 sales amounts, use the Top 10 Filter dialog box or conditional formatting. For more information, see Filter data in a PivotTable or PivotChart report and Add, change, or clear conditional formats.

What do you want to do?

Learn more about sorting

Sort data in column or row labels

Sort data in the values area

Learn more about sorting

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest). When sorting text, in some cases, data might have leading spaces inserted before data. For best results, remove the leading spaces before you sort text data. Unlike sorting a cell range or Excel table, you cannot do a case-sensitive sort of text, or sort by format, whether by cell color, font color, or icon set.

Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows Help system.

 Note    In a field that is organized in levels, you can sort all of the items for a lower level together by collapsing the upper levels before you sort. For more information, see Expand or collapse details in a PivotTable or PivotChart report.

 Top of Page

Sort data in column or row labels

1. Select a column or row field in a PivotTable report or PivotChart report.

In a PivotTable report, click a column or row label.

In a PivotChart report, click the field 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.). To sort items in a series field, click the corresponding column label. To sort items in a category field, click the corresponding row label.

2. On the Options tab, in the Sort group, do one of the following:

Text  

• To sort in ascending alphanumeric order, click Sort A to Z.
• To sort in descending alphanumeric order, click Sort Z to A.

Numbers  

• To sort in ascending alphanumeric order, click Sort Smallest to Largest.
• To sort in descending alphanumeric order, click Sort Largest to Smallest.

Dates and times  

• To sort in ascending alphanumeric order, click Sort Oldest to Newest.
• To sort in descending alphanumeric order, click Sort Newest to Oldest.
3. Optionally, to customize the sort operation, on the Options tab, in the Sort group, click Sort.

In the Sort <Field name> dialog box, select the type of sort that you want by doing one of the following:

• To return items to their original order, click Data source order. This option is only available for OLAP source data.
• To drag and arrange items the way that you want, click Manual.
• To select a field to sort by in ascending sort order, select Ascending (A to Z) by, and then select the field from the drop-down list.
• To select a field to sort by in descending sort order, select Descending (A to Z) by, and then select the field from the drop-down list.

Tip  Read the Summary section at the bottom of the dialog box to verify your choices.

4. Optionally, to continue customizing the sort operation, click More Options.

In the More Sort Options dialog box, do one or more of the following:

• To enable or disable the sort operation each time that the PivotTable report is updated, under the AutoSort section, select or clear the Sort automatically every time the report is updated check box.
• To sort in a user-defined sort order by using a custom list, under the First key sort order section, select the custom list from the drop-down list. This option is only available if you have not selected the check box under the AutoSort section.

Microsoft Office Excel provides built-in, day-of-the-week, and month-of-the year custom lists, and you can also create your own custom list. For more information, see Sort data in a range or table.

 Notes 

• To enable or disable custom lists, set or clear the Use Custom Lists when sorting check box on the Filters & Totals tab in the PivotTable Options dialog box. Clearing this check box may also improve performance when you sort large amounts of data. For more information, see PivotTable options.
• The custom list sort order is not retained if 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 report.
• To change the type of sort, do one of the following:
• To sort by grand total values, click Grand Total.
• To change the row or column, select Values in selected row or Values in selected column, and then enter a reference to a cell in the row or column with the values to sort by in the box.

Alternatively, to select a row or column, click Collapse Dialog to temporarily hide the dialog box, select the row or column, and then press Expand Dialog .

Tip  Read the Summary section at the bottom of the dialog box to verify your choices.

 Top of Page

Sort data in the values area

1. Select a value field in a PivotTable report.
2. On the Options tab, in the Sort group, do one of the following:
• To sort in ascending alphanumeric order, click Sort Smallest to Largest.
• To sort in descending alphanumeric order, click Sort Largest to Smallest.
3. Optionally, to customize the sort operation, on the Options tab, in the Sort group, click Sort.

In the Sort By Value dialog box, do one or more of the following:

• Under Sort options, do one of the following:
• To sort numbers in ascending sort order, select Smallest to Largest.
• To sort numbers in descending sort order, select Largest to Smallest.
• Under Sort direction, do one of the following:
• To sort values vertically, select Top to bottom.
• To sort values horizontally, select Left to right.

Tip  Read the Summary section at the bottom of the dialog box to verify your choices.

 Top of Page

Excel > Filtering, sorting, and conditionally formatting data

Add, change, or clear conditional formats

Excel 2007

Tags  cell color; change color; color; conditional; conditional formatting; format a cell; formatting; highlight; pivot

What are tags?

Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.

What do you want to do?

Learn more about conditional formatting

Format all cells by using a two-color scale

Format all cells by using a three-color scale

Format all cells by using data bars

Format all cells by using an icon set

Format only cells that contain text, number, or date or time values

Format only top or bottom ranked values

Format only values that are above or below average

Format only unique or duplicate values

Use a formula to determine which cells to format

Clear conditional formats

Learn more about conditional formatting

Conditional formatting helps you answer specific questions about your data. You can apply conditional formatting to a cell range, an Excel table, or a PivotTable report. There are important differences to understand when you use conditional formatting on a PivotTable report.

The benefits of conditional formatting

Whenever you analyze data, you often ask yourself questions, such as:

• Where are the exceptions in a summary of profits over the past five years?
• What are the trends in a marketing opinion poll over the past two years?
• Who has sold more than $50,000 dollars this month?
• What is the overall age distribution of employees?
• Which products have greater than 10% revenue increases from year to year?
• Who are the highest performing and lowest performing students in the freshman class?

Conditional formatting helps to answer these questions by making it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. A conditional format changes the appearance of a cell range based on a condition (or criteria). If the condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell range is not formatted based on that condition.

 Note    When you create a conditional format, you can only reference other cells on the same worksheet; you cannot reference cells on other worksheets in the same workbook, or use external references to another workbook.

Conditional formatting for a PivotTable report

Conditional formatting in a PivotTable report is different than a cell range or an Excel table in several ways:

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

The default method of scoping fields in the Values area is by selection. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to options button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box. The three methods of scoping give you greater flexibility depending on your needs:

Scoping by selection  Use this method if you want to select:

• A contiguous set of fields in the Values area, such as all of the product totals for one region.
• A discontiguous set of fields in the Values area, such as product totals for different regions across levels in the data hierarchy.

Scoping by value field  Use this method if you want to:

• Avoid making many discontiguous selections.
• Conditionally format a set of fields in the Values area for all levels in the hierarchy of data.
• Include subtotals and grand totals.

Scoping by corresponding field  Use this method if you want to:

• Avoid making many discontiguous selections.
• Conditionally format a set of fields in the Values area for one level in the hierarchy of data.
• Exclude subtotals.

When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by default. However, when you scope by corresponding field, instead of by using all visible values, you can optionally apply the conditional format for each combination of:

• A column and its parent row field.
• A row and its parent column field.

 Top of Page

Format all cells by using a two-color scale

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and red color scale, you can specify that higher value cells have a more green color and lower value cells have a more red color.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

3. Select a two-color scale.

Tip  Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and the bottom color represents lower values.

Tip  You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select 2-Color Scale.
7. Select a Minimum and Maximum Type. Do one of the following:
• Format lowest and highest values   Select Lowest Value and Highest Value.

In this case, you do not enter a Minimum and Maximum Value.

• Format a number, date, or time value   Select Number, and then enter a Minimum and Maximum Value.
• Format a percentage   Select Percent, and then enter a Minimum and Maximum Value.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

• Format a percentile   Select Percentile and then enter a Minimum and Maximum Value.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Use a percentile when you want to visualize a group of high values (such as the top 20thpercentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

• Format a formula result   Select Formula, and then enter a Minimum and Maximum Value.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

8.  Notes 
• Minimum and Maximum values are the minimum and maximum values for the range of cells. Make sure that the Minimum value is less than the Maximum value.
• You can choose a different Minimum and Maximum Type. For example, you can choose a Minimum Number and Maximum Percent.
9. To choose a Minimum and Maximum color scale, click Color for each, and then select a color.

If you want to choose additional colors or create a custom color, click More Colors.

The color scale that you select is displayed in the Preview box.

 Top of Page

Format all cells by using a three-color scale

Color scales are visual guides that help you understand data distribution and variation. A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

3. Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.

Tip  Hover over the color scale icons to see which icon is a three-color scale.

Tip  You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select 3-Color Scale.
7. Select a Minimum, Midpoint, and Maximum Type. Do one of the following:
• Format lowest and highest values   Select a Midpoint.

In this case, you do not enter a Lowest and Highest Value.

• Format a number, date, or time value   Select Number, and then enter a Minimum, Midpoint, and Maximum Value.
• Format a percentage   Select Percent, and then enter a Minimum, Midpoint, and Maximum Value.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

• Format a percentile   Select Percentile and then enter a Minimum, Midpoint, and Maximum Value.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

• Format a formula result   Select Formula, and then enter a Minimum, Midpoint, and Maximum Value.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

8.  Notes 
• Minimum, Midpoint, and Maximum values are the minimum, midpoint, and maximum values for the range of cells. Make sure that the Minimum value is less than the Midpoint value, which in turn, is less than the Maximum value.
• You can choose a different Minimum, Midpoint, and Maximum Type. For example, you can choose a Minimum Number, Midpoint Percentile, and Maximum Percent.
• In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique requirements.
9. To choose a Minimum, Midpoint, and Maximum color scale, click Color for each, and then select a color.

If you want to choose additional colors or create a custom color, click More Colors.

The color scale that you select is displayed in the Preview box.

 Top of Page

Format all cells by using data bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Data Bars, and then select a data bar icon.

Tip  You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select Data Bar.
7. Select a Shortest Bar and Longest Bar Type. Do one of the following:
• Format lowest and highest values   Select Lowest Value and Highest Value.

In this case, you do not enter a Shortest Bar and Longest Bar Value.

• Format a number, date, or time value   Select Number, and then enter a Shortest Bar and Longest Bar Value.
• Format a percentage   Select Percent, and then enter a Shortest Bar and Longest Bar Value.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

• Format a percentile  Select Percentile and then enter a Shortest Bar and Longest Bar Value.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one data bar proportion and low values (such as the bottom 20th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.

• Format a formula result   Select Formula, and then enter a Shortest Bar and Longest Bar Value.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

8.  Notes 
• Make sure that the Shortest Bar value is less than the Longest Bar value.
• You can choose a different Shortest Bar and Longest Bar Type. For example, you can choose a Shortest Bar Number and Longest Bar Percent.
9. To choose a Shortest Bar and Longest Bar color scale, click Bar Color.

If you want to choose additional colors or create a custom color, click More Colors.

The bar color that you select is displayed in the Preview box.

10. To show only the data bar and not the value in the cell, select Show Bar Only.

 Top of Page

Format all cells by using an icon set

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Icon Set, and then select an icon set.

Tip  You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select Icon Set.
• Select an icon set. The default is 3 Traffic Lights (Unrimmed). The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set.
• If you want, you can adjust the comparison operators and threshold values. The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom.
• Do one of the following:
1. Format a number, date, or time value   Select Number.
2. Format a percentage   Select Percent.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

3. Format a percentile  Select Percentile.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one data bar proportion and low values (such as the bottom 20th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.

4. Format a formula result   Select Formula, and then enter a formula in each Value box.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

• To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order.
• To show only the icon and not the value in the cell, select Show Icon Only.

 Notes 

• You may need to adjust the column width to accommodate the icon.
• There are three sizes of icons. The size of the icon that is displayed depends on the font size that is used in that cell.

 Top of Page

Format only cells that contain text, number, or date or time values

To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to "SouthEast".

 Note    You cannot conditionally format fields in the Values area of a PivotTable report by text or date, only by number.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.

3. Select the command that you want, such as Between, Equal To Text that Contains, or A Date Occurring.
4. Enter the values that you want to use, and then select a format.

Tip  You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
5. Under Select a Rule Type, click Format only cells that contain.
6. Under Edit the Rule Description, in the Format only cells with list box, do one of the following:
• Format by number, date, or time   Select Cell Value, select a comparison operator, and then enter a number, date, or time.

For example, select Between and then enter 100 and 200, or select Equal to and then enter 1/1/2006.

You can also enter a formula that returns a number, date, or time value. If you enter a formula, start it with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

• Format by text   Select Specific Text, select a comparison operator, and then enter text.

For example, select Contains and then enter Silver, or select Starting with and then enter Tri.

Quotes are included in the search string, and you may use wildcard characters. The maximum length of a string is 255 characters.

You can also enter a formula that returns text. If you enter a formula, start it with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

• Format by date   Select Dates Occurring, and then select a date comparison.

For example, select Yesterday or Next week.

• Format cells with blanks or no blanks   Select Blanks or No Blanks.

 Note    A blank value is a cell that contains no data and is different than a cell that contains one or more spaces (which are text).

• Format cells with error or no error values   Select Errors or No Errors.

Error values include: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, and #NULL!.

7. To specify a format, click Format.

The Format Cells dialog box is displayed.

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

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells based on a cutoff value that you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department personnel analysis.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.

3. Select the command that you want, such as Top 10 items or Bottom 10 %.
4. Enter the values that you want to use, and then select a format.

Tip  You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Apply Rule To, to optionally change the scope fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
5. Under Select a Rule Type, click Format only top or bottom ranked values.
6. Under Edit the Rule Description, in the Format values that rank in the list box, select Top or Bottom.
7. Do one of the following:
• To specify a top or bottom number, enter a number and then clear the % of the selected range check box. Valid values are 1 to 1000.
• To specify a top or bottom percentage, enter a number and then select the % of the selected range check box. Valid values are 1 to 100.
8. Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By default, the conditional format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

• A column and its parent row field, by selecting each Column group.
• A row and its parent column field, by selecting each Row group.
9. To specify a format, click Format.

The Format Cells dialog box is displayed.

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

Format only values that are above or below average

You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.

3. Select the command that you want, such as Above Average or Below Average.
4. Enter the values that you want to use, and then select a format.

Tip  You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
5. Under Select a Rule Type, click Format only values that are above or below average.
6. Under Edit the Rule Description, in the Format values that are list box, do one of the following:
• To format cells that are above or below the average for all of the cells in the range, select Above or Below.
• To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation.
7. Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By default, the conditionally format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

• A column and its parent row field, by selecting each Column group.
• A row and its parent column field, by selecting each Row group.
8. Click Format to display the Format Cells dialog box.
9. 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

Format only unique or duplicate values

 Note    You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.

3. Select Duplicate Values.
4. Enter the values that you want to use, and then select a format.

Advanced formatting

1. Select one or more cells in a range, table, or 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. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

4. Under Select a Rule Type, click Format only unique or duplicate values.
5. Under Edit the Rule Description, in the Format all list box, select unique or duplicate.
6. Click Format to display the Format Cells dialog box.
7. 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

Use a formula to determine which cells to format

If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range.

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

2. Do one of the following:
• To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

• To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

3. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
• Selection, click Just these cells.
• Corresponding field, click All <value field> cells with the same fields.
• Value field, click All <value field> cells.
4. Under Select a Rule Type, click Use a formula to determine which cells to format.
• Under Edit the Rule Description, in the Format values where this formula is true list box, enter a formula.

You must start the formula with an equal sign (=) and the formula must return a logical value of TRUE (1) or FALSE (0).

Example 1: Use one conditional format with multiple criteria and cell references outside of the range of cells

Example 2: Shade every other row by using the MOD and ROW functions

• Click Format to display the Format Cells dialog box.
• 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.

 Note    You can enter cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) in a formula by selecting cells directly on a worksheet. Selecting cells on the worksheet inserts absolute cell references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.). If you want Microsoft Office Excel to adjust the references for each cell in the selected range, use relative cell references (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.).

 Top of Page

Clear conditional formats

• Do one of the following:

Worksheet  

1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Clear Rules.

2. Click Entire Sheet.

A range of cells, table, or PivotTable  

3. Select the range of cells, table, or PivotTable for which you want to clear conditional formats.
4. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Clear Rules.
5. Depending on what you have selected, click Selected Cells, This Table, or This PivotTable.

 Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• Conditional Formatting - Thick Border?
• How do I use conditional formatting for an entire column?
• Conditional formating of a chart?
• Conditional formating to highlight dates 30, 60, and 90 days out?
• How to change a cell color based on its content using macro?
• Incorporating Cell color fill in an "if" logical function?

See Also

• Find cells that have conditional formats
• Manage conditional formatting rule precedence

Excel > PivotTable reports and PivotChart reports > Using the PivotTable and PivotChart Wizard

Consolidate multiple worksheets into one PivotTable report

Excel 2007

Consolidating data is a useful way to combine data from different sources into one report. For example, if you have a PivotTable report of expense figures for each of your regional offices, you can use a data consolidation to roll up these figures into a corporate expense report. This report can contain sales totals and averages, current inventory levels, and highest selling products for the whole enterprise.

What do you want to do?

Learn about consolidating multiple ranges

Consolidate multiple ranges

Learn about consolidating multiple ranges

To summarize and report results from separate worksheet ranges, you can consolidate data from each separate worksheet range into a PivotTable report on a master worksheet. The separate worksheet ranges can be in the same workbook as the master worksheet or in a different workbook. When you consolidate data, you are assembling it so that you can more easily update and aggregate data regularly or as needed.

The resulting consolidated PivotTable report can have the following fields in the PivotTable Field List, which you can add to the PivotTable report: Row, Column, and Value. In addition, the report can have up to four page filter fields, called Page1, Page2, Page3, and Page4.

 Note    The PivotTable and PivotChart Wizard uses the term page field in the user interface. In Microsoft Office Excel 2007, a page field is now called a report filter field in other parts of the PivotTable user interface.

Setting up the source data

Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together. Do not include any total rows or total columns from the source data when you specify the data for the report. The following example shows four ranges in cross-tab format.

Page fields in data consolidations

Data consolidation can use page fields that contain items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) representing one or more of the source ranges. For example, if you're consolidating budget data from the Marketing, Sales, and Manufacturing departments, a page field can include one item to show the data for each department, plus an item to show the combined data. The following example shows a consolidated PivotTable report that has one page field and multiple items selected.

Using named ranges

If the range of data is likely to change the next time that you consolidate the data (that is, the number of rows may change), consider defining a 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.) for each source range in the separate worksheets. Then use those names when you consolidate the ranges in the master worksheet. If a source range expands, you can update the range for the name in the separate worksheet to include the new data before you refresh the PivotTable report.

Other ways to consolidate data

Excel also provides other ways to consolidate data that work with data in multiple formats and layouts. For example, you can create formulas with 3D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.), or you can use the Consolidate command (on the Data tab, in the Data Tools group).

For more information, follow the links in the See Also section.

 Top of Page

Consolidate multiple ranges

You can use the PivotTable and PivotChart Wizard to consolidate multiple ranges. In the wizard, you can choose between using no page fields, a single page field, or multiple page fields.

Consolidate data without using page fields

To combine the data from all the ranges and create a consolidation that does not have page fields, do the following:

1. Click a blank cell in the workbook that is not part of a PivotTable report.
2. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

Tip

1. Click the arrow next to the toolbar and then click More Commands.
2. Under Choose commands from, select All Commands.
3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
3. On the Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next.
4. On the Step 2a page of the wizard, click I will create the page fields, and then click Next.
5. On the Step 2b page of the wizard, do the following:
1. For each cell range, click Collapse Dialog to temporarily hide the dialog box, select the cell range on the worksheet, press Expand Dialog , and then click Add.

Tip

2. Under How many page fields do you want?, click 0, and then click Next.
6. On the Step 3 page of the wizard, select a location for the PivotTable report, and then click Finish.

Consolidate data by using a single page field

To include a single page field that has an item for each source range, plus an item that consolidates all the ranges, do the following:

1. Click a blank cell in the workbook that is not part of a PivotTable report.
2. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

Tip

1. Click the arrow next to the toolbar and then click More Commands.
2. Under Choose commands from, select All Commands.
3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
3. On the Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next.
4. On the Step 2a page of the wizard, click Create a single page field for me, and then click Next.
5. On the Step 2b page of the wizard, do the following:
1. For each cell range, click Collapse Dialog to temporarily hide the dialog box, select the cell range on the worksheet, and then press Expand Dialog .

Tip

6. Click Next.
7. On the Step 3 page of the wizard, select a location for the PivotTable report, and then click Finish.

Consolidate data by using multiple page fields

You can create multiple page fields and assign your own item names for each source range. This lets you create partial or full consolidations; for example, one page field that consolidates Marketing and Sales apart from Manufacturing, and another page field that consolidates all three departments. To create a consolidation that uses multiple page fields, do the following:

1. Click a blank cell in the workbook that is not part of a PivotTable report.
2. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

Tip

1. Click the arrow next to the toolbar and then click More Commands.
2. Under Choose commands from, select All Commands.
3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
3. On the Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next.
4. On the Step 2a page of the wizard, click I will create the page fields, and then click Next.
5. On the Step 2b page of the wizard, do the following:
1. For each cell range, click Collapse Dialog to temporarily hide the dialog box, select the cell range on the worksheet, press Expand Dialog , and then click Add.

Tip

2. Under How many page fields do you want?, click the number of page fields that you want to use.
3. Under What item labels do you want each page field to use to identify the selected data range?, for each page field, select the cell range, and then enter a name for that range.

Example

• If you selected 1 under How many page fields do you want?, select each range, and then enter a different name in the Field One box. If you have four ranges and each one corresponds to a different quarter of the fiscal year, select the first range, enter Q1, select the second range, enter Q2, and repeat the process for Q3 and Q4.
• If you selected 2 under How many page fields do you want?, do the same as the previous example in the Field one box. Then, select two ranges, and enter the same name, such as H1 and H2, in the Field two box. Select the first range, enter H1, select the second range, enter H1, select the third range, enter H2, select the fourth range, and then enter H2.
4. Click Next.
6. On the Step 3 page of the wizard, select a location for the PivotTable report, and then click Finish.

 Top of Page

See Also

• Consolidate data in multiple worksheets

Excel > PivotTable reports and PivotChart reports > Using the PivotTable and PivotChart Wizard

Optimize memory in a PivotTable report

Excel 2007

 Note    You can optimize memory only for a PivotTable report that is connected to an Open Database Connectivity (ODBC) data source. You must use the PivotTable and PivotChart Wizard to complete the following procedure.

1. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

Tip

1. Click the arrow next to the toolbar, and then click More Commands.
2. Under Choose commands from, select All Commands.
3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
2. On the Step 1 page of the wizard, select External data source, and then click Next.
3. On the Step 2 page of the wizard, click Get Data.
4. Connect to a data source.

For more information about how to connect to an ODBC data source, see Use Microsoft Query to retrieve external data.

5. On the Step 3 page of the wizard, click Options.
6. In the PivotTable and PivotChart Wizard Options dialog box, select the Optimize memory check box.

Excel > PivotTable reports and PivotChart reports > Using the PivotTable and PivotChart Wizard

Retrieve PivotTable data one report filter item at a time

Excel 2007

If your PivotTable report is connected to an external Open Database Connectivity (ODBC) data source that contains a lot of data, retrieving this data can cause your computer to run out of memory or queries (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.) to run slowly. To prevent these problems, apply a report filter to your PivotTable report to retrieve only 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.) that is needed for the current set of values, depending on the selected items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.).

In this article

Overview on retrieving report filter data

Retrieve report filter data for each item or for all items

Overview on retrieving report filter data

You can configure a report filter to retrieve selected data from the data source of a PivotTable report by using the PivotTable Field Advanced Options dialog box in the PivotTable and PivotChart Wizard. By default, Microsoft Office Excel retrieves all the source data for a PivotTable report, which includes data that is filtered out (and thus not displayed in the report), when you create or 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. When you configure a report filter to retrieve data only for the item that you specify in the filter, you can retrieve smaller amounts of data on an as-needed basis. Each time that you display a different item in the filter, only the subset of the data for the new item is retrieved. You can configure as many report filters as you want and then apply them as needed.

How performance is affected

When a report filter is configured to retrieve data for one or more (but not all) items, the (All) item summarizing the complete set of data is unavailable (dimmed), and the Show Report Filter Pages command (on the Options tab, in the PivotTable group, when you click the arrow next to Options) is also unavailable. Each time that you select a different item, you retrieve less data, but you still have to wait for the data to be retrieved. If your computer becomes disconnected from the external data source, you will not be able to select a different item until you can reconnect to the data source. However, this option does let you work with much larger amounts of data than you could otherwise.

If you retrieve all the data for all the items, the initial data retrieval operation may take longer, but you can then select new items in the report filter without a delay because all the data that you need is loaded into the memory on your computer. You can also open a summary of the complete set of data by selecting the (All) item and reorganize the report to use the report filters in other areas. The (All) option gives you the most versatility when the total amount of data to be retrieved is within your computer's available memory limits.

Moving report filters

If you drag a report filter that is configured to retrieve all data for all items to another position, for example if you try to make it a row label, Excel tries to retrieve the data for all the items, and you might run out of computer resources. However, you can prevent a user from doing this. For more information, see the section Retrieve report filter data for each item or for all items.

Retrieving data for items when a report filter isn't available

This ability to use this feature is not available under the following circumstances:

• Reports based on source data from 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.) databases automatically query for data on an as-needed basis, and so do not use this feature.
• The PivotTable report is sharing the data cache with one or more other PivotTable reports.
• The ODBC driver (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) for the external database you're querying does not support parameter queries (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.). The ODBC drivers that are supplied with Microsoft Office all support parameter queries. You can contact third-party driver vendors to determine whether their drivers have this support.

 Top of Page

Retrieve report filter data for each item or for all items

 Note     You can use this feature only with a PivotTable report that is connected to an ODBC data source. You must use the PivotTable and PivotChart Wizard to complete the following procedure.

7. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

Tip

1. Click the arrow next to the toolbar and then click More Commands.
2. Under Choose commands from, select All Commands.
3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
8. On the Step 1 page of the wizard, select External data source, and then click Next.
9. On the Step 2 page of the wizard, click Get Data.
10. Connect to a data source.

For more information about how to connect to an ODBC data source, see Use Microsoft Query to retrieve external data.

11. On the Step 3 page of the wizard, click Layout.
12. In the Layout dialog box, drag one or more fields to the Report Filter area.
13. For each report filter field, do the following:
1. Double-click the field.

The PivotTable Field Advanced Options dialog box appears.

2. To retrieve only up-to-date data from the data source every time that you select a new item in a report filter, click Query external data source as you select each report filter field item (requires less memory).

To prevent a report filter field from being moved to the Row Labels, Column Labels, or Values areas, select the Disable pivoting of this field (recommended) check box.

To return to the default operation, retrieving all the data from the data source every time that you select a new item in a report filter, click Retrieve external data for all report filter field items (faster performance).

 Top of Page

See Also

• Use a report filter in a PivotTable report or PivotChart report

Excel > PivotTable reports and PivotChart reports > Using the PivotTable and PivotChart Wizard

Unshare a data cache between PivotTable reports

Excel 2007

By default, PivotTable reports that are based on the same data source — a cell range in a worksheet or a data connection  — share a data cache, but you can unshare this data cache by using several methods.

What do you want to do?

Learn more about the PivotTable data cache

Unshare the data cache between PivotTable reports

Learn more about the PivotTable data cache

The data cache of a PivotTable report is an area of internal memory on your computer that is used by Microsoft Office Excel to store the data for the report. To help improve performance and reduce the size of your workbook, Excel automatically shares the PivotTable data cache between two or more PivotTable reports that are based on the same cell range or data connection. If the cell range or data connection for two or more PivotTable reports is different, the data cache cannot be shared between those reports.

However, there may be times when you do not want to share a data cache between two or more PivotTable reports that are based on the same data source. For example:

• You do not want calculated fields and items to be displayed in all the PivotTable reports.
• You do not want fields to be grouped in the same way in all the PivotTable reports.
• You do not want all the PivotTable reports to refresh at the same time.
• You want to use a feature that is not supported when you use a shared data cache, such as retrieving data in a report for a selected item or items when you apply a report filter.

 Note    The data cache for a PivotTable report that is connected to 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 cannot be shared because it is used in a different way than non-OLAP data sources.

 Top of Page

Unshare the data cache between PivotTable reports

There are several ways to unshare a data cache, including the following:

• Use the PivotTable and PivotChart Wizard to create a new PivotTable report that is based on the same cell range as another report without sharing the data cache.
• Unshare the data cache between PivotTable reports that are based on a cell range by temporarily redefining the data range to force Excel to unshare the data cache.
• Unshare the data cache between two or more PivotTable reports that are based on the same data connection by creating a unique data connection for each PivotTable report in the workbook.

Tip (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.)

Create a PivotTable report that is based on the same cell range as another report without sharing the data cache

1. Ensure that there is an existing PivotTable report that is based on the same range that you want to use for the new PivotTable report.
2. Click any blank cell in the worksheet outside the PivotTable report.
3. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

Tip

1. Click the arrow next to the toolbar, and then click More Commands.
2. Under Choose commands from, select All Commands.
3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
4. On the Step 1 page of the wizard, click Microsoft Office Excel list or database, and then click Next.
5. On the Step 2 page of the wizard, select the range of data on which you want to base the new PivotTable report, and then click Next.
6. When the PivotTable and PivotChart Wizard displays a message asking if you want to share the data cache, click No.
7. On the Step 3 page of the wizard, select a location for the new PivotTable report, and then click Finish.

Unshare the data cache between PivotTable reports that are based on a cell range

14. Ensure that there are at least two PivotTable reports based on the same cell range and that these reports share the same data cache.
15. Click a cell in the PivotTable report for which you want to unshare the data cache.
16. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

Tip

1. Click the arrow next to the toolbar, and then click More Commands.
2. Under Choose commands from, select All Commands.
3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
17. On the Step 3 page of the wizard, click Back to return to the Step 2 page.
18. On the Step 2 page of the wizard, make sure that the same range of data on which you want to base the PivotTable report is selected, but that at least one fewer row is included in the selection.

For example, if the range is $A$1:$E$286, change the range to $A$1:$E$285.

19. Click Next.
20. On the Step 3 page of the wizard, make sure that Existing worksheet is selected and that the location is the same, and then click Finish.

The PivotTable report now has a different data cache but is based on a different data range.

21. Make sure that a cell in the PivotTable report for which you want to unshare the data cache is selected.
22. To start the PivotTable and PivotChart Wizard again, press ALT+D+P.
23. On the Step 3 page of the wizard, click Back to return to the Step 2 page.
24. On the Step 2 page of the wizard, change the range of data back to the original range.

For example, if the current range is $A$1:$E$285, change the range back to $A$1:$E$286.

25. Click Next.
26. On the Step 3 page of the wizard, make sure that Existing worksheet is selected and that the location is the same, and then click Finish.

The new PivotTable report is now based on the same data range as the other report, but has a different data cache.

Unshare the data cache of two or more PivotTable reports that are based on the same data connection

1. Make sure that there are at least two PivotTable reports that are based on the same data connection and that these reports share the same data cache.

Confirm that the same data connection is used between PivotTable reports

1. On the Data tab, in the Connections group, click Connections.
2. In the Workbook Connections dialog box, select the PivotTable report connection.
3. Under Locations where connections are used in this workbook, click the link that displays the text Click here to see where the selected connections are used.

The PivotTable reports that use this data connection are displayed.

2. Make sure that there is a connection file for the data connection on your computer or on the network.

Create a connection file for a data connection in a workbook

1. On the Data tab, in the Connections group, click Connections.
2. In the Workbook Connections dialog box, select the PivotTable report connection.
3. Click Properties.
4. In the Connection Properties dialog box, click the Definition tab, and then click Export Connection File.
5. In the File Save dialog box, save the current connection information as an .odc file.
6. Click OK, and then click Close.
3. Click any cell in the PivotTable report for which you want to unshare the data cache.
4. 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 appears.

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

The Existing Connections dialog box appears.

6. Select a data connection from the Select a Connection list in either the Connection files on the network or Connection files on this computer category, and then click Open.

 Note    You may need to click Browse to locate the connection file.

7. Click OK.

The PivotTable report now has a different data cache.

 Top of Page