About PivotTable lists

This topic contains:

Overview

Analyzing data in a PivotTable list

Source data

More information

Overview

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

Where does the PivotTable list get its data? 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 and which subset of the data to make available from the source database. 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.

PivotTable lists and charts The creator 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 change or refresh the PivotTable list, you'll see the same changes reflected in the chart. How to work with a chart that is associated with a PivotTable list.

Terminology PivotTable lists use terms such as "field" and "detail area" to describe the data. If you work with Microsoft Excel PivotTable reports or Microsoft Access PivotTable forms, you'll find the PivotTable list terminology familiar.

Return to top

Analyzing data in a PivotTable list

The creator of the PivotTable list sets it up to display one of the many possible views of the data. You can use the toolbar and the PivotTable Property Toolbox 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.

How a PivotTable list displays 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 Sales.

Example of a PivotTable list with all detail fields

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 to reorder the rows. For example, sort on the Sales field to see which sport and quarter produce the highest gross.

  • Add subtotals or add 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 all the details of their content. You can display your PivotTable list like the preceding example by moving all the fields to the detail area if the source data is set up to allow you to do this.

Summarizing the data By creating a total field and moving fields to other areas, you can quickly summarize the data. For example, start with the PivotTable list in the preceding example. By creating a Sum of Sales total field, moving the Sport field to the row area, and moving the Quarter field in the column area, as shown in the following example, you can then easily see how the third-quarter golf sales stack up against sales for another sport or quarter, and you can see grand total sales.

Example of a PivotTable list summarizing data

The cell displaying third-quarter golf sales summarizes the sales amounts for every record that has Golf for the sport and Qtr3 for the quarter.

Saving your 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 do 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 analyze the data further 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 2000 data access page Design view or FrontPage 2000 to open the Web page and use the advanced features of PivotTable lists. For information, see Help in Access or FrontPage.

Return to top

Source data

Databases PivotTable lists can display data from network databases, including both OLAP databases and relational databases, such as Microsoft Access or Microsoft SQL Server. For more information about OLAP databases, see OLAP source data. PivotTable lists can also display data from other types of databases on your network and data from spreadsheet databases such as Excel lists. The type of source database and the type of data that's included in the PivotTable list that you're viewing determine what kinds of views and summaries you can create from within the Web browser.

When the source database itself changes or is updated with new information, you can refresh the PivotTable list to obtain the latest data.

The creator of a PivotTable list determines the source data; that is, the creator 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.

Viewing the fields available from the source data A PivotTable list that you view might not include all of the fields available from the source data. If more data is available, you can add fields to the PivotTable list. When you add fields, you use the list in the Field List dialog box, available from the Field List [image] button on the toolbar.

Example of a field list

Example of a field list

You can scroll and click the Plus box and Minus box boxes to display or hide fields. Fields already displayed in the PivotTable list are shown in bold.

OLAP source data On-Line Analytical Processing (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 dimensions and levels of detail.

For example, a geographical dimension could have Country, Region, and City levels, and a time dimension could have Year, Quarter, Month, and Day levels. These dimensions translate into row, column, or filter fields in a PivotTable list. Each dimension field in a PivotTable list can display data from lower or higher levels of detail.

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. You'll also notice that the fields for dimensions are displayed in sets; so when you move one field in a set, the other fields move with it. In the field list, dimension fields have Dimension field icons beside them, and data fields have Measure field icons.

Return to top

More information

View details for summarized data

Format a PivotTable list

Print a PivotTable list

Return to top


Elements of a PivotTable list

Example of a PivotTable list that shows element names

Fields Fields correspond to a column of data and can summarize multiple rows of information. Types of fields include row fields, column fields, data fields, and filter fields.

Item A unique element of data within a field. Items in row and column fields are listed down columns and across rows. 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 additional detail data for an item is available for display, the Plus box expand indicator appears beside the item.

Row field A field in the row area. In the previous example, 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 example, Quarters is a column field with two items, Qtr2 and Qtr3. 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. In the example, Region is a filter field you can use to display data for all regions or for one region at a time. Filter fields perform the same functions as page fields in Microsoft Excel PivotTable reports and Microsoft Access PivotTable forms.

Data area The part of a PivotTable list that contains data fields.

Data field A field that contains summary values from the source data. In the example, 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, with the Sum summary function. In the example, the cell containing 30,984 displays Davolio's total sales for dairy products in the second quarter. This cell combines the sales amounts from every record in the source data that has Dairy for the product, Davolio for the salesperson, and Qtr2 for the quarter.

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 the product sold might be used to count the number of dairy sales Davolio made in the second quarter. A PivotTable list can also summarize data using Min and Max summary functions, which yield the smallest and largest values, respectively.

Detail area The part of a PivotTable list (not shown in the example) that can display all of the rows, or records, from the source data. A PivotTable list can display detail rows and summarized data simultaneously. Fields in the detail area are called detail fields.

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

In PivotTable lists that are based on source data from OLAP databases, you can select items at different levels in a dimension field. Date and time fields provide different levels in all types of source data. For example, clicking the drop-down arrow for a Year field might display the following:

Drop-down list for OLAP dimension field

Example of a drop-down list for an OLAP dimension field

The gray check for the year 1999 indicates that it is currently selected for display but some of the items under it are not selected for display. The year 1998 is hidden. Within 1999, Qtr1 is hidden. Clicking Show All selects all items in the field at all levels for display.


Print a PivotTable list

  • On the Internet Explorer File menu, click Print.

Notes

  • If the PivotTable list is wider than the printed page, the part on the right side that does not fit is 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 level detail or hide detail, the hidden detail 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 all of 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.


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 are lost. You cannot save your changes in a file.

To make changes to a PivotTable list and save your changes in a file, you need to open the PivotTable list in a design program, such as Microsoft FrontPage 2000 or data access page Design view in Microsoft Access. Learn about design programs for PivotTable lists. You can also export the PivotTable list to an Excel PivotTable report, and then save the Excel report. For more information, see Excel Help.


Undo all changes

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

Note You cannot undo individual changes to a PivotTable list. Learn about working with PivotTable lists in different browsers.


About working with PivotTable lists in different browsers

To view and work with PivotTable lists, you must have the Microsoft Office Web Components installed. You can do this by installing Microsoft Office 2000 or, if your company has an Office 2000 site license, by downloading the Office Web Components from your corporate intranet. The full set of features that PivotTable lists on the Web can provide, such as letting you interact with data in a PivotTable list from within your Web browser, is not available in all browsers.

Microsoft Internet Explorer 5 and later With this browser, you have access to all PivotTable list features that can be used in a browser. Changes you make, such as formatting and layout changes, are retained when you exit a PivotTable list and return 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 in a browser, and your changes are retained when you exit a PivotTable list. In some cases, you must also have Internet Explorer 5 installed to view PivotTable lists on Web pages that were created or modified in Microsoft Access data access page Design view.

Microsoft Internet Explorer 4.0 You have access to all features accessible from the toolbar and right-click menus in the PivotTable list, but you cannot access the PivotTable Property Toolbox. Your changes are not retained after you exit a PivotTable list. In some cases, you must also have Internet Explorer 5 installed to view PivotTable lists on Web pages that were created or modified in Microsoft Access data access page Design view.

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 viewing a chart that's 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.

Required fields You must add at least one total field to a PivotTable list for the data to be reflected in the chart. If the PivotTable list has more than one total field, the chart displays the data from the leftmost total field. The row and column fields in a PivotTable list make up the category and series data in your chart. If all the displayed 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 or column area.

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

Chart based on PivotTable list

A chart that's based on a PivotTable list. The Type column field in the PivotTable list is shown in the chart as category data (Beverages, Meat, and so on). The Salesperson row field is shown in the chart as series data. 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 is automatically updated to reflect the changes:

Updated chart based on changes in PivotTable list

An updated chart that reflects changes to the 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 in 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 now the Type field is shown in the legend as the series, and the Salesperson and Region fields are shown as the categories.


How to get assistance designing your own PivotTable list

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.

This topic provides information about how to start designing a PivotTable list and then get additional Help:

Designing your own PivotTable list

Getting Help designing PivotTable lists

Getting Help writing scripts and programs for PivotTable lists

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.

Using Microsoft FrontPage 2000 as the design program To create a new PivotTable list for users who have Microsoft Internet Explorer version 4 or later, or to change the design of a PivotTable list that was published from Microsoft Excel, Microsoft FrontPage 2000 is recommended. To interact with PivotTable lists that you create in FrontPage, users must have Internet Explorer 4 or later and the Microsoft Office Web Components. FrontPage 2000 is available in Microsoft Office 2000 Premium. The Office Web Components are available in Office 2000 Standard, Professional, and Premium.

Using Microsoft Access as the design program Use data access page Design view in Microsoft Access 2000 to create a new PivotTable list for users who have Microsoft Internet Explorer version 5 or later, or who use Internet Explorer 4 or later but can also have Internet Explorer 5 installed. Microsoft Access provides specialized features for creating Web pages to access and work with data from databases. To interact with PivotTable lists that you create in Access, users must install Internet Explorer 5 or later and the Microsoft Office Web Components. Microsoft Access 2000 is available in Microsoft Office 2000 Professional and Premium. The Office Web Components are available in Office 2000 Standard, Professional, and Premium.

Using Microsoft Excel as the design program Use Microsoft Excel 2000 if you are familiar with Excel PivotTable reports or have a PivotTable report that you want to publish to a Web page. To interact with PivotTable lists that you publish from Excel, users must have Internet Explorer 4 or later and the Microsoft Office Web Components.

Using Microsoft Visual Basic or a scripting language If you are a programmer, you can write programs or scripts to create a PivotTable list. Both FrontPage 2000 and data access page Design view in 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 Microsoft Visual Basic.

Return to top

Getting Help designing PivotTable lists

Working with a PivotTable list in a design program When you create or activate a PivotTable list in a design program such as FrontPage 2000 or Access 2000 data access page Design view, the PivotTable Property Toolbox 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.

Getting Help in the design program While you are working in a design program, you can get Help designing 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 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).

