Tracking Course Completions in Excel
  • 5 Minutes to read
  • Dark
    Light

Tracking Course Completions in Excel

  • Dark
    Light

Article summary

About Tracking Course Completions in Excel

Using user and enrollment data exported from SmarterU, you can use Microsoft Excel to create a visual representation of which users have completed courses that you are tracking. 

The process consists of the following parts:

  1. Downloading and preparing the sample file
  2. Exporting users from SmarterU
  3. Exporting completed enrollments from SmarterU
  4. Copying users to the sample file.
  5. Copying completed enrollments to the sample file
  6. Specifying course names

Downloading and Preparing the Sample File

To download and prepare the sample file:

  1. Download the WYD Learning Plan Demo.xlsx file. 
  1. Open the sample file that you downloaded.
You may see the Calculating message during the following steps.
  1. Clear the data from the User Dashboard Data worksheet by doing the following:
    1. Select the User Dashboard Data worksheet.
    2. Press CTRL+A to select all the sample data in the worksheet.
    3. Press DELETE to remove the sample data.
  2. Clear the data from the Enrollment Report Data worksheet by doing the following:
    1. Select the Enrollment Report Data worksheet.
    2. Press CTRL+A to select all the sample data in the worksheet.
    3. Press DELETE to remove the sample data.

NEXT STEP: Exporting Users

About the Calculating Status Message

While completing some steps in this article, Excel may display a Calculating message at the bottom of the window. This message is normal and displays because the file contains numerous formulas that Excel attempts to re-calculate whenever you make a change. 

Diagrama  Descrição gerada automaticamente com confiança média

If you see this message and are still making changes to the file, press ESC to cancel the re-calculation.

When you have finished making changes to the Excel file, you should allow the re-calculation process to finish; this ensures that the data shown in the Completion worksheet is correct. 

Exporting Users

In order to use the sample Excel file with your data, you'll need to export users from SmarterU. 

To export users:

  1. Follow the steps detailed on the Exporting Users page. Be sure to select Export to CSV (full)
  2. When you receive the email stating that your file is ready to download, download the file. 

NEXT STEP: Exporting Completed Enrollments

Exporting Completed Enrollments 

In this part of the process, you'll need to export completed enrollments from SmarterU. 

To export completed enrollments:

  1. From the navigation bar, expand Report Admin.
  2. Select Enrollments.
  3. In the Filters section, check the Completed checkbox for the Filter by Progress Status filter. 

Interface gráfica do usuário, Texto, Aplicativo  Descrição gerada automaticamente

  1. Click Run Report.
  2. From the Actions list located on the right above the table, select Export to CSV (full).

Interface gráfica do usuário, Site  Descrição gerada automaticamente

  1. In the Send Email To field, enter your email address.
  2. Click Confirm.
  3. When you receive the email stating that your file is ready to download, download the file. 

NEXT STEP: Copying Users to the Sample File

Copying Users to the Sample File 

 You may see the Calculating message during this part of the process.

To copy users to the sample file:

  1. From the user export file that you downloaded, do the following:
    1. Press CTRL+A to select the user data.
    2. Press CTRL+C to copy the user data.
  2. From the sample file:
    1. Select the User Dashboard Data worksheet.
    2. Be sure that cell A1 is selected.
    3. Press CTRL+V to paste the user data into the worksheet. 

Pasting the user data into the User Dashboard Data worksheet populates the Completion worksheet with your users' information.

NEXT STEP: Copying Completed Enrollments to the Sample File

Copying Completed Enrollments to the Sample File

You may see the Calculating message during this part of the process.

To copy completed enrollments to the sample file:

  1. From the completed enrollments file that you downloaded, do the following:
    1. If your account has custom user fields, delete the custom user field columns from the file.

Interface gráfica do usuário, Aplicativo, Tabela, Excel  Descrição gerada automaticamente

Custom user fields can be found between the Allow Feedback column (i.e., column Y) and the Organization column.

