When conducting business process maturity assessments, we sometimes find spreadsheets supporting vital business functions. More often than not, these spreadsheets are unsecured, unbacked-up and unsupported by IT. 

So, when is it appropriate to use Excel and when does it present an unacceptable risk to your organisation? The easiest way to answer this question is to first explain Excel's pros and cons.

The pros of Excel

• Excel is already installed on most computers.
• Spreadsheets are easily distributed to people who can then work on them 'off the network'.
• Spreadsheets can be used to create applications without having to ask IT - most computer-literate people can create simple spreadsheets; power-users can create sophisticated applications.
• Spreadsheets are good for storing and manipulating lists of unstructured data, for performing ad-hoc financial and statistical calculations, and for creating attractive reports and charts.

The cons of Excel

• When individuals create their own spreadsheets they create pockets of data that might be useful to the rest of the organisation but that will probably never be shared and will be lost when that person leaves.
• It is easy to get formulas wrong in a spreadsheet.  The resulting data values can be incorrect without anyone knowing. This is a particularly prevalent issue in large, complex spreadsheets.
• If multiple people use the same spreadsheet, it is possible for one person to save over another person's changes (without either person knowing this has happened).
• In many spreadsheets it is impossible to prevent accidental corruption, e.g. someone deleting a column or accidentally sorting only the subset of a larger range.
• A spreadsheet contains no change history - you can't see what data or formulas have been changed, when and by whom.
• Cell values are usually not validated or enforceable, leading to data becoming 'dirtier' over time.
• Excel spreadsheets and ranges within them can be password protected. However, a lost password can result in the spreadsheet being locked forever.
• Because Excel applications are usually not built by the IT department (applying sound practices such as documenting the design and testing the end-product), knowledge on how a particular spreadsheet works, and how to use it, becomes diluted over time. This situation is exacerbated when different people modify a spreadsheet over time - the spreadsheet becomes more complex and no one person knows how or if it works.

When is it appropriate to use Excel?

Use a spreadsheet for ad-hoc modelling, for capturing data in a one-off situation, or as a temporary solution while a permanent solution is being worked on. If the data becomes lost or corrupted, it might be inconvenient but it should not have a significant impact on the business.

Excel spreadsheets are easy to create but the integrity of spreadsheet data, especially in larger spreadsheets, can be questionable. As spreadsheets are modified by different people over time, and as people join and leave an organisation, knowledge on how the spreadsheet is supposed to work becomes diluted. The worst case scenario is that the data is no longer correct but no-one notices.

You should therefore assess the risks of using a spreadsheet as part of a vital business function. What would be the impact if the data integrity was compromised? What would be the business impact if the data or formulas were incorrect, but the spreadsheet continued to form the basis of business decisions or continuted to be communicated as fact to its intended audience?  If loss or corruption of the data is cause for concern, the spreadsheet should probably be replaced.

What to do

If you believe there is an unacceptable risk in using a particular spreadsheet in a critical business process:

• Engage IT early to start understanding the alternatives.
• Ensure the spreadsheet is being regularly and successfully backed-up.
• Document how the spreadsheet is supposed to work and how it should be used.
• Appoint someone to do a thorough audit of the spreadsheet to verify it works as intended and that the data is correct.
• Replace the spreadsheet with commercial off-the-shelf software or a customised solution.