About designing and publishing PivotTable lists for the Web

This topic provides a road map for the process of designing a PivotTable list and publishing it on the Web.

Overview

Before you begin

Step 1: Prepare the source data

Step 2: Create or open a PivotTable list

Step 3: Attach the source data to the PivotTable list

Step 4: Allocate space for the PivotTable list

Step 5: Design the data layout

Step 6: Restrict access in the browser

Step 7: Add other controls, automation, or Help

Step 8: Test and publish

Terminology in PivotTable lists

Overview

If you are new to PivotTable lists A PivotTable list is a Microsoft Office Web Component control that you can use on a Web page to present summarized data from a database or other source. Users who view the page can interact with the control from the Web browser. If this is the first time you've read about this control or begun designing or modifying one, you might want to learn more about what a PivotTable list is and what users can do with a PivotTable list in the browser.

If you need general information about Web page design, consult Help for the program you use to create your Web pages or Web site. Two programs that you can use to create Web pages with PivotTable lists are Microsoft Access 2000 data access page Design view (available in Microsoft Office 2000 Professional and Office 2000 Premium) and Microsoft FrontPage (available in Office 2000 Premium).

Design process The process of designing a PivotTable list has several steps, each of which is covered in more detail in this topic. The following is an overview of that process:

  • Preparing before you begin: Make sure that a PivotTable list is the right control for what you want to do and what your users will expect to do in the browser. Determine that the design program you're using is the right one for your planned design.

  • Preparing the source data (included in step 1 of this process): Identify and prepare a connection to the source data, which is the data you'll make available in the PivotTable list.

  • Setting up the PivotTable list control (steps 2, 3, and 4): To design the control itself, create the PivotTable list on your Web page or other document, attach the source data to it, and allocate space for it to use.

  • Designing the data layout (step 5): Design the initial presentation and organize and format the data.

  • Customizing your PivotTable list (steps 6 and 7, optional): Restrict access to detail data or features, create an accompanying chart, write scripts to automate tasks for the user, or provide your own help.

  • Testing and publishing (step 8): Check that the PivotTable list looks and works the way you want in the browser, make any changes and corrections, and then make the Web page available to users of your Web site.

Return to top

Before you begin

How will users work with the data? Before you begin designing a PivotTable list, you should consider whether your users will interact with the data or only view the data.

  • On a Web page If you plan to publish a PivotTable list on a Web page, keep in mind that interactivity is determined by which browser the user has. If you think your users will want to interact with the PivotTable list, make sure they have one of the browsers that fully support PivotTable lists.

  • In another program If you're designing the PivotTable list for use in a program other than a Web browser, make sure you understand what PivotTable list capabilities are available in that program. Learn about other programs in which you can use a PivotTable list.

Do you have the right control for your task? PivotTable lists are designed to present lists and information from databases for interactive use. Depending on the results you envision, you might want to use a different Microsoft Office Web Component control or include other controls along with the PivotTable list. Learn about when to use a PivotTable list.

Do you have the right design program? The design program is the program you use to create the PivotTable list. Different design programs have different capabilities. For example, to include a chart that reflects the data in your PivotTable list, you must use Microsoft Access data access page Design view. Learn about capabilities of different design programs.

Return to top

Step 1: Prepare the source data

The source data is the information that you want to display and analyze in the PivotTable list. Depending on the design program you're using and the type of source data you want to display, you might need to set up a data source or create a query before you can include the data in the PivotTable list. A data source is a means of recording the information necessary to connect the PivotTable list to the data in a database, and a query is a means of specifying which information to retrieve from the database. Learn about the types of source data you can use and setup considerations. For information and instructions about accessing source data from your design program for use in a PivotTable list, see Help for that design program.

If you started with an Excel PivotTable report When you publish a PivotTable report from Microsoft Excel to a Web page, Excel does part of the design work for you. However, some elements of PivotTable reports are changed when you save a report as a PivotTable list for the Web. There are some elements, such as custom calculations, that you might need to modify to display the results you want. How to work with a PivotTable list that was published from Excel.

Return to top

Step 2: Create or open a PivotTable list

The specific steps to create the control vary for different design programs. For specific information, see Help for your design program. Or if the Help for your design program does not provide a procedure, learn how to create a new PivotTable list.

If you have an existing PivotTable list that you want to modify, such as one saved from a Microsoft Excel PivotTable report or copied from another Web page, you can open the file containing the PivotTable list in a design program and then activate the control by following the instructions in that program (usually by clicking or double-clicking the PivotTable list). For details, see Help for your design program.

Locating and sizing the PivotTable list When you create a new PivotTable list, you'll usually designate an initial area of your Web page for the list to occupy, relative to other elements on the page. After you attach the source data (in step 3 of this process), you can allocate the amount of space it can use and then move the PivotTable list to the best position (see step 4 of this process).

Working within the PivotTable list After you create or select a PivotTable list in a design program, you work in the PivotTable list and continue to design it by using the toolbar and the PivotTable Property Toolbox in the PivotTable list.

Return to top

Step 3: Attach the source data to the PivotTable list

If you're working with an existing PivotTable list, the source data might already have been associated with the control and might also have been stored with the control itself. For a new PivotTable list, you need to establish a connection with the source data you've prepared in your design program. How to attach source data to a PivotTable list.

Return to top

Step 4: Allocate space for the PivotTable list

Allocating the initial size Within the PivotTable list control itself, you can set the overall size, including setting a fixed height and width within which the data can be scrolled or allowing the PivotTable list to expand up to a maximum size that you set and displaying scroll bars as necessary. The size you set overrides the initial size of the rectangle you drew or otherwise specified when you created the PivotTable list. Alternatively, you can allow the PivotTable list to expand or shrink as much as necessary to display the data. You can also set the size and row height for the detail area within a PivotTable list.

Changing the size later on These settings determine both the size of the PivotTable list in the browser and the size of the area you have to work with while designing the presentation of the data in the design program. Later, if you want a different amount of space for the PivotTable list, or you want different settings for the browser than you are using to design, you can change these settings.

Return to top

Step 5: Design the data layout

After you create a PivotTable list and add source data to it, the next step is to determine how you want the data to be presented when users first open your Web page.

Fit the presentation to your users' needs Consider how users are likely to interact with the data. On the one hand, if all of your users are familiar with PivotTable lists and will want to create their own data layouts and summaries, then you might want to merely display some of the fields from the source data. On the other hand, if your users might need a particular type of summary or view of the data, or rely on you to do the layout work, or have a mix of different skill levels, then you might want to lay out, organize, and format the data yourself. Learn about what users can do with a PivotTable list in the browser.

Keep in mind that in some browsers, such as Microsoft Internet Explorer version 3.x, users cannot view or interact with the data. To interact with the data, users must have a browser that supports PivotTable lists.

Set up the initial layout To display data in the PivotTable list, add fields from the source data to the drop areas for the different types of fields. Also consider how you want to summarize the data by creating and displaying totals.

Organize the data After you add the fields and totals, you have several options to arrange how the results are displayed. You can sort the data to change the order of items of data in a field. To display a subset of the data, you can filter the fields to display only items of data that match the values you want to display, or you can selectively display or hide detail for a field or item of data.

Format the data Formatting options include setting fonts and background colors, changing the display formats for numbers, aligning text within cells, changing column widths, displaying or hiding the drop areas for fields and formatting their captions, and displaying or hiding the expand indicators ( Plus box or Minus box) next to items. How to format a PivotTable list. Also, you can add and format a title for the PivotTable list.

Return to top

Step 6: Restrict access in the browser

Even in a browser that fully supports PivotTable lists, such as Microsoft Internet Explorer 5, users do not have access to some of the commands and features that you can use in a design program. To prevent users from making changes or accessing parts of the source data that you don't want to make available, you can set more restrictions. Learn about restricting access in the browser.

Return to top

Step 7: Add other controls, automation, or help

Include a chart You can use another Microsoft Office Web Component, the Chart Component, to display a separate chart of the data that's in your PivotTable list. In the browser, when a user changes the data displayed in the PivotTable list, the chart is automatically updated. For example, if your PivotTable list provides data about stock prices over time, you can include a chart on the Web page to display the price trends graphically. When the user selects different stocks to view in the PivotTable list, the chart is automatically updated to reflect the selected stocks. Learn about creating and using a Chart Component control.

Write scripts You can access and control many of the features and capabilities of PivotTable lists by using Web scripts or other programs. If you are familiar with writing scripts for Web pages in a language such as Visual Basic Scripting Edition (VBScript) or JavaScript, you might want to add scripts to the Web page to automate some tasks in the browser. For example, you might provide a script to change the PivotTable list to several of the most useful layouts and then add buttons to the Web page to select each layout.

Your design program might provide tools for writing scripts. For example, the Microsoft Office programs provide the Microsoft Script Editor. For information about writing scripts, see Help for your design program. For information about accessing features of PivotTable lists by using scripts, see the programming Help for PivotTable lists.

Provide help In the browser, users have access to separate Help available from within a PivotTable list that explains how to use its features, unless you restrict access to this Help. You can supplement this run-time Help by providing specific instructions or information on your Web page about using your PivotTable list. For example, you could tell users which database is supplying the source data and whom to contact for the password if you require one for access. Learn about providing your own help for a PivotTable list in the browser.

Return to top

Step 8: Test and publish

Check the PivotTable list in a browser Before making the PivotTable list available to your users, it is recommended that you test it in a Web browser. Microsoft Internet Explorer 5 or later is recommended for users who want to interact with PivotTable lists. If your users have different browser versions, you might want to test the PivotTable list in each version to make sure all your users will be able to accomplish the tasks you intend at run time. If you provide your own help for the Web page, make sure your help looks and works the way you intended. Learn about working with PivotTable lists in different browsers.

Make changes If you're not satisfied with the PivotTable list and Web page, you can go back into the design program and make changes. In addition to changing the settings you made while you designed the data layout and customized the PivotTable list, which are covered in earlier sections of this topic, you can do the following in the PivotTable list:

  • Change the layout

  • Remove fields

Publish the Web page When you are satisfied with the results, make your Web page available to your users. For information about publishing Web pages, see Help for your design program or consult with your Webmaster.

Return to top


What is a PivotTable list?

A PivotTable list is an interactive table that you can use to analyze data dynamically from within your Web browser. You can use a PivotTable list to view and organize data from lists or databases, look for information or details, and create personalized summaries and reports. However, you cannot add, edit, or delete the data values that are displayed in a PivotTable list.

Example of a PivotTable list

A simple PivotTable list displays a summary of data from a database.

Analyzing data

Create an initial design When you create a PivotTable list, you select data from an external database or other source that you want to make available to viewers on your Web page. This data becomes the source data in the PivotTable list. You can then set up an initial presentation or view of the data, such as the summary shown in the preceding example, that analyzes the data in one of the many ways possible. You do this work in a design program, such as Microsoft Excel 2000, Microsoft FrontPage 2000, or Microsoft Access 2000 data access page Design view.

Analyze the data further in the browser After you make the PivotTable list available on a Web page by publishing it, users who have a browser that supports PivotTable lists can also analyze the data. In the browser or other run-time environment, users can display different fields and change your initial view to suit their needs. Depending on your design program and methods, you might also be able to make the PivotTable list available in programs other than browsers.

Creating a summary report

Start with a basic list The following series of examples show how you, working in a design program, or a user, working in a browser, could turn a set of records from a database into a summary report. In the simplest form that you can create, a PivotTable list displays the records from the source data in a grid with the field names across the top. The following example contains fields named Sport, Quarter, and Sales.

Example of a PivotTable list with all detail fields

A simple PivotTable list presents a table of records from a database as a set of detail fields.

