About PivotTable lists

A PivotTable list is an interactive table that you can use to analyze data dynamically from within your Web browser. You can use a PivotTable list to view and organize data from lists or databases, look for information or details, and create personalized summaries and reports.

A PivotTable list can present existing data from a source elsewhere on your network. The person who creates the PivotTable list determines which source data to use; that is, the designer decides what source database to use and what subset of that data to make available. That subset determines what you, the viewer, can summarize. For example, if you want to summarize product sales and inventory levels, the source data would need to provide the sales figures and the inventory counts for your products. You can't type new values in the list's calculated cells or redefine the source data that the list displays, but you can change the way the data is displayed and organized. Also, you can refresh the PivotTable list so that it reflects any changes in the source data.

The designer of a PivotTable list can include an associated chart that displays the same data graphically. If the Web page you're viewing has such a chart, when you refresh or make certain changes to the PivotTable list, you'll see the changes reflected in the chart. Similarly, certain changes you make to the chart are reflected in the PivotTable list.

Analyzing data

The designer of the PivotTable list sets it up to display one of many possible views of the data. You can use the PivotTable list toolbar and the Commands and Options dialog box to change how the data is displayed. You can look for specific data, display larger or smaller amounts of detail, or move the rows and columns to different areas to calculate different summaries of the data.

In its simplest form, a PivotTable list displays data in rows and columns, with similar facts in the same column. Each column is called a field. The following example contains fields named Sport, Quarter, and Sum of Sales.

Example of a PivotTable list

A simple PivotTable list resembles a table of records from a database or a set of rows from a spreadsheet list. To further analyze the data, you can:

  • Filter the fields to find data. For example, display only the data about tennis sales in the third quarter.
  • Sort items to reorder the rows. For example, sort on the Sales field to see which sport and quarter produced the highest gross.
  • Group items in row and column areas. For example, you can group the first two quarters to compare sales in the first half of the year with sales in individual quarters.
  • Add subtotals or other calculations to the list. For example, display the largest sales amount for each sport.

A PivotTable list has different areas where you can move fields to summarize them or to view the details of their content. You can display your PivotTable list like the preceding example by moving all fields to the detail area if the source data is set up to allow you to do this.

Summarizing data

By creating a total field and moving fields to other areas, you can quickly summarize the data. By creating a Sum of Sales total field, moving the Sport field to the row area, and moving the Quarter field to the column area, you can easily see how third-quarter golf sales stack up against sales for another sport or quarter, and you can see grand total sales.

About source data

PivotTable lists can display data from network databases, including both On-Line Analytical Processing (OLAP) databases and relational databases, such as Microsoft Access or Microsoft SQL Server. PivotTable lists can also display data from other types of databases on your network, and data from spreadsheet databases such as Microsoft Excel lists. The type of source database and the type of data included in the PivotTable list that you're viewing determine what kinds of views and summaries you can create from within the Web browser.

OLAP is an approach to database server processing that organizes large amounts of data in ways that make it accessible and understandable to people who want to analyze business trends and results. These databases arrange categories of data in fieldsets (also called dimensions) and levels of detail.

For example, a Geography fieldset could have Country, Region, and City levels, and a Time fieldset could have Year, Quarter, Month, and Day levels. These levels translate into row, column, or filter fields in a PivotTable list.

If you work with PivotTable lists that are based on OLAP databases, such as one created with Microsoft SQL Server OLAP Services, you'll notice that you can't move fields to the detail area, and certain fields can be used only in the data area.

Saving changes

If you're using Microsoft Internet Explorer 4.01 or later, changes that you make to the PivotTable list are retained during the browser session, even when you return to the PivotTable list after viewing other Web pages. However, if you reopen the Web page — for example, by clicking the Internet Explorer address bar and pressing ENTER — your changes are lost.

Regardless of which Web browser you're using, changes that you make do not affect the original PivotTable list. Therefore, other users who might be viewing the same PivotTable list at the same time will not see your changes.

Working with other programs

You can copy data from the PivotTable list to other programs, such as Microsoft Word, or, if you want to further analyze the data or print a customized version of the data, you can export the list to a Microsoft Excel PivotTable report. If you want to create your own PivotTable list or make design changes to an existing one, you can use Microsoft Access or Microsoft FrontPage to open the Web page and use the advanced features of PivotTable lists. For more information, see Help in Access or FrontPage.

Browser and license requirements

To work with an interactive PivotTable list in the browser, you must have the Microsoft Office Web Components installed, a valid Microsoft Office XP license, and Microsoft Internet Explorer version 4.01 or later. Without a proper license, you might be able to view the components but not interact with them. See your network or computer administrator to see if your system meets the necessary requirements to interact with a PivotTable list.

Disabled features

Some elements in your PivotTable list may have been protected by the designer of the Web page. For instance, the designer may have disabled adding or moving fields, disabled sorting and filtering, disabled the toolbar from being displayed, or disabled display of the Commands and Options dialog box. If you have problems accessing these features, and your computer meets browser and license requirements, contact the designer of the Web page.


About working with PivotTable lists in different browsers

To work with interactive PivotTable lists in Microsoft Internet Explorer 4.01 and later, you must have the Microsoft Office Web Components installed and an appropriate Microsoft Office XP license. If you don't have an Office XP license, you can install the components and view them on a Web page, but you cannot interact with them.

Following are the levels of browser support available if you have the Office Web Components installed and an Office XP license.

Microsoft Internet Explorer 5 and later

In this browser, you have access to all PivotTable list features that are accessible from a browser. Changes you make, such as formatting and layout changes, are retained when you leave a PivotTable list and browse back to it later in the same browser session.

Microsoft Internet Explorer 4.01, 4.1x, and 4.2x

You have access to all PivotTable list features accessible from a browser. Changes are retained when you leave a PivotTable list and browse back to it later in the same browser session. You must have Internet Explorer 5 installed to view PivotTable lists on data access pages created in Microsoft Access.

Other browsers

In earlier versions of Microsoft Internet Explorer and in other browsers, you cannot view PivotTable lists. A PivotTable list either appears as an outlined box on the Web page or does not appear at all.


About Microsoft Office Web Components

Microsoft Office Web Components is a set of ActiveX controls installed with Microsoft Office XP. With Office Web Components installed on your computer, you can interactively use a spreadsheet, a PivotTable list, and a chart on a Web page in Microsoft Internet Explorer 4.01 or later. If you want to use the components on a data access page in Microsoft Access, you should have Internet Explorer 5 or later.

If Office Web Components is not installed on your computer but you have an Office XP site license that permits intranet distribution, you can configure the components so that users will be prompted to download Office Web Components from your corporate intranet. This allows users at your site to use a data access page without having Office XP software installed on their computers.

For information about how to configure Office Web Components, see the Microsoft Office XP Resource Kit.


About View-only mode for Office Web Components

If you do not have a Microsoft Office XP software license installed on your computer or accessed via a Microsoft Office XP application, you can view a Microsoft Office Web Component — a Spreadsheet Component, Chart Component, or PivotTable Component — on a Web page, but you cannot interact with it.

To be able to interact with and use full functionality of the component you are viewing, you must install Microsoft Office XP or have access to an Office XP license. If you are in a corporate or group environment, check with your network administrator for assistance.

When you are in View-only mode, you can:

  • View data.
  • Print.
  • Select and scroll.
  • Use hyperlinks.
  • Resize a component.
  • Select sheets in the Spreadsheet Component.
  • Expand or collapse members in a PivotTable list.
  • Refresh data in a PivotTable list.
  • Use the About, Help, and Refresh commands. Help topics that deal with interactivity (such as formatting cells, dragging fields, sorting, filtering, and so on) do not apply.

All commands except About, Help, and Refresh are unavailable in View-only mode.


About designing PivotTable lists

Some of the content in this topic may not be applicable to some languages.

When you view and interact with a PivotTable list in your Web browser, you are working with a control that was created in another program called a design program. In a design program, you can select the source data that you want a PivotTable list to display, add the PivotTable list to a Web page you're designing, and publish the Web page on a Web server. Help about designing a PivotTable list is available from within the design program.

Designing your own PivotTable list

To learn more about designing a PivotTable list, first choose a design program to work in. You can start in any of the supported design programs, and then later work on the same PivotTable list in a different design program if you need different design features. For example, if you have designed a PivotTable report in Microsoft Excel, you can use Excel to save or publish the report to a PivotTable list on a Web page. If you want to customize the resulting PivotTable list to change the default settings, or if you want to add other controls to the page, you can then open and modify the Web page in Microsoft FrontPage.

You can use the following design programs to create PivotTable lists.

Microsoft FrontPage

FrontPage is the recommended design program for creating PivotTable lists for users who have Internet Explorer version 4.01 or later, and for modifying PivotTable lists saved or published from Microsoft Excel. In FrontPage, you can create PivotTable lists on Web pages, modify PivotTable lists that were designed in other programs, and then use the features of FrontPage to construct and manage your Web site.

Microsoft Access

Access provides specialized features for creating Web pages that operate on and display data from Microsoft Access and Microsoft SQL Server databases. For example, you can use the field list to locate data and drag it onto a PivotTable list. You can establish links between PivotTable lists and other controls that display and manipulate data from databases on a Web page. If a page includes data other than that displayed in a PivotTable list or other Microsoft Office Web Component, users of your Web page must have Internet Explorer 5 or later to view the page.

Microsoft Excel

Excel is an excellent design environment if you want to try different layouts and adjust the source data that you make available for your PivotTable list. You can access external data within Excel and create an Excel PivotTable report. From Excel, you save or publish a PivotTable report to a PivotTable list on a Web page. (A PivotTable report is the Excel version of a PivotTable list.) The source data you specified in Excel is automatically attached to the PivotTable list, and most Excel PivotTable report features are included when you publish the PivotTable list.

You might want to format the PivotTable list and make adjustments to its size and appearance in another design program. If any of the users of your Web page have Internet Explorer version 4.01 or later, be sure to use Microsoft FrontPage to modify PivotTable lists published from Microsoft Excel. Excel publishes PivotTable lists to Web pages with support for Internet Explorer 4.01 and later, and FrontPage maintains this support. If you modify a Web page that was published from Excel in Microsoft Access by using data access page Design view, however, your users might need to install Internet Explorer 5 in addition to Internet Explorer 4.01 before they can use your Web page.

Microsoft Visual Basic or a scripting language

If you are a programmer, you can write programs or scripts to create a PivotTable list. Both Microsoft FrontPage and Microsoft Access allow you to write scripts. If you want to design a PivotTable list for use in a custom solution instead of a Web browser, you can use a programming environment such as Visual Basic.

Microsoft Word is not a suitable design program for PivotTable lists.

Getting design help

When you create or activate a PivotTable list in a design program such as Microsoft FrontPage or Microsoft Access, the Commands and Options dialog box provides additional properties and commands that you can use to customize how the PivotTable list can be used in the browser. The design program may also provide its own commands and features for preparing source data and attaching it to a PivotTable list.

While you are working in a design program, you can get design help for a PivotTable list in either of two ways:

Design program Help

For information about the commands and features available in the design program to work with PivotTable lists, see Help in the design program. For example, in Microsoft Access, click the Office Assistant, or click Microsoft Access Help [image].

Microsoft Excel provides both general Help about saving and publishing Excel data for use on Web pages and specific information about preparing data for use in a PivotTable report and then publishing the PivotTable report as a PivotTable list on a Web page. To access this Help in Excel, click the Office Assistant, or click Microsoft Excel Help [image].

PivotTable list design Help

In a PivotTable list that you have created or activated in a design program, click Help [image] on the PivotTable list's toolbar. This method displays design Help topics about the properties and commands you can use within the PivotTable list in the design program. This design Help is not the same as the Help in the browser (that is, the Help topics you are currently viewing).

Writing scripts and programs for PivotTable lists

You can use scripts and programs to automate PivotTable lists, or you can use PivotTable lists in run-time programs other than Web browsers, programs that you develop in a programming environment such as Microsoft Visual Basic.

Writing scripts

Microsoft Office XP programs provide the Microsoft Script Editor to help you write scripts. For example, when working with data access pages in Microsoft Access, you can use the Script Editor in Design view to customize a PivotTable list. For information about running the Script Editor, see Help in your Microsoft Office design program. In the Script Editor, you can display additional Help about using its features and writing scripts.

Object model Help

