Excel errors like #REF! or #NULL! can be a real pain, but they are the best errors you could have! They scream to you, “I’ve messed up, come fix me quick!” and you can take the action needed to fix them.
But what if a formula told you $1,234.50 plus 12.5% tax was $1,338.81? Would you have reason to doubt it? Would you have spotted that it was $50 adrift? Chances are, like most people, you would have accepted it as being correct and not given it a second thought.
What if these types of errors are happening throughout your spreadsheets? What will the cumulative effects be on your business?
Errors that produce plausible, but incorrect results are the hardest to spot and the most dangerous for your business. However, you can protect your business from these business wrecking errors with some simple steps.
Do You Think Your Business is Safe From These Simple Errors?
Some people think these issues won’t happen to them, or that you would have to be stupid to fall for them. This very dangerous thinking has caught out some of the biggest organizations in the world. Here are just some of the biggest cases on record;
Fidelity Magellan Fund – A missing minus sign resulted in a $2.6 billion error. An accountant working on the Fidelity Magellan Fund omitted a minus sign when they were entering a loss of $1.3 billion. This instantly turned it into a gain of $1.3 billion, resulting in a $2.6 billion reporting error.
Fannie Mae – Rushed process changes cause a $1.1 billion error. In 2003, Fannie Mae rushed in new accounting standards and processes. The impact on the company’s many spreadsheets was not considered and as a result, it generated numerous errors. Reporting errors ended up totaling $1.1 billion.
TransAlta – A cut-and-paste error led to a $24 million overspend. A “clerical error” at TransAlta (Canadian power generator) resulted in them over-buying $24 million of power transmission hedging contracts. They traced the cause back to a simple clerical cut-and-paste error.
Kodak – $9 Million severance pay error. In 2005, Kodak was forced into adjusting its financial reports for reporting a $9 million loss that never existed. The cause was too many zeros being entered for an employee’s severance package.
MI5 – Formatting error leads to the bugging of thousands of wrong numbers. A formatting error resulted in the last 3 digits of phone numbers being replaced with “000”. As a result, MI5 bugged thousands of wrong numbers in error.
All these errors have 3 important common features;
- They were all simple errors caused by humans.
- Their results could have been devastating for the businesses involved.
- They were all checked by “experts” before being released into the wild.
Reread the five cases above and think about how many of those errors have you have been guilty of in the past. My guess is you would have committed at least half of them at some point.
So, if the biggest organizations on the planet, and their experts, make and miss these kinds of errors, what is protecting your business from them?
The bad news is you can never make your spreadsheets 100% safe. Humans are brilliant at finding new and imaginative ways of breaking things. No matter how many safeguards you put in place, someone will get around them. The good news is you can take measures to reduce the risk of these errors by at least 90%.
For this article I have split the measures you can take into 2 major areas, Building Safer Spreadsheets and Building a Safer Business.
Building Safer Spreadsheets
There are a lot of measures you can build into your spreadsheet to make them safer for your business.
Depending on your business and the data you are processing, the exact methods you can use are almost limitless. The methods I have listed below are just some core methods you should use in your spreadsheets. Whether you apply just some of them, or all of them, will depend on your business’s needs.
The reason most people attempt to edit a spreadsheet is that they don’t understand what its purpose is or how it works. They often then think they know a better way. We can counter this with simple documentation, which I recommend you make a standard feature of all your spreadsheets.
Introduction Sheet – This a front or cover sheet that states the purpose of the spreadsheet descriptively as possible. This sheet should contain other useful information such as data sources used, who created the spreadsheet, a list of the spreadsheet’s stakeholders, and a list detailing each of the sheet’s purposes.
Header Notes – For each column or row header, right-click on the header and add a note describing what that row/column does. If it is a row/column you enter data in, describe what type of values are accepted (text, number, date, etc). If it is a calculation row/column, document the maths used to generate the result.
A further benefit of all these notes in your spreadsheet is that it will make your life a lot easier should you need to fix it or update it years down the road. Trust me, I know this from bitter experience – there is nothing worse than having to reverse engineer your own work before you can work on it again.
Add Data Validation
You can head off a lot of errors by validating data as it is entered and forcing users to fix their mistakes as they go. There are lots of ways of doing this, and the 5 methods I use the most are listed below;
Conditional Formatting Warnings – This can warn users they have entered an unexpected value into a cell. For example, if you are expecting a positive number to be entered into a cell, conditional formatting can turn the cell red if anything, but a positive number is entered. So, if the user enters text, a negative number, or a date, the cell will turn red to warn the user of their mistake.
Limit Users Data Entry Options – You can limit what a user can enter into a cell with just a few clicks. When you do this, you can limit a user to entering;
- Whole numbers
- Decimal numbers
For each of these data types, you can also opt to set upper and lower limits. For example, you could limit the user to entering numbers between 50 and 100, or a date between 1st Jan 2020 and 31st Dec 2022. For text entries, you can set limits on the length of the text, so it meets your minimum and maximum length needs.
If your user’s input fails to meet the criteria you set, it is rejected by Excel. You can set a customized error message, so your user gets meaningful feedback on their mistake.
One of the most common options is to use a drop-down selection, which forces the user to select a value from a given list. This is ideal if you need to commonize a field of data. For example, in a salutation field, someone could enter ‘MR’, ‘mr’, ‘Mr’, or ‘Mister’ if you allowed them to free type it. With a Drop-down we could limit the user to just using ‘Mr’, making all misters the same in your data.
Check Formulas and Checksums – You can add additional formulas to check the user’s inputs. For example, if the user enters a sales tax amount that is higher than the sales value, a checking formula could pick this up and display a warning message.
The rules that these error checking formulas check can be as simple or as complex as you need.
You could also add checksums to your columns or rows. For example, if you had 100 rows of sales data that include the net sales amount, the sales tax amounts, and the gross sales amounts. You could add up all three columns and check one total against the other. Does the total sales tax equal what the sales tax is when calculated from the total net sales figure? If it doesn’t you know there is an issue.
Limit Peoples Ability to Edit and Change Things – There are several layers of protection you can apply to a spreadsheet to stop people from making unauthorized changes.
- Cell / Sheet Protection – This will allow you to password protect cells in a worksheet. You should apply this to sensitive formulas that you want to keep unaltered.
- Workbook Protection – This will allow you to password-protect the structure of your workbook. This means users won’t be able to add or delete worksheets.
- Macro/VBA Protection – If your workbooks rely on macros or VBA coding, you will need to protect them as well. By setting the protection properties of the project, you will stop users editing or even seeing your VBA code.
Beware, if you introduce password protection, at any level, you will need somewhere to record your passwords. It is possible to hack your way past worksheet and workbook protection level passwords, but it is normally a slow brute force approach.
Reduce the Need for Edits – I’ve lost count of the number of spreadsheets I have come across, where the same variable is used throughout a spreadsheet and is entered directly into thousands of formulas. For example, look at this formula;
Its function is to calculate the sales total when a sales tax of 10% is applied to an amount in cell A1. In a typical accounting spreadsheet, you can expect to see the same 10% applied to thousands of values in thousands of formulas.
So, what happens when the sales tax rate changes? Someone will have to go through the spreadsheet and change thousands of formulas. Hopefully, they won’t miss any or introduce any other errors in the process.
To solve this, store common variables in a variables table. In this table, you then give each variable its own named range. So, in this example, the value 1.1 would be stored in a named range called “sales_tax”.
You can now change all your formulas to something like this;
Now, if the sales tax percentage changes, you only need to edit in one place – i.e. in the “sales_tax” named range. So, one quick edit now eliminates the risk associated with editing thousands of formulas.
All these methods take a bit of planning and forethought to implement, but investing in them now significantly reduces the risk of future errors in your business. Is it better to invest a bit of time now, or to waste lots of time later on fighting an avoidable problem in your data?
Building a Safer Business
The last piece of the spreadsheet safety jigsaw is your own internal business practices. If you use spreadsheets to implement or monitor your business processes, they have already become integral to your business processes and should be treated as such.
There is a golden rule to follow;
Processes to control your spreadsheets, and how they are used, are straightforward and easy to maintain. However, you will need to invest a bit of time and planning beforehand. Below are 5 simple, but powerful, processes you can implement in your business today.
Establish Spreadsheet Ownership and Stakeholders
Each spreadsheet in your business should be owned by an individual or a department, and they should be the only ones allowed to edit or update them. They should also have a list of stakeholders who would be directly affected by any changes to the spreadsheet.
Establishing these individuals or departments has some big safety benefits;
- When updating the spreadsheet, the owners of the spreadsheet will know the spreadsheet inside out and may know a better way of achieving the desired results. This saves development time and reduces the number of edits required.
- The owners of the spreadsheet can work with the stakeholders to head-off any knock-on effects that updates could create elsewhere in the business.
- It gives the owners of the spreadsheet a chance to combine several change requests into one piece of work. This will reduce the number of versions of a spreadsheet, making version management easier.
Set Up a Change Request System
A change request process does not need to be complicated or involve any additional software. A change request can be a simple Word document that lists;
- Who is requesting the change.
- What was the date of the request.
- What spreadsheet needs changing.
- What needs changing.
- What are the benefits of the change.
- What are the expected knock-on effects.
- What priority is the change.
The change request is passed to the owner of the spreadsheet for investigation before they carry out any work. The owner may then point out that there are already solutions that meet the requester’s needs. They may also point out dangers that the requester wasn’t aware of. Or that it clashes with another change request.
Once the work is completed, the owner should ask the requester to check a test version of the spreadsheet. The spreadsheet should then only be released for use after the requester has signed off the work done.
This is simply getting an independent person to sense check any new spreadsheet or major changes to an existing spreadsheet. This should ideally be someone from the owner’s team or someone familiar with the business processes involved.
Their only task is to find errors, or gaps in the logic used. They need to poke holes in the development work and to ask questions like “Why are you doing it this way over that way?”.
This simple step can stop some simple errors having a massive impact on your business.
The larger your business is, the more people will share copies of spreadsheets or create their own copies of spreadsheets. You could end up with hundreds of copies of the same spreadsheet floating about.
Some will be more complete than others. Some will reflect your up-to-date business practices, and some will be years out of date.
Spreadsheets should be stored in a central repository where they can be accessed by those people that need them. You then need to deter people from storing additional copies on their local drives.
Your spreadsheet files should also have a version number added to them e.g. Spreadsheet Tool v3.02.05.xls. A three-part version number is a standard approach for giving software version numbers and can easily be applied here.
- The first number shows how many times the spreadsheet was rebuilt from scratch. In this example, they have rebuilt it 3 times.
- The second number shows how many times updates have added or removed functionality from the spreadsheet. In the above example, it has happened 2 times.
- The last number shows the numbers of minor tweaks and bug fixes carried out. (These shouldn’t change any of the spreadsheet’s functionality other than fixing broken functionality)
If you have spreadsheets that get passed around or used by multiple people, you need to audit them regularly to find edits or anything that stops it from working as expected. Ideally, the owner should take the spreadsheet offline and check for the following things;
- Unauthorized updates.
- Broken formulas that no longer refer to the correct cells or ranges.
- Additional rows/columns users have added.
- Anything that will stop the spreadsheet from working as expected.
If an audit spots a problem early on, it will stop the problem from compounding into something larger.
It is also worth auditing and sense checking any report spreadsheets that you base big business decisions on. E.g. annual profit-and-loss reports. You should do this straight after you complete your report and just before you release it. It could save you a lot of red faces and expensive errors.
Conclusions and Takeaways
Simple errors can potentially cost your business dearly! Although you can’t completely eliminate all errors, you can greatly reduce the risk of them happening in 2 stages.
Stage 1 is making your spreadsheets safe. Protecting formulas, validating data as it is entered and providing feedback on data entered, can all be achieved with easy to master features in Excel.
Stage 2 is addressing your business processes around your spreadsheets. Controlling who can edit your spreadsheets, how they can edit them, and when they can do edit them, can significantly reduce the risk you expose yourself to.
All the concepts and ideas in this article are simple, and you can start implementing them today. Your biggest investment will be some time and forward planning. Your reward will be a business that is more robust and safer from data errors.
Don’t forget the golden rule;
Also published on