|
STEP 1A: Click on one of the cells in the pivot table. On the Ribbon, Select the Design tab (FIGURE 1). NOTE: If you do not see that tab, you can go to Excel Options, Customize the Ribbon, to enable it. You will see the pivot table design tools toolbar appear. This toolbar will be used throughout our pivot table customization.
STEP 1B: One of a pivot table’s several design issues is having no grid lines. We will add some now. Before we apply a style, we can band the rows which will help in readability. STEP 1C: In the section, Pivot Table Style Options, check the box, “Banded Rows.” (FIGURE 2) STEP 1D: From the Pivot Table Style palette (FIGURE 3), select the bottom arrow (downward-pointing) of the 3 arrows that appear to the right of the palette. The last several choices of the top group add extra lines. Select Pivot Style Light 22 as shown in FIGURE 4. |
 FIGURE 2
 FIGURE 3
 FIGURE 4
|
FIGURE 5
|
STEP 2A. Select the PivotTable Analyze tab on the Ribbon (FIGURE 6).
STEP 2B. Select the cell in the pivot table, “Sum of Sales” (FIGURE 7).
STEP 2C. Select Field Settings from the Active Field group (FIGURE 8), which will open the Value Field Settings dialog box.
STEP 2D. From the Value Field Settings dialog box, keep the default selection “Sum”, which is highlighted. Select the “Number Format” button at the bottom of the dialog box, which will open the Format Cells dialog box (FIGURE 9).
STEP 3A. Referring to FIGURE 10, in the Format Cells dialog box, select the Number tab. STEP 3B. Select 0 for Decimal places. STEP 3C. Check the box, “Use 1000 Separator”. This adds a comma to all value fields with 4 or more numbers. STEP 3D. Select the OK button. Note: Whenever you are formatting a pivot table cell, use the Value Field Settings interface when you need to access the Format Cells dialog box. |
 FIGURE 6
 FIGURE 7
 FIGURE 8
 FIGURE 9
 FIGURE 10
|
|
From looking at FIGURE 11, we can see that all sales numbers have a comma and there are no additional decimal places added.
|
 FIGURE 11
|
|
STEP 4: Every field in the pivot table has a name, most of which are inherited from the source data. However, fields in the data section are given default names that aren’t very descriptive. We see the label, “Sum of Sales” above the categories, Desktops, Laptops (FIGURE 12). To change these value field names do this:
STEP 4A. Select the “Sum of Sales” field name that is to be changed by clicking on that cell. Select the PivotTables Analyze tab. Notice that on the Ribbon above the “Field Settings” you see “Sum of Sales” as the Active Field (FIGURE 13). Click mouse inside that window and change it to “Sales”. STEP 4B. Since we already have a field named Sales, Excel will not accept this name. Adding a space after Sales solves the problem. Then press the Enter key. |
 FIGURE 12
 FIGURE 13
|
It is helpful to give a pivot table a name that is meaningful. If you have a pivot table about Company XYZ, it is better to have a pivot table name, XYZ_SALES than PivotTable1. If considerable time has passed since you created the pivot table, it may be difficult to locate the correct pivot table you need. However, if you have assigned a meaningful name to the pivot table, locating it will be much easier. We will now cover how to change the pivot table name in STEP 5.
STEP 5B. Select the Pivot Table Tools Analyze tab on the Ribbon. NOTE: If you do not see that tab, you can go to Excel Options, Customize the Ribbon, to enable it.
STEP 5C. On the far left end of the Ribbon, in the PivotTable section under the name “PivotTable”, the default pivot table name is shown in a window, “PivotTable3” (FIGURE 14).
|
STEP 6: Click your mouse in the PivotTable Name window and change the name to “XYZ_Sales” (pivot table names cannot have spaces) (FIGURE 15).
The pivot table now has a new name, XYZ_Sales. |
 FIGURE 15
|