Midlands Microsoft Office Provider

2.5 Improving Pivot Table Readability; Practice Exercise 14 – Intro to Excel 2016 Pivot Tables

Practice Exercise 14: Perform miscellaneous steps to improve pivot table readability
We can change 4 things to our pivot table to improve the readability:

  1. We need to change the report layout to tabular form to enhance the readability. 
  2. We will need an additional field, Cost, for use in creating a Calculated Field (Profit) later.
  3. Column headers from fields that are in the Values quadrant will need to be renamed.
  4. The formatting of the Sales and Profit fields need to be changed to Currency formatting, so the numbers have dollar symbols.
Still use the data file, RANGE TO TABLE TO PIVOT TABLE2. ​
STEP 1: Select any cell in the pivot table. Select the PivotTable Tools Design tab on the Ribbon (FIGURE 1).

STEP 2: In the Layout section of the Ribbon, select the Report Layout icon (FIGURE 2). Select the choice from the drop-down menu, “Show in Tabular Form”
                                                                      FIGURE 1
                                                                    FIGURE 2
STEP 3: ​On the Field List check the field, Cost (FIGURE 3).
Notice that the Cost field was automatically placed in the Values quadrant as Sum of Cost.
                                                           FIGURE 3
Pivot table with “Cost” field added, with the label, “Sum of Cost” (FIGURE 4).
FIGURE 4
​STEP 4: Select the field header, “Total Sum of Cost”.
 

​

​STEP 5: Right-button mouse that click that cell and select the shortcut menu choice, “Value Field Settings.” (FIGURE 6).
                                                                  FIGURE 5
                                                                 FIGURE 6
STEP 6: In the Custom Name window, enter “Cost” (with a space following Cost)
Since there is already a field name with this name, Excel will not allow this name. However, if you add a space after the name, Excel interprets it as a different name (FIGURE 7).
 

STEP 7: We want to change the formating to Currency.
Select the Number Format button located at the bottom of the dialog box.
                                                          FIGURE 7
STEP 8: From the Format Cells dialog box select the category “Currency” (FIGURE 8). Select 2 for Decimal places.
 
STEP 9: Select the OK button.
 
STEP 10: Select the OK button on Value Field Settings.
 
Notice that the field header, “Sum of Cost” has been replaced with “Cost”.
Also notice that the column now has Currency formatting which shows numbers as dollar amounts.
                                                   FIGURE 8
STEP 11: Select the field header, “Total Sum of SalesQty” (FIGURE 9).
 
 

​

Right-button mouse that click that cell and select the shortcut menu choice, “Value Field Settings” (FIGURE 10).
                                                             FIGURE 9
                                                             FIGURE 10
STEP 12: ​In the Custom Name window, delete all in window and replace with “SalesQty” (FIGURE 11).
Since there is already a field name with this name, Excel will not allow this name. However, after entering SalesQty add a space.
 
STEP 13: Select the OK button.
 
Notice that the field header, “Sum of SalesQty” has been replaced with “SalesQty”.
                                                         FIGURE 11
STEP 14: This is our pivot table with Modified Value Field Settings as shown in FIGURE 12.
FIGURE 12
STEP 15: Select the field header, “Total Sum of SalesQty”. 
 

​

​
Right-button mouse click that cell and select the shortcut menu choice, “Value Field Settings” (FIGURE 14).
                                                                   FIGURE 13
                                                                   FIGURE 14
STEP 16: In the Custom Name window, enter “Sales” (with a space following Cost) (FIGURE 15).
Since there is already a field name with this name, Excel will not allow this name. However, if you add a space after the name, Excel interprets it as a different name.
 
STEP 17: Select the Number Format button.
                                      FIGURE 15
STEP 18: From the Format Cells dialog box select category Currency (FIGURE 16).
 
STEP 19: Select 0 for Decimal places.
 
STEP 20: Select OK button.
 
STEP 21: Select OK button on Value Field Settings.
 
STEP 22: Notice that the field header, “Sum of SalesQty” has been replaced with “SalesQty”.
 
Notice that the Sales field data is classified as Currency since the numbers now have dollar symbols.
                             FIGURE 16
Pivot table with fields modified as shown in FIGURE 17
FIGURE 17
STEP 23: To improve readability, the field headers can be temporarily removed, although it would be wise to remove them just before printing.
Select the Analyze tab on the Ribbon and from the section “Show”, select “Field Headers”.  Cick the Field Headers icon. The Year, Month, Market, Values, Cateogory headers are now removed. To replace them, click Field Headers again (FIGURE 18).
                                         FIGURE 18
Pivot table with Fields headers removed as shown in FIGURE 19.
FIGURE 19
Module 2 completed. Take the Module 2 quiz here.