Return to top

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 2000 programs provide the Microsoft Script Editor to help you write scripts. For example, you can use the Script Editor from Microsoft Access data access page Design view to customize a PivotTable list. For information about running the Script Editor, see Help in your 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 whenever you install the Office Web Components, of which PivotTable lists are one component. Depending on the design program you're using, you can access this Help in either of two ways:

  • Help in Visual Basic In Visual Basic or Visual Basic for Applications, you can create a reference to the Microsoft Office Web Components library. You will then have access to Help through 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 Visual Basic for Applications.

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

Return to top


Keyboard shortcuts in PivotTable lists

What do you want to do by using keyboard shortcuts?

Activate, move around in, and deactivate a PivotTable list

Select elements within a PivotTable list

Carry out commands

Display, hide, filter, and sort data

Add fields and totals and change the layout of a PivotTable list

Format a PivotTable list


Activate, move around in, and deactivate a PivotTable list by using keyboard shortcuts

Press | To
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.


Select elements within a PivotTable list by using keyboard shortcuts

Press | To
The TAB key | After you activate a PivotTable list, press the TAB key to select each field and then the entire PivotTable list, cycling through these selections in sequence.
SHIFT+TAB | Select the previous field or the entire PivotTable list.
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. You can extend a selection down and to the right.
SHIFT+PAGE DOWN | Extend a selection down one screen. A screen is all of the data currently displayed in the PivotTable list.
SHIFT+PAGE UP | Reduce a selection by one screen.
CTRL+SPACEBAR | Select the field for the currently selected item of data, total, or detail cell.
SHIFT+SPACEBAR | Select the entire row containing the currently selected cell.
CTRL+A | Select the entire PivotTable list.
PAGE DOWN | Display the next screen. A screen is all of the data currently displayed in the PivotTable list.
PAGE UP | Display the previous screen.
ALT+PAGE DOWN | Display the next screen to the right.
ALT+PAGE UP | Display the previous screen to the left.


Carry out commands by using keyboard shortcuts

Press | To
F1 | Display Help topics. For information about keys to use in the Help window, see Help for your Web browser.
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. See ALT+ENTER for information about the PivotTable Property Toolbox.
ESC | Close the shortcut menu without carrying out a command.
CTRL+7 | Display or hide the toolbar.
ALT+ENTER | Display the PivotTable Property Toolbox. No keyboard shortcuts are available to carry out commands or set properties from the toolbox.
ALT+F4 | Close the PivotTable Property Toolbox.
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+E | Export the PivotTable list to Microsoft Excel.


Display, hide, filter, and sort data by using keyboard shortcuts

Press | To
CTRL+8 | Display or hide the Plus box and Minus box expand indicators beside items.
CTRL+PLUS SIGN | Display detail for the currently selected item.
CTRL+MINUS SIGN | Hide detail for the currently selected item.
ALT+DOWN ARROW | Open the drop-down 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.
PLUS SIGN on the numeric keypad | Display lower levels of detail, if available, for the current item in the drop-down list for a field.
MINUS SIGN on the numeric keypad | Hide lower levels of detail for the current item in the drop-down list for a field.
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).


Format a PivotTable list by using keyboard shortcuts

The first seven keyboard shortcuts in the following table change the number format of the selected element. Learn about number formats.

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

Press | To
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.


Add fields and totals and change the layout of a PivotTable list by using keyboard shortcuts

This topic lists:

Keys for working with the field list

Keys for adding fields and totals

Keys for changing the layout

Keys for working with the field list

Press | To
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.
PLUS SIGN on the numeric keypad | Expand the current item in the field list to display its contents. Or expand Totals to display the available total fields.
MINUS SIGN on the numeric keypad | 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 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.

Return to top

Keys for adding fields and totals

