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

    image061

    The pivot table skeleton should appear.

    image063

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

    

    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:

    pivot1

    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

    COLUMN SEPARATOR AREA: Position Type

    ROW SEPARATOR AREA: Position Title

    BASE DATA AREA: Actual FTE

  16. image079

    The pivot table should look like this:

    image081

    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):

    image083

    Select Field Settings…

    Select Sum.

    image085

    Click Number…

    Select Number and 2 decimal places then click OK.

    image087

    The data should have changed to decimals.

    image089

  18. To re-order the Position Type columns:Right click Casual > Order > Move to End

    image091

    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)

image083

Select Table Options…

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

image093

Click OK.

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

image095

2) To hide the Totals column:

image097

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

Select Table Options…

Uncheck Grand Total for Rows (circled below)

image099

The totals column should now have been removed.

image101

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)

image103

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

image105

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

image107

4) To show only selected position titles:

Click the down arrow to select the Position Title filter

image109

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

image111

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

image113

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.

image115

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…

image117

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.

image119

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