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

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 the values in the PivotTable may be displayed as follows:

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.

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.

For more information about customizing a PivotTable, click Microsoft Excel Help on the Help menu, type custom calculations for pivottable and pivotchart data fields in the Office Assistant or the Answer Wizard, and then click Search to view the topic.