Pivot Table analysis

We recommend that you analyse UCAS annual datasets using Pivot Tables. These allow you to choose the rows, columns and filters you require in a table, and will calculate values automatically from the underlying data.

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. Please note that Pivot Table analysis varies slightly in different versions of Excel, but the principles are the same.

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 OW3.

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

Excel will open 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 MS 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. In later versions of Excel, the list of variables is normally open next to the Pivot Table and you can add to the table by dragging the names into the required places.

If the list is not automatically available, you can re-open it by selecting any cell in the Pivot Table. Alternatively, you can click on Data / PivotTable and PivotChart Report (or Pivot Table in earlier versions) from the top menu bar, then Layout - or select the Pivot Table icon.

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

To remove an element from a table simply click on the grey title panel of the element and drag it outside the boundary of the table. Similarly, to alter the layout of the table, drag the grey title panels to other positions within the table.

The arrows on the grey panels produce drop-down menus when selected. This allows you to select which groups are included in the variable. To exit these menus press OK to save or Cancel to exit without retaining your changes.

You can also alter the variables within the table by double-clicking on the grey panels. This will display a dialog allowing you to add/remove various subtotals to the variables and also to hide a variable.

Adding multiple variables
Another way of controlling which fields within a variable are displayed within a Pivot Table is to filter one variable by using another variable. This can be done by placing the variable you wish to filter by in the Page area of the table. This can be used to view the data relating to only one aspect of the variable.

Adding a second variable to a row or column can give the same effect. Adding the second variable to the column field rather than the row field results in the same information but with a different layout. These two differing layouts may both be useful as each makes it easier to compare different aspects of the data.

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 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 and 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 group items open a dataset and click with the left mouse button on the first cell you wish to group. Keeping the button pressed, move the mouse to the other cells to group and then let go of the button. With the mouse pointer inside the selected area, click with the right mouse button and select Group and outline followed by Group from the menu displayed.

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 and 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 or up 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
As well as viewing the data in the default numerical format, it is also possible to view the data as calculated fields and also to add and remove sub-totals and totals as required.

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.

One of the most useful formats is percentages - of the total, row or column values. These can be created whilst creating the table in the layout screen. When you have chosen the required data fields, right click on any you wish to alter and a menu will appear. Click the options button and the dialog box will expand and the Show data as field will display the default setting of normal.

Changing this setting and then selecting OK will cause the data in the altered column or row to change to reflect your choice of format.

Charts & Graphs
Creating a chart from a set of data can often make visual interpretation of the data set much faster and easier.

Pivot Charts
To create a Pivot Chart from a Pivot Table, right-click on the Pivot Table and select the Pivot Chart option. A chart will appear displaying the data from within the Pivot Table graphically.

The data types and variables that appear on the Pivot Chart will be displayed as they appear in the Pivot Table, with the data type as grey panels with drop-down menus. These panels have the same functionality as those in the Pivot Table, allowing you to change the categories within the variables that are displayed; they can also be dragged to different areas of the chart to change the layout. Similarly variables can be added and removed from the chart by dragging them from the Pivot Chart menu or dragging them out of the active chart areas.

Beware, if you make any changes to the Pivot Chart as described in this section, the data in the underlying Pivot Table will also be changed.

Pressing the chart icon on the Pivot Table tool bar will cause the chart wizard to be displayed, this will allow you to alter the cosmetic appearance of the graph, i.e. changing the graph type (line, bar, scatter graph etc) or adding labels and titles to the chart.

The Chart Wizard
The second method of creating a chart is to highlight the cells that contain the data that you wish to view graphically and select the Chart Wizard. This can be done by clicking the Chart Wizard icon on the menu bar or by selecting the chart option from the insert drop-down menu on the menu toolbar.

Using the Chart Wizard you will be able to create a chart from any data set not just from a Pivot Table.

The first screen allows you to choose the type of chart that you wish to produce. It will also display a sample chart that would be produced from your data. Once you have selected a chart type press 'Next' and a sample of your chosen chart using your data will be displayed with an option to amend the data range if required. The data range is simply the group of cells containing the data that comprises the chart.

If the data is more complex, e.g. if you do not wish to include every adjacent cell within a row or column then you can select the series tab instead, this will allow you to select custom arrangements of cells and series.

It also allows you to add, edit and remove data series and to select different data ranges from the underlying data and also to select cells containing the name of each data series.

The last option, category X-axis labels allows you to select cells that contain labels for the x-axis if required.

Once you have chosen your data series and selected Next, the third stage in the Chart Wizard will be displayed.

The options available here allow you to add titles and labels to the chart and to edit the axes and gridlines on the chart. When you have finished this step, select Next if you wish to specify the location of the completed chart or press Finish if you wish the chart to be displayed on the same sheet as the underlying data.

If at any time you wish to go back and amend a design decision then use the Back button on each step of the wizard.

If you wish to amend the layout of the chart, right clicking on different areas of the chart will activate menus. These menus contain options that allow editing of the chart in question.