Pivot Tables

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

  4. Check that the data you want to include in the pivot table is highlighted in the Range
  5. image059

  6. Click Next
  7. 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:

  8. Right click the toolbar (anywhere in the square below):
  9. image065

  10. Select PivotTable.
  11. Click Show Field List
  12. image067

  13. The fields should now be in view and you can drag the box to the right to lock it in place.
  14. image069 image071


    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

  15. Drag the following fields into the areas:HIDDEN FILTER AREA: None


    ROW SEPARATOR AREA: Position Title


  16. image079

    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.

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


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

In these two tutorials (two parts) you will learn a few cool tips regarding pivot tables:

1) To fill the empty values with zeros:Right click the base data field (circled below)


Select Table Options…

Type 0 in the empty cells, show (circled below)


Click OK.

The pivot table now should have replaced the blanks with zeros.


2) To hide the Totals column:


Right click the base data field (“Sum of Actual FTE” shown above)

Select Table Options…

Uncheck Grand Total for Rows (circled below)


The totals column should now have been removed.


3) To add a hidden filter to the pivot table:Drag the field that contains the filter into the hidden data filter area (ie – Org unit level 04)


Click the down arrow to select the org unit filter. Select the required filter (ie – Sport And Recreation)


The pivot table now shows only those positions in Org unit level 04, Sport And Recreation.


4) To show only selected position titles:

Click the down arrow to select the Position Title filter


To make selection easier drag the list outwards so more positions are visible


If there are many values in the list and you only want a few, start by un-checking Show All (shown below) to deselect all values


Select the positions you wish to show in the table. (ie – Athletes and Managers)

Click OK

The pivot table should now only contain Athletes and Managers.


5) To show percentages of position type for each position:

Right click the base data field (in this case “Sum of Actual FTE”)

Select Field Settings…


Click Options > >

Select % of row for Show data as

Click OK.

Note: In this list there are also options for % of column and % of total with the latter being very useful.


The pivot table now shows percentages but there may be error values for the rows that have no positions (as you can’t divide by zero!).

6) To remove errors from the pivot table:Right click the base data field (in this case “Sum of Actual FTE”)

Select Table Options…

Enter 0 in the For error values, show box

Click OK.

All errors should now be replaced by zeros.

Leave a Comment