Excel Pivot Table, also known as Pivot Table Reports are tables that display and summarize the data in other tables or data ranges. Pivot Tables can be manipulated to determine which subsets of data they display, how the data is organized and what types of calculations must be applied to summarize the data. This makes Pivot Tables useful for viewing data in different ways and for analyzing trends and patterns.
Pivot Tables can be used to perform various tasks:
- Display very large data ranges in a format that’s easy to interpret.
- Subtotaling and aggregating data
- Summarizing data based on data categories or subcategories
- Filtering, sorting and formatting data so that only relevant data displays, in an accessible and meaningful format
Before you can create a Pivot Table based on worksheet data, the data must be formatted correctly as mentioned in the section below.
Formatting Data To Create Pivot Table
Data on which Pivot Table is to be created must meet below rules:
- It must include column headers in the first row, to be used as Pivot Table field names
- Each column must contain related data entries
- Data range shouldn’t include any blank rows or columns
- Data range shouldn’t include subtotals inserted using the subtotal function because the Pivot Table feature automatically generates its own subtotals or summary values
Creating Pivot Table
Once data for which Pivot Table needs to be created is structured properly, we need to select the data range. Alternatively, select the first cell in the data range and Excel will automatically identify the full range. In Excel 2003, from top menu, select Data -> PivotTable and PivotChart Report as shown in the figure below:
This will launch Pivot Table wizard as shown in the figure below:
Once Pivot Table settings are specified and Finish button is pressed, an empty Pivot Table is added to the worksheet and the Pivot Table Field List pane opens as shown in the figure below:
In a Pivot Table report, each column in the range or table becomes a Pivot Table field. Using the Pivot Table Field List pane, which fields of data to be added to the Pivot Table can be chosen. Now, each of the field can be added to below 4 areas;
Row Area: When a field is added to the Row Area, it is displayed on the side of the report. By default, any selected field that contains non-numeric data is added to the Row Area.
Column Area: When a field is added to the Column Area, it is displayed as column labels.
Page Area: Adding a field in Page Area enables to filter the entire report based on the entries in the field. Once a field is added to the Page Area, it can’t be included in the Row Area or Column Area.
Data Area: Fields that contain numeric values are added to Data Area to display summary data and totals. By default, any selected fields that contains numeric data is added to the Data Area.
The features of Pivot Tables are dynamic and flexible. Even after a Pivot Table is created, it is easy to change what data it displays and the arrangement of this data.
Fields can be moved to different areas, fields can be removed and the order in which the fields are presented can be changed and so on.
You can download the sample XLS from PivotTable.
Hope that you find the information presented here useful. Feel free to let us know your query, feedback or suggestions.