Create a row field You can create a summary by moving fields to other areas and sorting fields to produce a logical organization. For example, the PivotTable list that follows takes the preceding example and shows how you could create a summary by moving the Sport field to the row area to organize the data by sport and then sorting the Quarter field to put the sales in chronological order.

Example of a PivotTable list with a row field

Moving the Sport field to the row area organizes the data so that the Golf records precede the Tennis records.

Create a column field To compare the sales for each sport in each quarter, you can move the Quarter field to the column area, as shown in the following example.

Example of a PivotTable list with a row and column field

Creating a column field results in a cross-tabulated view of sales by sport and quarter.

Add totals and hide detail To view and compare total sales instead of the detailed sales data, you can add a Sum of Sales total field and then hide the detail data for the Sport and Quarter fields. A total field provides the values that are summarized in the PivotTable list. In the following example, the lower-right cell shows a total of the Tennis sales in Qtr4 — that is, the sum of the sales figures from every row in the source data that contains Tennis for the sport and Qtr4 for the quarter.

Example of a PivotTable list with summary data

Adding a total field provides a summary of the sales data.

Further analysis options To create the summary report that is shown at the beginning of this topic, you could then display grand totals for the Sport and Quarter fields.

To further analyze the data, you could filter the fields to find specific data. For example, you could display only the data about tennis sales in the third quarter. You could also add other calculations to the list. For example, you could display the largest sales amount for each sport.

More information

What users can do with a PivotTable list in the browser

About designing and publishing a PivotTable list for the Web


How users can work with a PivotTable list in the browser

This topic provides information about how users who have the Microsoft Internet Explorer Web browser can interact with a PivotTable list that you create. Understanding the browser capabilities of PivotTable lists can help you design them to better serve the needs of your users. Learn about what a PivotTable list is and can do. Learn about designing PivotTable lists.

This topic provides information about:

Working in a browser vs. working in a design program

The browser interface for PivotTable lists

What users can do in the browser

How the restrictions you set affect working in the browser

Terminology

Working in a browser vs. working in a design program

You can use the PivotTable Component to create a PivotTable list in a design program for access in a Web browser or another run-time environment. Microsoft Access 2000 data access page Design view is one of several design programs that you can use to create and modify PivotTable lists. Learn about the design programs you can use.

The run-time features discussed in this topic are available to users who have the Microsoft Office Web Components installed. They can do this by installing Microsoft Office 2000 or, if their company has an Office 2000 site license, by downloading the Office Web Components from their corporate intranet. Users must also have a Web browser that fully supports PivotTable lists. If you plan to create PivotTable lists for use in a program other than a Web browser, such as a Microsoft Visual Basic form that's part of a custom solution, see Help for your design program to find out how users of your work can access and interact with the PivotTable list.

Return to top

The browser interface for PivotTable lists

In the browser, users can activate a PivotTable list and interact with it by using the same toolbar and property toolbox that you use while you are designing the data layout. However, users don't have access to some features in the browser that are available to you in the design program.

Example of a PivotTable list on a Web page

In the browser, users can use the toolbar and the PivotTable Property Toolbox to interact with a PivotTable list.

In the browser, users can display the same PivotTable Field List that you use in the design program. The field list is available from the toolbar in the PivotTable list, and lists all of the fields you made available from the source data.

Example of a field list

In the browser, the user can view a list of all the fields that you've made available from the source database.

Return to top

What users can do in the browser

Users can display and analyze data Subject to any restrictions you set when you design the PivotTable list, the user can add fields from the field list and change the organization and display of the data. Users can analyze the data and create summaries, just as you can in the design program.

While keeping in mind the needs and skills of your intended users, you can perform all analysis in the design program and then publish the results accordingly. For general users, you can publish the finished summaries as separate PivotTable lists on your Web page. For more skilled users, you can make the raw source data available in a PivotTable list and leave the analysis up to the users. Learn about how to create a summary.

How the browser version affects what users can do To take full advantage of the capabilities of PivotTable lists, users need Microsoft Internet Explorer version 4.01 or later. In Internet Explorer 4.0, users have access to some of the PivotTable list capabilities. In other browsers, users cannot view or interact with the PivotTable list. Learn about the levels of support for PivotTable lists in different browser versions.

Access from the browser to the source database When you design a PivotTable list, you specify what source database to use and what subset of the data to make available as the source data in the PivotTable list. As the designer, you can also view information about the fields in the source database.

In the browser, users can display only the data you've made available; if a password is required to access the database, users must enter the password to display the data. The browser does not display the name or the type of the source database, nor does the browser allow users to view information about the fields in the database. Users can refresh the PivotTable list in the browser to reflect any changes in the source data, but they cannot gain more general access to the source database or make any changes to the data it stores.

Learn about source data for PivotTable lists.

Help for the user in the browser In the browser, the user has access to two types of Help: the Help system for the browser itself, and the additional Help content automatically provided from within a PivotTable list. The latter Help explains how to use the commands and features available in the PivotTable list in the browser. To view this Help, open a Web page that contains a PivotTable list in the browser, and click Help [image] on the toolbar in the PivotTable list. Learn about providing your own custom help for a PivotTable list in the browser.

Return to top

How the restrictions you set affect working in the browser

If you want to simplify the options available to users at run time, you can set restrictions that apply when the PivotTable list is viewed in the browser. For example, you can prevent users from displaying detail data. You might also want to set restrictions when you're doing the analysis tasks yourself in the design program and when you want to ensure that users don't modify some aspects of the way you've arranged the data. For example, you can prevent users from moving the row and column fields. Learn about restricting access in the browser.

Return to top


Elements of a PivotTable list

Example of a PivotTable list that shows its elements

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

Item A unique element of data within a field. Items of data 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 for a specific item. In the example, Region is a filter field you can use to display data for all regions or only 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, by using 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 by using the Min and Max summary functions, which give 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 display a drop-down list from which you can select the items to display and hide.

In PivotTable lists that are based on OLAP source data, you can select items at different levels in a dimension field. Date and time fields 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

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


Display the Help Table of Contents for your design program

When you display a PivotTable list Help topic, the Contents tab in the Help window lists only PivotTable list Help topics.

To display the topics in the Help system for your design program:

  1. Click the Answer Wizard tab in the Help window, type a question about a subject other than a PivotTable list, and then click Search.

  2. Click a topic in the Help system for your design program, and then click Display.

  3. Click the Contents tab to see the Help contents for the design program.


How to get assistance writing programs and scripts for PivotTable lists

You can use scripts and programs to automate PivotTable lists, or you can include 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 data access page Design view in Microsoft Access 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 Microsoft Office Web Components, of which PivotTable lists are one component. Depending on the design program you're using, you can get this Help in either of two ways:

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

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


Keyboard shortcuts for PivotTable lists

For information about activating and deactivating a PivotTable list by using the keyboard, see Help for your design program.

What do you want to do by using keyboard shortcuts?

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


Select elements within a PivotTable list by using keyboard shortcuts

Press | To
The TAB key | Select each field and then the entire PivotTable list, going 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 in a PivotTable list by using keyboard shortcuts

Press | To
F1 | Display Help topics. For information about keys to use in the Help window, see Help for your design program.
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 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 in a PivotTable list 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 (numeric pad) | Display lower levels of detail, if available, for the current item in the drop-down list for a field.
MINUS SIGN (numeric pad) | 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 PivotTable Field List dialog box

Keys for adding fields and totals

Keys for changing the layout

Keys for working with the PivotTable Field List dialog box

Press | To
CTRL+L | Display the PivotTable Field List dialog box, or activate it if it's already displayed.
Arrow keys | Move to the next item in the field list.
PLUS SIGN (numeric pad) | Expand the current item in the field list to display its contents. Or expand Totals to display the available total fields.
MINUS SIGN (numeric pad) | Collapse the current item in the field list to hide its contents. Or collapse Totals to hide the available total fields.
The TAB key | Alternately move to the most recently selected item, the Add to button, and the list next to the Add to button in the field list.
ALT+DOWN ARROW | Open the drop-down list next to the Add to button in the field list. Use the arrow keys to move to the next item in the list, and 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


When to use a PivotTable list

The Microsoft Office Web Components provide a set of tools that you can use to include interactive PivotTable lists, spreadsheets, and charts on your Web pages. Each component has features and capabilities that suit it to particular uses. This topic provides some guidelines to help you determine whether a PivotTable list or a spreadsheet or chart is the optimal choice for the tasks you expect users to perform with your Web page.

When a PivotTable list is the right choice

Displaying information from a separate database Use a PivotTable list when you want to provide information from a database — such as Microsoft Access or SQL Server records or OLAP data from a Microsoft SQL Server OLAP Services database — in a form that users can interact with and analyze from within the Web browser or in a run-time environment.

Viewing and analyzing, but not updating, data Users of a PivotTable list cannot update or change the underlying source data in any way in the browser. Use a PivotTable list if users will not need to enter changes to the data to perform their own calculations but will need to view and analyze existing data. For example, from Microsoft Access, you need a PivotTable list to publish crosstab-style data on a Web page.

Examples Here are some examples of good applications for PivotTable lists:

  • Making a database of sales figures available to users who want to compare sales for different products and regions.

  • Publishing stock price figures from a database that's updated frequently so that users can view and compare the performance of stocks that interest them.

  • Providing a product catalog that customers can filter to find the products they want and view the price, availability, and shipping cost information.

When to consider using a different control

If users need to enter and calculate data If users will need to enter changes to the data or enter formulas to calculate values, you might want to use an interactive spreadsheet on the Web page instead of a PivotTable list. For example, a loan payment calculator, where users can enter an amount to be financed and change the interest rate calculations, would be a better application for a spreadsheet than for a PivotTable list. You cannot access database data from a spreadsheet, however, in the ways in which you can from a PivotTable list.

If users need to update records in a database If you need a Web page control that can be used to access and update records in a database, see whether your design program provides one. Refer to Help for your design program for more information. Microsoft Access, for example, provides database controls, available in data access page Design view, that you can use for data entry tasks.

If users do not need to interact with the data If users will view data without interacting with it, a noninteractive table might be a simpler way to display static data. For information about other ways to make data available on a Web page, see Help for your Web page design program.

More information

What is a PivotTable list?

What users can do with a PivotTable list in the browser

Support for PivotTable lists in different browser versions


About support for PivotTable lists in different browsers

To view and work with PivotTable lists, users must have the Microsoft Office Web Components installed. They can do this by installing Microsoft Office 2000 or, if their company has an Office 2000 site license, by downloading the Office Web Components from their corporate intranet.

The full set of features that PivotTable lists on the Web can provide, such as letting users interact with data in a PivotTable list from within the Web browser, is not available in all browsers.

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

Microsoft Internet Explorer versions 4.01, 4.1x, and 4.2x Users have access to all PivotTable list features accessible from a browser. Changes are retained when users exit a PivotTable list and browse back to it later in the same browser session. In some cases, users must also have Internet Explorer 5 installed to view PivotTable lists on Web pages that were created by using data access page Design view in Microsoft Access.

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

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


About choosing a design program for a PivotTable list

A PivotTable list is a component that you create and design in one program, the design program, for use in another program, the run-time program. PivotTable lists are intended primarily for use in the Microsoft Internet Explorer Web browser. Learn about support for different browsers.

Supported design programs Design programs you can use to create PivotTable lists are Microsoft Excel, Microsoft FrontPage 2000, Microsoft Access 2000, and the Microsoft Visual Basic programming environment. You can create Web scripts to automate PivotTable lists in any of these design programs. Microsoft Word is not a suitable design program for PivotTable lists.

Working in more than one design program Depending on the content of your Web page and the tasks users will perform with your PivotTable list, some programs might be better suited to your design task than others. You can begin creating a Web page and adding components, including PivotTable lists, in one design program, and then open the Web page in another program to do further work.

