Microsoft KB Archive/214021

= XL2000: Data Number Formatting Is Lost After You Create a PivotTable =

Article ID: 214021

Article Last Modified on 9/26/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214021



SYMPTOMS
In Microsoft Excel, when you create a PivotTable, the formatting of the numeric values in the table may not be retained.

For example, if you create a PivotTable using the following column of data

$56.00

$67.00

$32.00

the values in the PivotTable may be displayed as follows:

56

67

32



CAUSE
This behavior can occur if you do not take steps to set and preserve the formatting that you apply to the data values in the PivotTable.



WORKAROUND
You can set the formatting of a field that contains numbers in a PivotTable either while you are creating the PivotTable or after you create it. To do so, follow the steps of the appropriate method below.

Method 1: While Creating the PivotTable

 * 1) In the PivotTable and PivotChart Wizard - Step 3 of 3 dialog box, click Options.
 * 2) In the PivotTable Options dialog box, under Format options, click to select the Preserve formatting check box.

Method 2: After Creating the PivotTable

 * 1) Select the cells in the PivotTable that contain the numeric field values that you want to format.
 * 2) On the Format menu, click Cells.
 * 3) In the Format Cells dialog box, click the Number tab, select the number format that you want, and then click OK.

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

