One day your colleague might sadly report you that their work is not finished on time because of a spreadsheet error. Let's dive deep into what could it mean and how to mitigate Excel use risks.
What is a spreadsheet error?
Simply put, it is a blanket term people use to refer to the misalignment between the expected and obtained Excel outputs. Sometimes, it is also used to describe a formula (or so-called hash) errors, such as NUM! or Div/0!.
How to avoid errors and limit spreadsheet risk?
The best way is to neither blame the tool (Excel or VBA), nor to attribute it to the generic "human error", but to always look for the root cause. This article is an overview of the systemic causes of the spreadsheet risk. Think of it as a set of good practice rules guiding you on how to error-proof your workbooks during all the stages of their life.
What are the main causes of Excel errors?
This article presents four major dimensions you need to control to prevent and not propagate Excel errors and discrepancies. They relate to data you use in your spreadsheets, workbook & macros development practices, organisation of the ongoing work and compatibility matters.
1) Error-free data origination & processing in Excel
A conscious approach to data gathering, cleaning and structuring lay the fundaments for further analytical steps. Organising data sets and fixing irregularities prevents future errors related to formula use, classification of data pieces into various categories. Avoid or minimise:
Manual data input Everybody makes typos once in a while. Avoid them with drop-down lists and system-generated inputs.
Weak data cleansing practices Just trying to make data look decent is not enough. Have a list of procedures to not forget anything, like removing those extra space in cells.
Poor taxonomies and data descriptions Your colleague might not know what you had in mind using the column header "adjusted annual spending". They might not be sure which purchases you considered as major. Define the classification criteria to make them clear without the need to consult you.
Careless data formating Are these American or Canadian dollars? Is the total spending column in red because it is important or because it is incorrect? Or maybe it's too high? Be precise with formatting and use it as a tool for highlighting important information.
Lack of knowledge about your data Why 240 clients that created their accounts before 2014 did not get their earned discounts? Because their client ID had 10 digits and not 8 like now and the text function didn't get their correct IDs extracting just 8 characters instead of 10.
2) Development and architecture of error-proof spreadsheets
To use a workbook for a long time, you have to structure it in a way that will allow reasonable modifications and expansions. It requires a trade-off between complexity and flexibility of your templates & automation files.
Common complexity-related mistakes are the following:
Using too heavy files When it takes minutes to load the file or 20 seconds to filter data, you risk crashing Excel. Remove unneeded data, use CSV file, get a faster laptop or use software designed for handling larger data sets.
Lack of data volume limits If this VBA template doesn't handle large data structures, then cap it at 100 000 lines and outline it in the file manual.
Unstable workbook connections Avoid linking files when not necessary, especially when used by multiple analysts. Instead, clearly define inputs and outputs and the frequency of data exports and imports.
Unstable reference chains Lookup, inside a lookup, with additional "indirect" function... Use less complex references or VBA macros with dynamic range finding.
Overcomplicated nested formulae use Using 10 nested ifs might produce the right result now, but have you tested all the 1024 logical cases? There are more user-friendly ways to do it, such as showing the criteria in a reference table.
Most popular spreadsheet flexibility issues that cause the files to break are:
Fixed range references That moment when 90% of your vlookup column doesn't load intended outputs... Consider dynamic cell references each time you define ranges in the formula syntax.
Inappropriate formulae replication Dragging the formula changed data in the wrong way? Study the rules of formula replication and remember to form a pattern before pulling it and to use dollar signs.
Insufficient use of IFERROR function When a not-anticipate value comes up and then other functions transform it into error, breaking the data links... Use IFERROR to fix those references or at least indicate manual checks.
Misuse of the formulae Quite often, you might not find the error until you come across the specific type of data. That's often common in valuation with financial functions. When an analyst assumes a 365-day year for calculating interest when the 360-day calendar should have been used, she might obtain the correct result when the period is a multiple of a full year. Always make sure that the formulae you use are suitable for all the future cases, not just the ones you used at the beginning.
No clear top to bottom data tracing When you are not sure how to track down why that department spent 4 times more than normal, that's most likely the cause. Never leave your data inputs untraceable and keep track which data sources feed which reports.
Unstable workarounds Just remove the rows with incomplete client claims, then find and replace all ticket codes ending with 865, 799 and B23, then... What was it? If there are some unforeseeable changes to your data processing routines, log them in workbook instructions. When you are sure they are permanent, have them included in your spreadsheets. Ask Excel Experts to improve your VBA macros or old templates to reflect the changes.
Usage of templates not allowing for customisation You become aware of this when you can't generate the report because you exceeded 50 months of input someone set as the limit. Make sure your templates and VBA macros can accommodate reasonable changes. Think of the ability to define data ranges in the technical set up tables or asking the user to include them in the user forms.
Macros not working/ working incorrectly. They just throw error codes! Have them properly tested before they are realised and make sure it is done in the natural environment to cover all potential macro error risk types.
3) Managing spreadsheet collaboration risk
After establishing a solid base for your spreadsheet work with templates and procedures, establish the policies for using files. Be clear with who can access which part of your Excel-based systems and who is responsible for augmenting macros and templates. Pay special attention to:
Access control When accessing salary data requires just finding payroll folder, it's time to change policies. Create access matrices specifying reading and editing permissions
Version control Your manager won't like to see four versions of the report in the same folder. Keep just one version of the report and back it up regularly if necessary. Only create additional versions when analysing different scenarios. Make sure your colleagues work on the same version.
Tracing changes Nothing is more disturbing than not knowing if your colleague updated the data inputs. Use automated logs powered with VBA macros or mandate your peers to register every important change to the file.
Unclear cell locking policies You are in a pickle when no one knows the password to unlock crucial data range in your template. Use deliberate policies for restricting file access and keep a backup of passwords.
Folder organisation If your folders are imperfect, you are probably losing several minutes every day. And that adds up! Establish simple and meaningful directory system that will make your files easy to find. You may also have them all listed in one workbook (just like with a table of contents) and access them by clicking on the hyperlink.
4) Overcoming Excel compatibility issues
Make sure that Excel integrates well with your broader environment to avoid some errors related to language coding, default system & security settings. Make sure you remember about:
Excel version compatibility issues You won't be able to run your macros in the online version of Excel. Make sure your solutions will work fine across all the versions of Excel used by the stakeholders of the spreadsheet project.
System-level setting incompatibility When your data is set to DD/MM/YYYY format in Windows settings, that will be the default short date format in Excel. It may sometimes cause problems when you upload data to, e.g. accounting software. When there is no internal cause for error, check the system settings. Also, remember to check for it when rolling out a multinational project and to expect to have many different date and language settings.
Simultaneous file usage Sometimes you can't open the file, because your colleague didn't close it on heir computer. To prevent that, use separate files for data inputs or integrate them without the need to use the file simultaneously.
Remember that you can never prevent all the errors. It is impossible and it doesn't pay off to attempt creating systems that account for every possible risk. Rather than that, you should see it as the investment in the future, where every hour of intelligent error-proofing translates into many hours saved on repairing them.