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 organisations of all sizes, from many different sectors.  An important part of the journey when adopting a solution to manage training and competency data is migrating data from various existing formats, which usually range from paper to ERP systems to Microsoft Excel.

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?

Many organisations will naturally choose spreadsheets for the following reasons:

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 dedicate tool for the task at hand?  Also, this saves having to beg your superior for a purchase requisition, that then has to be justified through a time-consuming purchase system.

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 organisations also need to capture:

• Skill descriptions/objectives
• Target levels and definitions/meanings
• Assessment specifics/expectations
• Certificates, documentation or evidence
• Training provider details (person/organisation)
• 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.

Single-editor limitations

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. 

Some organisations get around this by nominating a single data administrator.  Often however, the information given to them is peacemeal.  Also, the file cannot be viewed (e.g. for reporting) when this person is entering data.

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?

Also pivot tables and macros require the desktop computer's processing power.  This can be incredibly slow to generate the end result.

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.

Lastly, its very easy to steal data from files.  Excel even has its own "Share by Email" button for fast sharing.

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 therefore promotes the ignorance of standardisation.

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 other may adopt every shade in-between, with extra colours for dates.  This can be incredibly confusing for other people who are not familiar with those standards, and make aggregated reporting nearly impossible, e.g. how does Grade D compare with Level 40% or "U"?

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 organisation, IT are rarely kept informed of these changes, meaning the list of authors can become out of date, creating risk.  Also, its just another job for the poor folks in IT to do!

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 intutive and presents plenty of new challenges related to the above points.

Moving to a database-based solution

If your organisation is ready to move from spreadsheets to a database-driven approach that overcomes the above challenges, please get in touch.  We're ready to help you!