In this article, we walk through step by step how to use the Training (Learning & Development) Dashboard Excel Template. 

 

Overall, there are five key steps in this process. 



Let's look at each step in detail.


Step 1: Enter Employees data in Employees sheet

 click to enlarge image


To begin with, add all existing employees.

  1. Employee ID is required. It is a unique identifier of an employee.
  2. Enter Employee Name
  3. Enter Location, Department, Job Level, Manager, Job Title, Email are fields you can populate
    1. You can also rename these fields and use it to store other attributes of employees
  4. Join Date is a required field. This is the date when the employee joins the company.
  5. Exit date is the last date of employment of an employee. Enter this if the employee is no longer employee with the company.

When a new employee joins the company, add to this table. If an employee leaves the company, enter the date of leaving in the Exit Date column.  

The sheet can handle up to 1000 employees by default. It can be extended by unprotecting the sheet and adding more rows.

To paste data from another spreadsheet, please follow these steps

1. Make sure that the columns you are pasting are aligned to the columns in this template. For example, the first column should be Employee ID, second column should be Name.

2. When pasting, please use 'Paste Values'. Right click and choose 'Values' under Paste Options.


As a HR professional responsible for training management, identify the training needs of your company. Engage with the employees of the company and arrive at a list of training requirements.


Step 2: Create training offerings

Once the training needs are identified, take steps to provide the training needed. It could be developing the training material internally or reaching out to external trainers/companies.

Enter trainings available in the Trainings sheet.

 click to enlarge image

  1. Enter Training ID, a unique identifier of a training offering.
  2. Enter the name of the Training
  3. Start Date & End Date: These are the dates during which the training is actually available.
    1. For example, if the training is a day long training and is being offered for an entire month of Jan 2022, you will enter start date as 1-JAN-2022 and end date of 31-JAN-2022. Employee can start their training anyday between 1-JAN-2022 and 31-JAN-2022.
      1. Please note that this is not the duration of how long an employee will be in that training.  If it is an 8 hour training, you will enter the number of hours in the Hours column.  If it is a two day long training (16 hrs), then enter number of hours as 16.
    2. If the training is offered only on Jan 15, 2022, then enter both start and end dates as 15-JAN-2022.
    3. If the training is always open, enter an end date far in the future. (example 31-DEC-2025)
  4. Trainer Name
  5. Source: This could be used to flag whether the training is internally developed or external.
  6. Mode: This is to track whether the training is an online/virtual versus In-person training.
  7. Category & Subcategory: Categorize your trainings in two levels.
  8. Cost: Enter the cost your company has to pay for each employee who signs up for the specific training
  9. Hours: Enter the number of Hours of training associated with that specific training offering.
  10. # of Seats: Enter the maximum number of employees who can participate in that training.
    1. If no limit, enter a very high number like 1000 that cannot be reached.

 

Source, Mode, Category, Subcategory: You can enter any values that are relevant to your company. 


To change Currency, type in your currency as shown below.

By default you can enter up to 300 trainings. 

Step 3: Entering training enrollments

Once a training offering is scheduled, inform employees who can benefit from that training. Encourage them to sign up for training.

When employees sign up for training, enter them in the Enrollments sheet.


  • Enter employee ID from drop-down list
  • Enter training ID from drop-down list.
  • Enter the date when the employee will take part in that training.
  • Enter the status as ‘Enrolled’.
    1. If after enrolling the employee cancels the enrollment, change status to ‘Cancelled’.

 

By default you can enter up to 2000 enrollments. 

 

Monitoring training enrollments and promote


As employees sign up, monitor the number of employees enrolled in each training.

 click to enlarge image


In the Trainings sheet, you can see the number of seats still available, # enrolled & # cancelled.

Status of the training will be displayed as

  1. Training Not Started Yet – If the Training start date is in future.
  2. Training in progress – If the training start date has passed but the end date is in future
  3. Full – If the Training has not started but all the available seats have been taken
  4. Training Ended – if the training end date has passed.

 

You can filter on trainings easily. As an example, filter to ‘Training Not Started Yet’.