For example, if you have designed a PivotTable report in Microsoft Excel, you can use Excel to save or publish this report to a PivotTable list on a Web page. (A PivotTable report is the Excel version of a PivotTable list.) If you want to customize the resulting PivotTable list to change the default settings, or if you want to add other controls to the page, you can then open and modify the Web page in FrontPage 2000.

Microsoft Excel If you want to try different layouts and adjust the source data you make available for your PivotTable list, Excel is an excellent design environment. You can use tools such as Microsoft Query from within Excel to access external data and create an Excel PivotTable report. From Excel, you save or publish a PivotTable report to a PivotTable list on a Web page. The source data you specified in Excel is automatically attached to the PivotTable list, and most Excel PivotTable report features are included when you publish. Learn about Excel features that aren't included in a PivotTable list.

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

Microsoft FrontPage 2000 FrontPage is the recommended design program for creating PivotTable lists for users who have Internet Explorer version 4.x, and for modifying PivotTable lists saved or published from Excel. In FrontPage, you can create PivotTable lists on Web pages (point to Component on the Insert menu, and click Office PivotTable), modify PivotTable lists that were designed in other programs, and then use the features of FrontPage to construct and manage your Web site.

Microsoft FrontPage 2000 is available in Microsoft Office 2000 Premium.

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

Microsoft Access is available in Microsoft Office 2000 Professional and Office 2000 Premium.

Visual Basic If you need a more comprehensive programming environment than the scripting capabilities of Excel, FrontPage, and Access provide, you can design PivotTable lists in the Microsoft Visual Basic development system. Use Visual Basic when you want to include a PivotTable list in a custom solution. Users can view and interact with PivotTable lists on the Visual Basic forms in your programs. Instead of a browser, your form becomes the run-time environment, and your program can automate many PivotTable list features.

Getting more design information See the Help for the design program you want to use to find out whether the program supports designing PivotTable lists and what its procedures are to create and modify one.


Create a new PivotTable list

The specific steps for creating a PivotTable list depend on the program you are using to design your Web page. For example, in Microsoft Access the data can be added to a PivotTable list automatically. For more information, see Help for your design program. If your design program Help does not provide a procedure for creating controls, such as PivotTable lists, follow these steps:

  1. In your design program, open or create the Web page where you want the PivotTable list to appear.

  2. By using the design program's procedure for adding a control to a Web page, select the PivotTable list control and designate the location and initial size of the PivotTable list on the Web page.

  3. Add your source data to the PivotTable list.

Note After you have created a PivotTable list, you can adjust the size and make other settings. Learn about the process of designing a PivotTable list.


Set the size of a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list, or if you have not yet added data to the PivotTable list, click the PivotTable list.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

  5. Do one of the following:

    To automatically expand and shrink the size of the area on the Web page used by the PivotTable list to fit the data currently being displayed, select the AutoFit check box. Then, to prevent the PivotTable list from expanding beyond a specified size, type a value in pixels in the Maximum height box and/or the Maximum width box.

    To display the PivotTable list in an area of a fixed size, clear the AutoFit check box, and then type the height and width in pixels in the Height and Width boxes.

Notes

  • These settings cannot be changed in the browser.

  • If you set a maximum height or width, or specify a fixed size, scroll bars appear within the PivotTable list when more data is available than will fit in the area.

  • You can separately set the size for the detail area.


About source data for PivotTable lists

A PivotTable list is a means of bringing data from external sources into a Web page for display and analysis. When you design a PivotTable list, you select the source data that the control will use and make available in the browser, and you establish a connection to that source data. In the browser, users can then refresh the PivotTable list to view updated source data.

Microsoft Access 2000 data access page Design view provides a set of features and commands for preparing data for use in PivotTable lists. For information about the methods and procedures that Access provides for using data on data access pages, see Access Help. Learn about programs you can use to design PivotTable lists.

This topic applies to all PivotTable list design programs and provides information about:

Types of source data

Connecting to source data

Refreshing when the source data changes

Types of source data

PivotTable lists can display data from three types of sources: relational databases, Microsoft Excel worksheets, and On-Line Analytical Processing (OLAP) databases.

Relational databases A PivotTable list can display and summarize data from a set of records in a relational database, such as Microsoft Access or Microsoft SQL Server. When your source data is from a relational database, you can create new total fields in the PivotTable list, display detail data for items in fields, and move fields to the detail area.

Excel worksheet data When you create a Microsoft Excel PivotTable report that's based on data from an Excel worksheet and then save the report as a PivotTable list on a Web page, the data from the Excel worksheet becomes a separate HTML file in a subfolder for the Web page. When your source data is from an Excel worksheet, you can create new total fields in the PivotTable list, display detail data for items in fields, and move fields to the detail area.

For information about publishing Excel PivotTable reports to Web pages, see Excel Help. Learn about working with PivotTable lists published from Microsoft Excel.

OLAP databases 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. OLAP data is stored in structures called cubes and organized by level of detail within the cube. A set of levels, such as country, region, city, and site information, is called a dimension and can be used as a set of fields in a PivotTable list. An OLAP cube can be a server database, a cube file extracted from a server database, or a cube constructed in memory from records in a relational database, depending on the OLAP server and client software you are using.

In PivotTable lists that are based on source data from OLAP databases, dimension fields (also called fieldsets) can display different levels of detail. When you use source data from OLAP databases, you cannot create your own total fields, you cannot move fields to the detail area, and you cannot display detail data. However, OLAP databases provide their own tools for creating summary data and calculations. Because they precalculate summaries instead of returning large amounts of detail data to the client PivotTable list to be summarized, OLAP databases allow you to analyze larger amounts of data than would otherwise be possible in a PivotTable list.

For information about creating summary fields in an OLAP cube or database that can then be displayed in a PivotTable list, contact your OLAP database administrator, or see Help for the Microsoft Office OLAP tools (for example, the OLAP Cube Wizard or the Offline Cube Wizard, both of which come with Microsoft Excel).

Return to top

Connecting to source data

If you are using Microsoft Access 2000 data access page Design view as your design program, you can use the methods and commands in Access to prepare data and establish connections. For information and procedures, see Access Help.

Setting up a data source To use external source data within your design program, you set up a data source on your system. A data source is a set of named, stored information that connects your design program to an external database and retrieves data. To set up a data source, you'll need a driver program specific to the type of source data. Microsoft Office provides a set of drivers for popular database types. Your design program might provide additional drivers, and you can obtain other Office-compatible drivers from many database vendors.

If you want to work with both OLAP databases and relational databases in your design program, you need to set up data sources for both types. Your design program might provide a means of setting up data sources, or you can use Microsoft Windows tools or Office tools to perform this setup.

You can use the ODBC Data Sources (32bit) command within the Windows Control Panel to set up many of the drivers provided with Microsoft Office. For example, to set up the Microsoft SQL Server driver, click Add on the User DSN tab (DSN means data source name), double-click SQL Server, and then follow the instructions to name the data source and supply the information to make the connection to your database. You can also set up data sources when you add data to a PivotTable list.

You can also use Microsoft Query (a component of Microsoft Office) to set up data sources, including those for OLAP databases. For further information about setting up data sources, see your design program and Help for your data source driver.

Creating a query to retrieve the data For source data from a relational database, after you set up a data source, you need to create a query, or write an SQL select statement or procedure, to define and retrieve the set of records you want. For information about how to do this, see Help for your design program and the documentation for your database. For example, Microsoft Access has tools both to create queries and to make sets of records available in the Access field list (available from the Access View menu) without creating a query. You can then use the field list to select data for use in PivotTable lists. You can also use Microsoft Query to create queries for data in various supported databases.

OLAP cube data sources To use OLAP data, you need an OLAP server database, cube file, or cube definition. You do not need to set up a query to access the data in the cube; you need to set up only a driver and data source. For more information, see your OLAP provider documentation or the information in Microsoft Excel Help, Microsoft Query Help, or Microsoft Access Help about working with OLAP databases.

Adding the data to the PivotTable list When you add data to a blank PivotTable list, you set up a connection between the PivotTable list and the data source. Adding the data to the PivotTable list provides the PivotTable list with all of the information necessary to reestablish that connection and retrieve the data when users open the PivotTable list in the browser.

Your design program might provide tools that help you set up this connection. For example, if you're using Microsoft Access 2000 data access page Design view, you can locate data and drag it to the PivotTable list, adding the data to the control automatically. For information about how to set up a connection from your design program, see Help for the design program.

If your design program does not provide tools or Help to set up a connection, you can construct a connection string for your data source by using the PivotTable Property Toolbox. For information about connection string syntax, consult Help for the data source driver you are using and the programming Help for PivotTable lists.

How to use the PivotTable Property Toolbox to add data to a PivotTable list.

How drivers and data sources affect users of your Web page Users who want to interact with the PivotTable list from the browser need to install an ODBC driver or data source driver specific to the source database before trying to view the PivotTable list. This applies to all types of source data except Excel worksheet data. If you use a source database for which the driver is not installed as part of Office, make sure the users of your PivotTable list have access to the driver.

In the browser, however, users do not need to set up a data source for the external database. When you add the data to the PivotTable list in your design program, the database connection information that the browser needs is incorporated into the PivotTable list and saved as part of your Web page. Whether users have to enter a login ID and password for the external database depends on how you set up the data source you use when you design the PivotTable list.

Return to top

Refreshing when the source data changes

After you have a connection established to a database, you can refresh the PivotTable list when you need to reflect updates and changes to the source data.

Relational databases For source data from relational databases, refreshing requeries the source database — that is, refreshing retrieves both changes to the data and any new data that meets the criteria you established. If you want the query, select statement, or stored procedure to select a different set of data or fields from the database, see the documentation for the database or the program you used to create the query for information about making changes.

OLAP databases For OLAP source data, new data is retrieved from the server or cube file whenever you make changes to the PivotTable list that affect what data is displayed. For example, if you add a new field to the row area, summary values are retrieved for the new items of data that are now displayed. If you are working with an OLAP cube file, see the documentation for the program you used to create the cube file for information about updating the file when information in the original server database changes.

Excel worksheet data If you plan to publish a PivotTable report from Microsoft Excel, it's best to first save a copy of your workbook in Excel and then publish the PivotTable report to a PivotTable list on a Web page. When you want to refresh a PivotTable list that was published from Excel with new or changed data in the original Excel workbook, or when you want to select a different set of Excel data for the PivotTable list, you can change the original Excel PivotTable report and then republish this report to the PivotTable list. For instructions, see Excel Help.

How data is refreshed in the browser In the browser, data is retrieved when the user first displays the PivotTable list. When the user browses to a different Web page and then returns to the page with the PivotTable list, the user's changes to the PivotTable list are retained, but the page is not reloaded from the Web server and the data in the PivotTable list is not refreshed. The user can refresh the data by clicking Refresh [image] and cancel a refresh in progress by pressing ESC.

Return to top


Add data to a PivotTable list

Your design program might provide a simpler way to add data to a PivotTable list. For example, in Microsoft Access 2000 data access page Design view, you can drag data directly onto the PivotTable list. If you are using data access page Design view, see Access Help for instructions instead of using the procedure in this topic.

Use the following procedure if your design program does not provide a procedure to add the data. For more information, see Help for your design program.

