Creating a PivotTable
PivotTables are amazing! They provide the ideal way to quickly summarise, organise and analyse a large amount of data.
PivotTables have their name because you can pivot the row and column headings to display different configurations of the data.
The data that the PivotTable uses is known as the source data. This is usually stored in Excel (the example we will use throughout the tutorial), but it could come from an external source. If the source data changes, the PivotTable can be updated.
PivotCharts can be created from PivotTables providing a visual representation of your data.
To create a PivotTable you need to decide what fields (column headings) you want to include, how you want them organised, and the calculations to be performed.
This is not easy and at first PivotTables can appear quite daunting. However once you have created your first one, they begin to make more sense.
Download PivotTable.xlsx to follow the tutorial.
To create a PivotTable:
- Select the range of data you want to use for the PivotTable
- Click the Insert tab on the Ribbon
- Click the PivotTable button in the Tables group
- The Create PivotTable dialogue box appears
- Decide whether you want the PivotTable to appear in a new worksheet or within the existing worksheet
- Click Ok
If you want to use the whole data list, select a cell within the range. Excel will assume you want to use the whole list, saving you the job of selecting the range
Click the top half of the PivotTable button to use the shortcut to the Create PivotTable dialogue box. If you click the bottom half of the button, a list appears with options for creating a PivotTable or PivotChart
A moving dashed line appears around the data range to be used for the PivotTable, and the range is shown within the Table/Range field. If the range is not correct, select the data range you wish to use again
PivotTables are easier to handle on a new worksheet as they can be quite large and you may need the space, so let's select New Worksheet
An empty PivotTable appears on the left and on the right is the PivotTable Field List. It doesn't look like much yet, but that's because we need to pull some data into the table to give it something to work with.