How to Create Our First Pivot Table

How to Create Our First Pivot Table

Previous Lesson Summary

In the previous lessons we have had a first look at what a pivot table looks like. Subsequently we looked at the advantages of the pivot table against the principal other method of summarising data, static formula driven tables.

Lesson Synopsis

The lesson you’ve been waiting for. In this lesson we’re going to look at how to create a pivot table.
 
As part of the lesson we’ll cover:
a) the initial selection of data that supports the pivot table,
b) the creation of the Field List which shows the fields in the supporting dataset,
c) and the selection of fields and placing them in relevant parts of the Field List to create the Pivot Table

Resource Listing

The Video

Play Video

Lesson Notes

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.

.... and a little more!

The Supporting Data – The supporting data has to be in a certain format for the Pivot Table to be created, specifically, a single table of columnar data with titles for each column. We cannot form a single pivot table from two tables of data, there can be no blank columns, or columns of data without titles. We go into this more in the next lesson.

The Field List – The Field List can be called upon at any time, simply by left clicking on the pivot table, or right clicking on it and selecting the Field List.