Before you can add data to a PivotTable list, you must install an ODBC driver or data source driver (OLE-DB or OLAP support software) and set up a data source to connect to the database. Learn about data sources.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the PivotTable list, or if data has already been added to the PivotTable list, click the title bar of the PivotTable list.

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

  4. If the Data Source section is not expanded, click the Data Source bar.

  5. Under Get to data using, do one of the following:

    If you added a data source control to the Web page to provide the source data, click Data source control and then, in the list below the option, click the control to use. Data source controls are a feature of Microsoft Access. For information about creating and working with data source controls, see Access Help.

    If your Web page does not have a data source control or if you want to connect to different source data, click Connection, and then click Connection Editor. In the Data Link Properties dialog box, enter the data source information for your database. For information about the settings to make, see Help for your ODBC or data source driver, and also see Help for data source setup in your design program. Learn about source data for PivotTable lists.

  6. Under Use data from, do one of the following:

    To use the data from a specific element within the database, such as an existing table or query, click Data member, and then select the element you want from the list.

    To use a query or command — such as an SQL statement — to select the data, click Command text, and then type the query or command in the box. For information about the query or command syntax, see the documentation for your database.

Note These settings cannot be changed in the browser.


About restricting access to a PivotTable list in the browser

Some PivotTable list features are available only when you're working in the design program. Other features can be changed by users of a PivotTable list in the browser. For some of these run-time features, you can restrict access if you don't want users to make changes in the browser.

Effects of browser version on access Whether users can make changes to a PivotTable list at run time depends on what browser they are running.

Capabilities that are never available in the browser In the browser, users cannot use the following features, which are available only when you are working on the PivotTable list in a design program:

  • Changing the size of the PivotTable list

  • Changing the size of the detail area

  • Adding source data to the PivotTable list

  • Automatically displaying detail data for all items

  • Formatting the title bar

  • Formatting or changing the drop area captions

  • Displaying the PivotTable list from right to left

  • Displaying information about fields and source data

Capabilities that are usually available in the browser If their browser fully supports PivotTable lists, users can make many of the same changes to the PivotTable list in the browser that you can make in the design program. Learn about using PivotTable lists in the browser.

Restrictions that you can set To prevent users from making changes to a PivotTable list or accessing portions of the source data that you don't want to make available, you can apply the following restrictions:

  • Make the PivotTable Property Toolbox unavailable

  • Prevent row and column fields from being added and moved

  • Prevent detail data from being displayed

  • Prevent users from changing the filtering


About providing custom instructions for a PivotTable list

In the browser, Help is available for users from within a PivotTable list. This run-time Help explains how to use the features and capabilities of PivotTable lists. Depending on the skills of your users and the type of work they will want to do, you might want to supplement the run-time Help by providing custom instructions for your PivotTable list.

Help available from the browser For users who have Microsoft Internet Explorer version 4.01 or later and the Microsoft Office Web Components, Help specific to PivotTable lists is automatically available. The run-time Help is separate from the Help available from the browser's Help menu.

The run-time PivotTable list Help topics provide general information for all users about using PivotTable lists in the browser and are not the same as the PivotTable list design Help topics you are reading now. Other Microsoft Office Web Component controls, such as the spreadsheet control, also have their own separate Help available in the browser.

To see what Help is provided in the browser, create a PivotTable list on a Web page, open the Web page in Internet Explorer, click the PivotTable list, and then click Help [image] on the toolbar in the PivotTable list. In the Help window, you can view a table of contents and search the topic text. The Office Assistant is not available in the browser to answer questions.

Users who do not have the Office Web Components and Internet Explorer 4.01 or later do not have access to run-time Help. Learn about working with PivotTable lists in different browsers.

Providing custom instructions If you want to provide specific instructions or information about the source data, or if your PivotTable list will be used in Internet Explorer 4.0, you can provide your own instructions on the Web page. Although you cannot connect your instructions directly to the online Help provided with the PivotTable list, you can include text directly on the Web page that describes the PivotTable list, or you can provide a hyperlink to a separate Web page of instructions. For information about how to do this on different types of Web pages, consult Help for your Web page design program.


About working with PivotTable lists that were published from Microsoft Excel

Microsoft Excel PivotTable reports have some capabilities that are not retained when a report is saved or published as a PivotTable list. As a result, you might want to use another design program to make additional changes after publishing. One important criterion in choosing which design program to use is knowing which Web browser your users are likely to have installed. Learn about choosing a design program.

The following Excel PivotTable report features are impacted when you publish it as a PivotTable list:

Calculations

Formatting

Layout

Display options

Source data and refresh options

Calculations

The following calculation features change or are not retained when you publish or save a PivotTable report in Excel as a PivotTable list:

Calculated fields Not retained. PivotTable lists do not support Excel calculated fields. Any calculated fields in Excel are displayed in the PivotTable list as total fields that use the default summary function. Calculated field formulas aren't retained.

Calculated items Not retained. PivotTable lists do not support calculated items.

Summary functions Only Sum, Count, Min, and Max functions are retained. Data fields in Excel that are summarized by using other summary functions are not retained. The unsummarized fields are available in the field list for the PivotTable list, and you can use them in the design program to add total fields that use one of the supported summary functions.

Custom calculations Not retained. PivotTable lists do not support custom calculations. Any data fields that have custom calculations in Excel aren't retained. The source field that you used for the data field is available in the field list for the PivotTable list, and you can use it in the design program to add total fields that use one of the supported summary functions.

Custom subtotals Not retained. In a PivotTable list, subtotals and grand totals always use the same summary function as the total field. If you change the subtotal or grand total summary function in Excel, the subtotal or grand total reverts to the summary function of the total field in a published PivotTable list.

Asterisks that mark totals Asterisks appear next to subtotals and grand totals in PivotTable lists that are based on source data from OLAP databases, even if you turned the asterisks off in the Excel PivotTable report. Subtotal and grand total calculations in PivotTable lists can include or exclude any items of data that are hidden by filtering.

Return to top

Formatting

The following formatting features change or are not retained when you publish or save a PivotTable list from Excel:

Character and cell formatting Not retained. Changes you make to fonts, text size, text color, and cell backgrounds in Excel, including any AutoFormats that you apply, are lost. How to format a PivotTable list in the design program.

Number formats Partially retained. Number formats that you apply to PivotTable fields in Excel are retained, but number formats that you apply to individual cells are not retained. If you format an Excel PivotTable field with a custom number format or an Excel number format different from the number formats supported in PivotTable lists, the published result is displayed with the same number format as the Excel PivotTable report. However, if you subsequently change the number format for the field in the design program or browser, you will not be able to return the field to the custom number format. In a PivotTable list, you can customize number formats only by using a program or script in the design program. Learn about getting Help for writing programs and scripts.

Print settings Not retained. The Excel settings to repeat PivotTable row and column labels on each page, repeat items labels after page breaks, and set page breaks between sections have no effect on how a PivotTable list is printed from the design program or the browser. Learn about printing PivotTable lists.

Return to top

Layout

The following layout features change or are not retained when you publish or save a PivotTable report in Excel as a PivotTable list:

Grouped items in fields Partially retained. Any selected items and numeric items that are grouped in Excel are no longer grouped in the PivotTable list, and group fields from Excel are not retained. PivotTable lists do not provide a means of grouping items in fields.

Grouped items in date fields are retained, and the items in these fields are automatically arranged in levels so that you can select the level you want (week, month, quarter, and so forth) in the design program or browser.

Custom sort orders Not retained. If you use a custom sort order in an Excel PivotTable field, the data in the PivotTable list is instead in the order in which it is retrieved from the source database. In the design program or browser, you can sort data in the PivotTable list, but you cannot create or use custom sort orders, nor can you manually rearrange the items of data in a field.

Indented format Not retained. If you use the Format Report command in Excel to create an indented-format PivotTable report, the PivotTable list is not in indented format. The fields are in the same positions, but the character and cell formatting is lost, and if you inserted blank rows between item groups in Excel, the blank rows are not present in the published PivotTable list.

Subtotals at top of item groups Not retained. If you display subtotals at the top of each subtotaled item group in Excel, the subtotals appear at the bottom of each group in the PivotTable list. You can change the horizontal alignment of subtotal items in the design program or browser, but you cannot change their vertical position.

Return to top

Display options

The following display options change or are not retained when you publish or save a PivotTable report in Excel as a PivotTable list:

Top/bottom items AutoShow Not retained. If you set a field in an Excel PivotTable report to display a subset of the items (PivotTable Field Advanced Options dialog box in Excel), all items in the field are displayed in the PivotTable list. In the design program, you can sort the field to display the top or bottom items first and then filter the field to display only the items you want, but PivotTable lists have no automatic top or bottom subset capability.

Errors and empty cells Not retained. If you customize how errors and empty cells are displayed in Excel, the customizations are lost in the published PivotTable list. PivotTable lists display errors and empty cells that were published from Excel as blank cells.

Page fields in rows or columns Not retained. If you change the Page layout and Fields per column settings (PivotTable Options dialog box in Excel), the changes have no effect on the published PivotTable list. Filter fields are always arranged across the top of the PivotTable list.

Return to top

Source data and refresh options

The following source data and refresh options change or are not retained when you publish or save a PivotTable report in Excel as a PivotTable list:

Password settings Not retained. In Excel, if you save your password with a query that you created to retrieve external data for a PivotTable report, users in the browser will still be prompted to enter the password when they open a Web page that contains the PivotTable list.

Refresh settings Not retained. If you set background refresh or the page field retrieval options in Excel, these options don't affect the published PivotTable list. PivotTable lists do not support background refresh or retrieving data for each item in a filter field individually. Learn about refreshing a PivotTable list.

Changing the source data Not available. From a PivotTable list in the browser, users cannot define a different range of Excel source data or change the query to select different external data. If you want to change the source data, you can make this change in Excel and then republish the PivotTable list. Learn about source data for PivotTable lists.

Client-server settings Not available. If you publish an Excel PivotTable report that's connected to an offline cube file, you cannot reconnect to the original server database from the published PivotTable list, and you cannot make changes to the content of the offline cube file from the design program or browser. If you need to perform either of these operations, you can do so in Excel and then republish to the PivotTable list.

Return to top


About creating and using a chart that's based on a PivotTable list

When you create a chart that's based on a PivotTable list, the data that's displayed in the PivotTable list is plotted in the chart. When you change the view of the data in the PivotTable list, the chart is dynamically updated to reflect the changes. For example, if you add a field to the row or column area, the chart is updated to reflect the new field as either category or series data. When you filter data or hide details in the PivotTable list, or when you remove a field so that is no longer included in the view, the data is also removed from the chart.

Before you begin Before you create the chart, be sure to move all the data you want to see in the chart to the row and column areas in your PivotTable list. By default, the data in row fields is displayed as series data in the chart, and the data in column fields is displayed as category data. You must also add at least one total field to the PivotTable list to have the data displayed in the chart. If you add more than one total field, the chart uses the data from the leftmost total field.

Creating a chart To create a chart, use your design program's procedure for adding a control to a Web page. For example, in Microsoft FrontPage 2000, you would point to Component on the Insert menu, and then click Office Chart. After you add the chart control, follow the instructions in the Microsoft Office Chart Wizard. In the wizard, you can select the type of chart you want and change the series data to use the data in column fields, rather than row fields.

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

Chart based on PivotTable list

A chart that's based on a PivotTable list. The PivotTable list column field Type is shown in the chart as category data (Beverages, Dairy, 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).

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

Chart updated with changes to PivotTable list

A chart that's updated with 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 of the PivotTable list. The Region field was added to the column area and filtered to show only East and North. The chart updated so that the data from the Type field is displayed in the legend as series, and the data from the Salesperson and Region fields supplies the categories.

More information

Create a chart that's based on a PivotTable list


Create a chart for the Web that's based on a PivotTable list

