Formatting a Pivot Table

Modifying Fields and Labels

You can quickly change the Custom Name of a field or item without displaying the Field Settings or Value Field Settings dialog boxes. The following procedures do not change the Source Name of the field or item.

1. Click the field or item that you want to rename. 
2. Go to PivotTable Tools > Analyze, and in the Active Field group, click the Active Field text box. 
3. Type a new name. 
4. Press ENTER.

Modifying Values

You can change the kind of calculation used in PivotTable value fields so instead of its number, you see a percentage of a total, a running total, the difference from another value, or its rank.

Right-click the value field in the PivotTable, and then pick Show Values As. Pick the option you want, such as % of Parent Total, or % Difference From.

To show calculations side by side with the values they’re based on (for example, to show the % of Grand Total next to the subtotal), first, you need to duplicate the value field by adding it more than once. In the Field List, drag the field you want to duplicate to the Values area, and place it right below the same field.

In the example shown, three value fields were added a second time to the Values area; they have "_2" appended to their names.

Below, the "Sum of 1/1/14" field is shown twice in the PivotTable report, so you can see the value for each city and their parent (the East or West region). You also see the percentage of the grand total for each region (in rows 6 and 9).

Using the Layout Group on the Design Tab

1. Click anywhere in the PivotTable. This displays the PivotTable Tools, tab on the ribbon. 
   
2. On the Design tab, in the Layout group, click Report Layout, and then do one of the following:

• To keep related data from spreading horizontally off of the screen and to help minimize scrolling, click Show in Compact Form. 
• In compact form, fields are contained in one column and indented to show the nested column relationship. 
• To outline the data in the classic PivotTable style, click Show in Outline Form. 
• To see all data in a traditional table format and to easily copy cells to another worksheet, click Show in Tabular Form.

Applying a Style to a PivotTable

1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. 
2. Click Design, and then click the More button in the PivotTable Styles gallery to see all available styles. 
   
3. Pick the style you want to use. 
4. If you don’t see a style you like, you can create your own. 
5. Click New PivotTable Style at the bottom of the gallery, provide a name for your custom style, and then pick the options you want.

Changing PivotTable Style Options

1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. 
2. Click Design > Banded Rows (or Banded Columns).

Manually Formatting a PivotTable

You can do another formatting manually by using available formatting options from the Home tab.

Using the PivotTable Options Dialog

Use the PivotTable Options dialog box to control various settings for a PivotTable.

PivotTable Name

Displays the PivotTable name. To change the name, click the text in the box and edit the name.

Available Tabs

• Layout & Format. 
• Total & Filters. 
• Display. 
• Printing. 
• Data. 
• Alt Text