Pivot table analysis

We recommend that you analyse UCAS Annual Datasets using Pivot Tables.

The information below is designed as a guide to get you started - when it comes to using Pivot Tables there really is no substitute for practice.

Our Statistical and Analytical Services Team can provide hands-on training on all aspects of UCAS data, including Pivot Tables. For more information, please contact stats@ucas.ac.uk, or tel: 01242 544896, quoting SAAST.

Creating a Pivot Table
Customising the Pivot Table
Editing the table
Sorting
Grouping
Moving category data
Drilling
Copying Pivot Tables
Calculations
Charts

Creating a Pivot Table
Once you have opened the UCAS dataset as an Excel spreadsheet, select Data followed by Pivot Tables (or PivotTable and PivotChart Report in later versions) from the top menu bar. There are also Pivot Table icons you can display on the main toolbar.

Excel then presents the Pivot Table Wizard with which you create the table. The first step is to define the data source.

In all Annual Datasets, the data comes from a "Microsoft Excel List or Database". This option is the default.

Click on Next.

Here you are prompted for the location of the source data.

Excel usually comes up with a default of some kind, but to simplify matters, we have already defined the appropriate range as 'data'.

Enter 'Data' (without the quotes) and select Next.

Now you can start to build the table. (If you are using Excel 2000, you will first need to select Layout.)

The icons to the right of the window are the titles used at the top of each column of data in the dataset and are used to create the table of your choice.

The key point to note is that category data (i.e. InstOrd, InstName, InstRegn and Age) should be used for the Page, Row and Column parts of the table whilst the numeric variables should be used for the Data. The Page field can be used to limit the data further by selecting just one category at a time from the drop-down list that is automatically created.

Drag the required icons to their positions.

Click on Next and you will have the choice of putting the Pivot Table on a New worksheet or on the Existing worksheet.

Select New worksheet and click on Finish.

Customising the Pivot Table
The real advantage of having data in electronic format is the ease with which individual tables can be modified and combined to show just the data you are interested in.

The first point to note is that, having created a Pivot Table you are able to edit it without having to re-create it. This is done by selecting any cell in the Pivot Table and clicking on Data / Pivot table (or PivotTable and PivotChart Report in later versions) from the top menu bar - or selecting the Pivot Table icon. (In later versions you will also need to select Layout to return to the Pivot Table Wizard box).

In later versions of Excel, there is also a PivotTable toolbar, which you can use to make the changes you require.

Editing the table
There are also some short cuts you can use to re-design the table.

One of these is to drag the title bars (the grey boxes describing the page, row and column contents) to a new location.

Similar options are available for changing page, row and column data. Again, using the Pivot Table, drag the title bar by pressing the left mouse key and moving the mouse slowly up and down between the first three rows.

Three different icons are displayed depending on where the mouse is. These icons indicate that the data will be shown as either a page, row or column item.

Sorting
Pivot Tables can be sorted in the same way as normal spreadsheet data. Select the range of data you require to sort and click on Data / Sort.

By default, all Pivot Tables when first created are sorted alphabetically. However, having been sorted manually, edited versions of the Pivot Table retain the sorted order.

Grouping
One of the problems with printed tables and also static spreadsheet tables is that they often contain too much data.

Pivot Tables can be manipulated in such a way as to remove some of the clutter from your data. This is done by grouping the data.

To do this open a dataset and click with the left mouse button on a cell, keeping the button pressed, move the mouse to another cell and then let go of the button. With the mouse pointer inside the selected area, click with the right mouse button. From the menu displayed select 'Group and outline' followed by 'Group'.

This process may be repeated.

Moving category data
If the data displayed does not come out in the order you want, and it can't be sorted as you would like, you can move individual items in the table. Select the field(s) you wish to move. Now move the mouse to the edge of the selected cell(s) until it appears as an arrow. Click on the left mouse button and hold it down whilst moving the pointer down the columns. The selected cell(s) will be moved when you let go of the mouse button.

Drilling
Another useful function is the facility in Excel to 'drill down' into the Pivot Table data. To show detailed information for a specific category, click twice on the left mouse button on the chosen category. Then select the further details you wish to view.

This process can be repeated for categories within this category.

Copying Pivot Tables
Pivot Tables may be copied in exactly the same way as other spreadsheet data. This is especially useful if you have previously spent time grouping some of the data since Excel will retain the groups for use in the copied table.

Since the data used to create each Pivot Table is held within the table (i.e. you don't have to have the original dataset present in the same workbook as the table to which it relates) you can copy Pivot Tables to new workbooks that contain just Pivot Tables.

To copy a Pivot Table, select the whole of the table (including any page fields) and type Control-C (or use the top menu bars Edit / Copy). Select the cell where you want the table to be copied to (this might be the same worksheet, a new worksheet in the same workbook or a new workbook), and type Control-V (or Edit / Paste from the top menu bars). The table can now be manipulated independently of the original.

If the table is in its final form it will save space if you use the Paste Special function in the Edit menu (instead of Paste). This enables you to copy just the contents of the cells without having to save the data that was used to create the table. To do this select the destination cell and choose Edit / Paste Special from the menu. Then check the button labelled Values in the dialogue box before clicking on OK. The table is now just text - as if you'd typed in the data.

Calculations
Calculations on Pivot Table cells can be done in the same way as for any other spreadsheet. Typically, they can be used to calculate ratios or used to directly compare 'your' data with that of your 'competitors'.

What is especially useful about having such calculations is that, with the addition of one or more page fields, the resultant table is updated as soon as you select a new page item. Again it is crucial that both tables are updated to show comparable data.

It is also possible to amend the pivot table to calculate data directly. Open the Pivot Table Wizard box (click on Data / Pivot table (or PivotTable and PivotChart Report, then Layout, in later versions) from the top menu bar). Next, double-click the Data variable in your table with the left mouse button. Select the calculation you require from the list and press OK. Then select OK/Finish to update your table.

Charts
Creating charts in Excel is probably as big a topic as Pivot Tables themselves. However, they are well worth learning since, once created they can be used to easily show new data.

To create a chart, position the cursor over one of the worksheet titles - enabling you to select the type of chart you require; the data you wish to chart; titles; legends and so on. Select any chart and follow through the steps in the 'Chart Wizard'. If you are using Excel 2000, you will also be able to create a Pivot Chart, which can be manipulated in the same way as Pivot Tables themselves.

Even in earlier versions of Excel, charts can easily be modified by use of Page field of the Pivot Table. Having produced a chart, check the figures against the Pivot Table previously created. Now, change the Pivot Table by selecting a new page item. (Click on the down arrow next to the Subject page field and select a subject group). The chart is automatically updated.

One point to note is that it is particularly helpful to link chart titles to the data actually displayed. This can be done by typing an equals sign in the chart title followed by a worksheet and cell reference.

Pivot Tables themselves duplicate the data. If you produce a Pivot Table in a new workbook and give that workbook to someone else you are not only giving him or her the table but the data and the ability to change the table. It may be better to create the table and copy it to a new workbook using the Paste Special option (checking the Values box) in the Edit menu.

Subsets of the data can be produced from the original dataset by double-clicking with the left mouse button on a Pivot Table (data) cell. Excel will 'extract' all of the rows in the original data that were used in creating the data in the cell and put them into a new worksheet. This is ideal if you wish to give a subset of the data to a colleague.