The specific steps for creating a chart that's based on a PivotTable list depend on the design program you are using to create your Web page. For more information, see Help for that program. If your design program Help does not provide a procedure for creating controls such as charts, follow these steps:

  1. In your design program, open or create the Web page where you want the chart to appear.

  2. If you don't have a PivotTable list on the Web page yet, create the PivotTable list.

    Be sure to add at least one total field, and move the data you want to see in the chart to the row and column areas. Data in row and column fields is used for the series and categories in your chart.

  3. By using the design program's procedure for adding a control to a Web page, select the chart control and designate the location and initial size of the chart on the Web page.

  4. Follow the instructions in the Microsoft Office Chart Wizard.

Note In Step 3 of the Chart Wizard, you can specify whether the data in the row fields or the column fields of your PivotTable list should be the series for your chart.


Save changes to a PivotTable list

To save your changes, use the design program's command to save the file containing the PivotTable list. For information about the save command, see Help for your design program.


Delete a PivotTable list

You cannot delete a PivotTable list from a Web page in the browser.

  1. In your design program, select the PivotTable list control. For instructions, see Help for your design program.

  2. Press DELETE.


Display and use the PivotTable Property Toolbox and the toolbar

What do you want to do?

Toolbar

Learn about toolbar buttons and commands

Hide the toolbar

Display the toolbar when it's hidden

PivotTable Property Toolbox

Use the toolbox

Learn about the toolbox commands and options

Make the toolbox unavailable in the browser

Not sure which choice you want?


About the PivotTable Property Toolbox and the toolbar

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 while you are designing it. In the browser, users can also use the toolbar to work with the PivotTable list. Rest the pointer on each toolbar button to display the name of its command.

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. If you select a control on the Web page other than a PivotTable list or other Microsoft Office Web Component control, the toolbox is hidden until you again select an Office Web Component control. The toolbox includes some commands that aren't present on the toolbar or shortcut menus. To display the toolbox, click the Property Toolbox button [image] on the toolbar in the PivotTable list.

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 (expand) and hide the contents of the sections by clicking the bars. How to use the PivotTable Property Toolbox.

Some properties and commands are available in the toolbox both when you are designing the PivotTable list and when it's displayed in the browser. Others are available only when you are working in a design program. Learn about the commands and properties in the PivotTable Property Toolbox.

Access to the toolbar and the PivotTable Property Toolbox in the browser If you don't want users to have access to the commands and property settings in the toolbox, you can make the toolbox unavailable in the browser.

If you don't want users to have access to the toolbar in the browser, you can hide the toolbar. To ensure that users cannot redisplay the toolbar, you must also make the toolbox unavailable because the toolbox includes a command to redisplay the toolbar. You must also hide the title bar to prevent access to the shortcut menu command to display the toolbar. Hiding the title bar does not prevent access to some of the commands provided on the toolbar. These commands will still be available from the shortcut menus for fields, items of data, and other selections.

If you do not want users to be able to make changes to the PivotTable list in the browser, consider providing the data as a noninteractive HTML table. Learn about designing a PivotTable list, including restricting user access and alternative ways to publish data on a Web page.


Display the toolbar in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

Notes

  • To display the toolbar if the title bar is hidden, 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].

  • Displaying the toolbar in the design program also makes the toolbar available when the PivotTable list is viewed in the browser.


Hide the toolbar in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the PivotTable list.

  3. Click Property Toolbox [image].

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

  5. Click Toolbar [image].

Note To ensure that users cannot redisplay the toolbar in the browser, you must also make the PivotTable Property Toolbox unavailable because the toolbox includes a command to redisplay the toolbar. You must also hide the title bar to prevent access to the shortcut menu command to display the toolbar. Hiding the title bar does not prevent access to some of the commands provided on the toolbar. These commands will still be available from the shortcut menus for fields, items of data, and other selections.


Toolbar buttons and commands for PivotTable lists

Toolbar buttons and commands are the same both when designing the PivotTable list and when viewing it in the browser.

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

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. You can use filtering to display only the rows or columns that match values you specify.

AutoCalc Adds a total field to the field list, summarizing the data in the selected field by using the Sum, Count, Min, or Max summary function. Unavailable in PivotTable lists that are based on source data from OLAP databases.

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 in a field by displaying the items of data 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 in a field by displaying the items of data 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 is displayed.

Move to Detail Moves the selected field to the detail area so that all data in the field appears in unsummarized form. Unavailable in PivotTable lists that are based on source data from OLAP databases.

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 farther from the data and are 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 and are to the right or bottom.

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

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

Export to Excel Runs or switches to Microsoft Excel and copies the PivotTable list into a new workbook as an Excel PivotTable report, which is 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 fields to the PivotTable list.

Help In a design program, displays Help topics about how to design and use a PivotTable list. In a browser, displays Help topics about how to use a PivotTable list in the browser.


Use the PivotTable Property Toolbox

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

    How?

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

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

    See a summary of the commands and properties available from the PivotTable Property Toolbox.

Notes

  • If you leave the PivotTable Property 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.

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

  • To prevent access to the PivotTable Property Toolbox in the browser, you can make the toolbox unavailable. The toolbox is always available in the design program.

  • Learn more about the PivotTable Property Toolbox.


PivotTable Property Toolbox commands and options

This topic lists all of the properties and commands available in the PivotTable Property Toolbox. The available commands depend on the part of the PivotTable list that you have selected. Learn about the elements that make up a PivotTable list. How to select parts of a PivotTable list.

If you are writing a program or script for a PivotTable list, you can find reference information about the objects, methods, and properties that are used by the PivotTable Property Toolbox options and commands in the Microsoft Office Web Components Visual Basic Reference.

To quickly find a command or property in this topic To find information about a specific command or property, click the Search tab in the Help navigation pane, type the name of the command or property in the Type in the keyword to find box at the top of the tab, and then click List Topics. Click the name of this topic, and then click Display. When you scroll through this topic, all instances of the name you typed will be highlighted. To turn this highlighting on or off, click Options [image] on the toolbar in the Help window, and then click Search Highlight On or Search Highlight Off.

To find a command or property by using the following list Click the item in the list that describes what you currently have selected in the PivotTable list:

All selections

The entire PivotTable list

A detail field

A row or column field

A filter field

A total field

An item of data in a field

A subtotal or grand total

Return to top

All selections

The following options are available whenever you display the PivotTable Property Toolbox, regardless of what is selected in the PivotTable list, including when you select a cell or a range of cells. These options are available both in the design program and in the browser.

Option | Description
General bar
Selection box | Indicates the part of the PivotTable list that is currently selected.
Help | Displays Help topics.
Show/Hide bar
Title bar | Displays the title bar at the top of the PivotTable list. This command becomes available once you add data to a new PivotTable list.
Toolbar | Displays the toolbar in the PivotTable list.
Field list | Displays the field list for the PivotTable list.
Expand indicators | Displays expand indicators beside items of data in row and column fields for which detail data can be displayed or hidden.
Drop areas | Displays the drop area outlines and captions. This command becomes available once you add data to a new PivotTable list.

Return to top

The entire PivotTable list

The Data Source and Advanced sections are also available in a new PivotTable list before you add data.

Option | Description
Format bar
PivotTable title box | Provides the text for the title bar at the top of the PivotTable list.
Background | Sets the background color for the entire PivotTable list.
Data Source bar (not available in the browser)
Data source control option and list | Specifies a data source control on the Web page to supply the source data.
Connection option and box | Supplies a connection string to add data to the PivotTable list.
Connection Editor button | Constructs a connection string from data source information that you provide in the Data Link Properties dialog box.
Data member option and list | Specifies the data from a specific element within the database as the source data.
Command text option and box | Supplies a query or command, such as an SQL statement, to select data from the database.
Totals bar
Total all items check box | When calculating subtotals and grand totals, includes the values for items of data that are hidden by filtering.
Total orientation list | Displays total fields in row or column orientation.
Display Empty Items bar
Row check box | Displays items that have no data in row fields.
Column check box | Displays items that have no data in column fields.
Detail Data Format bar (not available in the browser)
AutoFit detail data check box | Allows the detail area to expand or shrink to the size needed to display the data.
Maximum width box | Sets the largest width to which the detail area can expand when AutoFit is selected.
Maximum height box | Sets the largest height to which the detail area can expand when AutoFit is selected.
Detail data width box | Sets a fixed width for the detail area when AutoFit is not selected.
Detail row height box | Sets the row height for rows in the detail area.
Title Bar Format bar (not available in the browser)
Text format | Displays the title bar text bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the title bar text from the list of fonts available on your system.
Size list | Sets the size of the title bar text from the font sizes available on your system.
Background | Sets the background color for the title bar.
Drop Area Captions bar (not available in the browser)
Row area box | Provides the text for the row field drop area.
Column area box | Provides the text for the column field drop area.
Detail data area box | Provides the text for the detail field and data field drop area.
Filter area box | Provides the text for the filter field drop area.
Text format | Displays the drop area captions bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the drop area captions from the list of fonts available on your system.
Size list | Sets the size of the drop area caption text from the font sizes available on your system.
Background | Sets the background color for the drop areas.
Advanced bar (not available in the browser)
Expand items by default list | Automatically displays detail data for all items when the PivotTable list is first displayed.
Allow details check box | Allows display of detail data in the browser.
Allow Property Toolbox check box | Allows access to the PivotTable Property Toolbox in the browser.
Lock filters check box | Prevents changes to filtering and items of data that are displayed or hidden in the browser.
Lock row/column fields check box | Prevents users from moving fields to or from the row and column areas in the browser.
AutoFit check box | Allows the entire PivotTable list to expand or shrink to the size needed to display the data.
Maximum height box | Sets the largest height to which the entire PivotTable list can expand when AutoFit is selected.
Maximum width box | Sets the largest width to which the entire PivotTable list can expand when AutoFit is selected.
Height box | Sets a fixed height for the entire PivotTable list when AutoFit is not selected.
Width box | Sets a fixed width for the entire PivotTable list when AutoFit is not selected.
Display right to left check box | Displays the PivotTable list so it reads right to left for viewing in languages that read right to left.
Details available text | Indicates whether the source data includes detail data.

Return to top

A detail field

Option | Description
Format bar
Text format | Displays the data cells for the detail field bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the data cells for the detail field from the list of fonts available on your system.
Size list | Sets the size of the font for the data cells for the detail field from the font sizes available on your system.
Background | Sets the background color for the data cells for the detail field.
Horizontal alignment list | Sets the alignment of the text within the data cells for the detail field.
Number format list | Sets the display format for numbers in data cells for the detail field. See a list of the available number formats.
Width box | Sets the width in pixels of the columns displaying the detail field.
AutoFit field check box | Allows the column width for the detail field to expand or shrink as needed to display the data.
Captions bar
Caption box | Provides the label for the field.
Fieldset caption box | For a field that is part of an OLAP dimension, provides the label for the dimension.
Sort bar
Sort direction list | Sorts the items of data in the field in Ascending, Descending, or Default order. The default sort order is the order in which the items are received from the source database.
Advanced bar (not available in the browser)
Base field name text | Displays the name of the field in the original source database.
Data field text | If the source data for the PivotTable list is not an OLAP cube database, displays the name of the field in the source data that supplies the data for this field.
Data type text | Displays the data type of the field in the source data.
Field name text | Displays the name of the field in the PivotTable list.
Field type text | Displays the type of field: Regular, Time, Unknown, or User Defined.
Fieldset orientation text | Indicates what area the field is currently displayed in.
Fieldset type text | For a field that is part of an OLAP dimension, displays the type of dimension: Time, Other, Unknown, or User Defined.
Fieldset name text | For a field that is part of an OLAP dimension, displays the name of the dimension.

Return to top

A row or column field

