A Pivot table is in essence a way to extract data and present it in a readable form. Pivot tables are a great way to interrogate data and create tables of information that can be refreshed when the raw data changes!

Working example: you require a table that shows the following:

• How many positions (Actual FTE) you have in total
• Broken down by Position Title
• Broken down by Employment Status (Perm/Temp/Casual)

Once you have the raw data you create the pivot table on your raw data:

1. From the toolbar select Data > Pivot Table and Pivot Chart Report…
2. Click Next



3. Check that the data you want to include in the pivot table is highlighted in the Range



4. Click Next
5. If you want your pivot table to appear in a new worksheet (tab) click finish. Otherwise, If you wish it to appear in the same worksheet (ie – below the data) click Existing worksheet and select the first cell after the data and then click finish
   

   The pivot table skeleton should appear.

   

   If the Pivot Table Field List (shown on the right, above) is not in view then you will need to open it:

6. Right click the toolbar (anywhere in the square below):



7. Select PivotTable.
8. Click Show Field List



9. The fields should now be in view and you can drag the box to the right to lock it in place.



The next step is to drag the fields you need into the table areas to create the table. The pivot table structure is as follows:



Typical usage of the areas:
HIDDEN FILTER AREA: Recurrently funded positions filter
COLUMN SEPARATOR AREA: Position Type, Employment Type, Employment Basis, Classification
ROW SEPARATOR AREA: Org Units, Locations, Position Titles
BASE DATA AREA: Headcounts, FTE’s

10. Drag the following fields into the areas:HIDDEN FILTER AREA: None
    COLUMN SEPARATOR AREA: Position Type
    ROW SEPARATOR AREA: Position Title
    BASE DATA AREA: Actual FTE



The pivot table should look like this:



Notice that the base data has defaulted to “Count of Actual FTE”. This means that each position has been counted as 1 irrelevant of the FTE value. If the base field was Headcount this would be correct (as all values would be 1) however it’s FTE values and they require a sum not a count.

11. To change the base field to a sum right click the field (circled below):
    

    Select Field Settings…

    Select Sum.

    
    
    

    Click Number…
    Select Number and 2 decimal places then click OK.
    

    The data should have changed to decimals.

    

12. To re-order the Position Type columns:Right click Casual > Order > Move to End
    
    You now have your finished table with the columns in order of Permanent, Temporary, Casual.