For help developing a program or script to work with a PivotTable list, you can use the Microsoft Office Web Components Visual Basic Reference to display information about the object model, properties, and methods specific to PivotTable lists. This object model Help is installed when you install the Office Web Components, of which the PivotTable Component is one. Depending on the design program you're using, you can get this Help in either of two ways:

  • Help in Microsoft Visual Basic In Visual Basic or Visual Basic for Applications (VBA), you can create a reference to the Microsoft Office Web Components library. You can then get Help in the object browser or by selecting a keyword and pressing F1. For information about creating references and using object model Help, see Help in Visual Basic or VBA.

  • Help in other environments If you are using another development environment, locate and double-click the Help file Owcvba10.chm.


Save changes to a PivotTable list

If you're using Microsoft Internet Explorer 4.01 or later, changes that you make to the PivotTable list are retained during the browser session, even when you return to the PivotTable list after viewing other Web pages. However, if you reopen the Web page — for example, by clicking the Internet Explorer address bar and pressing ENTER — your changes will be lost.

To make changes to a PivotTable list and save your changes in a file, open the PivotTable list in a design program such as Microsoft FrontPage or Microsoft Access.

You can also export the PivotTable list to a Microsoft Excel PivotTable report, and then save the Excel report. For more information, see Excel Help.


Undo changes to a PivotTable list

  • To restore the PivotTable list to the state it was in when you first viewed it in Microsoft Internet Explorer, close Internet Explorer and then start Internet Explorer again.

Note You cannot undo individual changes to a PivotTable list.


Print a PivotTable list

  • On the Microsoft Internet Explorer File menu, click Print.

Notes

  • If the PivotTable list is wider or longer than the printed page, the areas that don't fit on the page are not printed.
  • The Web browser prints only the parts of the PivotTable list that are not hidden and that you have scrolled to during the browser session. For example, if you display only higher levels of detail or if you hide detail data, the hidden detail data is not printed. And if you scrolled from the beginning to the end of the data in the PivotTable list, the entire list is printed.
  • For more control over how the data is printed, including the ability to print a long list and set page breaks, you can export the PivotTable list to Microsoft Excel. For information about printing from Excel, see Excel Help.


Elements of a PivotTable list

Example of a PivotTable list that shows element names

1 Filter field

2 Row fields

3 Column field

4 Field drop-down arrow

5 Data field

6 Expand Indicator

Fields

Each field corresponds to a column of data and can summarize multiple rows of information. Types of fields include row fields, column fields, data fields, detail fields, and filter fields.

Item

A unique element of data within a field. Items of data in row and column fields are listed down rows and across columns. The cell where a row and column intersect displays summarized data for the items listed at the top of the column and the left side of the row. When a lower level of items is available for display, an expand indicator Plus box appears beside the item. The items at the lowest level don't display expand indicators. The expand indicators ( Plus box and Minus box boxes) to the right of an item indicate that detail data is available for that item.

Row field

A field in the row area. In the above PivotTable list, Product and Sold By are row fields. Items in row fields are listed down the left side of a PivotTable list. Inner row fields, such as Sold By, are closest to the data area; outer row fields, such as Product, are to the left of the inner row fields.

Column field

A field in the column area. Items in column fields are listed across the top of a PivotTable list, above the data fields. In the above PivotTable list, Year is a column field with two items, 1996 and 1997. Inner column fields are closest to the data area; outer column fields are above the inner column fields.

Filter field

A field in the filter area that you can use to filter the entire PivotTable list to display summarized data for specific items. In the above PivotTable list, Region is a filter field that you can use to display data for all regions or only selected regions. Filter fields perform the same functions as page fields in Microsoft Excel PivotTable reports.

Data field

A field that contains summary values from the source data. In the above PivotTable list, Sales is a data field that contains summarized sales amounts — each value in the data field is the sum of the entries from the Sales field or column in the source data.

A data field usually summarizes numeric data, such as statistics or sales amounts, by using the Sum summary function. In the above PivotTable list, the cell containing 30,984 displays Davolio's total sales for dairy products in 1996. This cell combines the sales amounts from every record in the source data that has Dairy for the product, Davolio for the salesperson, and 1996 for the year.

The underlying data can also be text, summarized by default with the Count summary function, which yields the number of items — for example, a data field that contains the type of product sold might be used to count the number of dairy sales that Davolio made in 1996. A PivotTable list can also summarize data by using the Min and Max summary functions, which give the smallest and largest values, respectively.

Data area

The part of a PivotTable list that contains data fields.

Detail field

A field that displays all of the rows, or records, from the source data.

Detail area

The part of a PivotTable list that contains detail fields.

Field drop-down arrow

The arrow Field arrow at the right side of each field. Click this arrow to display a list from which you can select the items to display and hide.

In PivotTable lists that are based on OLAP source data, you can select items at different levels in a dimension field. Date and time fields and fields with custom groups provide different levels in all types of source data. For example, clicking the arrow next to a Year field might display the following:

Drop-down list for OLAP dimension field

In this example, the cleared check box next to 1998 indicates that the year 1998 is hidden. The shaded check box next to 1999 indicates that the year 1999 is currently selected for display but some of the items under 1999 are not selected for display. Within 1999, Qtr1 is hidden. The selected check box next to 2000 indicates that the year 2000 is selected for display. Selecting (All) selects all items at all levels for display.


Select a PivotTable list or its elements

Select the entire PivotTable list

  • If the title bar is displayed, click the title bar. Otherwise, press CTRL+A.

Note You can't display the title bar if the PivotTable list is not bound to a data source.

Select one or more data cells

  • To select a single cell, click it. To select a group of cells, click and drag the mouse pointer until all the cells you want are selected.

Select a row, column, or filter field

  • Click the field label.

Note Selecting a row or column field also selects all of the items of data in the field.

Select a detail field

  • Click the field label. If detail data is displayed for several items so that the field label is displayed more than once, clicking one instance of the field label selects all instances.

Select a total field

  • Click any field label for the total field.

Select one or more items

  • To select a single item, click the item label.
  • To add more items to the selection, press and hold the CTRL key when clicking the item labels.
  • To select a range of items, click the label of the first item, press SHIFT, and then click the label of the last item.

Note Selecting an item selects all of the data for that item.

Select a subtotal or grand total

  • Click the subtotal or grand total.

Note Selecting a subtotal or grand total also selects all of the data for the subtotal or grand total.

Select the title bar or a drop area

  1. Click the title bar.

  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Captions tab.

  3. In the Select caption list, select the item you want.

Select a custom property of a row or column field

  1. Click the row or column field, or one of its items.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Item Properties tab.
  3. In the Item properties list, select the property you want.

    Note If the selected field does not have any custom properties, the Item properties list will be empty.

    You can set the caption, number format, and display location settings for the selected property. The formats you select affect only the selected property.

  4. To set the font and style settings of a property, select Property caption or Property value from the Format list.

Note Font and text style settings that you apply to a custom property affect all custom properties in the PivotTable list, but only when they are displayed in the report. The formatting is ignored when the custom property is displayed in the ScreenTip.


About viewing a chart based on a PivotTable list

The designer of the Web page you have open might have created a chart that displays information from the PivotTable list graphically. This topic provides information about how the chart works and what happens to the chart when you make changes to the PivotTable list.

Automatic update

When you change a PivotTable list that a chart is based on, the chart is automatically updated to reflect the changes in the PivotTable list. For example, if you move a field to the row or column area, the chart will automatically be updated to reflect the field as either category or series data. When you hide information (for example, when you filter the data or remove a field from the PivotTable list) so that data is no longer included in the view of the list, the information is also removed from the chart. Similarly, when you make changes to a chart, the PivotTable list is automatically updated to reflect the changes in the chart.

Examples of charts that are based on PivotTable lists

The following example shows a chart based on a PivotTable list. The Sum of Sales field shows total sales for each type of product per salesperson. The blue arrow in the Salesperson field label indicates that the field is filtered to display totals for only four of the salespeople.

Chart based on PivotTable list

1 The PivotTable list column field Type is shown in the chart as category data (Beverages, Dairy, and so on).

2 The Salesperson row field is shown in the chart as series data.

3 The names of the series are displayed in the legend (Buchanan, Davolio, and so on).

When you add or move fields in the PivotTable list, the chart automatically updates to reflect the changes.

Updated chart based on changes in PivotTable list

In the preceding example, the Salesperson field was moved to the column area and the Type field was moved to the row area of the PivotTable list. The Region field was added to the column area and filtered to show only East and North. The chart was automatically updated so that the data from the Type field is displayed in the legend as a series, and the data from the Salesperson and Region fields supplies the categories.

You can sort, filter, and move fields within the chart.


Change title, captions, and labels

You can change the title of the PivotTable list, the field names, the names of items within fields, and names of total fields. The changes you make don't affect the source data.

  1. Click Commands and Options [image] on the PivotTable list toolbar.
  2. Do one or more of the following:

    Change the title bar text or the caption of a drop area

    1. Click the title bar of the PivotTable list.
    2. Click the Captions tab in the Commands and Options dialog box.
    3. In the Select caption list, select the element whose caption you want to change.
    4. In the Caption text box, type the new title or caption.

    Change the caption of a field, fieldset, or custom group field

    1. Do one of the following:
      • To change the caption of a field, click the field.
      • To change the caption of a fieldset, click a field that belongs to the fieldset.
    2. Click the Captions tab in the Commands and Options dialog box.
    3. In the Select caption list, select the element whose caption you want to change.
    4. In the Caption text box, type the new title or caption.

    Change the caption of an item property

    1. Click the field to which the item property belongs.
    2. Click the Item Properties tab in the Commands and Options dialog box.
    3. In the Item properties list, click the property whose caption you want to change.
    4. In the Caption text box, type the new title or caption.

    Change the caption of an item or custom group

    1. Click the item or custom group whose caption you want to change.
    2. Click the Captions tab in the Commands and Options dialog box.
    3. In the Caption text box, type the new title or caption.

Note If you don't want a caption for the selected item, clear the Caption text box.


View information about a field or item

  1. Do one of the following:
    • For information about a field, click the label of the field.
    • For information about an item of data in a field, click the item.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Captions tab.


Keyboard shortcuts in PivotTable lists

Keys for activating, moving around in, and deactivating a PivotTable list

The TAB key
Select or activate each control on a Web page in sequence. After you activate a PivotTable list, pressing the TAB key selects elements within the PivotTable list.
CTRL+TAB
Deactivate (or exit) a PivotTable list and select or activate the next control on the Web page

Keys for selecting the elements of a PivotTable list

The TAB key
Move the selection from left to right, and then down
ENTER
Move the selection from top to bottom, and then to the right
SHIFT+TAB
Select the cell to the left. If the current cell is the leftmost cell, SHIFT+TAB selects the last cell in the previous row.
SHIFT+ENTER
Select the cell above the current cell. If the current cell is the topmost cell, SHIFT+ENTER selects the last cell in the previous column.
CTRL+TAB
Select the detail cells for the next item in the column area
CTRL+ENTER
Select the detail cells for the next item in the row area
SHIFT+CTRL+TAB
Select the detail cells for the previous item in the column area
SHIFT+CTRL+ENTER
Select the detail cells for the previous item in the row area
Arrow keys
Move the selection in the direction of the arrow key. If a row or column field is selected, press DOWN ARROW to move to the first item of data in the field, and then press an arrow key to move to the next or previous item or back to the field. If a detail field or an item of data is selected, press DOWN ARROW or RIGHT ARROW to move to the first cell in the detail area or data area.
SHIFT+arrow key
Extend or reduce the selection in the direction of the arrow key
CTRL+arrow key
Move the selection to the last cell in the direction of the arrow key
SHIFT+ALT+arrow key
Move the selected item in the direction of the arrow key
HOME
Select the leftmost cell of the current row
END
Select the rightmost cell of the current row
CTRL+HOME
Select the leftmost cell of the first row
CTRL+END
Select the last cell of the last row
SHIFT+CTRL+HOME
Extend selection to the leftmost cell of the first row
SHIFT+CTRL+END
Extend selection to the last cell of the last row
CTRL+SPACEBAR
Select the field for the currently selected item of data, total, or detail
SHIFT+SPACEBAR
Select the entire row containing the currently selected cell
CTRL+A
Select the entire PivotTable list
PAGE DOWN
Display the next screen
PAGE UP
Display the previous screen
SHIFT+PAGE DOWN
Extend a selection down one screen
SHIFT+PAGE UP
Reduce a selection by one screen

Keys for carrying out commands