Option | Description
Format bar
Text format | Displays the field label bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the field label from the list of fonts available on your system.
Size list | Sets the size of the font for the field label from the font sizes available on your system.
Background | Sets the background color for the field label.
Width box | Sets the width in pixels of the column displaying the field.
AutoFit field check box | Allows the column width for the field to expand or shrink as needed to display the data.
Captions bar
Caption box | Provides the text for the field label.
Fieldset caption box | For a field that is part of an OLAP dimension, provides the text for the dimension label.
Sort bar
Sort direction list | Sorts the items of data in the field in Ascending, Descending, or Default order. The default sort order is the order in which the items are received from the source database.
Advanced bar (not available in the browser)
Base field name text | Displays the name of the field in the original source database.
Data field text | If the source data for the PivotTable list is not an OLAP cube database, displays the name of the field in the source data that supplies the data for this field.
Data type text | Displays the data type of the field in the source data.
Field name text | Displays the name of the field in the PivotTable list.
Field type text | Displays the type of field: Regular, Time, Unknown, or User Defined.
Fieldset orientation text | Indicates what area the field is currently displayed in.
Fieldset type text | For a field that is part of an OLAP dimension, displays the type of dimension: Time, Other, Unknown, or User Defined.
Fieldset name text | For a field that is part of an OLAP dimension, displays the name of the dimension.

Return to top

A filter field

Option | Description
Format bar
Text format | Displays the field label bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the field label from the list of fonts available on your system.
Size list | Sets the size of the font for the field label from the font sizes available on your system.
Background | Sets the background color for the field label.
Captions bar
Caption box | Provides the label for the field.
Fieldset caption box | For a field that is part of an OLAP dimension, provides the label for the dimension.
Advanced bar (not available in the browser)
Base field name text | Displays the name of the field in the original source database.
Data field text | If the source data for the PivotTable list is not an OLAP cube database, displays the name of the field in the source data that supplies the data for this field.
Data type text | Displays the data type of the field in the source data.
Field name text | Displays the name of the field in the PivotTable list.
Field type text | Displays the type of field: Regular, Time, Unknown, or User Defined.
Fieldset orientation text | Indicates what area the field is currently displayed in.
Fieldset type text | For a field that is part of an OLAP dimension, displays the type of dimension: Time, Other, Unknown, or User Defined.
Fieldset name text | For a field that is part of an OLAP dimension, displays the name of the dimension.

Return to top

A total field

Option | Description
Format bar
Text format | Displays the data cells for the total field bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the data cells for the total field from the list of fonts available on your system.
Size list | Sets the size of the font for the data cells for the total field from the font sizes available on your system.
Background | Sets the background color for the data cells for the total field.
Horizontal alignment list | Sets the alignment of the text within the data cells for the total field.
Number format list | Sets the display format for numbers in data cells for the total field. See a list of the available number formats.
Width box | Sets the width in pixels of the columns displaying the total field.
AutoFit field check box | Allows the column width for the total field to expand or shrink as needed to display the data.
Total Caption bar
Caption box | Provides the label for the total field.
Alignment within rows list | Aligns the subtotal and grand total items in the total field with respect to the detail area, if displayed. Alignment can be Automatic, Left, Center, or Right. Automatic selects the default (left) alignment.
Total Function bar
Function list | Sets the summary function for the total field.
Advanced bar (not available in the browser)
Total source field text | Displays the name of the field in the source data that provides the values used to calculate the totals.
Total name text | Displays the name of the total field.
Total type text | Displays the type of total. Calculated uses one of the built-in summary functions. User Defined uses a user-developed program or script.

Return to top

An item of data in a field

Option | Description
Format bar
Text format | Displays the item label bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the item label from the list of fonts available on your system.
Size list | Sets the size of the font for the item label from the font sizes available on your system.
Background | Sets the background color for the item label.
Horizontal alignment list | Sets the alignment of the text within the cell for the item label.
Advanced bar (not available in the browser)
Item name text | Displays the name of the item from the source data.
Item unique name text | Displays the complete item name including the field name and, if the item is from an OLAP database, the dimension name.
Item caption text | Displays the label for the item in the PivotTable list

Return to top

A subtotal or grand total

Option | Description
Format bar
Text format | Displays the data cells for the subtotal or grand total bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the data cells for the subtotal or grand total from the list of fonts available on your system.
Size list | Sets the size of the font for the data cells for the subtotal or grand total from the font sizes available on your system.
Background | Sets the background color for the data cells for the subtotal or grand total.

Return to top


Make the PivotTable Property Toolbox unavailable

This procedure makes the PivotTable Property Toolbox unavailable in the browser at run time. You can continue to use the PivotTable Property Toolbox in the design program.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

  5. Clear the Allow Property Toolbox check box.

Note This setting cannot be changed in the browser. In the design program, the PivotTable Property Toolbox is always available, and this setting has no effect.


Add fields to a PivotTable list

What do you want to do?

Add fields

Row or column field

Data field

Filter field

Detail field

Total field

Specify how fields are used

View information about a field and its source data

Prevent row and column fields from being added and moved

Not sure which choice you want?


About adding fields to a PivotTable list

When you add the data to a PivotTable list, you specify the database to take the data from and the subset of that data to make available as the source data for the PivotTable list. From this source data, you can select fields for initial display in the PivotTable list. Learn about source data for PivotTable lists.

Viewing the available fields Click Field List [image] on the toolbar in the PivotTable list 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.

Source data limitations If your PivotTable list is based on source data from an OLAP database, such as one created by using Microsoft SQL Server OLAP Services, you'll notice that you can't 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 be used only as data fields.

Adding fields

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

Example of detail fields

Row and column fields Use row and column fields to summarize and compare data. These fields display 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

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

Example of a data field

Filter fields A filter field is similar to a page field in a Microsoft Excel PivotTable report. Use filter fields to confine the view to a particular part of the available data. When you 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 it to other areas. When you move a field, you change the layout of the PivotTable list, and you change the data that's displayed and the summaries that are calculated.

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

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 remove it from the available source data. A field that you remove is no longer displayed in the PivotTable list, but it remains available in the field list for viewing.

Specifying how fields are used

The features for adding fields and creating summaries are also available in the browser, unless you restrict the user's access. Learn about working with PivotTable lists and creating summaries in the browser.

Viewing information about fields You can view information about fields in a PivotTable list, including information about the field in the source database from which the data is taken and the type of data the source database is supplying for the field. This information is not available to users in the browser.

Restricting layout changes in the browser If you don't want users to change which fields you've designated as row and column fields, you can prevent row and column fields from being added and moved in the browser. You can also restrict access to detail so that users can't move fields to the detail area, and restrict access to filtering so that users can't move fields to the filter area.


Add a new row or column field to a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

  4. Click the field.

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

  6. Click Add to.

Notes

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

  • If you don't want users to add new row and column fields, you can prevent row and column fields from being added and moved in the browser.


Add a new data field to a PivotTable list

By default, data fields that summarize numeric data use the Sum summary function. Fields that summarize other types of data use the Count summary function. How to view the data type of a field.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

  4. Click the field, and in the list in the lower-right corner of the field list, click Data Area.

  5. 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 to a PivotTable list

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

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

  4. Click the field, and in the list in the lower-right corner of the field list, click Filter Area.

  5. Click Add to.

Notes

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

  • If you don't want users to be able to add new filter fields, you can make the filtering commands unavailable in the browser.


Add a new detail field to a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

  4. Click the field, and in the list in the lower-right corner of the field list, click Detail Data.

  5. Click Add to.

Notes

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

  • If you don't want users to add new detail fields, you can restrict access to detail in the browser.


Prevent row and column fields from being added or moved in a PivotTable list

This procedure prevents users from adding or moving row and column fields in the browser. After you complete the procedure, however, you can't add or move any more fields in the design program.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

  5. Select the Lock row/column fields check box.

Notes

  • This setting cannot be changed in the browser.

  • If you also don't want users to be able to add or move fields in the filter area, you can make the filtering commands unavailable in the browser.

  • If you also don't want users to be able to add or move fields in the detail area, you can restrict access to detail in the browser.

  • In Microsoft Excel, this restriction has no effect. Users who export a PivotTable list to Excel from the browser can then use the Excel features to add and move any fields available in the source data. To prevent users from moving fields in this way, you can restrict access to the Export to Excel command in the browser by turning off the toolbar and restricting access to the PivotTable Property Toolbox. Learn about restricting access to features in the browser.


View information about a field and its source data in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the field for which you want to view information.

    For information about an item of data in a field, click the item.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

    Learn about the information displayed about fields and items.

Note This information cannot be viewed in the browser.


Remove a field from a PivotTable list

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

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the field label of the field that you want to remove from the PivotTable list.

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


Change the layout of a PivotTable list

What do you want to do?

Specify how fields can be moved

Show the drop areas for moving and adding fields

Prevent row and column fields from being added and moved

Move fields

Move a field to the row or column area

Move a field to the data area

Move a field to the filter area

Move a row or column field to a higher level

Move a row or column field to a lower level

Move a field to the detail area

Add or delete fields

Add new fields

Remove a field

Not sure which choice you want?


About changing the layout of a PivotTable list

You can change how the data in a PivotTable list is summarized by changing the layout. A different layout lets you calculate and compare summarized values for different elements in your data, or display summaries for a subset of the data. When you make the PivotTable list available to users in the browser, users can make further changes to your initial layout.

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 you've hidden them, 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 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 does not have to include all of the fields that are available from the source data. If more data is available, you can add fields to the PivotTable layout. For example, if the PivotTable list summarizes sales revenue, and the source data also includes sales quantities, you might add the quantity field as a data field to summarize both revenue and quantity of products sold. You can also remove fields from the PivotTable layout that you no longer want to see.

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, 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 the items in a field are hidden by filtering when the arrow next to 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 one created with 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 that fields in the data area cannot be moved to other areas.

Restricting layout changes in the browser You can restrict the user's ability to change the layout of the PivotTable list by preventing row and column fields from being added and moved in the browser.


Display the drop areas for moving and adding fields in a PivotTable list

To display the drop areas, you must first add data to the PivotTable list.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

Notes

  • If you've hidden the 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 displayed, but you can still move fields to the areas.


Move a field to the row or column area of a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Display the drop areas for the PivotTable list.

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

  4. 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 in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

  3. 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 of a PivotTable list

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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Display the drop areas for the PivotTable list.

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

  4. 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 of a PivotTable list

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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Display the drop areas for the PivotTable list.

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

  4. 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 don't want users to be able to move fields into or out of the filter area, you can make the filtering commands unavailable in the browser.


Move a field to the detail area of a PivotTable list

You cannot move fields to the detail area if the PivotTable list is based on source data from an OLAP database.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

Tip You can prevent users from moving fields to the detail area by restricting access to detail data in the browser.


Change the column order in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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


Display totals in a PivotTable list

What do you want to do?

Add and format total fields

Add a total field to a PivotTable list

Change the summary function for a total field

Learn about summary functions

Display total fields horizontally or vertically

Display and format subtotals and grand totals

Display subtotals and grand totals for a field

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

Include or exclude data hidden by filtering in subtotals and grand totals

Not sure which choice you want?


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 created by you to summarize the data from other fields in the source data. For example, if the source data has a Sales field that records the amount of each individual sale, a Sum of Sales total field could add up the sales figures for each category in the PivotTable list, or a Count of Sales total field could tally the number of sales for each category. 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.

Source data and total fields In PivotTable lists that are based on source data from OLAP databases, the total field values are precalculated on the database server that supplies the source data. As a result, 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. Learn about source data for PivotTable lists.

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. Depending on the type of source data, you can also use more than one summary function to summarize the data in the same field by adding more total fields, each using a different summary function.

Changing where total fields 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 by using the values in the Sum of Sales total field.

