Tracking Course Completions in Excel

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 appropriate sample file based on whether your account has the learning plans feature enabled.

2. Open the sample file that you downloaded.

3. 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.
4. 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. 

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. 

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

6. In the Send Email To field, enter your email address.
7. Click Confirm.
8. 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 

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

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.

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

2. Press CTRL+A to select the data.
3. Press CTRL+C to copy the data.

2. 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.

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.

Optional Next Steps:

• Change the conditional formatting.
• If you need to report on more than 10 courses, you may insert columns for additional courses. 
• If you have more than 500 users, you may extend the formula for additional users. 

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.

3. 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.

4. 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.

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.