Press | To
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+B | Turn subtotals and grand totals on or off for the selected field in the PivotTable list.

Return to top

Keys for changing the layout

Press | To
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.

Return to top


About the toolbar and the PivotTable Property Toolbox

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 over the buttons to see the command names.

If you don't see the toolbar, the person who created the PivotTable list may have hidden the toolbar. How to display the toolbar when it's hidden.

PivotTable Property Toolbox The toolbox includes commands, such as Show Toolbar, and properties you can set, such as the background color or column width. The contents of the toolbox change as you select different parts of a PivotTable list. The toolbox includes some commands that aren't present on the toolbar or right-click menus.

The commands and properties in the toolbox are distributed among several sections, each identified by a bar that displays the name of the section. The available sections change depending on what part of the PivotTable list you have currently selected. When you first display the toolbox, the General commands and properties are displayed. You can display, or expand, and hide the contents of the sections by clicking the bars. How to use the PivotTable Property Toolbox.

If the toolbox is not displayed, click the Property Toolbox button [image] on the toolbar. If the button is not available, the person who created the PivotTable list might have turned off access to the toolbox. Contact the person who created the PivotTable list for assistance.


Display the toolbar in a PivotTable list

  • Right-click the title bar of the PivotTable list, and then click Toolbar on the shortcut menu.

Notes

  • If the PivotTable list has no title bar, right-click the PivotTable list, and then click Property Toolbox on the shortcut menu. If the Show/Hide section is not expanded, click the Show/Hide bar, and then click Toolbar [image].

  • If the Property Toolbox command is not available on the shortcut menu, the person who created the PivotTable list might have turned off access to the toolbox. Contact the creator of the PivotTable list for assistance.


Toolbar buttons and commands

Office logo cube Office logo cube Displays the About Microsoft Office Web Components dialog box, which shows which version you have installed. The cube spins while data is being retrieved.

Copy Copies the selected data from a PivotTable list to the Clipboard so that you can paste the selection into other programs. If you need an interactive copy in Microsoft Excel, use Export to Excel [image] instead.

Sort Ascending Sorts data in the selected field in A-Z 0-9 order.

Sort Descending Sorts data in the selected field in Z-A 9-0 order.

AutoFilter Turns filtering on or off for all fields. Filtering lets you display rows or columns that match only the values you specify.

AutoCalc Adds a total field to the field list, summarizing the data by using Sum, Count, Min, or Max.

Subtotal Displays subtotals or hides subtotals for the selected field.

Move to Row Area Moves the selected field so that it becomes a row field. Row fields summarize data by displaying the items of data in a field down the rows of a PivotTable list.

Move to Column Area Moves the selected field so that it becomes a column field. Column fields summarize data by displaying the items of data in a field across the columns of a PivotTable list.

Move to Filter Area Moves the selected field so that it becomes a filter field. Filter fields filter the entire PivotTable list so that only data for the selected item in the field is displayed.

Move to Detail Moves the selected field to the detail area so that all data in the field appears in unsummarized form.

Promote Moves the selected inner row or column field to the next outer level. When a PivotTable list has more than one row field or column field, the outer fields are the ones that are further from the data, to the left or top.

Demote Moves the selected outer row or column field to the next inner level. When a PivotTable list has more than one row field or column field, the inner fields are the ones that are closer to the data, to the right or bottom.

Expand or Collapse Hides all detail or shows all detail for the selected field, item of data, or cell in the data area.

Refresh Updates the PivotTable list from the source file or database, displaying any new data and adding any new fields to the field list.

Export to Excel Runs or switches to Excel and copies the PivotTable list into a new workbook as an Excel PivotTable report, the interactive Excel equivalent of a PivotTable list.

Property Toolbox Displays the PivotTable Property Toolbox so that you can carry out commands and set properties.

Field List Displays the list of fields available from the source data for the PivotTable list so that you can add more data to the PivotTable list.

Help Displays Help topics about how to use a PivotTable list.


Hide the toolbar in a PivotTable list

  1. Click the PivotTable list.

  2. Click Property Toolbox [image].

  3. If the Show/Hide section is not expanded, click the Show/Hide bar.

  4. Click Toolbar [image].

Note If the Property Toolbox [image] button is not available on the toolbar, the person who created the PivotTable list might have turned off access to the toolbox. Contact the person who created the PivotTable list for assistance.


Use the PivotTable Property Toolbox

  1. Select the part of the PivotTable list for which you want to view or set properties or carry out commands.

    How?

  2. On the toolbar in the PivotTable list, click Property Toolbox [image].

    If the toolbar isn't displayed, right-click the PivotTable list, and then click Property Toolbox on the shortcut menu.

  3. To expand or hide the contents of a section, click the bar with the name of that section.

Notes

  • The toolbox includes commands, such as Toolbar, and properties you can set, such as background color or column width.

  • When you type or edit entries in the toolbox, you must click another part of the toolbox or press ENTER for your changes to take effect.

  • If the Property Toolbox [image] button and the shortcut menu command are both unavailable, the person who created the PivotTable list might have turned off access to it.

  • Properties that you set for totals affect all of the totals in the PivotTable list.

  • If you leave the toolbox displayed while you select different parts of the PivotTable list or other Microsoft Office Web Component controls, 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 control, the toolbox is hidden.


About finding data

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

Filtering to find data Filtering is a quick way to work with a subset of the data in a PivotTable list. 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 you hide the other items.

Examples of a filtered PivotTable list

First the Sport field is filtered to display only Golf sales, and then the Quarter field is filtered so that only Golf sales in Qtr3 are displayed.

When a PivotTable list that you are viewing has been filtered, the drop-down arrows Field arrow for the filtered fields are blue instead of black.

Selecting multiple items in multiple fields Filtering is particularly helpful when a PivotTable list has a large amount of source data and you want to focus on the areas that interest you. 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 more than one field at a time to further narrow the focus of the list. How to filter a field.

Filtering based on the data in one cell 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.

Turning off filtering To display all of the data for all fields in a PivotTable list, you can remove filtering. The filter settings are retained so that when you turn filtering back on, the same data that was previously displayed and hidden is again displayed and hidden. Filter settings are also retained when you move fields to change the layout of the PivotTable list. However, if you begin making new filter settings with filtering turned off, your previous settings are cleared.

Using a filter field to filter the entire PivotTable list You can also filter data by using filter fields. When you select a different item for a filter field, the data that's displayed and calculated in the entire PivotTable list changes. You can add new filter fields to the PivotTable list or move existing fields to the filter area.

Effects of filtering on calculations By default, subtotals and grand totals include all of the data in a field, including both the displayed and hidden data. 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.

If you need a search command 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

  1. Click the drop-down 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 that you want to display, and clear the check boxes for the items of data that you want to hide.