SHIFT+F10
Display the shortcut menu for the selected element of the PivotTable list. Use the shortcut menus to carry out commands in the PivotTable list.
Underlined letter
Carry out a command on the shortcut menu
ESC
Close the shortcut menu without carrying out a command
CTRL+7
Display or hide the PivotTable list toolbar
ALT+ENTER
Display the Commands and Options dialog box
ALT+F4
Close the Commands and Options dialog box
F9
Refresh the PivotTable list from the source data
ESC
Cancel a refresh operation in progress
CTRL+C
Copy the selected data from the PivotTable list to the Clipboard
CTRL+V
Paste contents of the Clipboard
CTRL+X
Move selected data to the Clipboard
CTRL+E
Export the PivotTable list to Microsoft Excel

Keys for displaying, hiding, filtering, and sorting data

CTRL+8
Display or hide the expand indicators ( Plus box and Minus box boxes) beside items
CTRL+PLUS SIGN (numeric pad)
Expand the currently selected item
CTRL+MINUS SIGN (numeric pad)
Hide the currently selected item
ALT+DOWN ARROW
Open the list for the currently selected field
The TAB key
Alternately move to the most recently selected item, the OK button, and the Cancel button in the drop-down list for a field
Arrow keys
Move to the next item in the drop-down list for a field
SPACEBAR
Select or clear the check box for the current item in the drop-down list for a field
CTRL+>
Display detail, if available, for the current item
CTRL+<
Hide detail for the current item
ENTER
Close the drop-down list for a field and apply any changes you made
ESC
Close the drop-down list for a field without applying your changes
CTRL+T
Turn AutoFilter on or off
CTRL+SHIFT+A
Sort data in the selected field or total in ascending order (A – Z 0 – 9)
CTRL+SHIFT+Z
Sort data in the selected field or total in descending order (Z – A 9 – 0)
ALT+SHIFT+UP ARROW or ALT+SHIFT+LEFT ARROW
Move the selected member up or left
ALT+SHIFT+DOWN ARROW or ALT+SHIFT+RIGHT ARROW
Move the selected member down or right

Keys for adding fields and totals, and changing the layout of a PivotTable list

Keys for working with the field list

CTRL+L
Display the field list, or activate it if it's already displayed
Arrow keys
Move to the next item in the field list
SHIFT+UP ARROW
Move to the previous item and include it in the selection
SHIFT+DOWN ARROW
Move to the next item and include it in the selection
CTRL+UP ARROW
Move to the previous item, but don't include the item in the selection
CTRL+DOWN ARROW
Move to the next item, but don't include the item in the selection
CTRL+SPACEBAR
Remove the item from the selection, if the item that has focus is included in the selection, and vice versa
PLUS SIGN (numeric pad)
Expand the current item in the field list to display its contents. Or expand Totals to display the available total fields.
MINUS SIGN (numeric pad)
Collapse the current item in the field list to hide its contents. Or collapse Totals to hide the available total fields.
The TAB key
Alternately move to the most recently selected item, the Add to button, and the list next to the Add to button in the field list.
ALT+DOWN ARROW
Open the drop-down list next to the Add to button in the field list. Use the arrow keys to move to the next item in the list, and then press ENTER to select an item.
ENTER
Add the highlighted field in the field list to the area in the PivotTable list that's displayed in the Add to list
ALT+F4
Close the field list

Keys for adding fields and totals

CTRL+SHIFT+S
Add a new total field for the selected field in the PivotTable list by using the Sum summary function
CTRL+SHIFT+C
Add a new total field for the selected field in the PivotTable list by using the Count summary function
CTRL+SHIFT+M
Add a new total field for the selected field in the PivotTable list by using the Min summary function
CTRL+SHIFT+X
Add a new total field for the selected field in the PivotTable list by using the Max summary function
CTRL+SHIFT+E
Add a new total field for the selected field in the PivotTable list by using the Average summary function
CTRL+SHIFT+D
Add a new total field for the selected field in the PivotTable list by using the Standard Deviation summary function
CTRL+SHIFT+T
Add a new total field for the selected field in the PivotTable list by using the Standard Deviation Population summary function
CTRL+SHIFT+V
Add a new total field for the selected field in the PivotTable list by using the Variance summary function
CTRL+SHIFT+R
Add a new total field for the selected field in the PivotTable list by using the Variance Population summary function
CTRL+SHIFT+B
Turn subtotals and grand totals on or off for the selected field in the PivotTable list
CTRL+F
Add a calculated detail field

Keys for changing the layout

CTRL+1
Move the selected field in the PivotTable list to the row area
CTRL+2
Move the selected field in the PivotTable list to the column area
CTRL+3
Move the selected field in the PivotTable list to the filter area
CTRL+4
Move the selected field in the PivotTable list to the detail area
CTRL+LEFT ARROW
Move the selected row or column field in the PivotTable list to a higher level
CTRL+RIGHT ARROW
Move the selected row or column field in the PivotTable list to a lower level

Keys for formatting a PivotTable list

To use the following shortcuts, first select a detail field or a data cell for a total field.

The first seven keyboard shortcuts change the number format of the selected element.

CTRL+SHIFT+~ (tilde)
Apply the general number format to values in the selected total or detail field
CTRL+SHIFT+$
Apply the currency format, with two decimal places and negative numbers in parentheses, to values in the selected total or detail field
CTRL+SHIFT+%
Apply the percentage format, with no decimal places, to values in the selected total or detail field
CTRL+SHIFT+^
Apply the exponential number format, with two decimal places, to values in the selected total or detail field
CTRL+SHIFT+#
Apply the date format, with the day, month, and year, to values in the selected total or detail field
CTRL+SHIFT+@
Apply the time format, with the hour, minute, and AM or PM, to values in the selected total or detail field
CTRL+SHIFT+!
Apply the numeric format, with two decimal places, thousands separator, and a minus sign for negative values, to values in the selected total or detail field
CTRL+B
Make text bold in the selected element of the PivotTable list
CTRL+U
Make text underlined in the selected element of the PivotTable list
CTRL+I
Make text italic in the selected element of the PivotTable list

Keys for following hyperlinks

To use the following shortcuts, first select the hyperlink cell or item.

ENTER
Follow the hyperlink
SHIFT+ENTER
Open the link in a new window

Keys for adding, editing, or deleting data

F2
Enter edit mode
The TAB key
Accept edit, move focus right, and then move focus down
ENTER
Accept edit, move focus down, and then move focus right
SHIFT+TAB
Accept edit, move focus left, and then move focus up
SHIFT+ENTER
Accept edit, move focus up, and then move focus left
ESC
Discard edit
DELETE
Delete all selected rows

Keys for working with Help

Keys for working in the Help window

F6
Switch between the navigation pane and topic pane
ALT+O
Display the Options menu to access any Help toolbar command
ALT+SPACEBAR
Display the program Control menu
ALT+F4
Close the Help window

Keys for working in the navigation pane

CTRL+TAB
Switch to the other tab
ENTER
Open or close a selected book, or open a selected Help topic
DOWN ARROW
Select the next book or Help topic
UP ARROW
Select the previous book or Help topic
SHIFT+F10
Display shortcut menu

Keys for working in the topic pane

ALT+RIGHT ARROW
Go forward to a Help topic you viewed previously
ALT+LEFT ARROW
Go back to a Help topic you viewed previously
The TAB key
Select the next hidden text or hyperlink, or Show All or Hide All at the top of a topic
SHIFT+TAB
Select the previous hidden text or hyperlink
ENTER
Perform the action for the selected Show All, Hide All, hidden text, or hyperlink
UP ARROW or DOWN ARROW
Scroll toward the beginning or end of a Help topic
PAGE UP or PAGE DOWN
Scroll toward the beginning or end of a Help topic in larger increments
HOME or END
Go to the beginning or end of a Help topic
CTRL+P
Print the current Help topic
CTRL+A
Select the entire Help topic
CTRL+C
Copy selection to the Clipboard
SHIFT+F10
Display shortcut menu


About the toolbar, field list, and Commands and Options dialog box

The PivotTable list toolbar

The toolbar in a PivotTable list appears at the top of the list and provides a set of commands that you can use to work with the data in the PivotTable list. Rest the pointer on each toolbar button to display the name of its command.

The PivotTable list toolbar

If you don't see the toolbar, the designer of the PivotTable list might have disabled it.

The PivotTable list field list

Use the field list to view the fieldsets and fields that are available from the source data and to add fields to the row, column, filter, data, and detail areas of the PivotTable list. You can scroll and click the expand indicators ( Plus box or Minus box boxes) next to a fieldset to show or hide fields. In the field list, the fields that are not bold are available but not yet displayed in the PivotTable list. The bold fields are already displayed.

Field List of PivotTable list or view

You might not be able to show the field list if the designer of the PivotTable list chose to disable it.

The Commands and Options dialog box

Use this dialog box to do common tasks, such as filtering data, formatting text, and showing or hiding elements. The Commands and Options dialog box includes some commands that aren't present on the toolbar or shortcut menus. The tabs displayed in the dialog box change as you select different parts of a PivotTable list. If you select a control on the Web page other than a PivotTable list or other Microsoft Office Web Component, the dialog box is hidden until you activate the component again.

You might not be able to show the Commands and Options dialog box if the designer of the PivotTable list chose to disable it.


Show or hide the toolbar of the PivotTable list

  1. Right-click the PivotTable list, and then click Commands and Options.
  2. Click the Behavior tab.
  3. Select or clear the Toolbar check box.


Show or hide the title bar of the PivotTable list

  1. On the PivotTable list toolbar, click Commands and Options [image], and then click the Behavior tab.
  2. Select or clear the Title bar check box.

Note To display the title bar, you must first add data to the PivotTable list.


Show or hide the field list of the PivotTable list

  • If the PivotTable list toolbar is displayed, click Field List [image] on the toolbar. Otherwise, right-click the PivotTable list, and then click Field List on the shortcut menu.


Show or hide the Commands and Options dialog box

Do one of the following:

  • If the PivotTable list toolbar is displayed, click Commands and Options [image] on the toolbar.
  • If the toolbar is hidden, right-click the PivotTable list, and then click Commands and Options.

Note If you leave the Commands and Options dialog box displayed while you select different parts of the PivotTable list or other Microsoft Office Web Components, the available sections and their contents change to reflect your current selection. If you select a part of the Web page other than an Office Web Component, the dialog box will be hidden.


Show or hide drop areas

When the drop areas are hidden and the PivotTable list does not have a field in the row, column, data, or filter area, you can't drag fields to these areas.

  1. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Behavior tab.

  2. Select or clear the Drop areas check box.

Note You will not see the drop areas if the PivotTable list is not bound to a data source.


About adding and removing fields

Depending on how the person who created a PivotTable list has set up the data, you might be able to display additional data or remove data that you don't want to view or summarize.

The designer of the PivotTable list determines the database to get the data from and the subset of that data to make available as the source data for the PivotTable list. From this source data, the designer selects the fields that are initially displayed in the PivotTable list and determines how the data is summarized.

You can't change which source data is available, but if fields are available in the source data that are not currently displayed in the PivotTable list, you can add these fields to display additional data.

For example, if the PivotTable list summarizes sales revenue, and the source data also includes the quantity sold of each product, you could add the Quantity field as a data field to summarize both revenue and quantity of products sold. If the PivotTable list summarizes products by type and salesperson, and a Region field is available, you could add that field to view sales by region. If you add the Region field as a filter field, you can filter the entire PivotTable list to display only data that applies to the regions you select.

Viewing the fields available to the PivotTable list

Use the field list to view the list of fieldsets and fields that are available from the source data, and to add fields to the row, column, filter, data, and detail areas of the PivotTable list. In the field list, the fields that are not bold are available but not yet displayed in the PivotTable list. The bold fields are already displayed. You can scroll and click the expand indicators ( Plus box or Minus box boxes) next to the fieldsets to show or hide fields in the field list.

Adding fields to the detail area

Use detail fields to display all of the available detail data and see what data is available to be summarized. When you add fields to the detail area, you display all of the detail records from the source data for the fields. Field names become column labels, and the detail records are displayed in rows below the column labels.

Example of detail fields

1 Detail fields

2 Detail data

If your PivotTable list is not based on source data from an OLAP database, you can add a calculated detail field that uses an expression to calculate detail values.

Adding fields to the row or column area

Use row and column fields to summarize and compare data. These fields display the unique items of data within a field down rows or across columns. The cell at each row and column intersection summarizes the data for an item.

Example of row and column fields

1 Column field

2 Column field item

3 Row field

4 Row field items

Adding fields to the data area

Data fields provide the values to be summarized in the PivotTable list. When you add a field to the data area, the values from the field are summarized for the row and column field items.

Example of a data field

1 Data field

2 Summarized data

