How to Create Our First Pivot Table

It is very simple to create a pivot table as long as the dataset being summarised is in the correct format (we go into this more in the next lesson).

To create a pivot table, simply highlight the dataset to be summarised, ensuring that the titles along with the data are highlighted. Go to the Insert tab by pressing Alt + N or selecting the tab with the mouse, then in the Tables section of the ribbon, select Pivot Table.

A pop up menu will then appear asking you to confirm or input the location of the dataset which will be summarised in the Pivot Table. The pop up menu will show the name of the range if it has been formatted as a table (in the video for example, the name of the range is ‘Table1’), and if it hasn’t it will show the cell coordinates making up the range.

The same pop up menu then asks where in the workbook, the pivot table should be placed. By default, the selected option will be a ‘New Worksheet, but you can also select an ‘Existing Worksheet’. If the latter option is chosen then you must give Excel the location in the existing worksheet where you’d like the Pivot Table to be placed.

Once the location is selected, the Field List pops up along with the placeholder for where the Pivot Table will go. The Field List is like the Settings screen for the Pivot Table. It shows first, all the fields or columns that are in the dataset for sumarising. These fields can then be dragged to one of four quadrants, Values, Columns, Rows and Filters to create the Pivot Table.

The field to be summarised should go into the Values quadrant. If the selected field is a column of values then it will be automatically summed i.e. all the values in the field will be added together. If the selected column is a text column, then the cells of the column with a character will be counted.

If you’d like to summarise the field placed in the Values quadrant by columns, then drag another field into the Columns quadrant. If you’d like the data in the Values quadrant to be further analysed by rows then add a field to the Rows quadrant.

Finally if you would like to restrict the data going into the Pivot Table based on certain characteristics, then add the field with the required characteristic into the Filter quadrant. This will force a drop down menu to appear above the Pivot Table which can be used to filter the Pivot Table to only include the desired data.