Example of a subtotal and grand total in a PivotTable list

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

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-aligned, 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 using in the PivotTable list, you might want subtotals and grand totals to include or omit the values for items of data you've hidden by filtering. You can specify that all subtotals and grand totals either include or exclude any hidden data.

Return to top


Add a total field to a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

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

Notes

  • If you remove a total field from a PivotTable list, 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. However, you cannot add these fields to the row, column, detail, or filter area.


Change the summary function for a total field in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

    How?

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

  4. 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 does not allow you to change the summary function — for example, when the source data is from an OLAP database. Learn about source data for PivotTable lists.

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


Summary functions for PivotTable lists

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

Sum Calculates the sum of the values.

Count Tallies the number of values. Empty values, such as zero-length strings (strings that contain no characters), are included in the count, but null values (values that indicate missing or unknown data) are not included.

Min Returns the smallest value in the field being summarized.

Max Returns the largest value in the field being summarized.

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 in a PivotTable list

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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

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

  5. 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 in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

  3. 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 in a PivotTable list

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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

    How?

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

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

  5. 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 in a PivotTable list

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

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

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

  5. 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 in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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


Display details for summarized data in a PivotTable list

What do you want to do?

Choose what detail to display

Display the expand indicators next to items

Expand data to show lower-level data

Display detail data for an item or cell

Automatically display detail data for all items

Display items that have no data

Specify how detail is displayed

Set the size of the detail area

Make additional details unavailable in the browser

Not sure which choice you want?


About displaying details for summarized data in a PivotTable list

You can set up a PivotTable list to display any amount of detail that's available in the source data. You can display all of the available detail, only high-level summaries, or any level of detail in between, and you can display selected detail for items of data that you want to emphasize. Unless you restrict access, users can change the PivotTable list in the browser to display different levels and amounts of detail.

Choosing what detail to display

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 would look like this:

Example of expand indicators

If you've hidden 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.

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

Displaying detail data A PivotTable list where all fields are in the detail area presents all of the detail rows or records, unsummarized, from the source data. For example, the detail are might display individual invoice records. When you move fields from the detail area to the row and column areas, you can create summaries of the data, or you can display the specific detail rows or records that make up the summarized value for an item or cell. You can display detail data only if detail is available in the source data. For example, OLAP databases often provide only summary data rather than detail data. Learn about source data for PivotTable lists.

By default, detail data is not initially displayed in the browser when a user adds fields to the PivotTable list, but you can automatically display detail data for all items if you prefer.

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

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

Specifying how detail is displayed

Setting detail area size When you display detail for an item or cell, a detail area expands within the PivotTable list to display the detail rows. By default, this area expands to the size that is needed to accommodate the data to be displayed, within any limits you've set for the size of the PivotTable list as a whole. To specify how much of the available space the detail area is allowed to take up when users display detail in the browser, you can set the maximum detail area size and row height.

Restricting access to detail data in the browser You can display the amount and level of detail that you consider appropriate, and then prevent users from displaying additional detail in the browser.

Exporting to Microsoft Excel gives access to all detail In Excel, any restrictions you set on viewing detail data are no longer in effect. Users who export a PivotTable list to Excel from the browser can then use the Excel features to view all of the detail available in the source data. To prevent users from accessing the detail data in this way, you can restrict access to the Export to Excel command in the browser by turning off the toolbar and restricting access to the PivotTable Property Toolbox. Learn about restricting access to features in the browser.


Set the size and row height for the detail area in a PivotTable list

The detail area is the part of a PivotTable list where detail data is displayed. If detail data is displayed for more than one item of data, the PivotTable list might have more than one detail area. When you set the size of the detail area, this size is independent of the size you set for the PivotTable list as a whole.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Detail data format section is not expanded, click the Detail Data Format bar.

  5. Do one of the following:

    To automatically expand and shrink the size of the detail area to fit the data currently being displayed, select the AutoFit detail data check box. Then, to prevent the detail area from expanding beyond a specified size, type a value in pixels in the Maximum height box and/or the Maximum width box.

    To display the detail data in an area with a fixed width, clear the AutoFit detail data width check box, and then type the width in pixels in the Detail data width box.

  6. To set a fixed height for all rows in the detail area, type a value in pixels in the Detail row height box.

Notes

  • These settings cannot be changed in the browser.

  • You can also set the size for the entire PivotTable list.


Display the expand indicators next to items in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the PivotTable list.

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

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

  5. Click Expand indicators [image].

Notes

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

  • If you don't want users to be able to change whether expand indicators are displayed, you can restrict access to the PivotTable Property Toolbox in the browser.


Automatically display detail data for all items in a PivotTable list

By default, detail data for items is initially hidden in the browser, and the user can display detail data for a field by clicking Expand [image].

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

  5. In the Expand items by default list, click Always.

Notes

  • To hide detail data for all items, click Never or Automatic in the Expand items by default list.

  • These settings cannot be changed in the browser.

  • Detail data usually is not available for display in PivotTable lists that are based on source data from OLAP databases. If detail data is not available, changing the Expand items by default setting has no effect. Learn about source data for PivotTable lists.


Expand an item in a PivotTable list to display lower-level data

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Make sure the expand indicators are displayed.

  3. 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. Learn about source data for PivotTable lists.

  • If you don't want users to expand items beyond the level you set, you can restrict access to detail in the browser.


Display detail data for an item or cell in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

Notes

  • If Expand [image] is unavailable, 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 often provide only summary data rather than detail data. Learn about source data for PivotTable lists.

  • If you don't want users to display detail data, you can restrict access to detail in the browser.


Display items in a PivotTable list that have no data

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

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

  5. 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 in a PivotTable list to display only higher-level data

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Make sure the expand indicators are displayed.

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


Hide detail data in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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


Make additional details unavailable for a PivotTable list

This procedure makes details that you haven't displayed unavailable in the browser. After you complete the procedure, however, you can't display additional details in the design program.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

  5. Clear the Allow details check box.

Notes

  • This setting cannot be changed in the browser.

  • In Microsoft Excel, this restriction has no effect. Users who export a PivotTable list to Excel from the browser can then use the Excel features to view all of the detail available in the source data. To prevent users from accessing the detail data in this way, you can restrict access to the Export to Excel command in the browser by turning off the toolbar and restricting access to the PivotTable Property Toolbox. Learn about restricting access to features in the browser.


Sort data in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Do one of the following:

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

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

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

  3. 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 the Sort Ascending button [image] or the Sort Descending button [image] again so that the button is not pressed in.

  • 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. If users have different regional settings in their browsers, their sort orders might be different from yours.

  • In a PivotTable list that's based on source data from an OLAP database, 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 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 in a PivotTable list

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 two columns, determine which is the outer column and which is the inner column. Sort the inner column first, and then sort the outer column. To list the products in alphabetical order for each salesperson, you would first sort the inner Product column and then sort the outer Salesperson column.

How to sort data in a PivotTable list.


Filter a PivotTable list to display specific data

What do you want to do?

Filter a field to display only the data you want

Display only data that matches a cell

Add a new filter field

Use an existing field as a filter field

Prevent users from changing the filtering for a PivotTable list in the browser

Not sure which choice you want?


About filtering data in a PivotTable list

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. Sorting, filtering by using filter fields, and filtering by using filtering commands can be done both when designing the PivotTable list and when viewing it in the browser. However, you can restrict some access to filtering in the browser.

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 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 to display only Golf sales in Qtr3.

When you filter a PivotTable list, the drop-down arrows Field arrow for the filtered fields are blue instead of black and the AutoFilter button [image] on the toolbar in the PivotTable list is pressed in.

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 specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus. 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.

Filtering is cumulative After you turn filtering on by clicking the AutoFilter button [image] so that the button is pressed in, each filter is added to the ones you already have in effect. For example, if you apply filters to one field, and then filter by selection in another field, both filters are applied. All filters remain in effect until you turn filtering off. To clear the existing filters, turn filtering off, and then begin selecting new items to filter.

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 to reflect that item. You can add new filter fields to the PivotTable list or move existing fields to the filter area.

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

Preventing users from changing filtering If you do not want users to change how you have the data filtered, you can make filtering unavailable in the browser.


Filter a field in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the drop-down arrow Field arrow for the field that contains the items of data that you want to filter.

  3. 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 the AutoFilter button [image] so that the button is not pressed in. The filter selections that you previously made are retained; so 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 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. When items in a field are hidden by filtering, the drop-down arrow Field arrow in the field label is blue.

  • If you don't want users to have access to this feature, you can make filtering unavailable in the browser.


Display only data that matches a cell in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

Notes

  • 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. When items in a field are hidden by filtering, the drop-down arrow Field arrow in the field label is blue.

  • If the AutoFilter button [image] 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 the AutoFilter button is not pressed in, filtering by selection automatically removes any cumulative filtering you had previously set, and turns filtering on, starting a new accumulation.

  • To display all data for all fields, click the AutoFilter button [image] so that the button is not pressed in. The filter selections that you previously made are retained; so 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 don't want users to have access to this command, you can make filtering unavailable in the browser.


Remove filters to display all data in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

Notes

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

  • If you don't want users to be able to remove your filters, you can make the filtering commands unavailable in the browser.


Prevent users from changing the filtering for a PivotTable list

This procedure prevents users from changing the filtering in the browser at run time and also prevents you from further changing the filtering in the design program.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

  5. Select the Lock filters check box.

Note This setting cannot be changed in the browser.


Format a PivotTable list

What do you want to do?

Apply formatting to cells and data

Select parts of a PivotTable list to format

Set the font and text style

Set background colors for cells

Align text within a cell

Display the entire PivotTable list so that it reads right to left

Change number formats

Specify the formats for every element in the PivotTable list

Change and format titles and labels

Display or hide the title bar

Change labels and names

Format the title bar

Format total fields, subtotals, and grand totals

Display total fields horizontally or vertically

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

Format the drop areas

Hide the drop areas

Format the drop area captions

Not sure which choice you want?


About formatting a PivotTable list

How formatting works in PivotTable lists

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 both in the design program and at run time in the browser. 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 might 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 loses its previous formatting and 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, underlined, or italic. Changes that users make to the browser's settings for fonts and colors have no effect on PivotTable lists. How to set text formats.

Aligning text in a cell You can display values so that they are left-aligned, right-aligned, or centered horizontally in the cells. You cannot change the values' vertical alignment. 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. Changes that users make to the browser's settings for colors 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. In the browser, the settings in the user's Regional Settings Properties dialog box in the Windows Control Panel affect how the number formats you set are displayed. How to change number formats.

How to format every element in a PivotTable list

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

The procedures for applying text formatting and applying background formatting are the same for every element. Select the element first, and use the general procedures for applying text formatting and applying background formatting.

How to select elements in a PivotTable list.

How to apply text formatting and background formatting.

To format a PivotTable list

  1. Select a field in the row, column, or filter area and apply the text formatting and background formatting you want for the field labels.

  2. Select an item of data in a row or column field and apply the text and background formatting and horizontal alignment you want for the item labels, detail field labels, subtotal labels, grand total labels, and total field labels.

    How to change the horizontal alignment.

  3. Select a total field and apply the text and background formatting, horizontal alignment, and number format you want for the data cells in total fields.

    If your PivotTable list does not have any total fields, you can create a total field, format it, and then remove the field from the layout.

    How to change the number format for data.

  4. Select a grand total for each field and apply the text and background formatting you want for the data cells for subtotals and grand totals in that field.

    To display a grand total, you need to move the field to the row or column area and display subtotals and grand totals for the field.

  5. If your source data allows you to display detail data, set the alignment of subtotal and grand total items relative to the detail data for each total field.

  6. If your source data allows you to display detail fields, move each field to the detail area and apply the text and background formatting, horizontal alignment, and number format you want for each field.

  7. Select the entire PivotTable list, and apply the background format you want for the area above the row fields and to the left of the column fields. Apply the text and background formatting you want to the title bar.

  8. Display the drop areas and apply the text and background formatting you want to them.


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 this | 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] . You must add data to a PivotTable list before you can display its title bar.
Data cells | Click a cell to select it. Click and 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 in a PivotTable list

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 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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Select the element that you want to format.

    How?

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

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

  5. Click the format options you want.

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

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

    To underline the text, click Underline [image].

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

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

    To change the font size, 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 in a PivotTable list

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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Select the element you want to change.

    How?

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

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

  5. In the Horizontal Alignment box, click the alignment you want: Left, Center, Right, or Automatic. Automatic alignment displays numbers and dates so that they are right-aligned and text so that it is left-aligned.

