XL: Data Number Formatting Lost Creating Pivot Table

• 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 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.

REFERENCES

Excel 5.0

Pivot Tables, customizing

Additional query words: XL97 97 customize custom

Keywords : kbualink97 xlpivot
Version : WINDOWS:5.0,5.0c,7.0,97; winnt:5.0
Platform : WINDOWS winnt
Issue type :