Notes

  • To display or hide all of the data for a field, select or clear the Show All check box.

  • A shaded check box indicates that some or all of the lower level items are hidden. To see which lower-level items are hidden, click the Plus box beside the item with the shaded check box.

  • To display all data for all fields, click AutoFilter [image] so that the button is no longer pressed in. The filter selections that you previously made are retained, and the next time you click the AutoFilter button, the fields are again filtered. If you don't want to retain your filter selections, make sure the AutoFilter button is not pressed in when you start selecting items to filter. If the button is not pressed in, selecting new items to filter automatically turns filtering on and removes your former filter settings.

  • If you move a field that is filtered, your filter settings for the field are retained when the field is in the data or filter area, where the items aren't hidden. This means that when you move the field back to the row, column, or detail area, the previously hidden items are again hidden. Also, if you remove a field and later add the field back to the layout, the same items are again hidden. You can tell that items in a field are hidden when the arrow next to the field label is blue.


Display only data that matches a cell

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

Notes

  • If the AutoFilter [image] button on the toolbar is pressed in, filtering by selection is cumulative, that is, it adds an additional filter to those you already have in effect. If AutoFilter is not pressed in, filtering by selection automatically removes any filtering you had previously set and turns filtering on, which starts a new accumulation.

  • To display all data for all fields, click AutoFilter [image] so that the button is no longer pressed in. The filter selections that you previously made are retained, and the next time you click the AutoFilter button, the fields are again filtered. If you don't want to retain your filter selections, make sure the AutoFilter button is not pressed in before you start selecting items to filter.

  • If you move a field that is filtered, your filter settings for the field are retained when the field is in the data or filter area, where the items aren't hidden. This means that when you move the field back to the row, column, or detail area, the previously hidden items are again hidden. Also, if you remove a field and later add the field back to the layout, the same items are again hidden. You can tell that items in a field are hidden by filtering when the drop-down arrow Field arrow next to the field label is blue.


Remove filters to display all data

  • On the toolbar in the PivotTable list, click AutoFilter [image] so that the button is not pressed in.

Tip If you click AutoFilter [image] again to turn filtering back on, the previously set filters are restored.


Sort data in a field

  1. Do one of the following:

    To sort the data in a field based on the item labels, click the label of the field 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 you want to sort by.

    To sort the data in a field based on subtotal or grand total values, click a subtotal or grand total value for the field you want to sort. If you click a grand total value in the bottom row of the PivotTable list, the columns will be sorted instead of the rows.

  2. On the toolbar in the PivotTable list, click one of the following:

    To sort the field in A-Z 0-9 order, click Sort Ascending [image].

    To sort the field in Z-A 9-0 order, click Sort Descending [image].

Notes

  • To undo the sort and return the items of data in the field to their previous order, click Sort Ascending [image] or Sort Descending [image] again so that the button is not pressed in.

  • When you sort a field for which lower and higher levels of detail are available, the current level and lower levels are sorted, but higher levels are not sorted.

  • The order in which numbers, text, and special characters are sorted depends on the source data and your regional settings in the Windows Control Panel.

  • When you sort a field in a PivotTable list that's based on source data from an OLAP database, the current and lower levels are sorted. The higher levels determine which lower-level items are sorted together. Solve problems sorting data from OLAP databases.

  • You can sort by more than one column by sorting each column separately.


About sorting data by more than one column

To sort by more than one column, you can 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, determine the order in which you want to sort the columns first, and then work backward, sorting the outermost column last. For example, if you're sorting on 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 sort the inner Product column first and then the outer Salesperson column.

How to sort data in a PivotTable list.


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 in 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 workspace. To view the outlines of these drop areas if they've been hidden, display the drop areas.

Example of drop areas

After you move fields to the drop areas so that the drop area captions are covered up, you can still drag fields to the drop areas, or you can use the commands on the toolbar to move fields.

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.

Changing the level of row and column fields 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. More detail can be displayed for inner fields than for outer fields.

Example of inner and outer row fields

You can change the organization of these fields by moving fields to a higher level so that inner fields become outer fields, or you can move fields to a lower level.

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. How to move a field to the filter area.

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

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

Moving fields doesn't affect filtering When you filter a field, your filter settings for the field are retained if you move the field to the data or filter area. This means that when you move the field back to the row, column, or detail area, the previously hidden items are again hidden. Also, if you remove a field and later add the field back to the layout, the same items are again hidden. You can tell that the items in a field are hidden by filtering when the arrow in the field label is blue.

Restrictions on adding and moving fields If you work with a PivotTable list that is based on source data from an OLAP database, such as a database created by using Microsoft SQL Server OLAP Services, you'll notice that you can't move fields to the detail area. You'll also notice that some fields can be moved only to the row, column, and filter areas, and fields in the data area cannot be moved to other areas.


Display the drop areas for moving and adding fields

  • Right-click the title bar of the PivotTable list, and then click Drop Areas on the shortcut menu.

Notes

  • If the PivotTable list has no title bar, right-click the PivotTable list, and then click Property Toolbox on the shortcut menu. If the Show/Hide section is not expanded, click the Show/Hide bar. Click Drop Areas [image].

  • When fields have been moved to all of the areas, the drop area outlines and captions are not visible, but you can still move fields to the areas.


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

Tip To view only the summarized data for a new row or column field, click the Minus box boxes for the items of data in the field.


