Preview
Improving Pivot Table Readability; Practice Exercise 13
Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2.
We can change 4 things to our pivot table to improve the readability:
- We need to change the report layout to tabular form to enhance the readability.
- We will need an additional field, Cost, for use in creating a Calculated Field (Profit) later.
- Column headers from fields that are in the Values quadrant will need to be renamed.
- The formatting of the Sales and Profit fields need to be changed to Currency formatting, so the numbers have dollar symbols.
STEP 1: Select any cell in the pivot table. Select the PivotTable 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”

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.

STEP 4: Let’s switch the order of the data from left-to-right. Our left-most columns of data will be our “Sales”. The columns of data “SalesQty” will be to the right of the “Sales” data. The columns of data “Cost” will be to the right.
To switch the data columns placement as described above, we simply go into the “Values” quadrant of the Field List, and move “Sum of Sales” so it is the top field in the quadrant (FIGURE 4). The “Sum of SalesQty” field will be below it. Our data should look like the pivot table below in FIGURE 5.
STEP 5:Pivot table from previous step (FIGURE 5).


STEP 6: Select the field header, “Sum of Cost” (FIGURE 6).

STEP 7: Right-button mouse that click that cell and select the shortcut menu choice, “Value Field Settings.” (FIGURE 7).

STEP 8: 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 8).
STEP 9: We want to change the formatting to Currency. Select the Number Format button located at the bottom of the dialog box (FIGURE 8).

STEP 10: From the Format Cells dialog box select the category “Currency“. Select 2 for Decimal places (FIGURE 9).
STEP 11: Select the OK button on the Format Cells box.
STEP 12: Select the OK button on Value Field Settings box.
Notice that the field header, “Sum of Cost” has been replaced with “Cost”.
Also notice that the field header, “Total Sum of Cost” has been replaced with “Total Cost”.
We can also see that the entire column, “Total Cost” data is shown as “Currency”.

STEP 13: Select the field header, “Sum of SalesQty” (FIGURE 10).

Right-button mouse that click that cell and select the shortcut menu choice, “Value Field Settings” (FIGURE 11).

STEP 14: In the Custom Name window, delete all in window and replace with “SalesQty” (FIGURE 12).
Since there is already a field name with this name, Excel will not allow this name so we have to add a space.
STEP 15: Select the OK button.
Notice that the field header, “Sum of SalesQty” has been replaced with “SalesQty”.
Also notice that “Total Sum of SalesQty” has been replaced with “Total SalesQty”.

STEP 16: Select the field header, “Sum of Sales” (FIGURE 13).

Right-button mouse click that cell and select the shortcut menu choice, “Value Field Settings” (FIGURE 14).

STEP 17: In the Custom Name window, enter “Sales” (FIGURE 15).
Since there is already a field name with this name, Excel will not allow this name (you will see a notification). However, if you add a space after the name, Excel interprets it as a different name.
STEP 18: Select the Number Format button.

STEP 19: From the Format Cells dialog box select category Currency (FIGURE 16).
STEP 20: Select 0 for Decimal places.
STEP 21: Select OK button.
STEP 22: Select OK button on Value Field Settings box.
STEP 23: Notice that the field header, “Sum of Sales” has been replaced with “Sales”.
Also notice the header, “Total Sum of Sales” has been replaced with “Total Sales”.
Notice that the Sales field data is classified as Currency since the numbers now have dollar symbols.

STEP 23 (Cont.) Pivot table with fields modified as shown in FIGURE 17

STEP 24: 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”. Select the Field Headers icon. The Year, Month, Market, Values, Cateogory headers are now removed. To replace them, click Field Headers icon again (FIGURE 18).

STEP 25: Pivot table with Fields headers and Expand/Collapse buttons removed as shown in FIGURE 19.

END OF LESSON
Leave a Reply