A look at the reason why spreadsheets like Microsoft Excel aren't suitable to store training and competency data, and why many organisations choose to use them anyway.
Over the last 15 years, I've had the benefit of working with
Firstly, let me confess that I really like Excel. I've been using it since version 5.0, and its functionality and intuitiveness make it one of Microsoft's finest creations (IMHO). However, this power and convenience often make it a de-facto choice for storing data that spreadsheets really aren't designed for. Over the years, I've seen Excel creations that really are the subject of nightmares. While I will refer to Excel in my examples below, this is primarily because our clients use it. The same can be said for OpenOffice, or any other alternative spreadsheet format.
Why are spreadsheets used in the first place?
Spreadsheets are easy & convenient
You don't need a degree in IT to use them. In fact, tools like Microsoft Excel will allow any types of data to put in there, in any way you choose. While this presents problems for reporting (see later), Excel can easily become a convenient data dumping ground, even for unrelated data.
Excel is also readily available on most office-based computers, so is naturally just a click away. Factor in that most staff will save to a network drive (and now the cloud) for simple sharing, and it quickly becomes a collaborative tool.
Spreadsheets are cheap
If Excel is already there on your computer, why bother spending money on a
People are too busy
There aren't enough hours in the day, and our inboxes keep filling up. Therefore, there simply isn't time to go find an alternative.
People don't know what they don't know
The biggest challenge is educating people to what's achievable/possible with new software solutions. Also, some people will naturally feel uncertain when reading about new "software platforms" or "software as a solution". Therefore, it's much easier to stick to our trusted spreadsheets.
So why aren't spreadsheets suitable for training and competency data?
Below are some reasons why spreadsheets aren't suitable for storing your training and competency data.
Spreadsheets are 2D - but you require 4D
Spreadsheets generally have rows and columns, with different worksheets that make up the overall workbook.
This is fine for matrix-style layouts, but training and development is a continuous cycle. Therefore, rows and columns lack the ability to conveniently record re-assessments through time. While it can be done, a worksheet will quickly become noisy making it hard to see the latest data only.
What is needed is a way to see only the most recent occurrence of each training/assessment entry, while retaining the rest for historical evidence:
Supporting Evidence & Detail
The matrix-style of display only captures a small amount of data. Most
- Skill descriptions/objectives
- Target levels and definitions/meanings
- Assessment specifics/expectations
- Certificates, documentation or evidence
- Training provider details (person/
- Internal/external provider/assessment details
- Training costs
- Training feedback
- Assessor comments
When reporting, data should also be displayed only for active employees (not leavers).
All of this becomes impossibly complex within a spreadsheet format.
Only a single person can edit a spreadsheet file. Once open, the file is locked by them. This means if you have multiple Team Leaders all assessing people on a busy production line, people are going to have to wait before the previous editor has finished. This presents a risk of data loss, where people either lose patience or forget what to enter.
Limited reporting capabilities
Software such as Microsoft Excel makes producing simple charts and reports extremely easy. However, anything beyond basic data lookups can be tricky. Most engineers will argue that macros can achieve most things, but these present different challenges such as security warnings. Also, if the engineer who created your hugely-complex spreadsheet leaves, who will understand how to keep the file updated?
Staff remits & data protection/privacy
Having a large spreadsheet with all employees within it can create data protection and privacy challenges. This is especially true if competence/experience is linked to pay.
Alternatively, having a separate file for each department, controlled by a Team Leader/Manager presents the challenge of complexity when compiling data for reports. One simple change to a single file can break the data links, and wreak havoc on the reports.
Isolated departments with their own standards
Having a different training spreadsheet for each department also allows departments to operate in stealth, using their own grading systems. Some departments may use levels 1-10, others 0%-100%, others A-F, others ILU.
Having different files
Bespoke formats for each author
Because spreadsheets are so flexible, there's nothing to stop local authors adopting their own formats. Some might use red/green to signify competence/non-competence, while
Access control is time-consuming
A training spreadsheet located on the hard drive of the author is only generally available to the author. What happens when multiple people require access/control?
Typically, it becomes the job for IT to place the file on a network share (or cloud), and assign access rights. Over time, as people join and leave the
Version control and corruption
It's very easy to type an invalid value in a spreadsheet cell. In a spreadsheet with 100,000 rows of data, that value may never be noticed. Most IT backups will rotate on a weekly/monthly cycle, meaning that historical invalid data will be baked into the file permanently.
Also, should a backup need to be restored, this process is a full overwrite. That means if the file is corrupted, all new data (good or bad) entered since the last valid backup data will be lost.
Lastly, should data not quite be entered correctly, reports and output data will be corrupted:
Nowhere to store supporting evidence
Spreadsheets store simple text/numeric values. There's nowhere to store certificates and evidence documents that may be essential to support the competency levels entered.
Sure, we can place those files into a directory on the computer (or network share/cloud) and create a hyperlink, but then many of the problems above also are now placed on our document store. Also, simply changing the name of the document directory, or moving it will break every hyperlink in the spreadsheet.
Cloud spreadsheet data cannot (yet) connect to cloud files
At the time of writing, Excel wouldn't connect to online files (hosted in SharePoint) for the purpose of creating formulae. The only solution we found was to use Microsoft Power BI to aggregate data, but this was far from
Moving to a database-based solution