Working with the ISDP course training data in Excel

Create a data table and apply formula to analyse completion data.

Once you have exported data (step 4) you will use Excel. The steps below assume you have basic Excel skills, including opening and saving files.

If you already had access to a training data report before following these instructions, please check your Power BI window to identify which report you are using. These instructions are for the Compliance General report. If you have exported data from the Compliance Enhanced or the Training Providers report, the Excel formula will differ, but it is included in the instructions.

Prepare the Excel sheet so it is ready to work with

  1. Select Downloads to open the Excel Spreadsheet or navigate to where you saved it and open it.
  2. To ensure the column titles remain visible, 'freeze' the top row of data.
  3. Select cell B2.
  4. Go to the View tab on the ribbon.
  5. Select Freeze Panes and then select Freeze Panes from the dropdown.

Hide unnecessary columns

The report contains many columns of data. For this task, some of the columns are superfluous, and you can hide the unnecessary columns to make it easier to see the data; however, don't hide columns A–F, AB–AF, and AL–AP.

  1. Highlight columns P–AC, this is all the People Data that isn’t needed for this exercise.
  2. Right-click and select Hide.

Note: Do NOT delete any columns as the following formula won’t work.

 

Add a formula to identify course completion in the Compliance General report

To find out who completed the course in the last year, add two extra columns and formulas after the last column of your current data. If you have exported data from the Enhanced or Training Providers report, this formula won't work; please refer to the specific instructions for those reports.

  1. Add a column title in cell AU1 called "InfoSec Comp".
  2. In cell AU2, enter this formula to check if the course was completed within the last 366 days:
     =IF(COUNTIFS(F:F, F2, AO:AO, "Yes") > 0, "Yes", "No") 
  3. Copy the formula down the entire column by double-clicking the small square at the bottom right corner of cell AU2.

 

Add a formula to identify who needs to retake the course

To work out who has completed the course 11 months ago.

  1. Create another column titled "11 Months" in cell AV1.
  2. In cell AV2, enter the following formula to identify those who need to retake the course within the next 31 days:
     =IF(AND($AU2="Yes",$AM2>=335,$AM2<=366),"Yes","No")
  3. Copy the formula down the entire column by double-clicking the small square at the bottom right corner of cell AV2.

Add a formula to identify course completion in the Enhance or Training Providers report

Instructions for the Enhanced and Training Providers reports. These reports include 2 extra columns.

  1. Add a column title in cell AW1 called "InfoSec Comp".
  2. In cell AW2, enter this formula to check if the course was completed within the last 366 days:
     =IF(COUNTIFS(F:F, F2, AQ:AQ, "Yes") > 0, "Yes", "No") 
  3. Copy the formula down the entire column by double-clicking the small square at the bottom right corner of cell AW2.

 

Add a formula to identify who needs to retake the course

To work out who has completed the course 11 months ago.

  1. Create another column titled "11 Months" in cell AX1.
  2. In cell AX2, enter the following formula to identify those who need to retake the course within the next 31 days:
    =IF(AND($AW2="Yes",$AO2>=335,$AO2<=366),"Yes","No")
  3. Copy the formula down the entire column by double-clicking the small square at the bottom right corner of cell AX2.

 

Contact CoSy Support us via our form

Popular links