- Helps to manipulate data without using formulas
- Quickly summarize large dataset into smaller aggregated dataset
- Helps to display engaging charts and graphs (Pivot charts)
Before creating pivot table, make sure to
- There is only 1 header row
- Remove blank rows, columns or cells
- Ensure fields are formatted as numbers
- Format the dataset as table (Select any cell in dataset and then
go to "Home" tab > Click "Format as table" icon.
Then Choose style, range for the dataset)
Creating pivot table:
1) Go to "Insert" tab > Click "Pivot Table" icon.
2) From the new dialogue box, select table range for dataset and "New Worksheet"
3) In the new sheet,
- choose column fields that will appear as column names by dragging
- choose row fields that will appear as row index by dragging
- choose value fields that will appear as aggregated values by dragging
- The dragged fields will have dropdown activated. From the
"Value field" dropdown select "Value Field Settings" and choose
"Number Format" for formatting and "Summarize value Field by" for
aggregating
4) For new calculations, go to "Pivottable Analyze" tab >
Click "Field, items, sets" > Select "Calculated Field" >
Insert formula (eg: = Field A * Field B) > Click "OK".
It will be added automatically in "Values" section.