Troubleshooting Common Pivot Table Issues

Symptom:

I keep getting the error "The PivotTable field name is not valid."

Hypothesis:

When you try to create a pivot table, you get the following error message:

The PivotTable field name is not valid. To create a PivotTable report,
you must use data that is organized as a list with labeled columns.
If you are changing the name of a PivotTable field, you must type
a new name for the field.

Result:

This message means that one or more columns in your data source do not have a header name. To correct this problem, go to the dataset you are using to create the pivot table and make sure that all columns have a header name.

Excel PivotTables

Excel PivotTables are a powerful data analysis tool, but they are not always easy to use.

Symptom:

Response Is Slow With External Data

Hypothesis:

• You're using certain external data sources For some types of external data sources, including large databases, on-sheet layout will be too time consuming. For example, a report based on a cube created with the OLAP Cube Wizard in Microsoft Query is likely to be slow to display layout changes. If you start to do on-sheet layout and data is slow to appear after you drag the fields into place, you can click PivotTable Wizard on the PivotTable toolbar to return to step 3, and then click Layout.

• You need to change the page field settings Use Layout if your PivotTable or PivotChart report is based on external non-OLAP source data and you want to create a page field that retrieves data for each item separately. Learn about using page fields to work with large databases. For PivotTable and PivotChart reports that are based on source data from OLAP databases, the page field options are not available.
  

Procedure:

• If you have not yet completed the PivotTable Wizard, but suspect that the layout process may be slow, click the Layout button in step 3 of the wizard. This step displays the Layout dialog box, which lets you define the PivotTable layout without the delays that might occur when using drag and drop after the wizard is complete.

• If you have already completed the PivotTable Wizard and find that the layout process is slow, select the Always Display Items command on the PivotTable menu. This command turns off data display, letting you complete the layout without waiting for data retrieval. When the layout is complete, select the command again to turn data display back on.

Another way to speed data retrieval is to use a Page field so that only part of the data to be retrieved at once (although this is not possible with OLAP data sources). Here’s how (when using the Layout dialog box to define the report):

1. Drag a field onto the Page area.
2. Double-click the field to open the PivotTable Field dialog box.
3. Click the Advanced button.
4. Select the option Query External Data As You Select Each Page Field Item.

Result:

This technique can also help when you receiving Out of Memory or Out of Resources error messages. It is also possible to avoid these messages by selecting the Optimize Memory option in the PivotTable Options dialog box.

When you create a table linked to external data, you might run into problems with slow response in your worksheet. Sometimes error messages pop up, too. You might not be able to fix all these problems because they might occur because of problems in the external data source itself or, if the data source is remote, in the connection to it. There are a few things you can try that may make a significant difference, however.

Many of these problems arise when laying out the PivotTable after completing the PivotTable Wizard. As you drag fields onto the PivotTable, Excel tries to retrieve the data, which is a process that can be slow.

Font: Peter Aitken