Adding fields to the filter area

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Use filter fields to confine the view to a particular part of the available data. When you select an item in a filter field, data is displayed and calculated only for that item. For example, if you add a Region filter field, you can have the PivotTable list display and calculate data for the regions you select.

Filter field example

1 Filter field

2 Filter field item

3 Summary for the selected region

Adding total fields

You can create new data fields by adding total fields to the PivotTable list. Total fields provide summarized data for use as data fields. For example, if your source data includes a Sales field that shows the amount of each sale, you could add a Sum of Sales total field to calculate the total sales, or a Count of Sales total field to calculate how many units were sold.

You can also add a calculated total field that uses an expression to calculate total values.

Removing fields

To eliminate data that you don't want to see or summarize, you can remove fields from the PivotTable list layout. Removing a field does not remove it from the available source data. A field that you remove is no longer displayed in the PivotTable list, but it remains available in the field list for viewing.

Moving fields

After you add a field to a PivotTable list, you can move it to other areas. When you move a field, you change the layout of the PivotTable list, and you change the data that's displayed and the summaries that are calculated.

Impact of adding and removing fields on filtering

Filter settings are retained when you remove a field. If you remove a field and later add the field back to the layout, the same items are again hidden.

Viewing information about fields

You can view information about fields in a PivotTable list, including information about the field in the source database from which the data is taken and the type of data that the source database supplies for the field.

Restrictions on adding and removing fields

If the designer of the PivotTable list has set restrictions on adding and removing fields in the browser, you won't be able to add or remove fields. If your PivotTable list is based on source data from an OLAP database, such as one created by using Microsoft SQL Server OLAP Services, you'll notice that you can't add fields to the detail area. You'll also notice that some fields can be added only to the row, column, and filter areas. Other fields, listed under Totals in the field list, can be used only as data fields.


Add or remove a field

Add a field

Add a field to the row, column, data, filter, or detail area

  1. On the toolbar in the PivotTable list, click Field List [image].

  2. Use the scroll bars and the expand indicators ( Plus box and Minus box boxes) to find the field you want to add to the PivotTable list.

  3. Click the field, and in the lower-right corner of the field list, select the area you want to add the field to.

  4. Click Add to.

Notes

  • If Add to becomes unavailable when you select Row Area, Column Area, Data Area, or Filter Area, the field you selected might not be available for use as a row, column, data, or filter field. If your PivotTable list is based on source data from an OLAP database, only the fields that are not listed under Totals in the field list can be used as row, column, data, or filter fields.

  • If Add to becomes unavailable when you select Detail Data, detail data might not be available from the source data. For example, if your PivotTable list is based on source data from an OLAP database, often only summary data, rather than detail data, is available.

Add a total field

You can add total fields to the data area, but not to the row, column, detail, or filter area.

  1. Click the field that contains the values you want to summarize.

  2. On the PivotTable list toolbar, click AutoCalc [image], and then click the summary function you want.

Note The AutoCalc button is unavailable when the source data for the PivotTable list does not allow you to add total fields — for example, when the source data is from an OLAP database.

Add a calculated total or detail field

You can add a total or detail field that uses an expression to calculate data.

  1. On the PivotTable list toolbar, click Calculated Totals and Fields [image].

  2. On the menu that appears, do one of the following:

    • To create a total field, click Create Calculated Total.

    • To create a detail field, click Create Calculated Detail Field.

  3. In the Calculation tab of the Commands and Options dialog box, type the expression you want, and then click Change.

Tip

To add field references, select a field that you want to use from the Insert Reference To box, and then click the Insert Reference to button.

Notes

  • To edit the expression that is being used in a calculated field, select the field, edit the expression, and click Change on the Calculation tab in the Commands and Options dialog box. The field values will be automatically recalculated using the new expression.

  • By default, data fields that summarize numeric data use the Sum summary function. Fields that summarize other types of data use the Count summary function.

Remove a field

When you remove a field from the layout of a PivotTable list, data from the field is no longer displayed; but the field remains available in the field list for viewing.

  1. Click the field label of the field that you want to remove from the PivotTable list.
  2. Drag the field outside the PivotTable list, until the pointer changes to Pointer with red X.

Note You can delete total fields and calculated detail fields from both the PivotTable list and the field list by right-clicking the field and then clicking Delete on the shortcut menu.


About changing the layout of a PivotTable list

You can change how the data in a PivotTable list is summarized by changing its layout. By using a different layout, you can calculate and compare summarized values for different elements of your data, or you can display summaries for a subset of the data.

Using the drop areas

You change the layout by moving the fields to predefined drop areas within the PivotTable list.

Example of drop areas

1 Drop areas

2 Detail area

After you move fields to the drop areas so that the drop area captions are covered up, you can still drag additional fields to the areas. You can also use the Add to button in the field list to add fields to different drop areas.

Moving fields to the row and column areas

When you move a field to the row area, the unique items of data within the field are displayed down the rows of the PivotTable list. When you move a field to the column area, the unique items of data are displayed across the columns.

Moving fields to the detail area

To view all of the detail records from the source data for the PivotTable list, you can move fields to the detail area. If you don't want to rearrange the fields or change the summarized data, you can view details alongside the summarized data.

Moving fields to the data area

Data fields provide the values to be summarized in the PivotTable list. When you move a field to the data area, the values from the field are summarized for the row and column field items.

Moving fields to the filter area

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Filter fields allow you to confine the view to a particular part of the available data. For example, when you move a Product field to the filter area, you can have the PivotTable list display a summary for one product at a time.

Moving row or column fields to a higher or lower level

When a PivotTable list has multiple row and column fields, the fields that are closest to the summarized data are referred to as inner fields. The other fields are outer fields.

Example of inner and outer row fields

1 Outer row field

2 Inner row field

3 Outer field items

4 Items for the inner field are repeated for each item in the outer field.

Adding and removing fields

The layout of a PivotTable list does not have to include all of the fields that are available from the source data. If more data is available, you can add fields to the PivotTable list layout. For example, if the PivotTable list summarizes sales revenue, and the source data also includes sales quantities, you might add the Quantity field as a data field to summarize both revenue and quantity of products sold. You can also remove fields from the PivotTable list layout that you no longer want to see.


Move a field or column

  1. Display the drop areas for the PivotTable list.

    How?

    When the drop areas are hidden and the PivotTable list does not have a field in the row, column, data, or filter area, you can't drag fields to these areas.

    1. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Behavior tab.

    2. Select or clear the Drop areas check box.

    Note You will not see the drop areas if the PivotTable list is not bound to a data source.

  2. Do one or more of the following:

    Move a field to the row or column area

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.
    2. Do one of the following:

      • To make the field a row field, click and drag the field label to the row area on the left side of the PivotTable list, until the pointer changes to Pointer with row area highlighted.

      • To make the field a column field, click and drag the field label to the column area near the top of the PivotTable list, until the pointer changes to Pointer with column area highlighted.

    Move a field to the detail area

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.

    2. Click and drag the field to the detail area at the bottom right of the view, until the pointer changes to [image].

    Move a field to the data area

    You can move fields from the row, column, and filter areas to the data area only if the PivotTable list is based on data from a source other than an OLAP database.

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.
    2. Click and drag the field to the data area at the lower-right side of the PivotTable list, until the pointer changes to Pointer with sigma.

    Note The field you drag to the data area is summarized based on the type of data in the field. Fields that contain numeric data are summarized with the Sum summary function. Fields that contain other types of data are summarized with the Count summary function. You can change the summary function for some fields.

    Move a field to the filter area

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.
    2. Click and drag the field to the filter area at the top of the PivotTable list, until the pointer changes to Pointer with filter area highlighted.

    Move a row or column field to a higher or lower level (Change the column order of fields)

    1. Point to the field label of the field you want to move to a different level until the pointer changes to Pointer with four-headed arrow.
    2. Do one of the following:

      • To move the field to a higher level, click and drag the field label to the left and drop it where you want.

      • To move the field to a lower level, click and drag the field label to the right and drop it where you want.


Set the size of a PivotTable list and its elements

Automatically resize the control on a Web page to fit its contents

  1. On the PivotTable list toolbar, click Commands and Options [image], and then click the Behavior tab.
  2. Select the Auto fit the control check box.

  3. To prevent the PivotTable list from expanding beyond a specified size, type a value in pixels in the Maximum height box and/or the Maximum width box.

Note Scroll bars appear within the PivotTable list when more data is available than will fit in the area.

Change the width of a column in the row, column, detail, or data area

  1. Point to the right boundary of the field label until the pointer changes to Pointer with perpendicular double-headed arrow.
  2. Do one of the following:

    • To increase or decrease the width, drag the boundary until the column is the width you want.

    • To make the column width fit the contents, double-click the column boundary.

Note You cannot adjust the height of the rows in a PivotTable list.


About displaying totals in a PivotTable list

PivotTable lists use two different types of totals to display summarized values. The first type, a total field, provides the summarized values for the PivotTable list. When your PivotTable list contains a total field, you can also display the second type — subtotals and grand totals — for the row and column fields.

Total fields

Total fields are data fields that are either provided in the source data or created by you to summarize the data from other fields in the source data. For example, if the source data has a Sales field that records the amount of each individual sale, a Sum of Sales total field could add up the sales figures for each category in the PivotTable list, or a Count of Sales total field could tally the number of sales for each category. You can also create a total field that uses an expression.

In the field list, the total fields are displayed at the top, under Totals. The Sum of Sales and Count of Units fields are available for inclusion in the PivotTable list as total fields.

Field list with total fields

Adding total fields

In some PivotTable lists, the total field values might be precalculated on the database server that supplies the source data. If this is the case, the commands to add new total fields, move total fields to the detail area, and change the summary function used to calculate the values in a total field are unavailable. To find out what type of source data is used in the PivotTable list you're viewing, contact the designer of the PivotTable list.

If the total fields available in the field list don't provide the summaries you want, and the source data allows it, you can add new total fields to a PivotTable list. Most often, you will add total fields to the data area. For example, in the following PivotTable list, the Sum of Sales total field has been added to the data area to display summary data for each sport and quarter.

PivotTable list with total field

1 The Sum of Sales total field.

A total field uses a summary function, such as Sum or Count. Depending on the type of source data, you can also use more than one summary function to summarize the data in the same field by adding more total fields, each using a different summary function.

You can also add a calculated total field that uses an expression. For example, to see the year-to-date sales figures, you could create a total field that uses the following expression:

Sum (YTD(), [Measures].[Sales])

The following PivotTable list shows sales and year-to-date sales figures.

PivotTable list with calculated total field

Changing the orientation of total fields

You can change the orientation of total fields in the PivotTable list. Displaying the fields in the row orientation lists them down the first column to the right of the row fields. Displaying them in the column orientation lists them across the first row below the column fields.

Showing total values as percentages

You can also show total values as a percentage of a row, column, grand total, or as a total of the item's parent in the row or column axis.

PivotTable list showing total values as percentages

1 These values will be used if you choose to show total values as a percentage of row total.

2 These values will be used if you choose to show total values as a percentage of column total.

3 These values will be used if you choose to show total values as a percentage of the total value of the item's parent in the row axis.

4 This value will be used if you choose to show total values as a percentage of the grand total.

Subtotals and grand totals

You can display subtotals for any row or column field in the PivotTable list if the list includes at least one total field and more than one row field or more than one column field. If you choose to display subtotals for a field, grand totals are also selected for display. In addition, subtotals and grand totals are displayed for every total field in the PivotTable list where it intersects the row or column field you select.

In the following example, subtotals and grand totals are displayed for both row fields by using the values in the Sum of Sales total field.

PivotTable list subtotal and grand total

1 Grand total

2 Subtotal

In the example above, when subtotals and grand totals are displayed for the Sport and Quarter fields, the PivotTable list includes subtotals for the Quarter field and a grand total for the Sport field.

If you move a field for which subtotals or grand totals are displayed out of the row or column area, the subtotals or grand totals become hidden, but the setting is retained. The subtotals or grand totals are redisplayed if you move the field back to the row or column area.

When you hide subtotals and grand totals for a field, they remain hidden regardless of where you move the field, even if you remove the field from the PivotTable list and later add it back.

Depending on the source data and the type of data you're viewing in the PivotTable list, you might want subtotals and grand totals to include or omit the values for items of data you've hidden by filtering. You can specify that all subtotals and grand totals either include or exclude any hidden data.


Summary functions available in a PivotTable list

The following table lists the summary functions provided by the AutoCalc [image] command that you use to create total fields.

