Spreadsheets are extremely powerful tools, especially when it comes to collecting information in an organized manner and if used properly, analyzing that information. What makes spreadsheets even more enticing is the “free” label that comes alongside them. Operating systems today come with spreadsheet software installed - whether you’re working on a Windows computer and use Microsoft Excel, or a Mac and use Numbers, the result is the same. The trouble typically comes when working to set up a spreadsheet for the first time and working to anticipate the types of challenges, or roadblocks that may come down the road, specifically when thinking about growth and scalability. Sure, your current solution works for 10 employees, but what about an organization that grows to over 200 employees, not to mention into the thousands. While your goal may not necessarily be adding employees to your organization, continued training and education is one of the latest workforce generation - the millennials - top consideration factors when choosing a new job and staying put. Adding to the frustration, you may be in an industry that requires that tracking of certifications, licenses, or anything else that may be regulated so getting this information up-to-date and into a digestible and understandable format is of high priority. We’re talking lots of man hours to ensure you’re not only recording the correct information, but that it is in a format that is agreeable for quick consumption.
That’s where we step in. From working with many organizations that have used excel or some sort of spreadsheet software to track employee training or certifications, we have the inside scoop into questions you should be asking yourself before you set out to create your first spreadsheet.
What are you planning to track?
This definitely seems like a no-brainer, obviously knowing what you’re going to track is important to get your spreadsheet in the right working order. But to take this a little further, are you tracking…
All of the above?
Understanding what you are tracking will help steer us into the next decision or question that needs to be answered.
How will the information be organized?
It’s pretty standard to see information gathered by employee or person - what certification did they take? When did they take it? When will it expire… does it even expire? From there it is important to narrow down the information that is present in the spreadsheet. Let’s say you are tracking multiple certifications that most employees would need to complete. It probably makes sense to make a tab within each spreadsheet file for each certification. This allows for separate information to be gathered by employee with start dates, end dates, expiration dates, completion grades or whatever metrics are the most important for you. As you continue adding tabs to the spreadsheet, this will account for each of the individual certifications, trainings, or licensures that you are working to keep track of.
In another example, each employee could serve as a tab that would eventually turn into an entire profile. While this is probably not the most ideal situation in most cases as it is not easily scalable - for small organizations this could allow one file to contain all employee’s information without working to find separate files.
To continue, we have also seen spreadsheets separated by department or job position, which in large organizations proves to be more helpful because there are so many differences that span positions or departments.
In one final example, we have seen spreadsheets set up by the path that employees/people need to take to earn a certification. In this example, the information that is required to obtain certain levels of knowledge or learning that is required for certifications are separated by tabs to ensure each employee goes through the necessary steps to achieve the ultimate goal.
Keeping these formatting suggestions in mind, once you have made your decision - the next question to ask is…
What does your industry/organization regulate or require from the department/position/role/etc.?
This may be the best way to answer the above questions, although it becomes a little bit more difficult when you are tracking an internal training program to determine what is and is not important to keep track of. In that case, we err on the side of the “more is best” philosophy, just keep track of every thing you can think of. In the case of regulated industries, however, it becomes clear, quickly, what is important to keep track of, from expiration dates, to continuing education credits/hours, etc.
This regulations can differ depending on location, department, industry, state, region, etc. In these cases, organizations typically find that keeping records separate by location may be better than keeping everything within a headquarters location.
Who has access to the sheet?
Google Sheets are always a good tool if it is necessary to ensure that a group of people has access to the same information. If you use a private server, or some file-sharing software - understanding what information is being tracked and who can view it in terms of confidentiality is important. Answering this question before setting up your spreadsheet will help when and if changes need to be made in who has access to the information that the sheet holds.
Who is in charge of updating the sheet?
Believe it or not, this is an important question to have answered before getting started. While more than one person can have access to the sheet for informational purposes, who exactly is in charge of updating the sheet? This can definitely be the case of too many cooks in the kitchen it multiple people are able to make changes to any part of the document. While it could make sense for one person to be in charge of the whole sheet, in other cases it may be that there is one person per department, or that each individual is in charge of their own records with approval from someone in HR or Training. Understanding who is going to be held accountable for the information that is present and will be answering to regulatory agencies, or those in authority will help clear up who is ultimately supposed to be trusted with the information.
What are the main goals in tracking this information?
Whether you are working to organize information, have a better understanding at the level of training that each employee possesses, or working to meet regulatory agency expectations - understanding why you need this information will help to guide how the information should be structured and what needs to take precedence in collecting.
These are the top 6 questions we have heard time and time again as important to consider before setting up a spreadsheet and can derail any progress made in terms of keeping organization throughout growth, not to mention scalability. What are some of the questions that you ask yourself when you are planning out your spreadsheets - not only so you are not reinventing the wheel, but also so these spreadsheets can be a long-term solution.