XL: Source Data for PivotTable Displayed as Number Signs

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

After you create a PivotTable, some data in the source range for the PivotTable may be truncated or the data may be displayed as number signs (#).

CAUSE

This problem may occur when you place the PivotTable below the source data for the PivotTable. When you create a PivotTable, the columns containing the PivotTable are sized automatically to fit the data displayed in the PivotTable.

The columns contained in a PivotTable will be resized when you perform any of the following operations:

• You create a PivotTable.
• You Refresh a PivotTable.
• You change the layout of the PivotTable by dragging a field or data cell.
• You select an item in a Page field.

WORKAROUND

To work around this problem, disable the Autoformat Table option in the PivotTable Wizard. To do this, follow the steps for your version of Microsoft Excel.

Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition

To disable the Autoformat Table option, follow these steps:

1. Select a cell contained in your PivotTable.

2. Click PivotTable Report on the Data menu.

3. Click Next to advance to Step 4 of the PivotTable Wizard.

4. Click Options. Click to clear the Autoformat Table check box, and then click OK.

5. Click Finish

Microsoft Excel versions 5.0 and 7.0 for Windows, 5.0 for the Macintosh

1. Select a cell contained in your PivotTable.

2. Click PivotTable on the Data menu.

3. Click Next to advance to Step 4 of the PivotTable Wizard.

4. Click to clear the Autoformat Table check box, and then click Finish.

STATUS

This is by design of Microsoft Excel.

REFERENCES

For more information about formatting PivotTables, click the Index tab in Microsoft Excel Help, type the following text

  PivotTables, formatting