Now you can see all the future scheduled trainings – how many seats are available, etc.

With this information, you can remind employees about the available trainings and encourage participation.


Step 4: Record Benefits and Feedback of Employees

As the training start date arrives, it is time for enrolled employees to participate in trainings. After they complete the training, they should inform you. You can update the status of the enrollment to ‘Completed’ in the Enrollments sheet.


In addition to Status, record the Rating and Benefits.

Rating is the numeric assessment by the employee of the training they participated in. You can do a rating system according to your needs as long as it is numeric. For example, the maximum rating can be 5, or 10. In the above screenshot, we used 5.0 as the maximum rating.

Benefit is the financial benefit you perceive the company will get due to the training received. If the employee’s performance will improve due to the training, enter an estimate of the benefit. This will help us assess the return on investment of the training program.

For example, an employee attends a time management or productivity training that costs $100. With the improved productivity the employee can now take on more tasks and that results in estimated $200 per year of increased company revenue or reduced expenses. Now, the benefit is $200. This estimation method can vary by company and as an organization you can agree on a specific estimation method.  Ultimately there needs to be a quantified benefit of each training received.

Note that the same training can have different benefits on different employees, because not all employees have the same skill level to begin with. This is why we enter the Benefits in the Enrollments table and not in the Trainings table.

Step 5: Measure the Training program for its effectiveness & efficiency


Having entering the data in previous steps, we are ready to benefit from all the automated calculations that the template performs.

Dashboard sheet has 4 pages.

  1. Training Dashboard (KPIs and Metrics)
  2. Training View
  3. Employee view
  4. Monthly Trends

 

Training Dashboard

Training Dashboard displays all the relevant KPIs and metrics to measure your Training or Learning & Development program.

 

The following are the KPIs and metrics.

KPIs

  1. Enrollment %
  2. Training Completion %
  3. Hours Trained per employee
  4. Avg. Enrollment per Training
  5. Cost per employee
  6. Cost per enrollment
  7. Avg. Rating
  8. Return on Investment

Metrics

  1. # of Active Employees
  2. Enrollments
  3. Enrolled Employees
  4. # Completed Trainings
  5. Cost
  6. # of Hours Trained
  7. Benefits
  8. # of Trainings
  9. Hours per Training (not shown on first page of Dashboard, but available in Training view page)


For more on definitions of these KPIs and Metrics, please see Definitions of Training Metrics


The dashboard is completely dynamic.

You can change the reporting dates. You can also filter by Training attributes as well as Employee attributes.


Important Note about the Slicers

The Employee Attributes slicers (Department, Location, Job Level, Manager, Job Title) on the left are integrated with the Employees sheet. Any filtering done on the Dashboard will be reflected in the table in Employees sheet. Any filtering done on the Employees sheet will be reflected in the Dashboard results.  

The Training Attributes slicers (Category, SubCategory, Source, Mode, Training Name, Trainer) on the right are integrated with the Trainings sheet. Any filtering done on the Dashboard will be reflected in the table in Trainings sheet. Any filtering done on the Trainings sheet will be reflected in the Dashboard results.   

To clear any selections in slicers,


To clear any selections in filters in tables,



Training view report


The second page of the dashboard allows us to evaluate training program along training related attributes.


This is also a dynamic report.

You can view all the metrics by Training Category, Subcategory, Training Name, Trainer, Mode and Source.

 

You can also choose which metric to sort the table by.


This allows us to analyze the data by various ways. I will show a few examples of how dynamic this report is.

To view the most enrolled training Categories,

 

Management category has 56 enrollments followed by Visualization with 51 enrollments.

To see the best rated trainers,



To view and compare the ROI of internal vs external trainings



You can also view ROI for each training,



To view the lowest completion % trainings


 

Employee View report


In the third page, like the training view, we can view metrics by employee attributes.

Some examples below.

To view Departments with the highest enrollment %


 

To view Locations with the highest investment per employee


To view Managers with the lowest Completion Percentage


Monthly Trends

In the final 4th page, we can view the monthly trends of all Training KPIs and metrics.