PivotTables are a tool for grouping, summarizing, and analyzing data contained in Excel lists or in tables created in other applications.
PivotTables can be used for:
- summarizing large amounts of similar data;
- reorganizing data;
- filtering and grouping data;
- creating charts.
Visually, PivotTables are a structure that allows data to be displayed in a three-dimensional form.
Before you start creating a PivotTable, it is advisable to carefully think through its logic and its structure.
You need to define the following fields that will be used in the PivotTable layout:
- Row fields and Column fields of the table;
- Data fields for totals (with a selected operation, such as SUM, AVERAGE, COUNT, etc.) — placed at the intersection of rows and columns;
- Filter field (the PivotTable “Page” field) — for performing the necessary slices (filters), which makes it possible to present information in a three-dimensional view.
- Filter (Page field) of the PivotTable
- Column fields of the PivotTable
- Values (Data fields) of the PivotTable
- Row fields of the PivotTable
Creating a PivotTable
PivotTables are created using the PivotTable command, which is selected from the list of the same name located in the Tables group on the Insert tab.
TIP
Whenever possible, place the PivotTable on a separate worksheet. When refreshing or grouping data in the PivotTable, information on the worksheet next to the PivotTable may be hidden.