Excel Pivot Table Instructions
- Return to the Statistics main page.
In the instructions below:
- 'click' means to position the mouse pointer over the data required and press and release the left mouse button once.
- 'double click' means to position the mouse pointer over the data required and press and release the left mouse button twice quickly.
- 'click and drag' means to position the mouse pointer over the data required and press and hold the left mouse button down while moving the mouse pointer to a new position. When the correct position is reached, release the mouse button.
The original pivot table will always remain available on the website - so don't be afraid to experiment!
The pivot table used in the instructions below is an example only and may not reflect actual university data. The instructions given apply to Microsoft Excel 2013. If you have another version of Excel, instructions may vary slightly.
- 1. Introduction to pivot tables
- Pivot tables are interactive and can be used to quickly summarise large amounts of data
- Data can be selected and arranged in whatever format is appropriate
- Selected data fields included in the pivot table can be quickly reorganised by using the mouse
- Numeric data is automatically summed in a pivot table, non numeric data is counted
- Data fields can be dragged from a list above the table into the pivot table
- Data fields can be dragged from a column and dropped into a row and vice versa
- Data filters can be applied to the pivot table by selecting the drop-down arrows in the list of fields
- Totals and subtotals can be displayed or hidden in a pivot table
- Data can be sorted or grouped in a pivot table
- Many other Excel tools and features are available to sort and format your pivot table
- You can save a pivot table to your own computer, or work with the table direct from your browser
- You will need to save your 'finished' pivot table to your computer if you wish to refer to it again
- If you save the pivot table using 'File' and 'Save As' it will save as a pivot table which can be further manipulated as required
- If you 'copy' and 'paste' data from the pivot table to a new Excel spreadsheet, it will save as data only and the pivot functions will be lost
- 2. Selecting data fields to include in the pivot table
These example instructions utilise the following Student Load (EFTSL) pivot table as available on the Planning & Analytics web page:
Once the pivot table has opened in Excel you will see the title, then a list of data fields at the top left (e.g. 'Enrolment Faculty', 'Program Name' etc), and a pivot table containing data and totals below. The data in the example pivot table shows the total EFTSL (load) - other pivot tables are available showing number of students (headcount), completions, research income etc.
To the right of your screen, you will also see the Pivot Table Fields panel:
This will show you the list of available fields which can be added to either the columns, rows, filters or values within your pivot table (more information on this process is shown below).
The Pivot Table Fields panel will show whenever the pivot table is activated - simply click anywhere within the pivot table to do this.
Suppose we want to see more detail in the data.
Moving a data field from the list of available fields into a new row of the pivot table:
Suppose we want to see the total student load (EFTSL) in each enrolment faculty by year.
In the Pivot Table Fields panel, click on the fieldname 'Enrolment Faculty' in the field list and drag the mouse pointer down to the Rows box. Release the mouse button, and the field will be added to the pivot table.
Ensure that the 'EFTSL' field shows in the Values box and that the 'Year' field shows in the Columns box (drag them into place if not):
The pivot table will now a breakdown of EFTSL in each enrolment faculty by year:
Filtering the data using a field already used in the pivot table:
Suppose we want to filter the data shown in the pivot table - e.g. so that only the most recent enrolment year is shown.
Click on the small drop-down arrow next to the Year field in the pivot table, select the check-box for the required year(s) and press OK:
The pivot table will now only show EFTSL data for the selected year(s):
Filtering the data by a field not already used within the pivot table:
The data can also be filtered by fields which are not already contained within the pivot table. For example, the table can be filtered by domestic/international status so that so that only EFTSL for domestic students is shown.
Within the Pivot Table Fields panel, ensure that 'Domestic/International' is shown in the Filters box. If not, drag it down from the list of available fields:
Once added to the Filters box, the field will show in the list of fields to the top of the pivot table:
Click on the small drop-down arrow next to the 'Domestic/International' field and select the 'Domestic' check box. Once OK is pressed, the data in the pivot table will be updated so that only EFTSL for domestic students is shown:
Adding an additional field to the pivot table:
Suppose we now want to break down the EFTSL data further to show a split by broad program level.
From the Pivot Table Fields panel the 'Broad Level' field can be dragged down from the list of available fields into either the Rows or Columns box - depending on how you want the data to be presented.
Moving the field into the Rows box will add each of the broad program levels into a row of the pivot table. The field can either be added before the existing 'Enrolment Faculty' field to show a breakdown by broad program level then enrolment faculty...
...or after to show a breakdown by enrolment faculty then broad level...
Alternatively, the field can be dragged into the Columns box which will split the data into columns instead of rows:
Removing a data field from the pivot table:
Suppose we do not want the broad program level data to show in the pivot table any more.
From the Pivot Table Fields panel, simply drag the 'Broad Level' field out of either the Rows or Columns box and place it back into the list of available fields at the top of the panel:
The pivot table will now show the data without the split by broad program level:
You have now mastered the basics of using a pivot table.
It is completely up to you how you arrange the pivot table - you can try various configurations of the fields within the table until the data is presented in the most suitable way for your requirements. Remember - the pivot tables will always remain available for download from the Planning and Analytics web page - if you want to start from scratch then a new copy can be downloaded without any of the changes you have made.
- 3. Working with totals in a pivot table
Depending on the data shown within a pivot table, grand totals of rows and columns may be shown, as well as sub-totals of rows:
Removing the subtotals for rows in the table:
Suppose we do NOT want to see the subtotals for 'Enrolment Faculty' in the above table.
Simply right-click on one of the sub-total lines:
...and uncheck the 'Subtotal "Field Name" ' option from the drop-down menu. The data will now be shown without row sub-totals:
Removing the grand totals for the table:
Suppose we do NOT want to see the grand totals for the above table.
Right-click anywhere within the pivot table and select 'Pivot Table Options' from the drop-down menu. This will open a dialogue box showing various options controlling how the pivot table is displayed. Click on the 'Totals & Filters' tab at the top of the box:
Next, uncheck one or both of the grand-total boxes as required (one controls the grand totals for rows within the pivot table, one controls the grand totals for the columns):
The pivot table will now show without the deselected grand totals:
Totals can be re-added to a pivot table by reversing the above instructions.
- 4. Saving a pivot table to your computer
You can work with the original pivot table opened through your browser, but if you want to keep a pivot table the way you have formatted it, you need to save a copy of it to your computer or network drive.
Saving the table as a pivot table:
Using this method, you will save the pivot table as it appears, and have a copy of the pivot table which can still be manipulated as if it were the original pivot table.
From the Excel toolbar at the top of the pivot table click on 'File', then click on 'Save As'. Select a folder on your computer or network drive where you wish to save the table. Change the file name if you wish, then click 'Save'.
You will now have your own copy of the pivot table saved locally.
Saving the table as data only:
Using this method, you will save the table data as it appears, and have a copy of the data only which can NOT be manipulated as a pivot table. This is useful if you wish to send the data to a third party (e.g. by email), or want to keep the formatted table for future use.
Select the rows of the table you wish to keep by highlighting the required cells within Excel. Right click with your mouse and select 'Copy' from the drop-down menu.
Open a new Excel file to copy the table into. Click on 'File', then click on 'New' and select 'Blank Workbook'. Paste the table into the new file by right-clicking and selecting 'Paste' from the drop-down menu.
Now save the new file on your computer by clicking on 'File', then click on 'Save As'. Select a folder on your computer or network drive where you wish to save the table. Change the file name if you wish, then click 'Save'.
You will now have a copy of the table on your computer as an Excel file, but not a pivot table.
More information and help on pivot tables can be found using the Excel Help facility. Additionally, many good written and video tutorials can be found online.