The pivot table provides views of multidimensional data in tabular form. It supports multiple measures and dimensions and subtotals at all levels.
The following figure shows a pivot table:
Insert a Pivot Table
Follow the steps in the procedure to insert a pivot table.
- From the Insert tab, select and drag the Pivot Table component to the layout. The following figure shows the empty pivot table structure.
- Drag and drop data fields from the Data Source pane to the row, column, and data positions.
Drag multiple fields to the pivot table and place them precisely to structure the pivot table, as shown in the following figure:
- By default the pivot table is inserted with no data formatting applied. To apply a format to the data, click the first column of data to enable the Pivot Table Data toolbar. On the Data Formatting group, select the appropriate format as shown in the following figure:
- Optionally resize the pivot table by clicking and dragging the handler in the lower right corner of the pivot table.
Customize a Pivot Table Menu
After you insert a pivot table customize the appearance and layout using these dynamic tabs.
Pivot Table tab
Pivot Table Header tab
Pivot Table Data tab
About the Pivot Table Tab
You can customize the appearance of a pivot table using the Pivot Table tab.
The following figure shows the Pivot Table tab.
This section describes filters and manage filters features.
See About Filters for a description of the Filter and Manage Filters features.
Customize the Display of Totals
The Pivot Table tab enables you to quickly customize the display of grand total and subtotal rows.
By default, the layout editor inserts the pivot table with the total and subtotal displays as shown in the tab:
Row Grand Total - Inserted at the bottom of table
Row Subtotal - Inserted at the top of each subgroup, with no row header
Column Grand Total - Inserted at the far right
Column Subtotal - Inserted to the left of each column subgroup, with no header
Change the positioning and display of totals and subtotals by clicking the appropriate group in the tab and selecting the desired layout pattern from the menu.
Convert a Pivot Table to a Chart
The Convert Pivot Table to a Chart command converts the pivot table to a default vertical bar chart.
After conversion, customize the table as described in About Charts.
The following figure shows the pivot table created in the preceding step converted to a vertical bar chart.
Switch Rows and Columns
Use the Switch Rows and Columns command to see a different view of the same data.
The following illustration shows the pivot table created in the previous step with rows and columns switched.
Customize the Pivot Table Headers
Use the Pivot Table Header tab to customize the fonts, colors, etc.
The Pivot Table Header tab is shown in the following figure:
Select the column or row header of the pivot table and use the Pivot Table Header tab to perform the following:
Customize the fonts, colors, alignment and other display features of the header.
Apply a sort order (for more information see About the Sort Option).
Apply data formatting (if the data type is number or date).
Customize the Pivot Table Data
Select the data area of the pivot table and use the Pivot Table Data tab to perform these actions. The commands in the Pivot Table Data tab are the same as the corresponding commands in the table Column tab.
The Pivot Table Data tab is shown in the following figure:
See the references for more information on their use.