Interface gráfica do usuário, Texto, Aplicativo  Descrição gerada automaticamente
  1. Press CTRL+A to select the data.
  2. Press CTRL+C to copy the data.
  1. From the sample file:
    1. Select the Enrollment Report Data worksheet.
    2. Be sure that cell A1 is selected.
    3. Press CTRL+V to paste the data into the worksheet. 

Pasting the data into the Enrollment Report Data worksheet populates the Completion worksheet.

NEXT STEP: Specifying Course Names

Specifying Course Names

The formulas on the Completion worksheet will use the course names specified in the first row to search the Enrollment Report Data worksheet.  

To ensure that the formulas are searching for the correct course(s), change the course names in cells I1 through R1 to the course names you would like to report on.

Uma imagem contendo Interface gráfica do usuário  Descrição gerada automaticamente

After entering a course name, the Excel worksheet analyzes and calculates your data. An "X" displays to indicate whether a learner has completed the course.

Interface gráfica do usuário, Aplicativo  Descrição gerada automaticamente

Optional Next Steps:

Inserting Columns for Additional Courses

If you have more than 10 courses that you'd like to track, you can insert columns to the worksheet by doing the following:

  1. Insert a new column by doing the following:
    1. Select column R.
    2. Press CTRL+C.
    3. Right-click column S. 
    4. Select Insert Copied Cells.
  2. Modify the formula by doing the following:
    1. Select cell S2. 
    2. Replace $R$1 with $S$1 to ensure that the formula searches for the course name specified in cell S1. 
    3. Press CTRL+SHIFT+Enter to set the formula in an array.

Uma imagem contendo Calendário  Descrição gerada automaticamente

  1. With the cell S2 selected, extend the series so that the formula is applied to the remaining rows in the worksheet. Be sure to select Copy Cells from the Auto Fill options.

Tabela, Excel  Descrição gerada automaticamente

  1. Change the course name in cell S2 to the course name you would like to include. 

Changing Conditional Formatting

The sample file includes conditional formatting that displays users who have the courses in the first row (i.e., cells I1-R1) with a green background. In the example below, user Anna Cruz displays with a green background because she has completed all 10 courses shown in row 1.

Interface gráfica do usuário, Aplicativo, Tabela  Descrição gerada automaticamente

If you want to...

Do this...

Remove conditional formatting from the Completion worksheet

Delete the Complete? column from the worksheet

Aplicativo  Descrição gerada automaticamente com confiança média

Apply conditional formatting to additional course columns

To apply the conditional formatting to course columns that you've added to the worksheet:

  1. Select the cell below the Complete? column header.

Gráfico de dispersão  Descrição gerada automaticamente com confiança média

  1. Modify the formula to include the additional course column(s) you would like to include in the calculation. In the example below, Course Name 11 was added in column S. The formula was adjusted to include the additional column.

Uma imagem contendo Interface gráfica do usuário  Descrição gerada automaticamente

  1. With the cell below the Complete? column still selected, extend the series so that the formula is applied to the remaining rows in the worksheet. Be sure to select Copy Cells from the Auto Fill options.

Apply conditional formatting for specific courses

If you want to apply conditional formatting based on whether a user completed a specific course: 

  1. Select the cell below the Complete? column header.

Gráfico de dispersão  Descrição gerada automaticamente com confiança média

  1. Modify the formula to include the course column(s) you would like to include in the calculation. In the example below, the conditional formatting is applied to a row if the user completed the course specified in cells I2 and O2 (i.e., Employee Onboarding and Winter Products). 

Interface gráfica do usuário, Aplicativo  Descrição gerada automaticamente

  1. With the cell below the Complete? column still selected, extend the series so that the formula is applied to the remaining rows in the worksheet. Be sure to select Copy Cells from the Auto Fill options.

Extending the Formula for Additional Users

If you have more than 500 users in your file, you will need to extend the formula to the additional rows.

To insert rows for additional users:

  1. Select the Completions worksheet.
  2. Select row 500 and any additional rows that you have added.
  3. Press CTRL+D. This copies the formula for the selected rows.
Attachments

Was this article helpful?