Sending reminders to staff to complete the ISDP 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 Create PivotTable dialog box is displayed in the middle of the page. Ensure you check three areas of the box before selecting OK.

1. Choose the data that you want to analyse and Select a table or range radio button will be selected, and Table/Range should be populated by default.
2. New Worksheet is selected by default.
3. Tick the Add this data to the Data Model 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. This is where the data will appear as the table is built. PivotTable fields are displayed on the right of the sheet and 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. Drag Email to the “Rows” section.
2. Drag SSO to the “Values” section.
3. Select the dropdown in Values.
4. Select Value Field Settings.
5. In the “Summarize value field by” area, using the scroll bar, scroll down to Distinct Count and select it.
6. Select OK.
7. Drag InfoSec Comp 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. 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.
2. 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.
3. Open your email client and paste the email addresses in the BCC field.
4. Compose an email and send it to the staff.

Send a reminder to those who will need to retake the ISDP course

Filter people who completed the course 11 months ago and will need to retake it within the next 30 days.

1. In the PivotTable Fields on the right-hand side of the page, swap InfoSec Comp in Columns with 11 Months by dragging Infosec Comp out and dragging in 11 Months to the columns area.
2. Repeat filtering Column Labels but this time select Yes, this will leave you with the email address of the people who need an 11-month reminder.

Monthly monitoring

Repeat the above steps monthly to ensure compliance and send reminders to staff whose course completion is expiring soon.