XL: Data Number Formatting Lost Creating Pivot Table

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel, when you create a PivotTable, the formatting of any numeric values in the table is not retained.

CAUSE

This behavior is by design in Microsoft Excel. The data values used in a pivot table are not formatted. For example, if you create a PivotTable using the following column of data

   Amount
   $56.00
   $67.00
   $32.00

   56
   67
   32

WORKAROUND

To set the formatting for your data when you create a PivotTable, you can format a field that contains numbers in a PivotTable, either while you are creating the PivotTable, or after you create the PivotTable. To do this, use the appropriate procedure below.

While creating the PivotTable (in the PivotTable Wizard):

1. In the PivotTable Wizard--Step 3 of 4 dialog box, double-click the field button of the field that contains the numeric data.

2. In the PivotTable field dialog box, choose the Number button.

3. In the Format Cells dialog box, select the desired number format and click OK. Click OK to close the PivotTable Field dialog box.

1. Select a cell in the PivotTable that contains one of the numeric field values that you want to format.

2. On the Data menu, click PivotTable Field.

   In Microsoft Excel 97, click PivotTable Report on the Data menu. Then, double-click the button for the field whose format you want to change.

3. In the PivotTable Field dialog box, click the Number button.

4. In the Format Cells dialog box, select the desired number format and click OK. Click OK to close the PivotTable Field dialog box.

5. In Microsoft Excel 97, click Finish.

REFERENCES

For more information about customizing a PivotTable, click the Search button in Help and type:

   PivotTables, customizing