Problem: In a pivot table, a Values field tends to appear in a General format. This doesn't always work for me. I might want thousands separators or even to show numbers in thousands. If I change the number format using the settings in the Home tab of the ribbon, the number format is lost after the next pivot table refresh.
Strategy: Number formatting is controlled from the Field Settings dialog. There are two approaches for displaying Field Settings for the correct field.
- Use the Active Field group in the Options tab of the ribbon. To use this field, you must select either the Sum of Revenue heading or a cell that contains a revenue amount. The Active Field box will show Sum of Revenue. You can then click Field Settings.
- Alternatively, you can go to the Revenue field in the Field List drop zones, open the flyout menu and choose Field Settings. For an image, turn back to Fig 810.
- Choose a cell in the pivot table to change the active field.
Follow these steps to specify a number format for a pivot table field:
- Display the Field Settings dialog for the Sum of Revenue field using either of the methods described above. Excel will display the Value Field Settings dialog.
- Click the Number Format button at the bottom of the dialog.
- Access the number format from here.
- Excel will display an abbreviated version of the Format Cells dialog with only the Number tab. Choose an appropriate numeric format. You can create custom formats. Click OK to close the Format Cells dialog and then click OK to close the Value Field Settings dialog.
- Apply a number format to the field.
Results: the Revenue field will now always show the selected format, no matter how the pivot table is changed.
- Excel keeps the number format, even after you move fields.
The above method formats all Sum of Revenue cells using the numeric formatting attached to the Sum Revenue field to assign a non-currency format.
Gotcha: One of the conventions in formatting tables says that you should include a currency symbol on only the first and total rows of a data set. There is no inherent way to do this with a pivot table. However, you can use the numeric formatting attached to the Sum of Revenue field to assign a non-currency format. Then you select the first row of cells and assign a currency format by pressing Ctrl+1 to display the Format Cells dialog. This will work initially, but will be lost when you change the table.