Function | Description
Sum | Calculates the sum of the values.
Count | Tallies the number of values. Empty values, such as zero-length strings (strings that contain no characters), are included in the count, but null values (values that indicate missing or unknown data) are not included.
Min | Returns the smallest value in the field being summarized.
Max | Returns the largest value in the field being summarized.
Average | Returns the average of the values in the field being summarized.
Standard Deviation | Returns the standard deviation of the values in the field being summarized, assuming the values are a sample of a larger population.
Standard Deviation Population | Returns the standard deviation of the values in the field being summarized, assuming the values are the entire population.
Variance | Returns the variance of the values in the field being summarized, assuming the values are a sample of a larger population.
Variance Population | Returns the variance of the values in the field being summarized, assuming the values are the entire population.

Note The summary functions available for a field depend on the type of data in the field. For example, if a field contains text, you cannot use Sum, Min, or Max, but you can use Count for that field.


Show or hide totals for a field

  1. Click the row or column field for which you want to show or hide subtotals and grand totals.
  2. To show totals, on the toolbar in the PivotTable list, click the Subtotal button [image] so that it is selected. To hide totals, click the button again.

Notes

  • Subtotals and grand totals are not displayed for fields in the detail area (the area where all of the individual records can be displayed).
  • When you choose to display them, subtotal and grand total values are displayed for every total field in the PivotTable list. You can't exclude individual total fields.
  • Subtotal and grand total calculations can include or exclude items of data that are hidden by filtering.


Customize a total field

Change the orientation of total fields

This procedure affects total fields in the PivotTable list. It has no effect on subtotals or grand totals.

  1. Click the title bar of the PivotTable list.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Report tab.
  3. Under Display totals as, select one of the following:
    • Column headings To display total fields across the first row below the column fields or in the top row if there are no column fields.
    • Row headings To display total fields down the first column to the right of the row fields or in the leftmost column if there are no row fields.

Show totals as percentages

  • Right-click the total field whose values you want to view as percentages, point to Show As, and then click one of the following:
    • Percent of Row Total To display the values as a percentage of the total value of each row.
    • Percent of Column Total To display the values as a percentage of the total value of each column.
    • Percent of Parent Row Item To display the values as a percentage of the total value of the item's parent in the row axis.
    • Percent of Parent Column Item To display the values as a percentage of the total value of the item's parent in the column axis.
    • Percent of Grand Total To display the values as a percentage of the PivotTable list's grand total value.


Include or exclude hidden data when calculating totals

This procedure affects all subtotals and grand totals in the PivotTable list.

  1. Click the title bar of the PivotTable list.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Report tab.
  3. Under Calculate totals based on, do one of the following:
    • To include hidden data, select All items (including hidden items).

    • To exclude hidden data, select Visible items only.

Notes

  • This setting affects data hidden by filtering row and column fields; data hidden by using filter fields is always excluded from totals.

  • When you choose to include hidden data, asterisks (*) appear next to the captions of subtotals and grand totals to note that the total values include hidden items as well as displayed items.


Edit a calculated field

  1. Select the calculated field whose expression you want to edit.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Calculation tab.
  3. Edit the expression, and then click Change.

    The data values will be automatically recalculated using the new expression.

Tip

To add field references, select a field that you want to use from the Insert Reference to drop-down box, and then click the Insert Reference To button.


About hyperlinks

A hyperlink is a pointer from one object to another. The destination is frequently another Web page, but it can also be a picture, an e-mail address, a file (such as a multimedia file or Microsoft Office document), or a program. The hyperlink itself can be displayed as text or as a picture.

You can use hyperlinks to do the following:

  • Navigate to a file or Web page on a network, intranet, or Internet

  • Send an e-mail message

  • Start a file transfer, such as a download or an FTP process

When you point to text or a picture that contains a hyperlink, the pointer becomes a hand Pointer in the shape of a hand, indicating that it is something you can click. When you click the hyperlink, the destination is displayed, opened, or run, depending on the type of destination. For example, a hyperlink to a sound file opens the file in a media player, and a hyperlink to a Web page displays the page in a Web browser.

Parts of a hyperlink address