Note You can also align subtotal and grand total items relative to detail data.


Set background colors for cells in a PivotTable list

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 colors for each detail field individually.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Select the element you want to change.

    How?

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

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

  5. 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 in a PivotTable list

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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Select the label or name you want to change.

    How?

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

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

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

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


Change the width of a column in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

Note You can also change the height of all rows in the detail area, although you cannot adjust the row height outside the detail area.


Change number formats in a PivotTable list

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 for your source data — you can move the field to the detail area temporarily, change the format, and then move the field back.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

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

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

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

    Learn about the available number formats.


Number formats for PivotTable lists

The settings in the Regional Settings Properties dialog box in the Windows Control Panel determine how number, currency, date, and time formats are displayed, both in the design program and at run time in the browser. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.

In Microsoft Visual Basic or in a script, you can assign custom number formats to the data. For more information, see Help for writing scripts and programs that use PivotTable lists.

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 so that it is right-aligned, with no thousands separator, and preceded by a minus sign if negative. Displays up to 11 digits, or 10 digits with a decimal point, rounding additional digits to the right of the decimal point. Displays numbers with more than 11 digits to the left of the decimal point in scientific format. Displays text so that it is 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 the AM or PM designator, omitting seconds. For example, 8:45 PM.
Short time | Displays a time in 24-hour format, omitting 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, using 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 in a PivotTable list

Hiding the drop areas hides them both in the design program and at run time in the browser. 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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  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 Drop Areas [image] so that the button is not pressed in.


Format the drop area captions for a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Drop Area Captions section is not expanded, click the Drop Area Captions bar.

  5. In the Row area, Column area, Detail data area, and Filter area boxes, make any changes you want to the caption text.

    If you don't want to show drop area captions at all, an alternative to deleting the drop area text is to hide the drop areas.

  6. Click the format options you want.

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

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

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

    To change the color of the captions, click the arrow next to Font Color [image], and then click the color you want. If you click the same text color as the background color, you won't be able to see the caption text.

    To change the font, click a different font in the Font list. The fonts available in the list are determined by the fonts you have installed on your system.

    To change the font size, click a different size in the Size list. The font sizes available are determined by the fonts you have installed on your system.

    To change the background color, click the arrow next to Background Color [image], and then click the color you want. If you set the background color to the same color as the text color, you won't be able to see the text.

Note These settings cannot be changed in the browser.


Display or hide the title bar in a PivotTable list

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

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  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. Do one of the following:

    To display the title bar, click Title Bar [image] so that the button is pressed in.

    To hide the title bar, click Title Bar [image] so that the button is not pressed in.

Note In the browser, users can display or hide the title bar and change the title bar text, unless you restrict their access to the PivotTable Property Toolbox. The options in the Title Bar Format section of the property toolbox are not available in the browser. How to format the title bar.


Format the title bar of a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

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

  5. In the PivotTable title box, type or change the text for the title.

    The Background command in the Format section formats the background area behind the data in the PivotTable list, not the title bar background.

  6. If the Title Bar Format section is not expanded, click the Title Bar Format bar.

  7. Click the format options you want.

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

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

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

    To change the color of the title text, click the arrow next to Font Color [image], and then click the color you want. If you click the same text color as the background color, you won't be able to see the text.

    To change the font, click a different font in the Font list. The fonts available in the list are determined by the fonts you have installed on your system.

    To change the font size, click a different size in the Size list. The font sizes available are determined by the fonts you have installed on your system.

    To change the background color, click the arrow next to Background Color [image], and then click the color you want. If you set the background color to the same color as the text color, you won't be able to see the text. To use the Windows system colors in both the design program and the browser so that the title bar on the PivotTable list matches the title bars of other windows, click Default.

Notes

  • If you don't want a title bar for the PivotTable list, click the Show/Hide bar and then click Title Bar [image] so that the button is not pressed in.

  • In the browser, users can display or hide the title bar and change the title bar text, unless you restrict their access to the PivotTable Property Toolbox. The options in the Title Bar Format section are not available in the browser.


Display a PivotTable list so that it reads right to left, or vice versa

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

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

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

  4. If the Advanced section is not expanded, click the Advanced bar.

  5. Do one of the following:

    To display the PivotTable list right to left, select the Display right to left check box.

    To display the PivotTable list left to right, clear the Display right to left check box.

Note This setting cannot be changed in the browser.


Refresh a PivotTable list with updated source data

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the PivotTable list.

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

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. While the PivotTable list is being calculated, press ESC.

Notes

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

  • After you cancel, the PivotTable list temporarily displays no data. To redisplay the data, click Refresh [image] on the toolbar in the PivotTable list.

  • How to correct problems with refreshing a PivotTable list.


Use a PivotTable list in Microsoft Excel or Microsoft Word

What do you want to do?

Export a PivotTable list to an Excel PivotTable report

Copy an entire PivotTable list to Excel or Word

Copy part of a PivotTable list to Excel or Word

Not sure which choice you want?


About using a PivotTable list in Microsoft Excel or other programs

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

Exporting to an interactive Excel PivotTable report

Copying data to another program for noninteractive use

Users have the same exporting and copying capabilities in the browser that you do in the design program.

Exporting to an interactive Excel PivotTable report

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

Preparing for export to Excel Excel PivotTable reports cannot display detail fields. When you export to Excel, detail fields will be available on the Excel PivotTable toolbar, but the fields won't be displayed in the report. If you want the Excel PivotTable report to reflect the appearance of the PivotTable list on the Web, before you export to an Excel PivotTable report, 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 The following PivotTable list features aren't supported in Excel PivotTable reports and, therefore, aren't exported:

  • Detail fields Excel PivotTable reports can't display detail fields. 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.

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

  • Week level in date fields When you export a PivotTable list that has the week level displayed, the Excel PivotTable report instead displays the month level. In Excel, you can group the field so that the days are displayed in seven-day groups. For information about grouping fields in PivotTable reports, see Help in Excel.

  • Restriction settings Any restrictions you set on moving fields, filtering, and viewing detail data in the PivotTable list are no longer in effect in the exported version. Because your users will also be able to export a PivotTable list to Excel from the browser, they can then use the Excel features to view all of the detail available in the source data. If you don't want users to gain access to the detail data in this way, you can prevent access to the Export to Excel command in the browser by turning off the toolbar and title bar, and restricting access to the PivotTable Property Toolbox. Learn about limiting access to features in the browser.

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 Excel or Word. When you copy the whole PivotTable list to another program, or copy selected parts of the data, the text and values from the PivotTable list become part of your Word document or Excel workbook. No connection is created between the original source data for the PivotTable list and the copy in the document or workbook, so you won't be able to refresh or update the data that was copied.

Copying to other programs You can also copy data displayed in a PivotTable list to other programs, such as Microsoft FrontPage or Microsoft Access. When you copy data to a program other than Excel or Word that does not support the HTML format that's used by PivotTable lists, 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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the title bar of the PivotTable list.

  3. On the toolbar in the PivotTable list, click Copy [image].

  4. Switch to Excel or Word, and click where you want the data from the PivotTable list to appear.

  5. 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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

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

    How?

  3. On the toolbar in the PivotTable list, click Copy [image].

  4. Switch to Excel or Word, and click where you want the data from the PivotTable list to appear.

  5. 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 to a Microsoft Excel PivotTable report

Use this procedure to export a PivotTable list from your design program 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. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click the PivotTable list.

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

Note Because PivotTable reports in Excel cannot display some PivotTable list layouts and calculations, the PivotTable report might look different from the PivotTable list in the design program. All fields and data available in the field list in the design program will be available for inclusion in the Excel PivotTable report, including nondisplayed fields, 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.


Troubleshoot PivotTable lists

Refreshing

I can't refresh the data in the PivotTable list from the source.

The query failed in a PivotTable list.

Filtering and sorting

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

Data in a field isn't sorted correctly.

Moving fields

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 connect to the source data that's specified for my PivotTable list.

I can't display detail data.

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

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

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

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

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


A cell in a PivotTable list displays ##### instead of a number.

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

  • Make the column wider so that the entire number fits.

  • Make the font smaller if you don't want to widen the column.

  • Change the number format to one that takes less space.


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

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

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


Data in a field in a PivotTable list isn't sorted correctly.

Levels might be hidden in 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 hidden 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 then 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 Microsoft Excel or Microsoft 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 data, 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, or you can publish the data again to a PivotTable list. For information on working with PivotTable reports in Excel and publishing them to a Web page, see Help in Excel.


I can't add or move the field I want in the PivotTable list.

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.


I can't connect to the source data that's specified my PivotTable list.

The source database might be unavailable For example, you might not have a network connection, or you might need access privileges or a password to connect to the server. In the browser, users who want to display and work with the PivotTable list will need the same access and permissions for the source database as you do in the design program. Contact the network or database administrator for assistance.

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. Users need to install the same support software to work with the PivotTable list in the browser. For information about the software you need, contact the database vendor.

The connection might not be set up correctly To connect to source data, you must first set up a connection, or data source, between the source database and your design program. Make sure the data source you set up in your design program is working and allows you to access the database. Check Help in your design program and in the data source driver or ODBC driver for setup information. When you add the data to the PivotTable list, make sure you include all necessary connection information. Learn about source data for PivotTable lists.


I can't display detail data in the PivotTable list.

Expand indicators might be turned off If you don't see the Plus box boxes next to 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 the expand indicators on.

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. Learn about types of source data for PivotTable lists.


I can't filter the data in the PivotTable list 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 for the Web. 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.

Use Access to view the data If you need partial-cell filtering, work with the source data in Microsoft Access instead of a PivotTable list. You might be able to view the data in a form or datasheet directly in Microsoft Access, where partial-cell filtering is supported. After you have filtered the data the way you want it in Access, you can use data access page Design view to create a new PivotTable list or change the existing one to use the filtered source data. For more information, see Access Help.


The fields are present in a PivotTable list, 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 PivotTable list is "unable to refresh" the data from the source.

Some PivotTable lists can't be refreshed PivotTable lists that you save or publish from Microsoft Excel worksheet data, Excel Web queries, and Excel text queries can't be refreshed from the original source data in other design programs or in the browser. In these cases, you must refresh the original Excel workbook and republish the data.

The source database might be unavailable For example, you might not have a network connection, or you might need access privileges or a password to connect to the server. In the browser, users who want to display and work with the PivotTable list will need the same access and permissions for the source database as you do in the design program. Contact the network or database administrator for assistance.

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. Users need to install the same support software to refresh the PivotTable list in the browser.

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, you might have to create a new PivotTable list. Contact the database administrator 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 design window, the toolbox and the field list might be 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.


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.


When I change the layout of a PivotTable list, 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.


Print a PivotTable list

To print a PivotTable list, use the print command in your design program. The results you get depend on the design program's print capabilities. For more information, see Help for your design program.

Note In Microsoft FrontPage 2000, you might not be able to print a PivotTable list correctly.