Change the level of a row or column field

  1. Click the field label for the field that you want to move to a different level.

  2. On the toolbar in the PivotTable list, do one of the following:

    To move the field to a higher level, click Promote [image].

    To move the field to a lower level, click Demote [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. Display the drop areas for the PivotTable list.

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

  3. 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 How the field that you drag to the data area is summarized depends 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

When you move a field to the filter area, you can filter the entire PivotTable list to display data only for the item you select in the filter field drop-down list.

  1. Display the drop areas for the PivotTable list.

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

  3. Drag the field to the filter area at the top of the PivotTable list, until the pointer changes to Pointer with filter area highlighted.

Note If you cannot drag fields to or from the filter area, the person who created the PivotTable list might have restricted access to filtering.


Move a field to the detail area

You cannot move fields to the detail area if the person who created the PivotTable list restricted access to detail data, or if the PivotTable list is based on source data from an OLAP database.

  1. Click the field label for the field that you want to move to the detail area.

  2. On the toolbar in the PivotTable list, click Move to Detail [image].


Change the column order

  1. Point to the field or item label at the top of the column until the pointer changes to Pointer with four-headed arrow.

  2. Drag the column to the new location.

Tip To quickly change the order of fields when you have multiple row fields or multiple column fields, click the field that you want to change, and then click Promote [image] or Demote [image] on the toolbar in the PivotTable list.


About displaying totals in a PivotTable list

PivotTable lists use two different types of totals to display summarized values. The first type, total fields, 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.

This topic explains how total fields work, how subtotals and grand totals work, and what changes you can make to how each is displayed.

Total fields

Subtotals and grand totals

Total fields

How total fields work Total fields are data fields that are either provided in the source data or are 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 type. In the field list, the total fields are displayed at the top, under Totals.

Example of a field list with total fields

The Sales and Units fields are available for inclusion in the PivotTable list as 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 creator of the PivotTable list.

Adding new total fields 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.

Example of a total field in a PivotTable list

The Sum of Sales total field, when added to the data area, provides summarized values for each sport and quarter.

If the source data allows it, you can also add total fields to the detail area. For example, to see the amount of each individual sale in the preceding example, you could add the Sum of Sales total field to the detail area. Note that you cannot add total fields to the row, column, or filter area.

Summary functions for total fields A total field can use any of the four summary functions — Sum, Count, Min, or Max. You can change the summary function to display a different type of summary, if the source data and type of field allow it. For example, if a field contains text, only the Count summary function is available for that field. Where the type of source data allows, 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.

Changing where total field labels are displayed 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.

Return to top

Subtotals and grand totals

Displaying 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, and subtotals and grand totals are displayed for every total field in the PivotTable list where it intersects the row or column field you select. How to display subtotals and grand totals for a field.

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

Example of subtotals and grand total in a PivotTable list

When subtotals and grand totals are displayed for the Sport and Quarter fields, the PivotTable list displays subtotals for the Quarter field and a grand total for the Sport field.

Moving or hiding subtotals and grand totals 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 this 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.

Changing alignment relative to detail data You can change the alignment of the text within the subtotal and grand total rows so that the text is left- or right-aligned or centered. You'll see the difference in alignment when you display detail for one or more items in a field that has subtotals and grand totals displayed.

Including and excluding hidden data Depending on the source data and the type of data you're viewing in the PivotTable list, you may 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.

Return to top


Add a total field to a PivotTable list

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

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

    If the AutoCalc button is unavailable, the source data for the PivotTable list might not allow you to add total fields. See the creator of the PivotTable list for more information.

Notes

  • If you remove a total field, the field remains available in the field list, and you can add it back into the PivotTable list. To delete a total field from both the PivotTable list and the field list, right click the total field and then click Delete on the shortcut menu.

  • You can add total fields to the data area, and, if the source data allows it, you can add total fields to the detail area. However, you cannot add these fields to the row, column, or filter area.


Change the summary function for a total field

  1. Select the total field for which you want to change the summary function.

    How?

  2. On the toolbar in the PivotTable list, click Property Toolbox [image].

  3. If the Total Function section is not expanded, click the Total Function bar.

    If the Total Function bar does not appear, the source data for the PivotTable list might not allow you to change the summary function. See the creator of the PivotTable list for more information.

  4. In the Function list, click the summary function you want for the total field.


Summary functions

The AutoCalc [image] command on the toolbar in a PivotTable list provides the following summary functions that you can use in total fields:

Sum Calculates the sum of the values.

Count Tallies the number of values. Empty values, such as zero-length strings, are included in the count, but null values are not included.

Min Returns the smallest value in the field being summarized.

Max Returns the largest value in the field being summarized.

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


Change the orientation of total fields

This procedure affects all total fields in the PivotTable list. It has no effect on subtotals or grand totals. How to change the alignment of subtotals and grand totals with respect to detail data.

  1. Select the entire PivotTable list by clicking the title bar.

    How?

  2. On the toolbar in the PivotTable list, click Property Toolbox [image].

  3. If the Totals section is not expanded, click the Totals bar.

  4. In the Total orientation list, do one of the following:

    Click Row 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.

    Click Column to display total fields across the first row below the column fields, or in the top row if there are no column fields.


Display subtotals and grand totals for a field

  1. Click the row or column field for which you want to display subtotals and grand totals.

  2. On the toolbar in the PivotTable list, click Subtotal [image].

Notes

  • Learn about how subtotals, grand totals, and total fields work in a PivotTable list.

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


Change the alignment of the subtotal and grand total items in a total field

Subtotal and grand total items for a total field are the labels, such as Sum of Sales or Count of Inventory, in the subtotal or grand total row or column. Alignment of subtotal and grand total items is relative to other cells in the detail area. To see the effect of the alignment changes, display detail for one or more items in a field that has subtotals and grand totals displayed.

  1. Select the total field for which you want to change the alignment of the subtotal and grand total items.

    How?

  2. On the toolbar in the PivotTable list, click Property Toolbox [image].

  3. If the Total Caption section is not expanded, click the Total Caption bar.

  4. In the Alignment within rows list, click the horizontal alignment you want (Automatic, Left, Center, or Right).


Include or exclude hidden data in subtotals and grand totals

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

  1. Select the entire PivotTable list by clicking the title bar.

    How?

  2. Click Property Toolbox [image] on the toolbar in the PivotTable list.

  3. If the Totals section is not expanded, click the Totals bar.

  4. Do one of the following:

    To include hidden data, select the Total all items check box.

    To exclude hidden data, clear the Total all items check box.

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 select the Total all items check box, asterisks appear in the subtotals and grand totals to note that the total values include the hidden items as well as the displayed items.


Hide subtotals and grand totals for a field

  1. Click the row or column field for which you want to hide subtotals and grand totals.

  2. On the toolbar in the PivotTable list, click Subtotal [image] so that the button is no longer pressed in.


About viewing details for summarized data

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

Expand indicators To change the level of detail displayed for each item of data in a field, use the expand indicators. The expand indicators are the Plus box and Minus box boxes that appear next to items. For example, if you have a row field named Product that includes the items Dairy and Meat, the expand indicators might look like this:

Example of expand indicators

If you don't see the expand indicators, you can display them. If they still don't appear for an item, that item might be the lowest level of detail available from the source data.

Viewing lower-level data When expand indicators are turned on, items that have lower-level data available to view have the Plus box box next to them. You can view lower-level data for an entire field or on an item-by-item basis, expanding only the items you want to see.

Viewing detail data A PivotTable list in which all fields are in the detail area displays all of the detail rows or records, unsummarized, from the source data. When a PivotTable list has row and column fields, you can view summaries of the data, or you can view the specific detail rows or records that make up the summarized value for an item or cell. You can view detail data only if the person who created the PivotTable list did not restrict access to detail, and only if detail is available in the source data. For example, OLAP databases often provide only summary data rather than detail data.

If you don't want to see detail data, you can hide the details.

Viewing higher-level summaries Items that are currently displaying lower-level data have the Minus box box beside them. You can collapse these items when you want to view only the higher-level data.

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 viewing sales for each month, and there were no sales in April, the Month field would not display an April item. If you want to include items for all twelve months, with blank cells for the months with no sales, you can display items that have no data.


Display the expand indicators next to items

  1. Click the PivotTable list.

  2. On the toolbar in the PivotTable list, click Property Toolbox [image].

  3. If the Show/Hide section is not expanded, click the Show/Hide bar.

  4. Click Expand indicators [image].

Note Items that don't have any lower-level data available don't have expand indicators.


Expand an item to display lower-level data

  1. Make sure the expand indicators are displayed.

  2. Click the Plus box box next to the item for which you want to see the next level of data.

Notes

  • To expand all of the items in a field, click the field label, and then click Expand [image] on the toolbar in the PivotTable list.

  • If an item doesn't have an expand indicator, the item might be the lowest level available for the source data. If your source data allows, you can display detail data for the lowest-level items.


Display detail data for an item or cell

  1. Click the item or cell for which you want to view the detail data.

  2. On the toolbar in the PivotTable list, click Expand [image].

Note If Expand [image] is unavailable, the creator of the PivotTable list might have restricted access to detail data, the item or cell might be the lowest level available for the source data, or detail data might not be available from the source data. For example, PivotTable lists based on source data from OLAP databases let you view different levels of data, but these lists often provide only summary data rather than detail data.


Display items that have no data

  1. Select the entire PivotTable list by clicking the title bar.

    How?

  2. On the toolbar in the PivotTable list, click Property Toolbox [image].

  3. If the Display Empty Items section is not expanded, click the Display Empty Items bar.

  4. To display row field items that have no data, select the Row check box.

    To display column field items that have no data, select the Column check box.


Collapse an item to display only higher-level data

  1. Make sure the expand indicators are displayed.

  2. Click the Minus box box next to the item for which you want to see only higher-level data.


Hide detail data

  1. Click the item of data for which the detail data is displayed.

  2. On the toolbar in the PivotTable list, click Collapse [image].

Note To hide detail for all of the items in a field, click the field, and then click Collapse [image].


About adding or removing data

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

How the data is initially selected The creator of a PivotTable 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 creator selects the fields that are initially displayed in the PivotTable list and determines how the data is summarized.

How you can display additional data 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 region you select.

Viewing and adding the available fields Click Field List [image] to view the list of 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 Plus box box to see more fields and the Minus box box to see fewer fields.

If the field list doesn't include data that you know exists in the source database, the creator of the PivotTable list didn't choose to include that data in the list.

Adding detail fields To see what data is available to be summarized, use detail fields. When you add a field to the detail area, you can view all of the detail records from the source data for the field. Field names become column labels, and the detail records are displayed in rows below the column labels.

Example of detail fields

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

Example of row and column fields

Adding data fields Data fields provide the summarized values 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. You can also 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 you could add a Count of Sales total field to calculate how many units were sold. Learn about totals in PivotTable lists.

Example of a data field

Adding filter fields A filter field is similar to a page field in a Microsoft Excel PivotTable report. Use filter fields to confine the display to a particular item of data from the field. When you add a filter field, data for all items in that field is initially summarized in the view. 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 one region at a time. How to add a filter field.

Example of a filter field

Moving fields After you add a field to a PivotTable list, you can move the field 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.

Restrictions on adding and moving fields If you work with a PivotTable list that is based on source data from an OLAP database, such as a database created by using Microsoft SQL Server OLAP Services, you'll notice that you can't move fields to the detail area. You'll also notice that some fields can be moved only to the row, column, and filter areas. Other fields, listed under Totals, can only be used as data fields.

Removing fields To eliminate data that you don't want to see or summarize, you can remove fields from the PivotTable layout. Removing a field does not delete 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, and you can add it back into the PivotTable list.


Add a new row or column field

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

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

  3. Click the field.

  4. In the list in the lower-right corner of the field list, do one of the following:

    To add a new row field, click Row Area.

    To add a new column field, click Column Area.

  5. Click Add to.

Note If Add to becomes unavailable when you select Row Area or Column Area, the field you selected might not be available for use as a row or column field, or the creator of the PivotTable list might have restricted moving fields to these areas. 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 and column fields.


Add a new data field

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

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

  2. Use the scroll bars and the 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 list in the lower-right corner of the field list, click Data Area.

  4. Click Add to.

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


Add a new filter field

When you add a filter field, you can filter the entire PivotTable list to display data only for the item you select in the filter field drop-down list.

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

  2. Use the scroll bars and the 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 list in the lower-right corner of the field list, click Filter Area.

  4. Click Add to.

Note If Add to becomes unavailable when you select Filter Area, the field you selected might not be available for use as a filter field, or the creator of the PivotTable list might have restricted access to filtering. 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 filter fields.


Add a new detail field

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

  2. Use the scroll bars and the 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 list in the lower-right corner of the field list, click Detail Data.

  4. Click Add to.

Note If Add to becomes unavailable when you select Detail Data, the creator of the PivotTable list might have restricted access to detail data, or 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.


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, and you can add it back into the PivotTable list.

  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 To delete a total field from both the PivotTable list and the field list, right click the total field and then click Delete on the shortcut menu.


Refresh a PivotTable list with updated source data

  1. Click the PivotTable list.

  2. On the toolbar in the PivotTable list, 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. You cannot cancel the refresh operation while the data is being retrieved. To cancel a refresh operation while the PivotTable list is being calculated, press ESC.

  • How to correct problems with refreshing a PivotTable List.


Cancel refreshing a PivotTable list

  • Press ESC.

Notes

  • You can cancel a refresh operation only when the PivotTable list is being calculated. While source data is being retrieved, you cannot cancel the retrieval.

  • After you cancel refreshing a PivotTable list, it temporarily displays no data. To redisplay the data, click Refresh [image] on the toolbar in the PivotTable list, or perform any operation that changes the layout of the PivotTable list.

  • How to correct problems with refreshing a PivotTable List.


About using a PivotTable list in Microsoft Excel or Microsoft Word

This topic contains information about:

Exporting to an interactive Excel PivotTable report

Copying data to another program for noninteractive use

Exporting to an interactive Excel PivotTable report

If you are familiar with Excel PivotTable reports and want to work with the data in this form, you can export the PivotTable list to Excel. (A PivotTable report is the Excel version of a PivotTable list.) 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 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, move all fields in the PivotTable list out of the detail area, and hide detail for items and cells so that no detail areas are displayed.

PivotTable list features that aren't exported Excel PivotTable reports can't display detail fields, so you won't see these fields in the Excel PivotTable report. All of the source data will still be available, however, and you will see all of the detail fields listed on the Excel PivotTable toolbar. For information about displaying detail for cells in Excel PivotTable reports, see Help in Excel.

For some types of source data, if you filter by selection, your filter settings aren't exported. In addition, filter settings for fields that aren't displayed aren't exported. However, you can refilter these fields in Excel.

Return to top

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 Word or Excel. 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 copied into Word or Excel.

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

Return to top


Copy an entire PivotTable list to Microsoft Excel or Microsoft Word

Use this procedure to copy a PivotTable list and paste it as a noninteractive, formatted table in Excel or Word. To copy the list as an interactive Excel PivotTable report, export the PivotTable list to Excel.

  1. Select the entire PivotTable list by clicking the title bar.

    How?

  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, you are copying only the formatting and data values. No link is maintained between the copy in Word or Excel and the original source database. The copy in Word or Excel is noninteractive and cannot be refreshed.

  • You can also copy part of a PivotTable list to Word or Excel.


Copy part of a PivotTable list to Microsoft Excel or Microsoft Word

Use this procedure to copy selected values in a PivotTable list and paste them as a noninteractive, formatted table in Excel or Word. To copy the data as an interactive Excel PivotTable report, export the PivotTable list to Excel.

  1. Select the part of the PivotTable list that you want to copy.

    How?

  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 part of a PivotTable list, you are copying only the formatting and values. No link is maintained between the copy in Word or Excel and the original source database. The copy in Word or Excel is noninteractive and cannot be refreshed.

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

  • You can also copy an entire PivotTable list to Word or Excel.


Export a PivotTable list on the Web to Microsoft Excel

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

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

  1. Click the PivotTable list.

  2. On the toolbar in the PivotTable list, click Export to Excel [image] 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 on the Web. All fields and data available in the field list on the Web — including fields that aren't in the layout of the PivotTable list — will be available for inclusion in the Excel PivotTable report, but detail fields won't appear in the report and some fields might not calculate the same values. For information about working with Excel PivotTable reports and adding fields to them, see Excel Help.


About formatting a PivotTable list to improve appearance or readability

If you have Microsoft Internet Explorer 4.01 or later, your formatting changes are retained when you exit the PivotTable list and return to it during your browser session. When you quit and then reopen Internet Explorer and return to the PivotTable list, your changes are gone.

Hiding the drop areas The drop areas are the outlined text boxes, such as "Drop Filter Fields Here," that you use to make layout changes. You can hide the drop areas when, for example, you want to print a PivotTable list without the drop area text. When you hide the drop areas, any drop areas that don't contain fields are removed from view. For example, if the PivotTable list has no filter fields, the filter field drop area is hidden.

Formatting similar elements Formats that you apply to one element can affect similar elements. For example, if you change the text color for a total, all totals in the PivotTable list are formatted with the new text color. If you move an element from one area to another, such as moving a detail field to the row area, the moved element takes on any formatting you've applied to its new area. How to select elements of a PivotTable list to format.

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

Aligning text in a cell You can display values left- or right-aligned or centered horizontally in the cells. You cannot change the values' vertical alignment. How to align text in a cell. You can also align subtotals and grand totals relative to detail data.

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. 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. How to change number formats.

If you want more formatting options 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.


Select elements of a PivotTable list

The Selection box in the General section of the PivotTable Property Toolbox indicates which element of the PivotTable list you currently have selected. To display the toolbox, click Property Toolbox [image] on the toolbar in the PivotTable list. How to identify the elements of a PivotTable list.

Multiple selection You cannot select more than one instance of an element at a time. For example, you cannot select more than one field at the same time. However, in most cases, the formatting that you apply to one element affects all elements of the same type in the PivotTable list, as described in the following table.

To select Do this
The entire PivotTable list Click the title bar.

If the PivotTable list has no title bar, right-click the PivotTable list and click Property Toolbox on the shortcut menu. Click the Show/Hide bar, and then click Title Bar [image].

If the Property Toolbox command is unavailable on the shortcut menu, the person who created the PivotTable list might have restricted access to it.
Data cells Click a cell to select it. Drag through a group of cells to select them.

You cannot format individual data cells.
Row, column, or filter field Click the field label.

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

Formats you apply to one field affect all row, column, and filter field labels in the PivotTable list.
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.

Formats that you apply to a detail field affect only the data cells for that individual field.
Total field Click any field label for the total field.

To display the total field labels, you must move at least one field to the row or column area, and you might need to hide detail for these fields.

Formats that you apply to a total field affect all total fields in the PivotTable list and affect only the data cells for the field. To format the total field labels, select an item of data in a row or column field.

See an example of a total field.
Item of data in a field Click the item label, being careful not to click the Plus box or Minus box indicator, if the item has one.

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

Formats that you apply to one item affect all item labels, detail field labels, subtotal labels, grand total labels, and total field labels in the PivotTable list.
Subtotal or grand total Click the subtotal or grand total.

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

Formats that you apply to a subtotal or grand total affect all data cells for the subtotals or grand totals in the row or column field for which subtotals and grand totals are displayed. To format the subtotal and grand total labels, select an item of data in a row or column field.

See an example of subtotals and grand totals.


Set the font and text style for data

You cannot set the font and text style for an entire PivotTable list in one operation. However, in most cases, formats that you apply to one element apply to all elements of the same type.

To set the font and text style text for the entire PivotTable list, select and individually format a row, column, or filter field; an item of data in a row or column field; and a total field. For every row and column field for which subtotals and grand totals are displayed, format the grand total or subtotal. If your PivotTable list can display detail fields, format each detail field individually.

  1. Select the element that you want to format.

    How?

  2. Click Property Toolbox [image] on the toolbar in the PivotTable list.

  3. If the Format section is not expanded, click the Format bar.

  4. Click the format options you want.

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

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

    To underscore 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, click a different size in the Size list.

Notes

  • If you set the text color to the same color as the background, you won't be able to see the text.

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


Align text within a cell

You cannot set the text alignment for an entire PivotTable list in one operation. However, in most cases, alignment that you set for one element applies to all elements of the same type.

To set the text alignment for all elements in the entire PivotTable list for which the alignment can be changed, select and individually change the alignment for an item of data in a row or column field, and a total field. If your PivotTable list can display detail fields, align the text in each detail field individually. You cannot change the horizontal alignment of row, column, or filter field labels.

  1. Select the element you want to change.

    How?

  2. Click Property Toolbox [image] on the toolbar in the PivotTable list.

  3. If the Format section is not expanded, click the Format bar.

  4. In the Horizontal Alignment box, click the alignment you want: Left, Center, Right, or Automatic. Automatic displays numbers and dates right-aligned and text left-aligned.

Note You can also align subtotals and grand totals relative to detail data.


Set background colors for cells

You cannot set background colors for all elements of a PivotTable list in one operation. However, in most cases, colors that you set for one element apply to all elements of the same type. For example, row, column, and filter fields are one type, so when you set the color for filter field labels, row and column field labels get the same color.

To set the background colors for all elements in the PivotTable list, individually select and set the background color for the entire PivotTable list, a row, column, or filter field; an item of data in a row or column field; and a total field. For every row and column field for which subtotals and grand totals are displayed, set the background color for the grand total or subtotal. If your PivotTable list can display detail fields, set the background color for each detail field individually. You cannot change the background color of the drop areas.

  1. Select the element you want to change.

    How?

  2. Click Property Toolbox [image] on the toolbar in the PivotTable list.

  3. If the Format section is not expanded, click the Format bar.

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

Note If you set the background color to the same color as the text, you won't be able to see the text.


Change labels and names

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. Select the label or name you want to change.

    How?

  2. Click Property Toolbox [image] on the toolbar in the PivotTable list.

  3. If the Captions section is not expanded, click the Captions bar.

  4. In the Caption box, type the new name.

Note To change the title bar text, click the title bar, and then type the new title in the PivotTable title box in the Format section of the toolbox.


Change the width of a column

  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:

    Drag the boundary until the column is the width you want.

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


Change number formats

You can change the format of numbers in detail fields and total fields. If the field for which you want to change the number format is in the row, column, or filter area — and moving fields to the detail area is allowed in your PivotTable list — you can move the field to the detail area temporarily, change the format, and then move the field back.

  1. Click the detail field or total field for which you want to change the number format.

  2. Click Property Toolbox [image] on the toolbar in the PivotTable list.

  3. If the Format section is not expanded, click the Format bar.

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

    Learn about the available number formats.


Number formats

The settings in the Regional Settings Properties dialog box 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 person who created the PivotTable list might have assigned custom number formats to the data. You cannot create custom number formats in the browser. The following number formats are available in the Number format list in the Format section of the PivotTable Property Toolbox.

Format | Effects
General number | Displays a number right-aligned, without a thousands separator, and preceded by a minus sign if negative. Displays up to 11 digits, or displays 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 Windows regional setting for the long date format. For example, Thursday, January 25, 2001.
Medium date | Displays a date according to the Windows regional settings for the month and date separator, and displays two digits for the year. For example, Jan/25/01.
Short date | Displays a date according to the Windows regional setting for the short date format. For example, 1/25/01.
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 AM or PM, 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, with a thousands separator if appropriate and two decimal places. 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 out two decimal places. The euro sign is placed before or after the number according to 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 with a thousands separator and two digits to the right of the decimal point. The Windows regional settings for numbers determine what's used for the thousands separator. For example, with a comma as the thousands separator, 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.


Hide the drop areas

When the drop areas are hidden and the PivotTable list does not already have any row, column, data, or filter fields, you cannot drag fields to these areas. However, you can still add and move fields by using the buttons on the toolbar in the PivotTable list.

  1. On the toolbar in the PivotTable list, click Property Toolbox [image].

  2. If the Show/Hide section is not expanded, click the Show/Hide bar.

  3. Click Drop Areas [image] so that the button is not pressed in.


Troubleshoot PivotTable lists

What do you need help with?

Refreshing

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

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

The query failed in a PivotTable list.

Filtering and sorting

The filtering commands don't work.

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

Data in a field isn't sorted correctly.

Moving fields

The commands that move fields don't work.

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

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

The PivotTable Property Toolbox or field list disappeared.

Data

I can't select the data I want.

I can't display detail data.

The data is hard to read.

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

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

Data is missing when I print a PivotTable list.

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

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

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

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

Why do totals in my PivotTable list have asterisks next to them?


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:

  • You can make the column wider so that the entire number fits.

  • You can make the font smaller if you don't want to widen the column.

  • You can change the number format to one that takes less space.


A chart that's based on a 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 make up the category and series data in your chart. If all of the displayed fields are in the detail area, 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.

Which programs are available from the Edit button in the Web browser depends 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 open the Web page from that program.


Data in a field isn't sorted correctly.

Levels might be missing from a dimension field When you sort a field in a PivotTable list that's based on source data from an OLAP database, the current and lower levels are sorted. The higher levels determine which lower-level items are sorted together. If one or more levels in a dimension are hidden when you sort data in a lower-level field, data is sorted as if the missing levels are present.

For example, if a dimension field includes Country, Region, and City levels, and the Region level is not displayed in the PivotTable list, when you sort items in the City field, the City items are sorted separately for each region, even though the region information is not displayed. As a result, if a country has North and South regions, the City items for the North region are sorted together, and the City items for the South region are sorted together. The sort would not combine all City items for the country, even though, with the Region level hidden, it appears that the City items should have been combined.

Include all levels before you sort To understand how the data is being sorted, remove all levels for the dimension, add the dimension field back into the PivotTable list, and then redisplay the levels so that none are missing above the level that you want to sort.


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.

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

Export to Microsoft Excel 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.


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

Print from Microsoft Excel For more control over how the data is printed, including the ability to print all of 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.


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. Learn about changing the layout of a PivotTable list.

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 creator 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 creator of the PivotTable list for more information.

Export to Microsoft Excel If you need to move fields, you can export a copy of the PivotTable list to 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 information about OLAP source data, see Excel Help.


I can't display detail data.

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

More details 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 the amount of detail available, contact the creator of the PivotTable list.

Displaying details might be restricted If the commands to turn on the expand indicators and display details are unavailable, the person who created the PivotTable list might have turned off the ability to display details. Contact the creator of the PivotTable list 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.


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

Filtering limitations Filtering capabilities that are available in Microsoft Excel worksheets and Excel PivotTable reports, including custom AutoFilter and top-ten filtering, are not available in PivotTable lists. Also unavailable is filtering that is based on part of the data in a cell, a feature which is available for forms and datasheets in Microsoft Access. Learn about filtering capabilities in PivotTable lists.

Export to Excel To use the Excel filtering features, export a copy of the PivotTable list to Excel.

Use Access to view the data 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.


The commands that move fields don't work.

Moving fields might be restricted The person who created the PivotTable list might have turned off the ability to move fields. For more information, contact the creator of the PivotTable list.

Export to Microsoft Excel If you need to move fields, you can export a copy of the PivotTable list to Excel. In the Excel PivotTable report, you won't be restricted from moving fields. For information about moving fields 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.

Export to Microsoft Excel 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.


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.


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 creator of the PivotTable list.

Export to Microsoft Excel If you need to filter the data, you can export a copy of the PivotTable list to 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.


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 creator of the PivotTable list to find out what type of source data the list has and, if the source data is one of these 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 creator 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 creator 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 creator 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 creator of the PivotTable list for more information.


The PivotTable Property Toolbox or field list disappeared.

When you click a control on the Web page other than a Microsoft Office Web Component control, or when you minimize and then restore the browser window, the toolbox and the field list are hidden.

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


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, you can turn off filtering to display all items, and then click Refresh [image] on the toolbar in the PivotTable list to run the query.


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 message "Calculating PivotTable" appears, 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.


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

Use the scroll bars in the browser The creator 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.


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 creator of the Web page.


Why do totals in my PivotTable list have asterisks next to them?

Asterisks appear next to totals in the following cases:

  • Totals include any hidden items When you set the option to include all items in totals, asterisks appear next to the totals to indicate that the totals include any hidden items in addition to the displayed items.

  • The PivotTable list was published from Microsoft Excel 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. However, if you save or publish the Excel report as a PivotTable list for the Web, the asterisks are displayed in the PivotTable list, even if you had turned them off in Excel.

You can change how totals are calculated 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.


Select an entire PivotTable list

  • Click the title bar.

    If the PivotTable list has no title bar, right-click the PivotTable list and click Property Toolbox on the shortcut menu. If the Show/Hide section is not expanded, click the Show/Hide bar, and then click Title Bar [image].

Note If the Property Toolbox command is unavailable on the shortcut menu, the person who created the PivotTable list might have restricted access to it.