Features

I’ve literally been buried in Excel trying to create tracking metrics for a training program. The problem is that there are too many variables to create a pivot table or other automated function to provide meaningful information. The key is to show how much training has been completed and not completed by month so the bigwigs know when we have to pull their employees for training and for how long.

Shown is an example, but the real thing is more complex as there are 10 classes, 11 departments, and 300+ names. I’ve searched the Internet for Excel ideas and tutorials, but most are focused on financials or creating forms. Nothing like this. We do not have a training program in place that would make it worth it to invest in software or create an Access database to give us what we need.

<edit>Finally making headway, but it is a long, slow method. It will be easy to maintain once I get all the formulas set up.</edit> A fresh night of sleep provides a new perspective and a decent solution to the Excel blackhole. Not the dream solution, but good enough to make bigwiggette a happy gal.

This is one way to get creative with Excel for non-monetary needs. The basic steps:

1. pivot tables based on original data (one for each class with all the departments).
2. create worksheet that connects to pivots so chart is automatically updated when pivots are refreshed. It calculates hours.
3. Put in a purty graphic chart.

The pivot tables (step 1) don’t organize the data in the way that we needed it, hence the reason for the worksheet (step 2). Plus, I added formulas to calculate the hours for the department per month. At the bottom is the total hours added from all the classes combined without the specific class information (just to show you what we’re looking at).

This tells the director the total number of hours that her people are in training by month. Looking at the graph (step 3), shows that most of the training is done in April with the Test team spending a total of 15 hours in April.

The Step 1 usage of Apr-Y, May-Y, and Jun-Y indicates the student takes the class in month and Y indicates the student has completed the class. The reason for combining the month and whether or not the person has attended is to avoid another column — another variable to the mess.

We had to track students required to take the class, but haven’t attended. This is done by using Apr-N, May-N, and Jun-N. Obviously, the ‘N’ represents ‘No.’ Blanks mean the student is not required to take the class and should not contribute to the numbers.

This is a simplified version of the spreadsheet. This concludes our Excel as a training tracking spreadsheet tutorial. Questions?