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:
- From the toolbar select Data > Pivot Table and Pivot Chart Report…
- Click Next
- Check that the data you want to include in the pivot table is highlighted in the Range
- Click Next
- 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:
- Right click the toolbar (anywhere in the square below):
- Select PivotTable.
- Click Show Field List
- The fields should now be in view and you can drag the box to the right to lock it in place.
- 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
- To change the base field to a sum right click the field (circled below):
Select Field Settings…
Select Number and 2 decimal places then click OK.
The data should have changed to decimals.
- 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.
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
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.