Excel Pivot Table Instructions

1. Introduction to pivot tables.

2. Selecting data fields to include in the pivot table.

Add a new column, Add a new row,
Remove a data field, Apply a filter

3. Working with totals in a pivot table.

Subtotals, Grandtotals

4. Saving a pivot table to your computer.

Save as a pivot table, Save as data only

1. Introduction to pivot tables

• Pivot tables are interactive and can be used to quickly summarise large amounts of data.
• Pivot tables are a built-in function of Microsoft Excel 5.0 or higher.
• 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 and take up a lot of disc space on your computer.
• If you 'copy' and 'paste' data from the pivot table to a new Excel spreadsheet, it will save as data only and you will not be able to pivot the data anymore. It will take up much less disc space on your computer.

In the instructions below:

1. 'click' means to position the mouse pointer over the data required and press and release the left mouse button once.
2. 'double click' means to position the mouse pointer over the data required and press and release the left mouse button twice quickly.
3. '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 in Office 2000. If you have another version of Excel, instructions may vary slightly.

2. Selecting the data fields for the pivot table

Use your internet browser ('Netscape', 'Explorer') to open a pivot table by clicking on the link.

University Pivot Table (xls 181kB).

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 pivot table may be the number of students enrolled (persons) or the total EFTSU (load).

Suppose we want to see more detail in the data.

Move a data field from the list of fields at the top into a new column of the pivot table:

Suppose we want to see how many students are in each Faculty.
Click on the fieldname 'Enrolment Faculty' in the field list and drag the mouse pointer down to the left hand side of the table until the shape of the mouse pointer changes to a 'column' shape (near the word 'Total'). Release the mouse button, and the field will be dropped into the table.

NOTE: If the mouse pointer changes to an 'X' shape, do not release the mouse key or the data field will be removed from the table. Too late? Don't worry, you can get back to the original table by reopening it from your internet browser or, for more advanced users, going to the pivot table wizard - right click on the word 'Data' and select wizard.

Move a data field from the list of fields at the top into a new row of the pivot table:

Suppose we also want to know how many male and female students are in each Faculty.
Click on the fieldname 'Gender' in the field list and drag the pointer down to the right hand side of the 'Faculty' column until the shape of the mouse pointer changes to a 'row' shape (near the word 'Data'). Release the mouse button, and the field will be dropped into the table.

The field could have been added as a second column in the table, instead of a row.

Move the field 'Gender' from a row to a column instead:
Click on the row name 'Gender' in the pivot table and drag the pointer down to the left hand side near the 'Faculty' column until the shape of the mouse pointer changes to a 'column' shape. Release the mouse button, and the field will be dropped into the table as a column.

If you now have the column 'Gender' first (on the left) and 'Faculty' on the right, but would like them the other way around, try clicking on one and moving it to the other location. Watch the shape of the mouse pointer to see if you are going to make a row or a column.

Remove a data field from the pivot table and put it back into the data field list at the top of the table:

Suppose we do not want the Faculty data in the table any more.
Click on the column name 'Faculty' in the pivot table and drag the mouse pointer up to the field list above the table. Release the mouse button, and the data field will be dropped back into the list of data fields, and removed from the pivot table.

Apply a filter to the pivot table by using the drop-down arrows in the data field list.

Suppose we want to filter the data in the table to show only Undergraduate students:

Click on the drop-down arrow to the right of the fieldname 'Program Level Group'. Click on the word 'Undergraduate'. Keep your eye on the pivot table data below as you do this. The data will change to only show Undergraduate numbers. To remove the filter, click on the drop-down arrow next to 'Program Level Group', then click on the word 'All'. Watch the data in the pivot table change back to include all students.

You have now mastered the basics of using a pivot table. Experiment with other fields to get the data you are interested in.

3. Working with totals in a pivot table

Use your internet browser ('Netscape', 'Explorer') to open a pivot table by clicking on the link.

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 pivot table may be the number of students enrolled (persons) or the total eftsu (load).

Add the fields Enrolment Faculty, Program Level and Gender to the table as three columns, left to right. (See instructions Section 2 if you do not know how to do this.)

You will see that the table automatically includes subtotals for Faculty and Program Level.

Remove the subtotals for 'Program Level' in the table:

Suppose we do NOT want to see the subtotals for 'Program Level' in the table.
Double click on the column name 'Program Level' in the pivot table. Under the heading 'subtotals', click next to the word 'none' instead of 'automatic'. Then click 'OK'. The pivot table will now not have the subtotals for 'Program Level'.

Remove the grand total for the table:

Suppose we do NOT want to see the grand total for the table.
Right click on the word 'Data' in the pivot table. In the drop down menu that appears, click on 'Table Options'. Uncheck the box next to 'Grand totals for columns'. Then click 'OK'. The pivot table will now not have a grand total.

Other subtotals and grand totals can be manipulated in a similar way.

4. Saving a pivot table to your computer

You can work with the original pivot table 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.

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! This will take up much more room on your computer than the second method below, but is more versatile.

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 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 pivot table on your computer.

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. It takes up much less space on your computer than keeping the whole pivot table.

Select the rows of the table you wish to keep. Click and drag down the row numbers on the left of the table (the rows selected will be highlighted.)
Click on 'Edit', then click on 'Copy'.

Open a new Excel file to copy the table into. Click on 'File', then click on 'New'.
Paste the table into the new file. Click on 'Edit', then click on 'Paste'.

Now save the new file on your computer.
From the Excel toolbar at the top of the pivot table click on 'File', then click on 'Save as'.
Navigate to a folder on your computer 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.