How to Create a Training Tracker in Google Sheets

Building a training tracker in Google Sheets is a straightforward way to monitor your team's learning and development efforts. A well-designed tracker serves as a central hub for course progress, completion dates, and performance, providing clarity for both managers and employees. This guide will walk you through creating a simple, yet powerful, training tracker from scratch, complete with automated calculations, data validation, and a dynamic summary dashboard to visualize your team’s progress.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Setting Up Your Core Training Data Sheet

First, we need a place to log all the raw training data. This will be the foundation of our tracker. Create a new Google Sheet and name the first tab "Training Log" or something similar.

In this sheet, set up the following columns in the first row:

• A: Employee Name - The person undergoing the training.
• B: Course Name - The title of the training module or course.
• C: Status - The current state of the training (e.g., Not Started, In Progress, Completed).
• D: Start Date - When the training was assigned or started.
• E: Due Date - The deadline for completing the training.
• F: Completion Date - The actual date the training was finished.
• G: Score/% - The employee's score or grade, if applicable.
• H: Training Duration (Days) - An automated column to calculate how long the training took.
• I: Timeliness - An automated column to see if the training was completed on time.

Once your headers are in place, freeze the top row for easy navigation by going to View > Freeze > 1 row. This keeps your headers visible as you scroll down through the records.

Your sheet now has a basic structure. It’s a good start, but entering data manually can lead to typos and inconsistencies ("Completed" vs. "complete" vs. "Done"). Let's fix that next.

Improving Data Integrity with Drop-Down Menus

To avoid data entry errors and keep your reports clean, we’ll use drop-down menus for columns with predefined options like 'Status'. This practice ensures that everyone uses the exact same terminology.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Create a "Lists" Sheet

It's best practice to keep your lists on a separate, dedicated sheet. Create a new tab and name it "Lists." This keeps your main tracker clean.

In the "Lists" sheet, create a header called Training Status in cell A1. Below it, list the statuses you want to use:

• Not Started
• In Progress
• Completed
• Failed

2. Apply Data Validation

Now, head back to your "Training Log" sheet.

• Select the entire 'Status' column by clicking on the column letter 'C'.
• Go to the menu and click Data > Data validation.
• In the criteria dropdown, select "Dropdown (from a range)."
• Click the grid icon next to the text box to select the data range. A small window will pop up.
• Click on your "Lists" tab and select the cells containing your statuses (e.g., A2:A5).
• Click OK, and then click "Save."

Now, every cell in the Status column will have a neat drop-down menu with your predefined options, eliminating guesswork and standardizing your data.

Pro Tip: You can use this same method for the 'Employee Name' and 'Course Name' columns. Simply create lists of your employees and available courses on the "Lists" sheet and link them via data validation.

Automating Calculations with Key Formulas

This is where your tracker starts working for you. By adding a few formulas, you can automate calculations and get instant insights without any manual effort.

Calculating Training Duration

We want to automatically calculate how many days it took an employee to complete their training. We'll put this formula in the 'Training Duration (Days)' column (Column H).

Go to cell H2 and enter the following formula:

=IF(F2="", "", F2-D2)

Let's break this down:

• IF(F2="", "": This is an IF statement. It checks if the 'Completion Date' cell (F2) is empty. If it is, this formula leaves the duration cell blank to keep things clean.
• , F2-D2): If the 'Completion Date' cell is not empty, the formula subtracts the 'Start Date' (D2) from the 'Completion Date' (F2) to get the total number of days.

Click the small blue square in the corner of cell H2 and drag it down to apply this formula to the entire column.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Checking On-Time Completion

Next, let's automatically flag whether training was completed on time, late, or if it's currently overdue. We'll use a nested IF formula in the 'Timeliness' column (Column I).

In cell I2, enter this formula:

=IF(C2="Completed", IF(F2<=E2, "On Time", "Late"), IF(TODAY()>E2, "Overdue", "On Track"))

This one looks more complex, but it's just a series of checks:

• IF(C2="Completed", ...: First, it checks if the 'Status' in cell C2 is "Completed".
• ...IF(F2<=E2, "On Time", "Late")...: If the status IS "Completed," it then checks if the 'Completion Date' (F2) is less than or equal to the 'Due Date' (E2). If so, it flags it as "On Time." Otherwise, it's "Late."
• ..., IF(TODAY()>E2, "Overdue", "On Track")): If the status is NOT "Completed," it moves to this part. It checks if today's date (using the TODAY() function) is past the 'Due Date' (E2). If it is, the training is "Overdue." Otherwise, it's considered "On Track."

Drag this formula down the column just like you did before. Your sheet is now much smarter and provides key information automatically.

Visualizing Status with Conditional Formatting

Scrolling through text is fine, but color-coding provides an immediate visual summary of your data. Let's use conditional formatting to color our 'Status' and 'Timeliness' columns.

To color-code your 'Status' column:

1. Select the entire column 'C' (Status).
2. Go to Format > Conditional formatting.
3. Under "Format rules," choose "Text is exactly" from the dropdown.
4. In the value box, type Completed.
5. Choose a green fill color under "Formatting style" and click "Done."
6. Click "Add another rule" and repeat the process for other statuses: "In Progress" with yellow, and "Failed" with red.

You can apply the same logic to the 'Timeliness' column, coloring "Overdue" and "Late" cells red to make them stand out instantly.

Building an Automated Dashboard Tab

Now for the fun part: creating a high-level summary dashboard. A dashboard gives managers a quick, birds-eye view of the training program without having to sift through the raw data.

Create a new sheet and name it "Dashboard."

Calculate Key Performance Indicators (KPIs)

First, we’ll set up a section for our main metrics. In one area of your dashboard, list the following labels:

• Total Enrolled Trainings
• Trainings In Progress
• Trainings Completed
• Trainings Overdue
• Overall Completion Rate

Next to these labels, we'll use simple formulas to pull the data from our "Training Log" sheet. If your "Training Log" has data in the 'Status' column (C), these formulas will work:

• Total Enrolled Trainings: =COUNTA('Training Log'!B2:B) (Counts all non-empty course cells)
• Trainings In Progress: =COUNTIF('Training Log'!C2:C, "In Progress")
• Trainings Completed: =COUNTIF('Training Log'!C2:C, "Completed")
• Trainings Overdue: =COUNTIF('Training Log'!I2:I, "Overdue")

For the Overall Completion Rate, we'll divide Completed by Total and format it as a percentage. Assuming 'Trainings Completed' is in cell B3 and 'Total Enrolled Trainings' is in B1 on your dashboard:

=B3/B1

Then, select that cell, go to Format > Number > Percent.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Visualizing Your Data with Charts

Charts and graphs are perfect for making your dashboard easy to understand.

Let's create a pie chart to show the breakdown of training statuses:

1. On your Dashboard sheet, create a small table with your statuses and their counts. It should look like this, pulling from your KPI calculations:

• Column 1: "In Progress", "Completed", "Not Started".
• Column 2: Use the cell references (e.g., =B2, =B3) to pull in the counts you calculated earlier.

1. Select this small table of data.
2. Go to Insert > Chart.
3. Google Sheets will likely default to a Pie Chart, which is perfect for this. If not, you can select it in the chart editor.
4. Customize the titles and colors to match your preferences.

You can create other charts as well, like a bar chart showing the number of completed trainings by employee or a timeline of completion dates. The QUERY and FILTER functions are your friends here for creating more advanced summaries.

Final Thoughts

With these steps, you’ve moved beyond a simple list and created a dynamic, insightful training tracker in Google Sheets. Your new system uses data validation, automated formulas, and a helpful dashboard to provide a clear, real-time view of your team's learning progress, giving you back time to focus on what matters: employee development.

While Google Sheets is fantastic for projects like this, managing data from multiple platforms like your CRM, ad accounts, and analytics tools can still feel manual and disconnected. We built Graphed to address that very challenge. Instead of exporting CSVs and fighting with pivot tables, you can connect your sources once, then use plain English to ask questions and instantly build live dashboards. It’s a powerful way to get insights from all your business data without ever getting stuck in spreadsheet-land again.