XL: Data Number Formatting Lost Creating Pivot Table

ID: Q118371

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

the values in the PivotTable are displayed as follows:

   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 method 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, click the Number button.

3. In the Format Cells dialog box, select the number format you want and

   click OK. Click OK to close the PivotTable Field dialog box.

After You create the PivotTable

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 number format you want and

   click OK. Click OK to close the PivotTable Field dialog box.

5. In Microsoft Excel 97, click Finish.

Note that this number formatting is not lost when you click the Refresh Data command to update the PivotTable.

REFERENCES

Excel 5.0

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

   Pivot Tables, customizing

Additional query words: XL97 97 7.00 5.00 customize custom

Keywords          : kbualink97 xlpivot 
Version           : WINDOWS:97,7.0,5.0,5.0c
Platform          : WINDOWS

Last Reviewed: January 8, 1999