Practice Exercise 14: Perform miscellaneous steps to improve pivot table readability
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.
Still use the data file, RANGE TO TABLE TO PIVOT TABLE2.
Pivot table with “Cost” field added, with the label, “Sum of Cost” (FIGURE 4).
FIGURE 4
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. |
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 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 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.