Pivot Tables

About this guide

We are now publishing a range of data in pivot table format. The purpose of this guide is to assist users to understand how to make the most of this data.

Prerequisites

A working knowledge of Microsoft Office Excel is required to use our pivot tables. These pivot tables are created in Microsoft Excel 2010.

For more information on how to use Microsoft Excel, see Microsoft Support.

Using pivot tables in Microsoft Excel

Pivot tables are a powerful, easy to use data analysis tool that enables you to reorganise, filter, group and summarise data by dragging and dropping variables into and out of a table.

Structure of a pivot table

The following diagram shows the basic structure of a pivot table. This picture specifically relates to the subclass 457 pivot table, but the principles are the same for all the department’s pivot tables.

Figure 1: Structure of a pivot table

Using fields to summarise data

What is a field?

  • Fields are also known as data items or variables. They are what enable us to specify the data to be displayed in the pivot table.
  • Specifically, the pivot fields may include applicant type, age group, gender, citizenship country and client location.
  • Within each field there are items. For example, the items in the field applicant type are primary and secondary.
  • Fields enable the data to be either summarised or filtered.
  • All of the fields can be moved to different areas in the pivot table to give different summaries of the data.
  • Clicking the down arrow at the right of the field name will reveal the item selector of the field, showing the items that make up that field.

Moving fields

Fields can be moved to any part of the pivot table.

1.    To move a field simply select the field name, drag and drop the field in the new location. The table will then be updated with the revised table layout and the data will update automatically.

2.    When you move fields around, you will see the cursor change shape indicating the area into which the field is to be dropped.

3.    If a field is placed in the wrong position, it is easy enough to pick up and move.

Stacking fields

Fields can be stacked in the row or column areas, enabling more complex tables to be created from the data.

1.    To stack fields, select the fields and drag and drop them in the appropriate area (one field at a time).

2.    You can stack several fields in a row and/or column, but stacking too many fields can make the data difficult to read.

3.    For example, in Figure 2, gender and selected age groups have been stacked in the row area, enabling the comparison of subclass 457 visa grants by gender and selected age groups across a range of financial years (columns).

Figure 2: Example of stacked fields



Filtering data

Fields can also be used to filter the data. For example in Figure 2 above, only the age ranges of 20 to 39 years were selected in the age group field. Similarly this can be done for other fields that may not specifically be in the table, for example country of citizenship.

To apply a filter, do the following:

1.    Click the down arrow at the right of the field box. This will reveal the items of the field.

2.    Click the item(s) to be filtered on, and click “OK”.
Note: if more than one item is required to be selected, please select the “select multiple items” tick box at the bottom left hand side of the expanded filter box, if it hasn’t already been selected.

3.    The table will then be updated with the updated data.

4.    If only one item is included in the field filter, it will be displayed next to the name of the field. If multiple items have been selected, it will display “multiple items”, if no filtering is applied to a field, it will display “all”.

5.    To remove the filter, follow the same procedure, but select “all”.

Sorting items and data

It is often convenient to sort items in different ways. For example you may want to sort the citizenship countries in your table into alphabetical order, or by the number of visa grants.

To sort items by name:

1.    Locate the field to be sorted in the row or column.

2.    Click the down arrow at the right of the field box.

3.    Click the sort buttons in the menu at the top of the drop down screen to sort the items based on their name.

To sort items by data values:

1.    Locate the field to be sorted in the row or column.

2.    Click the down arrow at the right of the field box.

3.    Click the “more sort options” from the menu.

4.    The sort menu will appear, select ascending or descending, and then select the variable by which to sort the data.

5.    This menu also enables you to manually sort data into an order that you wish.

6.    Once the sort order has been defined, select “OK”.

To sort non-selected items to make it easier to find the filter you want:

1.    Right click the name of the item and select “sort”

2.    Select “sort A-Z” from the extended drop-down menu. This will enable you to easily select multiple countries for example from the country of citizenship list.

Copying data from pivot tables

It is often useful to copy summarised data from a pivot table to another excel file for archiving, presentation or further analysis.

The data in the pivot tables has been locked to protect privacy, and therefore it is not an easy matter of selecting the cells to be copied and pasting the data elsewhere. However the data can be exported from the pivot table, to enable further analysis.

To export the data from the pivot table:

1.     Set up the table in the format you wish to export, including any filtering of the data.

2.     From the menu tabs select “file/save as”

3.     Enter the file name and location where you wish to save the file, but change the file format to csv (comma delimited) (*.csv). This will enable you to open the saved file in excel (or a large range of other programs), while preserving the privacy restrictions placed on the file.

4.     Select “OK”.