--- title: "Tracking Course Completions in Excel" slug: "tracking-course-completions-in-excel" updated: 2024-04-25T17:18:10Z published: 2024-04-25T17:18:10Z canonical: "support.smarteru.com/tracking-course-completions-in-excel" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.smarteru.com/llms.txt > Use this file to discover all available pages before exploring further. # 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](/v1/docs/tracking-course-completions-in-excel#downloading-and-preparing-the-sample-file) 2. [Exporting users from SmarterU](/v1/docs/tracking-course-completions-in-excel#exporting-users) 3. [Exporting completed enrollments from SmarterU](/v1/docs/tracking-course-completions-in-excel#exporting-completed-enrollments) 4. [Copying users to the sample file.](/v1/docs/tracking-course-completions-in-excel#copying-users-to-the-sample-file) 5. [Copying completed enrollments to the sample file](/v1/docs/tracking-course-completions-in-excel#copying-completed-enrollments-to-the-sample-file). 6. [Specifying course names](/v1/docs/tracking-course-completions-in-excel#specifying-course-names). ## **Downloading and Preparing the Sample File** To download and prepare the sample file: 1. Download the [WYD Learning Plan Demo.xlsx](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/WYD%20Learning%20Plan%20Demo.xlsx) file. 1. Open the sample file that you downloaded. You may see the [Calculating message](/v1/docs/tracking-course-completions-in-excel#about-the-calculating-status-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](/v1/docs/tracking-course-completions-in-excel#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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-mb0581dn.png) 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](/v1/docs/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](/v1/docs/system-email-templates-report-emails), download the file. **NEXT STEP:** [Exporting Completed Enrollments](/v1/docs/tracking-course-completions-in-excel#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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-32hzlhdx.png) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-nrbe9vze.png) 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](/v1/docs/system-email-templates-report-emails), download the file. **NEXT STEP:** [Copying Users to the Sample File](/v1/docs/tracking-course-completions-in-excel#copying-users-to-the-sample-file) ## **Copying Users to the Sample File**  You may see the [Calculating message](/v1/docs/tracking-course-completions-in-excel#about-the-calculating-status-message) during this part of the process. To copy users to the sample file: 1. From the [user export file that you downloaded](/v1/docs/tracking-course-completions-in-excel#downloading-and-preparing-the-sample-file), 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](/v1/docs/tracking-course-completions-in-excel#copying-completed-enrollments-to-the-sample-file) ## **Copying Completed Enrollments to the Sample File** You may see the [Calculating message](/v1/docs/tracking-course-completions-in-excel#about-the-calculating-status-message) during this part of the process. To copy completed enrollments to the sample file: 1. From the [completed enrollments file that you downloaded](/v1/docs/tracking-course-completions-in-excel#exporting-completed-enrollments), do the following: 1. If your account has [custom user fields](/v1/docs/account-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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-3lyo13ue.gif) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-41mrlp3o.png) 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](/v1/docs/tracking-course-completions-in-excel#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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-40l35sws.png) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-md5i8dee.png) ### **Optional Next Steps:** - [Change the conditional formatting](/v1/docs/tracking-course-completions-in-excel#changing-conditional-formatting). - If you need to report on more than 10 courses, you may [insert columns for additional courses](/v1/docs/tracking-course-completions-in-excel#inserting-columns-for-additional-courses). - If you have more than 500 users, you may [extend the formula for additional users](/v1/docs/tracking-course-completions-in-excel#extending-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. ![Uma imagem contendo Calendário Descrição gerada automaticamente](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-ugon3j2o.png) 1. With the cell S2 selected, [extend the series](https://support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db#bmfillinseries) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-hqqh2r6o.png) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-ud6b6i3k.png) | **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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-4uxpxngw.png) | | **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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-0aeq8uea.png) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-nyi44ldz.png) 1. With the cell below the *Complete?* column still selected, [extend the series](https://support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db#bmfillinseries) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-a3lcuz3u.png) 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](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/tracking-course-completions-in-excel-image-980haa1n.png) 1. With the cell below the *Complete?* column still selected, [extend the series](https://support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db#bmfillinseries) 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. ## Related - [Enrollment Report](/enrollment-report.md) - [Enrollment Report: Viewing a Learner's Progress](/enrollment-report-viewing-a-learners-progress.md) - [Exporting Users](/exporting-users.md) - [Learning Plans](/learning-plans.md) ## Attachments - [WYD Demo.xlsx](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/WYD Demo.xlsx) - [WYD Learning Plan Demo.xlsx](https://cdn.document360.io/7efaadab-fef6-4186-806a-2ca14c9ce9ea/Images/Documentation/WYD Learning Plan Demo.xlsx)