Identifying who and who has not completed the Harassment training

1. Select any cell within the data.
2. In the ribbon at the top of the sheet, select the Insert tab.
3. Select Pivot Table from the button on the left-hand side of the Insert ribbon.

A "PivotTable from table or range" dialog box is displayed in the middle of the page. Ensure you check 3 areas of the dialogue box before selecting OK.

1. First, "Table/Range:" should be populated by default and you don't need to change it.
2. Next, New Worksheet is selected by default, again you don't need to change this.
3. Finally, and importantly, tick the Add this data to the Data Model box in the bottom left of the dialogue box.
4. Select the OK button.

A new worksheet will be inserted into the spreadsheet and will be displayed with the template of a pivot table.

Build the pivot table

In the new worksheet the pivot table template is displayed on the left of the sheet (PivotTable1). This is where the data will appear as the table is built. PivotTable Fields are displayed on the right of the sheet and the main box at the top includes all of the fields in the exported data. These are used to build your table. At the bottom of the PivotTable Field list are 4 squares (Filters, Columns, Rows and Values) where you will drag the fields to. In the following task the Rows, Values and Columns areas will be used.

1. From the fields, drag Email to the “Rows” section.
2. Drag SSO to the “Values” section.
3. Select the dropdown on the right of Count of SSO in Values.
4. Select Value Field Settings.
5. In the “Summarise value field by” area, using the scroll bar, scroll down to Distinct Count and select it.
6. Select OK.
7. Back to the fields, scroll towards the bottom of the list and drag Completed to the "Columns" area. This will display who has and hasn't completed the course.

The pivot table will now have been created in the report base area on the left of the worksheet.

Filter and copy email addresses

1. Use Freeze panes (described earlier) when selecting B5.  This will enable you to always see the column headers. 
2. In the pivot table, filter the Column Labels to show only No by selecting the drop-down arrow on the right-hand side of Column Labels.
3. Copy the email addresses of staff who need to retake the course.
   Note. The list is used at your discretion and might include email addresses that are still current in the system but that you don't want to include.
4. Open your email client and paste the email addresses in the BCC field.
5. Compose an email and send it to the relevant staff members.
6. To check your percentage completion rate, divide the total figure for Yes, by the total for No 

Monthly monitoring

Repeat the above steps monthly to ensure compliance.