Midlands Microsoft Office Provider

4.4 Customize a Pivot Table by changing Value Field Settings and format numbers to currency – Practice Exercise 26 – Intermediate Excel 2016

Before beginning Practice Exercise 18, ensure the following is in place:

  • For Report Layout, ensure you are in the Tabular Form.
  • In the Columns quadrant of the Fields List, ensure that the Values field is above the Category field, as shown in FIGURE 1.
                                                                                    FIGURE 1
Practice Exercise 26 – Customize a Pivot Table by Changing the Value Field Settings and Format the Numbers to Currency
Now we will address readability issue #2 – Change Value Field Settings

STEP 1: On our pivot table, select the field header, “Sum of SalesQty” (FIGURE 2).
                                                                                   FIGURE 2
STEP 2: Right-button mouse that click that cell and select the shortcut menu choice, “Value Field Settings” (FIGURE 3).
                     FIGURE 3
STEP 3: In the Custom Name window, enter “SalesQty” (FIGURE 4).
Since there is already a field name with this name, Excel will not allow this name, so after entering “SalesQty” add a space.

STEP 4: Select the OK button.
Field header, “Sum of SalesQty” has been replaced with “SalesQty”. Notice that the other field “Total Sum of SalesQty” has also changed to “Total SalesQty” (shown below in FIGURE 8).
                                                  FIGURE 4
STEP 5: ​Select the field header, “Sum of Sales” (FIGURE 5).
                                                                                  FIGURE 5
STEP 6: Right-button mouse click that cell and select the shortcut menu choice, “Value Field Settings” (FIGURE 6).
                                                  FIGURE 6
STEP 7: In the Value Field Settings dialog box, in the Custom Name window, enter “Sales” (FIGURE 7). Since there is already a field name with this name, Excel will not allow this name, so after entering “Sales” add a space.
 

​STEP 8: Select the OK button.

Field header, “Sum of Sales” has been replaced with “Sales”. Notice that the “Total Sum of Sales” header has been changed to “Total Sales” (shown below in FIGURE 8).

It is better to format using Value Field Settings interface when formatting data. If you move the data, the formatting will stay intact. If you do not go through the Value Field Settings interface to format data, the formatting can change.
                                                    FIGURE 7
“Sum of SalesQty” header has been changed to “SalesQty”; “Sum of Sales” header has been changed to “Sales” (FIGURE 8). Readability issue #2 has been addressed.
FIGURE 8
STEP 9: Select the “Sales” header (FIGURE 9).
                                                                                  FIGURE 9
STEP 10: ​Right button mouse click cell and select “Value Field Settings” from shortcut menu (FIGURE 10).
                                                                                FIGURE 10
STEP 11: Select Number Format button, which will open the Format Cells dialog box (FIGURE 11).
                                                FIGURE 11
STEP 12: On the Format Cells dialog box (FIGURE 12), select:

  • Currency category
  • 0 Decimal places
  • Select OK button twice (once for the Format Cells box and once for the Value Field Settings box).
                                                  FIGURE 12
Readability issue #3 has been addressed; Sales Numbers formatting has been changed to Currency (FIGURE 13).
                                                                                                                                FIGURE 13
This is the end of this section. To continue, go to Module 4 Section 4.5 Customize a Pivot Table by preparing it for printing; Practice Exercise 27