A hyperlink address can have up to four parts that are separated by the number sign (#):

Part | Description
displaytext | The text that you see in the cell when you choose to display data as hyperlinks. For example, you might want to display a descriptive name for the World Wide Web site or object specified by the address and the subaddress. This is an optional field.
address | An absolute or relative path to a target document. An absolute path is a fully qualified URL or UNC path to a document. If you don't specify the address, displaytext is assumed to be the address.
subaddress | The location in the file or page. For example, you might want to point to a particular paragraph in the target document.
screentip | The text that appears when you rest the pointer on a hyperlink. This is an optional field.

Showing data as hyperlinks

You can choose to display data as hyperlinks in a row, column, or detail field by changing the field's property settings. When data is displayed as hyperlinks, you can click the hyperlinks to navigate to other Web pages or objects.

Grouping on fields that contain hyperlinks

The result of grouping on hyperlink fields depends on how the corresponding source data field is defined. If the source data field is a Character Large Object (CLOB), when you group on the hyperlinks you will see two group members — one for blank values and one for all non-blank values. If the source data field is defined as scalar, such as varchar, the values will be grouped on individual hyperlink values. The uniqueness of the member is based on the displaytext and the address parts of the hyperlink.


Show data as hyperlinks

  1. Click the row, column, or detail field that contains hyperlinks.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Format tab.
  3. Select the Display as hyperlink check box.


Show or hide ScreenTips

  1. Click the title bar.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Report tab.
  3. Select or clear the ScreenTips check box.

Note If you hide the ScreenTips, you won't see the item properties that are set to display as ScreenTips. Item properties set to display in the report will still be visible.


About displaying details and items

The levels of items and the amount of detail that are initially displayed in a PivotTable list are determined by the designer of the PivotTable list. You can change the PivotTable list to display any amount of detail that's available in the source data. You can display all of the available detail and item levels, only high-level summaries, or any level of detail in between, and you can display selected detail for items of data that you want to emphasize.

Displaying detail data

The items at the lowest level display Plus box and Minus box to their right. The items at the lowest level in the column area display Plus box and Minus box below the item captions. You can show or hide details for a specific item by clicking Plus box or Minus box.

Items at the lowest level

You can display detail data only if it is available in the source data and the designer of the PivotTable list has not restricted access to it in the browser. For example, OLAP databases often provide only summary data rather than detail data.

Automatically showing or hiding all details

By default, detail data is not initially displayed in the browser. But you can change the setting to automatically display detail data for all items if you prefer.

Displaying lower-level items

When the row or column area has more than one field, higher-level items will display expand indicators ( Plus box and Minus box boxes) to their left. Clicking the expand indicators will show or hide the items in the next lower level. If the innermost field on the PivotTable list is not the lowest in the item hierarchy, clicking the expand indicator of the innermost field will automatically add the next lower level of items to the PivotTable list.

You can display lower-level data for an entire field, or you can display it on an item-by-item basis, expanding only the items you want to see. Items in the lowest level will not display expand indicators. For example, if Country and City fields are in the row area, the items in the Country field will display expand indicators like this:

Example of expand indicators

1 Expand indicators

Automatically showing or hiding all items

After loading the PivotTable list, you can change the setting so that all items are either expanded or collapsed by default.

Hiding expand indicators

After you have expanded the levels to the extent you want, you can hide all the expand indicators in the PivotTable list.

Showing or hiding items that have no data

By default, a PivotTable list does not display items for which the source data provides no values. For example, if you're showing sales for each month and there were no sales in April, the Month field would not have an April item. If you want to include items for all 12 months, with blank cells for the months with no sales, you can display items that have no data.


Show or hide expand indicators next to items

  1. Click the title bar of the PivotTable list.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Behavior tab.
  3. Select or clear the Expand indicator check box.

Notes

  • This setting does not affect the Plus box and Minus box boxes that appear to the right of the items at the lowest level.
  • Items at the lowest level in a row or column area don't have expand indicators to their left.


Show or hide detail data for an item or field

  1. Do one of the following:

    • To show or hide detail data for a cell, or an item at the lowest level, select the item or cell.

    • To show or hide detail data for a row or column field, select the row or column field.
  2. Click Show Details [image] or Hide Details [image] on the PivotTable list toolbar.

Note To show or hide lower-level items, select the row or column field or item, and then click Expand [image] or Collapse [image] on the toolbar.


Show or hide items

  • To show or hide the lower-level items of a specific item in the row or column axis, click the expand indicator ( Plus box or Minus box box) next to the item.
  • To show or hide the lower-level items for all items in a row or column field, click the field label, and then click Expand [image] or Collapse [image] on the PivotTable list toolbar.

Notes

  • If the Plus box or Minus box box is not visible next to the item, select the Expand indicator check box on the Behavior tab of the Commands and Options dialog box. If after selecting the check box you don't see an expand indicator next to an item, the item might be at the lowest level available for the source data.

  • To show or hide details, select the item or field, and then click Show details [image] or Hide details [image] on the toolbar.


Show or hide items that have no data

  1. Click the title bar of the PivotTable list.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Report tab.
  3. Select or clear the Empty rows and Empty columns check boxes.


Automatically show all detail data

  1. Click the title bar of the PivotTable list.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Behavior tab.
  3. Set Expand details by default to Always.

Notes

  • To hide detail data for all items, select Never in the Expand details by default list. If you set Expand details by default to Automatic, data from tabular sources will be expanded by default, but data from OLAP databases will be hidden.
  • To automatically show all items in a PivotTable list, on the Behavior tab, set Expand items by default to Always.
  • If detail data is not available, changing the Expand details by default setting has no effect.


Automatically show all items

  1. Click the title bar of the PivotTable list.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Behavior tab.
  3. Set Expand items by default to Always.

Notes

  • To hide all items, select Never in the Expand items by default list. If you set Expand items by default to Automatic, fields from tabular sources will be expanded by default, but fields from OLAP databases will be hidden.

  • To automatically show all details in a PivotTable list, on the Behavior tab, set Expand details by default to Always.


Add, edit, or delete data

If you can't add, edit, or delete data in a PivotTable list, it means one of the following:

  • The designer of the Web page has set properties to prevent you from adding, editing, or deleting data.
  • The field you are trying to edit is a read-only field, and does not support edits.
  • The underlying table does not have a primary key.

Add data

  1. Click a cell in the blank row marked with an asterisk (*).
  2. Type the data you want in the first field, and then press TAB to move to the next field.
  3. Do one of the following:
    • Press ENTER to save the record and move to the next record.
    • Press ESC to leave the record without saving it.

Edit data

  1. Click the cell that you want to edit.
  2. Type the new value.
  3. Do one of the following:
    • Press ENTER to save the record and move to the next record.
    • Press ESC to leave the record without saving it.

Delete data

  • Do one of the following:
    • To delete the contents of a cell, click the cell and press DELETE.
    • To delete a record, click the leftmost cell of the record, and then press DELETE.
    • To delete multiple records, click the leftmost cell of the record, drag the mouse pointer to the last cell of the last record, and then press DELETE.

Notes

  • When entering data for a field that has a number format applied to it, you need not type formatted data. When you leave the cell, the format will be automatically applied to the cell contents.
  • Only detail cells can be edited directly in the PivotTable list. To change a caption or the title, use the Commands and Options dialog box.


About item properties

You can display additional information about items by displaying the custom properties of a row or column field. For example, if the database administrator has made available two custom properties, Manager and Phone Number, for the Store field, you can choose to display the property values for each item in the Store field, either in the PivotTable list or as ScreenTips. You cannot create custom properties in the browser.

The following illustration shows how the Employees report will look with the custom properties Education and Yearly Income displayed in the report.

PivotTable list with custom properties

The following illustration shows the Education property displayed in the report and the Yearly Income property displayed in the ScreenTip.

PivotTable list with custom properties

You can also format the property captions and values, and specify the order in which the properties are displayed in the report or ScreenTip.

Non-OLAP data sources do not support custom properties.


Show or hide item properties

You can show item properties of row and column fields either in the report or as ScreenTips.

  1. Click the field whose custom properties you want to show or hide.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Item Properties tab.
  3. In the Item properties list, select the property for which you want to change settings.
  4. In the Display in list, select one of the following:
    • Report To show the property caption and value next to the item names.
    • ScreenTip To show the property caption and value as part of each item's ScreenTip.
    • All To show the property caption and value in the report as well as in the ScreenTips.
    • None To hide the property caption and its value.
  5. Repeat steps 3 and 4 for each custom property of the selected field.

Notes

  • If you choose to show the properties as ScreenTips, ensure that the ScreenTips check box on the Report tab in the Commands and Options dialog box is selected.
  • You can change the order in which the custom properties are displayed in the report or ScreenTip.
  • You can format the caption and value of a custom property. The format does not affect the property when displayed in a ScreenTip.
  • Non-OLAP data sources do not support custom properties.


Specify the display order of item properties

  1. Click the field whose item properties you want to order.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Item Properties tab.

    The order in which the properties are listed in the Item properties box is the order in which the properties are displayed in the report or ScreenTips.

  3. Select the property that you want to display at the top, and click Up [image] until the property appears as the first item in the list.
  4. Order the rest of the properties by clicking Up [image] or Down [image].

Note If new properties are added after you order the list, the new properties will appear at the end of the list until you reorder them.


About formatting a PivotTable list to improve appearance or readability

Formatting elements

You can format row, column, total, or detail fields, the title bar and drop area captions, and custom properties of row or column fields in the following ways:

  • Setting text formats You can set the font, font size, and color for text, and make text bold, underlined, or italic. These settings are separate from your browser's settings for fonts and colors, which have no effect on PivotTable lists.

  • Aligning text in a cell You can display values so that they are left-aligned, right-aligned, or centered horizontally in the cells. You cannot change the values' vertical alignment.

  • Setting background colors You can change the background color for cells. These settings are separate from your browser's settings for colors, which have no effect on PivotTable lists.

  • Changing number formats You can change the formats in which numbers, dates, and times in a field are displayed to any of several predefined number formats. In Microsoft Visual Basic or in a script, you can assign custom number formats to the data.

    For example, you can display a number as currency or as a percentage, and you can display a date in long format, with the day and month spelled out, or in abbreviated format. Changes to the number format do not change the actual values, only how the values are displayed.

Formats that you apply to one element might affect similar elements. For example, if you change the text color for an item, all other items in the same field are formatted with the new text color.

Formatting an entire PivotTable list

You cannot set the formats for an entire PivotTable list in one operation. You need to format each element in the PivotTable list individually.

To customize the appearance of every element in a PivotTable list, you might need to change the layout temporarily to format elements that you don't plan to include in your initial presentation of the data. For example, to establish formats for a field in the detail area when the initial layout doesn't include that detail field, you'll need to move a field to the detail area, format it, and then move the field back to its previous area. When users move fields to different areas of the PivotTable list in the browser, the fields and their data take on the formatting you've established for fields and data in those areas.

For more control over formatting, including more text alignment options, custom number formats, and print formatting options, export the PivotTable list to Microsoft Excel. In Excel, your PivotTable list becomes a PivotTable report that you can format, set up for print, and save in a workbook file. For more information about working with PivotTable reports, see Excel Help.


Format a PivotTable list and its elements

  1. Select the element that you want to format.

    How?

    Select the entire PivotTable list

    • If the title bar is displayed, click the title bar. Otherwise, press CTRL+A.

    Note You can't display the title bar if the PivotTable list is not bound to a data source.

    Select one or more data cells

    • To select a single cell, click it. To select a group of cells, click and drag the mouse pointer until all the cells you want are selected.

    Select a row, column, or filter field

    • Click the field label.

    Note Selecting a row or column field also selects all of the items of data in the field.

    Select a detail field

    • Click the field label. If detail data is displayed for several items so that the field label is displayed more than once, clicking one instance of the field label selects all instances.

    Select a total field

    • Click any field label for the total field.

    Select one or more items

    • To select a single item, click the item label.

    • To add more items to the selection, press and hold the CTRL key when clicking the item labels.

    • To select a range of items, click the label of the first item, press SHIFT, and then click the label of the last item.

    Note Selecting an item selects all of the data for that item.

    Select a subtotal or grand total

    • Click the subtotal or grand total.

    Note Selecting a subtotal or grand total also selects all of the data for the subtotal or grand total.

    Select the title bar or a drop area

    1. Click the title bar.

    2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Captions tab.

    3. In the Select caption list, select the item you want.

    Select a custom property of a row or column field

    1. Click the row or column field, or one of its items.

    2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Item Properties tab.

    3. In the Item properties list, select the property you want.

      Note If the selected field does not have any custom properties, the Item properties list will be empty.

      You can set the caption, number format, and display location settings for the selected property. The formats you select affect only the selected property.

    4. To set the font and style settings of a property, select Property caption or Property value from the Format list.

    Note Font and text style settings that you apply to a custom property affect all custom properties in the PivotTable list, but only when they are displayed in the report. The formatting is ignored when the custom property is displayed in the ScreenTip.

  2. Click Commands and Options [image] on the PivotTable list toolbar.
  3. Do one of the following:
    • To format a drop area, click the Captions tab.
    • To format an item property of a row or column field, click the Item Properties tab.
    • To format all other elements of a PivotTable list, click the Format tab.
  4. Do one or more of the following:

    Specify font and style settings

    • To make the text bold, click Bold [image].

    • To make the text italic, click Italic [image].

    • To underline the text, click Underline [image].

    • To change the text color, click the arrow next to Font Color [image], and then click the color you want.

    • To change the font, click a different font in the font list.

    • To change the font size, select a size from the list next to the font list.

    Notes

    • The fonts and font sizes available in the lists are determined by the fonts you have installed on your computer.

    • You cannot set the font and text style for an entire PivotTable list in one operation.

    Align text

    • Click the alignment you want: Align Left [image], Align Center [image], or Align Right [image].

    Note You cannot set the text alignment for an entire PivotTable list in one operation. You cannot change the horizontal alignment of row, column, or filter field labels.

    Set background color

    • Click the arrow next to Fill Color [image], and then click the color you want.

    Note You cannot set the background color for an entire PivotTable list in one operation. You cannot change the background color of the drop areas.

    Change the number format of a field, total, or item property of a row or column field

    • In the Number list, click the format you want.

    Note The number format of a field does not affect the number format of other row, column, filter, or total fields. The number format of an item property does not affect other item properties.

Note You can't format the contents of a ScreenTip. Therefore, the formats you apply to an item property apply to the property only when it is displayed in the report.


Format the contents of a PivotTable list to read right to left or left to right

When you select right-to-left display, the elements of the PivotTable list are displayed as a mirror image of the default left-to-right display: The drop areas are on the upper-right side of the PivotTable list, row fields are in the rightmost columns, and items in column fields are listed from right to left.

  1. Click the title bar of the PivotTable list.
  2. Click Commands and Options [image] on the PivotTable list toolbar, and then click the Behavior tab.
  3. Select or clear the Display right to left check box.


Number formats for PivotTable lists

Some of the content in this topic may not be applicable to some languages.

The regional settings in the Windows Control Panel determine how number, currency, date, and time formats are displayed. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.

The following table lists the number formats supported in a PivotTable list:

Format | Effects
General | Displays a number right-aligned, with no thousands separator, and preceded by a minus sign if negative. Displays up to 11 digits, or 10 digits with a decimal point, rounding additional digits to the right of the decimal point. Displays numbers with more than 11 digits to the left of the decimal point in scientific format. Displays text left-aligned.
General Date | Displays a number as a date and optional time. For example, 8/31/99 05:54 AM. The fractional part of the number is the time. If a number has no fractional part, only a date appears. If a number has only a fractional part and no integer part, only a time appears.
Long Date | Displays a date according to the Microsoft Windows regional setting for the long date format. For example, Thursday, January 25, 2001.
Medium Date | Displays a date using the abbreviated month names and hyphens (-) to separate month, day, and year. For example, Jan-25-2001.
Short Date | Displays a date according to the Windows regional setting for the short date format. For example, 1/25/2001.
Long Time | Displays a time according to the Windows regional setting for the long time format. For example, 8:45:36 PM.
Medium Time | Displays a time in 12-hour format with an AM or PM designator, omitting the seconds. For example, 8:45 PM.
Short Time | Displays a time in 24-hour format, omitting the seconds. For example, 20:45.
Currency | Displays a number according to the Windows regional setting for currency. For example, with the default United States English regional settings, $2,532.75.
Euro Currency | Displays a number as euro currency, with a thousands separator and two decimal places. The placement of the euro sign and the decimal places are based on the regional settings for currency in Windows Control Panel. For example, with the default United States English regional settings, [image] 12.35.
Fixed | Displays a number with two decimal places. For example, 68.30.
Standard | Displays a number according to the Windows regional settings for numbers. For example, with a comma as the thousands separator and a period as the decimal symbol, 1,800.00.
Percent | Displays a number multiplied by 100, with two decimal places and a percent sign. For example, the value 0.8914 would be displayed as 89.14%.
Scientific | Displays a number in exponential notation. For example, 1.25E+10.
Yes/No | Displays No if the number is 0; displays Yes for any other number.
True/False | Displays False if the number is 0; displays True for any other number.
On/Off | Displays Off if the number is 0; displays On for any other number.

Note In Microsoft Visual Basic or in a script, you can assign custom number formats to the data. You can also type custom formats available in the PivotTable list object model in the Number box.


About sorting data

Sort in ascending or descending order

You can sort a sales column in ascending order or an employee name column in descending order.

You can also sort the PivotTable list separately for each column. The order in which you sort the columns determines how the data will be organized.

Example of data sorted by two columns

In this example, the list is organized by salesperson. The Product column was sorted first so that the products would be in alphabetical order; the Salesperson column was sorted second.

When sorting multiple columns, first determine the order in which you want to sort the columns, and then work backward, sorting the outermost column last. For example, if you're sorting two columns, determine which is the outer column and which is the inner column. Sort the inner column first, and then sort the outer column. To list the products in alphabetical order for each salesperson, you would first sort the inner Product column and then sort the outer Salesperson column.

Define a custom sort order

You can define a custom order for a row or column field when sorting in ascending or descending order does not meet your requirements. For example, you might want to show the values in the Title column ordered on the basis of the seniority of the title. If the column had three values — Vice President, General Manager, and Manager — sorting in ascending or descending order will not show the data in the order you want. In this case, you might want to explicitly define the order of the items for the Title column.

If items are added to a field after you define a custom sort order, the new items will appear at the bottom of the field until you rearrange them.


Sort data in a field

Sort in ascending or descending order

You can sort data that is displayed in natural or custom order in ascending or descending order.

  1. Do one of the following:

    • To sort the data in a field based on the item labels, click the label of the field that contains the data you want to sort.

    • To sort the data in a field based on the summary values in a column, click a cell in the column that contains the values that you want to sort on.

    • To sort the data in a field based on subtotal or grand total values, click a subtotal or grand total value for the field that contains the data you want to sort.

      Note If you click a grand total value in the bottom row, the columns will be sorted instead of the rows.

  2. On the PivotTable list toolbar, click Sort Ascending [image] or Sort Descending [image].

Note To sort on multiple fields, start by sorting the innermost field first and work backward, sorting the outermost field last.

Specify a custom sort order

  1. Select the item you want to move.
  2. Drag the selected item and drop it over the item above which you want to place it.
  3. Repeat steps 1 and 2 until you have the items in the order you want.

Note If a field with custom sorting has a parent field, each parent member will display the lower-level members in custom order.

Notes

  • The order in which numbers, text, and special characters are sorted depends on the source data and your regional settings in Microsoft Windows Control Panel.
  • To switch from ascending or descending sort to custom sort, click Sort Ascending [image] or Sort Descending [image] so that the button is not selected.


Remove sort order

Removing ascending or descending sort order will return the data either to its natural order, or if a custom order was specified, to the custom order.

  • Click the Sort Ascending button [image] or the Sort Descending button [image] on the PivotTable list toolbar so that the button is not selected.


About grouping items

You can group items in a row or column field in two ways.

Grouping on intervals

You can group items by specifying the type of interval and the size of the interval. For example, you can group a field with date values into different months, and specify the interval as 2, to create groups such as Jan-Feb, Mar-Apr, and so on.

The type of interval you can specify for a field depends on the data type of the field.

  • Numeric fields You can group in intervals of any integer, such as 1, 2, 5, 100, and so on. For example, you can group the EmployeeID field into groups of 1-100, 101-200, and so on.
  • Date and time fields You can group in intervals such as days, weeks, and quarters. For example, you can group the values in the ShippedDate field into months to show data for orders shipped in January, orders shipped in February, and so on.
  • Text fields You can group based on the first n characters of the individual items. For example, you can group the LastName field by first letter to create groups, such as A, B, and so on.

You can specify the start and end values for the grouping range. For example, if you specify the start range as 01-Jul-1999 while grouping the ShippedDate field in weekly intervals, the following groups will be created:

<01-Jul-1999, 01-Jul-1999, 08-Jul-1999, ...

Dates prior to 01-Jul-1999 are grouped into a single group titled <01-Jul-1999.

If you also specify the end value as 31-Dec-1999, the following groups will be created:

<01-Jul-1999, 01-Jul-1999, 08-Jul-1999, ... 31-Dec-1999, >31-Dec-1999

Dates that fall after 31-Dec-1999 are grouped into a single group titled >31-Dec-1999.

Custom grouping

You can randomly select items from a row or column field and group them into higher-level groups. For example, you can select from the Promotions row field all the promotions that run for a specific period and create a group. This would add a new row field named Promotions 2 above the Promotions row field with two members: Group1 and Other. You can change the caption of Group1 to Fixed and Promotions 2 to Category in the Commands and Options dialog box.

The Other group will contain all items that you did not include in the Fixed custom group. You can then select all the popular promotions from the Other group and create a new custom group that will be captioned Popular. After you do this, the Category field will have three members: Fixed, Popular, and Other.

The following illustration shows how the PivotTable list will look after the captions of the custom field and groups have been changed.

PivotTable list or view with custom groups

1 A custom group field. Contains custom groups as its items and appears as the parent of the field whose items you grouped.

2 Custom groups. Appear as the parent of items you explicitly selected to create the group.

3 The Other group is the parent of the items you did not assign to any specific custom group.

Creating and deleting custom groups and custom group fields

  • You cannot group items that belong to different fields or items that belong to parent items into a custom group.

  • When you create the first custom group for a field, a custom group field is automatically added as the field's parent. Similarly, when you delete the last custom group for a field, the custom group field is automatically deleted.

  • When you delete a custom group that is not the last custom group in the field, the deleted group's members automatically move to the Other group.

  • The groups you create are persisted, but exist only on the client side. So any groups you create are available on the Web page you create, but are not part of the source data.

Moving a custom group field to the detail area

When you move a fieldset that has custom group fields to the detail area or filter area, you will not see the custom group fields. However, the bound field will show the custom group hierarchy in the drop-down list, so you can filter data by selecting custom groups or individual values. When you move the fieldset back to a row or column area, the custom group fields will show in the fieldset.

Adding and removing nested custom groups

You can select two or more custom groups to create a higher-level grouping. For example, you can group the members of the Category field into two groups: high priority promotions and low priority promotions.

The following illustration shows how the data list will look with nested custom groups.

PivotTable list or view with nested custom groups

1 A custom group field that appears as the parent of the Category custom group field.

You can also remove a lower-level custom group. The following illustration shows what the data will look like after the Category field has been removed.

PivotTable list or view with custom groups

Note You can't group items if the designer of the PivotTable list chose to prevent grouping in the browser.


Group or ungroup items

Group items at intervals

  1. Select the row or column field whose items you want to group.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Filter and Group tab.
  3. Set Group items by to a value other than (No grouping).
  4. Set Interval to the value you want. For example, if you are grouping items by prefix characters, setting Interval to 1 would group items on A, B, C, and so on.
  5. To specify the value at which grouping begins, select the Start at check box and then enter a value in the text box.
  6. To specify the value at which grouping ends, select the End at check box and then enter a value in the text box.

    Note The end value must be higher than the start value.

Remove grouping at intervals

  1. Select the row or column field from which you want to remove grouping.
  2. Click Commands and Options [image] on the PivotTable list toolbar, and then click the Filter and Group tab.
  3. Set Group items by to (No grouping).

Create custom groups

  1. Click the label of the parent field that contains the items you want to group, and then click Expand [image] on the PivotTable list toolbar.
  2. Click the first item that you want to include in the group.

    To select more items, hold down the CTRL key and click each item.

  3. Right-click a selected item, and then click Group Items.
  4. Repeat steps 2 and 3 to create more groups.

Notes

  • When you add the first custom group to a field, a custom group field is added to the field set. The custom group field will be the parent of the field whose items you grouped.
  • You can change the caption of the custom group field and the custom groups in the Commands and Options dialog box.

Remove custom grouping

  • Do one of the following:
    • To remove a custom group, right-click the group caption, and then click Ungroup Items.
    • To remove a custom group field, right-click the field, and then click Delete.

Notes

  • You cannot right-click the Other item and ungroup it.
  • If the group you want to delete is the last custom group in the field, the custom group field will be deleted along with the group. If the group you delete is not the last custom group, the members of the deleted group will move to the Other group.


About finding data

PivotTable lists do not have a find or a search command. However, you can use the filter feature to find specific data values or all data that matches a value.

Filtering techniques

Filtering a field (Autofiltering) You can filter fields in the row, column, and detail areas. When you filter a field, you select one or more items of data in the field that you want to view, and hide the other items.

Filtered PivotTable list or view

1 First, the Sport field is filtered to display only Golf sales ...

2 ... and then the Quarter field is filtered to display only Golf sales in Qtr3.

3 When you filter a field, the drop-down arrow Field arrow for the filtered field changes to blue instead of black, and the AutoFilter button [image] on the PivotTable list toolbar is selected.

Filtering a field is particularly helpful when you have a large amount of source data but you want to focus on specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus.

Filtering based on the data in one cell (Filter by Selection) You can filter a field to display only data that matches the value in a selected cell. Filtering by selection is particularly useful for fields in the detail area, when you want to view all of the rows that contain a particular value.

Using a field in the Filter axis You can also filter data by using filter fields. When you select one or more items in the filter field, the data that's displayed and calculated in the entire PivotTable list changes to reflect those items. You can add new filter fields or move existing fields to the filter area.

Filter field example

1 Filter field

Conditional filtering You can apply a filter to a row or column field to show the top or bottom n items based on a total. For example, you can filter for the three cities that generated the most sales or the five products that are least profitable. Instead of n items, you can also choose to filter for a certain percentage of items. For example, you can filter for the top or bottom 25% of salespeople based on the number of orders handled. If there are 40 salespeople, after the filter is applied, you will see data for 10 people.

Combining multiple filters

Filters are additive. Each filter you apply is added to the ones you already have in effect. However, when you apply a filter by selection, the conditional filter already applied on the field is removed before the filter by selection is applied.

Conditional filters on a PivotTable list are independent of each other. The result of one conditional filter does not affect the result of another.

An autofilter is always applied before a conditional filter, regardless of the order in which you applied them. For example, if you set a conditional filter to show the top two cities based on sales, followed by an autofilter on the ShippedCity field to include only five cities, the PivotTable list will show the top two of the five cities you selected.

Removing and reapplying a filter

You can display all data for all fields by turning off autofiltering. However, the filter settings are retained, so that when you turn autofiltering back on, the same data that was previously displayed or hidden is again displayed or hidden.

Filter settings are retained when you move fields to change the layout. Also, if you remove a field and later add the field back to the layout, the same items are again hidden.

If you don't want to retain your filter selections, make sure the AutoFilter button [image] is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.

Identifying filters that are currently in effect

When items in a field are hidden by autofiltering or filter by selection, the drop-down arrow Field arrow in the field label is blue. Click the blue arrow to see the filter that is in effect.

When items in a field are hidden by conditional filtering, a funnel [image] appears to the left of the drop-down arrow Field arrow. You can get more information about the current filter in the Commands and Options dialog box.

Effects of filtering on calculations

Totals can include only the displayed data, or both visible and hidden data. The default is to exclude hidden data from totals — with the exception of PivotTable lists that are based on source data from OLAP databases and published from Microsoft Excel, which include hidden data in grand totals. You can change the setting that's in effect to include or exclude hidden data in totals. This setting affects only data that's hidden by filtering row and column fields; data that's hidden by using filter fields is always excluded from totals.

Searching specific data

To search through the data instead of filtering, or to find data that matches a pattern, you can export the PivotTable list to Microsoft Excel. For information about using the Excel Find command, see Excel Help.


Filter a field to display only the data you want

Filter a field in the row, column, filter, or detail area (Autofiltering)

  1. Click the arrow Field arrow for the field that contains the items of data that you want to filter.
  2. Select the check boxes for the items you want to display, and clear the check boxes for the items you want to hide.

Notes

  • To display or hide all of the data for a field, select or clear the (All) check box. A shaded check box indicates that some of the lower-level items are hidden. To see which lower-level items are hidden, click the expand indicator Plus box beside the item with the shaded check box.
  • When placed in the row, column, or detail area, a field always supports multiple selection. A field in the filter area can support either single or multiple selection. To switch from multiple to single selection, on the Filter and Group tab of the Commands and Options dialog box, clear the Allow selecting multiple items when in filter area check box.

Display only data that matches a cell (Filter by selection)

  • Right-click the cell that contains the value you want to display, and then click Filter By Selection on the shortcut menu.

Filter a row or column field for top or bottom n values or a percentage (Conditional filtering)

  1. Select the field that has the values you want to filter.
  2. Click Commands and Options [image] on the PivotTable list toolbar, and then click the Filter and Group tab.
  3. Set Display the to Top or Bottom.
  4. Set Items to a number in the list, or type the value you want.
  5. To show n number of items, clear the % check box. To show n% of items, select the % check box.
  6. Set Based on to a value in the list.

Notes

  • When items in a field are hidden by an autofilter or a filter by selection, the arrow Field arrow in the field label is blue. When items are hidden by a conditional filter, the filtered field displays a funnel [image] image to the left of the arrow.
  • When you apply a filter, the displayed totals are based on visible items only. You can change the setting to include hidden data.


Remove filters to display all data in a PivotTable list

When items in a field are hidden by filtering a field or filtering by selection, the arrow Field arrow in the field label is blue. When items are hidden by filtering for top or bottom values, the filtered field displays a funnel [image] image to the left of the arrow.

  • Do one of the following:

    Remove all filters

    • Click the AutoFilter button [image] on the PivotTable list toolbar so that the button is not selected.

    Note If you click AutoFilter [image] again to turn filtering back on, the previously set filters will be reapplied.

    Remove an autofilter or filter by selection from a field

    1. Click the blue arrow [image] of the field from which you want to remove the filter.
    2. Select the (All) check box and click OK.

    Remove a conditional filter from a field

    1. Select the field from which you want to remove conditional filtering.
    2. Click Show Top/Bottom Items [image] on the PivotTable list toolbar, and then click Show All.
Note If you don't want to retain your filters, make sure the AutoFilter button [image] is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.


Allow multiple selection in the filter area

You can specify whether the field in the filter area will allow single or multiple selection. In single-selection mode, selecting an item automatically clears the previous selection. In multiple-selection mode, when you select an item, previously selected items remain selected.

  1. Select the field in the filter area for which you want to change the setting.
  2. On the PivotTable list toolbar, click Commands and Options [image], and then click the Filter and Group tab.
  3. Select or clear the Allow selecting multiple items when in filter area check box.

Notes

  • By default, multiple selection is enabled.
  • This setting applies only when the field is in the filter area. If you move the field to a row, column, or detail area, the field will always support multiple selection.


Refresh a PivotTable list with updated source data

  1. Click the PivotTable list.
  2. On the PivotTable list toolbar, click Refresh [image].

Notes

  • The Office logo cube Office logo cube stops spinning when the refresh operation is complete. While the PivotTable list is being refreshed, you cannot interact with it or make changes to it.

  • To cancel a refresh operation, press ESC. You can cancel the refresh operation only when the PivotTable list is being calculated — not while source data is being retrieved.


About exporting a PivotTable list to Excel or other applications

If you are familiar with Microsoft Excel PivotTable reports and want to work with the data in Excel, there are several ways to accomplish this. However, if you open a Web page that contains a PivotTable list directly in Excel, you will not be able to view or modify the PivotTable list.

Exporting to an interactive Excel PivotTable report

To view and modify a PivotTable list on a Web page by using Excel, you can export the PivotTable list to Excel. The Excel PivotTable report will be connected to the same source data as the original PivotTable list, so all fields and data from the original PivotTable list will be available in Excel, and you can refresh the report if the source data changes.

Preparing for export to Excel Excel PivotTable reports cannot display detail fields. When you export to Excel, detail fields will be available on the Excel PivotTable toolbar, but the fields won't be displayed in the report. If you want the Excel PivotTable report to reflect the appearance of the PivotTable list on the Web, before you export to an Excel PivotTable report, either move all the fields out of the detail area, or hide detail data for items and cells so that the detail area is not displayed.

After you export You might notice some differences in your PivotTable list after you export it to Excel. This is because Excel PivotTable reports either do not support certain PivotTable list features, or they implement some features differently. For information about differences between PivotTable lists and Excel PivotTable reports, see Excel Help.

Copying data to another program for noninteractive use

Copying to Microsoft Word or Excel When you want to work with a snapshot of the data currently displayed in a PivotTable list, you can copy and paste the data into Excel or Word. When you copy the whole PivotTable list to another program, or copy selected parts of the data, the text and values from the PivotTable list become part of your Word document or Excel workbook. No connection is created between the original source data for the PivotTable list and the copy in the document or workbook, so you won't be able to refresh or update the data that was copied.

Copying to other programs You can also copy data displayed in a PivotTable list to other programs, such as Microsoft FrontPage or Microsoft Access. When you copy data to a program other than Excel or Word that does not support the HTML format used by PivotTable lists, the data is copied as unformatted text.


Export or copy a PivotTable list to Excel or other applications

Export a PivotTable list to Excel

Use this procedure to export a PivotTable list from your browser to Microsoft Excel as an interactive PivotTable report. The link between the Excel PivotTable report and the original source database is maintained.

For best results, either move all fields out of the detail area or hide detail data for items and cells before you export the PivotTable list to Excel.

  1. Click the PivotTable list.
  2. Click Export to Microsoft Excel [image] on the toolbar in the PivotTable list to run or switch to Excel and display the data in a PivotTable report.

Note Because PivotTable reports in Excel cannot display some PivotTable list layouts and calculations, the PivotTable report might look different from the PivotTable list in the browser. For information about working with Excel PivotTable reports and adding fields to them, see Excel Help.

Copy a PivotTable list to Excel or Word

Use this procedure to copy a PivotTable list, or a part of it, as a noninteractive, formatted table in Microsoft Excel or Microsoft Word.

  1. Do one of the following:
    • To copy the entire PivotTable list, click the title bar of the PivotTable list.
    • To copy a part of a PivotTable list, select the elements that you want to copy.
  2. On the toolbar in the PivotTable list, click Copy [image].
  3. Switch to Excel or Word, and click where you want the data from the PivotTable list to appear.
  4. Click Paste [image].

Notes

  • When you copy a PivotTable list, or a part of it, you are copying only the formatting and data values. No link is maintained between the copy in Excel or Word and the original source database.

  • You can copy part of a PivotTable list to other programs, such as Microsoft Access, as unformatted text.


Troubleshoot PivotTable lists

Interacting with the PivotTable list

I can't interact with a PivotTable list.

If you can view but not interact with a PivotTable list, you might not have the Microsoft Office Web Components installed or the appropriate Microsoft Office XP license. Be sure the components are installed and that you have a valid license.

I can't show the toolbar, title bar, field list, Commands and Options dialog box, or the shortcut menus.

The designer of the PivotTable list has restricted access to the PivotTable list tools, in order to prevent making changes in the browser. Contact the designer for assistance.

Getting help

I can't get help for a Microsoft Office Web Component displayed on a Web page.

The Web page might have been designed using an older version of the Microsoft Office Web Components. Help for previous versions is not installed with Office XP. You can download Help for previous versions of the components from Microsoft Office Web site. (This hyperlink goes to the Web. You can switch back to Help at any time.)

Moving fields

I can't add or move the field I want.

The field might already be displayed You cannot display the same field in more than one area.

The source data might limit how you can use fields Source data from On-Line Analytical Processing (OLAP) databases provides sets of fields for different levels of detail. Within a set of fields, you can hide levels of detail, but you cannot separate out fields in the set for display in different areas. To find out whether the source data in your PivotTable list is from an OLAP database, contact the designer of the PivotTable list.

Moving fields might be restricted The person who created the PivotTable list might have turned off the ability to move fields. Contact the designer of the PivotTable list for more information.

If you need to move fields, you can export a copy of the PivotTable list to Microsoft Excel. In the Excel PivotTable report, you won't be restricted from moving fields, and you can display a field in the filter (page) area and the data area simultaneously. However, Excel PivotTable reports have the same restrictions if you have OLAP source data. For information about moving fields in a PivotTable report, and for information about OLAP source data, see Excel Help.

When I change the layout, the data takes a long time to appear.

If the PivotTable list takes a long time to calculate, you can make several changes to the layout at one time and then retrieve the data when you are finished making changes.

  1. When the Office logo cube Office logo cube is spinning, press ESC to cancel the calculation. The PivotTable list then displays only the names of the fields.
  2. Change the layout, adding and moving as many fields as you want.
  3. To retrieve and calculate the data for the new layout, click Refresh [image] on the toolbar in the PivotTable list.

The Commands and Options dialog box or the field list disappeared.

When you click a control on the Web page other than a Microsoft Office Web Component, or when you minimize and then restore the browser window, the Commands and Options dialog box and the field list might be hidden.

To redisplay the dialog box or field list, click the PivotTable list so that it is reactivated.

Refreshing data

The PivotTable list is unable to refresh the data from the source.

Some PivotTable lists can't be refreshed PivotTable lists that were published from Microsoft Excel worksheet data, Excel Web queries, and Excel text queries can't be refreshed. In these cases, the original Excel workbook must be refreshed and the PivotTable list republished. Contact the designer of the PivotTable list to find out what type of source data the list has and, if the source data is one of the above types, whether the PivotTable list can be republished to provide an updated version.

The source database might be unavailable If the PivotTable list is based on source data from a database on a network, the database server might be unavailable, or you might not have access to the server. For example, you might not be connected to the network, or you might need access privileges or a password to connect to the server. Contact the designer of the PivotTable list to find out what type of source data and which database the list uses. Contact the database administrator if you're having trouble connecting to the server.

Microsoft Office might not support the source database If the source data is from a database that's not supported by Microsoft Office, you might need to install Office-compatible support software from the database vendor. Contact the designer of the PivotTable list to find out whether the source database is supported by Office and, if the database is not supported, what software the designer used to create it.

The source database might have changed Occasionally, changes to the way a database is organized on the server might prevent you from refreshing a PivotTable list. For example, if the source data is from an On-Line Analytical Processing (OLAP) database and the database is rebuilt with changes to the available data, a new PivotTable list might have to be created. Contact the designer of the PivotTable list for more information.

When I refresh a PivotTable list, other PivotTable lists on the Web page also change.

The Web page you are viewing was designed so that when you refresh one PivotTable list, the other PivotTable lists are also refreshed.

For information about the way the PivotTable lists are linked, contact the designer of the Web page.

The query failed in a PivotTable list.

The source database might be unavailable If the PivotTable list is based on source data from a database on a network, the database server might be unavailable, or the network or your connection to the network might have become unavailable. Contact the server or network administrator to check whether the server and network are available.

You might not have the permissions you need You might be able to connect to the database, but you might not have permissions to run the query for the PivotTable list. Contact the database administrator to find out about permissions.

Check the filtering in the PivotTable list Occasionally, a PivotTable list might be filtered to display specific items, and changes to the source database might eliminate one of those items, causing the query to fail. If this is the case, turn off filtering to display all items, and then click Refresh [image] on the toolbar in the PivotTable list to run the query.

Filtering data

The filtering commands don't work.

Filtering might be restricted The person who created the PivotTable list might have turned off the ability to change how fields are filtered. For more information, contact the designer of the PivotTable list.

If you need to filter the data, you can export a copy of the PivotTable list to Microsoft Excel. In the Excel PivotTable report, you won't be restricted from filtering fields. For information about filtering data in a PivotTable report, see Excel Help.

I can't filter the data the way I want to.

Filtering that is based on part of the data in a cell, a feature which is available for forms and datasheets in Microsoft Access, is not available for PivotTable lists. If you need partial-cell filtering, contact the person who created the PivotTable list to find out where the source data came from. You might be able to view this data in a form or datasheet directly in Access, which supports partial-cell filtering.

Displaying data

I can't display items or detail data.

Expand indicators might be turned off If you don't see the Plus box boxes to the left of items of data in row and column fields, make sure the expand indicators are turned on.

Items might not be available When an item of data is at the lowest level of detail available from the source data, the item does not have the Plus box box beside it, even when you turn on the expand indicators.

The source data might not include detail data Some types of source data, such as source data from On-Line Analytical Processing (OLAP) databases, do not have detail records available for display. To find out whether the source data in your PivotTable list is from an OLAP database and to find out the amount of detail available, contact the designer of the PivotTable list.

Displaying details might be restricted If the commands to turn on the expand indicators and display details are unavailable, the designer of the PivotTable list might have turned off the ability to display items or details. Contact the designer for more information.

If you need to view the detail data, you can export a copy of the PivotTable list to Microsoft Excel. In the Excel PivotTable report, you won't be restricted from displaying details. For information about displaying details in a PivotTable report, see Excel Help.

The data is hard to read.

Use a larger font The font size settings in your browser don't affect the size of text in a PivotTable list. To make small, hard-to-read text larger, you can change the font sizes for elements within a PivotTable list.

Use contrasting colors The color settings in your browser don't affect the colors in a PivotTable list. To intensify text so that it stands out from its background, you can set the font color and then set a contrasting background color for each element in the PivotTable list.

When you export a copy of a PivotTable list to Excel, you can zoom to magnify the data. For information about working with Excel PivotTable reports, see Excel Help.

When I page down, I can't see my selection.

Use the scroll bars in the browser. The designer of the PivotTable list designed the list so that when you change the layout, the list expands or shrinks to use as much space on the Web page as is needed to display the data. To see the additional data, use the browser's scroll bars to scroll through the entire Web page.

The fields are present, but all the data is missing.

You canceled calculation of the PivotTable list, or calculation stopped before it was complete. To display the data, click Refresh [image] on the toolbar in the PivotTable list.

Tip

Before you refresh the data, you can change the layout or filter the items in the fields.

Data is missing when I print a PivotTable list.

Make sure data is not hidden If you want to print data that's hidden by filtering, display the data before you print. To print detail data, display details before you print.

Check whether the PivotTable list is displayed in a fixed area PivotTable lists can expand and shrink on the Web page to use as much space as is needed to display the data, or they can be displayed in an area of a fixed size, depending on how the designer of the PivotTable list designed the list. If the PivotTable list is displayed in a fixed area, only the currently displayed area is printed.

Scroll through the entire PivotTable list When you print a PivotTable list that expands and shrinks as needed to display the data, only data that you have scrolled to in your current browser session is printed. Use the scroll bars to scroll to the top and bottom of the PivotTable list before you print the Web page.

For more control over how the data is printed, including the ability to print a long list and to set page breaks, you can export a copy of the PivotTable list to Microsoft Excel. For information about printing from Excel, see Excel Help.

Data is missing after I copy a PivotTable list into Excel or Word.

When you copy data in a PivotTable list to the Clipboard and paste it into Microsoft Excel or Microsoft Word, only visible rows and columns are copied. Any data that you have hidden or filtered out is not copied.

Before you copy data, turn off filtering to show all data in the PivotTable list, and display the detail data that you want copied.

To avoid having to show all the data first, you can export, rather than copy, the PivotTable list. When you export a copy of a PivotTable list to Excel, all of the data is automatically included, with the same filtering in effect. You have the same access to additional fields in the source data that you do in a PivotTable list, and you can change the filtering in Excel. From Excel, you can then copy data to Word or other programs. For information, see Help in Excel.

A cell displays ##### instead of a number.

When a number is too wide to fit in a cell, the PivotTable list displays number signs instead of the number. You can fix this in any of the following ways:

  • Make the column wider so that the entire number fits.
  • Make the font smaller if you don't want to widen the column.
  • Change the number format to one that takes less space.

A chart that's based on my PivotTable list is blank or has no data.

Add total fields For a chart to reflect data in a PivotTable list, you must add at least one total field to the PivotTable list.

Move fields to the row and column areas The row and column fields in a PivotTable list provide the category and series data in your chart. If all of the fields are in the detail area of the PivotTable list, for example, the chart has no categories and series to plot. From the detail area or field list, move at least one field to the row area or the column area.

Clicking the Edit button in my Web browser opens my Web page in the wrong program.

The programs that are available by clicking the Edit button in the Web browser depend on how the page was published.

If clicking the Edit button in your Web browser opens a program that you do not want to use, quit the program, start the program you want to use, and then open the Web page from that program.

Total fields in my PivotTable list have asterisks next to them.

Asterisks (*) appear next to captions of the total fields in the following cases:

  • When you set the option to include all items in totals, asterisks appear next to the caption of the total fields to indicate that the totals include any hidden items in addition to the displayed items.
  • In Excel PivotTable reports that are based on source data from On-Line Analytical Processing (OLAP) databases, the calculations for subtotal and grand total values always include the values for all items in the field, both displayed and hidden. Excel provides an option to turn off display of these asterisks.

In the PivotTable list, regardless of whether it's based on source data from an OLAP database, if you want to omit data that is hidden by filtering from subtotals and grand totals, you can set an option to include only displayed data. This option also hides the asterisks.