Formatting a Value Field in Excel Pivot Table

Follow by Email

The formatting of a value field must be saved in order to keep the formatting while creating Pivot Table & then 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.
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:


Please follow and like us:

One thought on “Formatting a Value Field in Excel Pivot Table

  1. How can I preserve a custom name of a value field even after it has been dragged out of the pivot table? I mean, when I drag out a field then later drag it back in, the custome name is gone, it goes back to Sum of Field Name with the number format also gone.

Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>