Once you have exported data (step 4) you will use Excel. The steps below assume that you have basic Excel skills including opening and saving files.
Prepare the Excel sheet so it is ready to work with
- Select Downloads to open the Excel Spreadsheet or navigate to where you saved it and open it.
- To ensure the column titles remain visible 'freeze' the top row of data.
- Select cell B2.
- Go to the View tab on the ribbon.
- Select Freeze Panes and then select Freeze Panes from the dropdown.
Hide unnecessary columns
The report contains many columns of data. For the purposes of this task, some of the columns are not needed 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
- Highlight columns P – AC, this is all the People Data that isn’t needed for this exercise.
- Right-click and select Hide.
Note: Do not delete any columns as the formula you will be entering next won’t work.
Add formula to identify course completion
To work out who has completed the course in the last year you will add extra columns and formula after the last column of the current data.
- Add a column title in cell AU1 called "InfoSec Comp".
- 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")
- Copy the formula down the entire column by double-clicking the small square at the bottom right corner of cell AU2.
Add formula to identify who needs to retake the course
To work out who has completed the course 11 months ago.
- Create another column titled "11 Months" in cell AV1.
- In cell AV2, enter the following formula to identify those who need to retake the course within the next 31 days:
=IF(AND($AW2="Yes",$AM2>=335,$AM2<=366),"Yes","No")
- Copy the formula down the entire column by double-clicking the small square at the bottom right corner of cell AV2.
