» Formatting a Value Field in Excel 2007

The formatting of a value field must be saved in order to keep the formatting while PivotTable structure is changed.

Option 1: Simple & Easy Formatting

To format and save a value field:

1. Select a cell in a value field.
2. Right click and select Number Format, then choose a desired formatting and click OK.
3. Right click and select Summarize Data By -> and select a function.
4. Select the title above the value field and type a new title, for example, change Sum of Quantity to Total Quantity.



Option 2: Complex Formatting

To format and save complex formatting:

1. Select and drag Quantity field in PivotTable Field List dialog box to Values area and locate it under the first Quantity field, drag it twice more, the PivotTable is now looking like this:



2. Select a cell in the second Quantity field (column C in the screenshot), Right click and select Value Field Settings.
Or
Select Options tab (in PivotTable Tools Ribbon) -> Field Settings (in Active Field Group).
3. In the Data Field Settings dialog box, type a title in the Custom Name box to appear at the summarize report (you cant use the original Source Name of the field).
4. Select Summarize by tab
-> and select the function to summarize values (in this example select the SUM function).
5. Select Show values as tab -> and select % of total from dropdown list and click OK.



6. Follow the instructions in Option 1 above and apply formatting to the third Quantity filed using the COUNT function.
7. Follow the instructions in Option 2 above and apply formatting to the fourth Quantity field using the COUNT function.
8. You may add the Income field as many times as needed and change the formatting.
The PivotTable is now looking like this: