We’ve all had to deal with errors in Excel spreadsheets.
Dealing with errors and troubleshooting can have a very significant effect on productivity – tracking down an error in a complex spreadsheet can take much longer than it did to create the spreadsheet in the first place. Reducing the likelihood of errors is therefore at least as much an efficiency issue as an error-prevention one.
This post will go through several techniques to employ to reduce errors in your sheets.
Good design is important for both the avoiding of significant errors as well as ensuring that your spreadsheets run as efficiently as possible. Poor design can cost a lot of time and money in terms of investigating and troubleshooting, without even considering the direct effects and potential damage to your business and personal credibility. Time invested in planning will generally save a great deal more over the development process.
It’s a good idea to employ a set of standards for consistency, that way when the spreadsheet is worked on by other individuals within your organisation it’s less likely that errors will occur. The areas where standards are usually set are:
- Documentation – a record of the workbook’s key features
- Formatting – cell formats that are applied consistently to highlight cells with different functions in a spreadsheet
- Structure – how different elements of a spreadsheet should be set up and how formulae should be structured
- Functions – to use / avoid
- Features – to use / avoid
- Competencies to possess
- Procedures – sign-off requirements, peer review etc.
2. Controls and checks
If you build in appropriate checks as part of each stage when developing your spreadsheet and continually refer to those checks, you should be able to isolate problems to manageable chunks of your workbook, making them much easier to track down and correct which will save you trawling through a whole workbook to find the errors.
Controls – You can control which cells users can enter information into and the type and range of data that they can enter. Cell locking and worksheet and workbook protection can help prevent users from inadvertently changing or overwriting cells, particularly those containing formulae.
Excel’s Data Validation feature allows you to specify the type of data that a user can type into a cell and set detailed criteria. For example, you can require that a cell entry is a number between two values:
(Using data validation to limit values entered)
Checks – Excel includes a range of functions that can determine the type of entry that a cell contains, such as whether a user has entered a number or a different type of data in a cell. These information functions return TRUE if the cell contents are of a particular type and FALSE if not:
The most common check uses the IF function to compare two values and return one result if the comparison evaluates as TRUE and an alternative result if the comparison is FALSE.
At its simplest, IF() can be used to make sure that two numbers in your workbook that should be identical are indeed the same:
- =IF(‘Sales by product’!A34=’Sales by country’!A34,”OK”,”Error”)
Another type of useful check is to set exception limits where possible so that extreme values are highlighted – these can then be easily identified and reviewed to check that there has been no error. Conditional formatting can help highlight checks that indicate an error or the possibility of one.
Whatever checks you employ it’s important to ensure they remain up to date. Some months down the line they may not be appropriate for the task at hand.
3. Consistent formulae
Each different formula you enter in an Excel spreadsheet needs to be checked to make sure it is correct. Ensuring that all the formulae in a block of cells are the same makes it easier to spot an inconsistent formula – perhaps where someone might have overwritten the contents of a formula cell.
Excel will automatically mark an inconsistent formula as a potential error as long as it’s not at either end of the row of formulae. However Excel’s built-in error-checking is far from perfect: whilst it should mark a formula that is not consistent with those on either side of it, a formula at either end of a block will not be marked as inconsistent, even if it is different than the rest of the formulae on the left or right.
4. Linking data to avoid duplication
As soon as you create more than one version of a value or set of values, you run the risk of changing one set without changing the other. Avoiding multiple copies of what should be the same data is important for individual values or cells, and for entire tables of data.
For example, if your spreadsheet uses the same bank interest rate for many calculations, the value of that interest rate should be entered only once in your spreadsheet, with all formulae that use the value including a reference to that one cell. Where your spreadsheet is based on one or more tables of data held in another system, it is generally better to link to that data rather than copy it or creating a static import.
By locking cells and protecting sheets and the structure of workbooks, it is possible to make it less likely that other users will inadvertently overwrite formula cells and thereby compromise the integrity and accuracy of the entire workbook.
To lock a cell just right click on the cell, select Format Cell and then in the Protection tab check the lock cell button. You might be surprised to find that all the cells, in all the sheets in your workbook, start off as locked.
To protect a sheet, go to the Review ribbon tab, Changes group, Protect Sheet option:
You can choose from a range of actions that you want to allow the user to continue to be able to use, or to prohibit, including whether the user can even select locked or unlocked cells. If you turn on sheet protection without setting a password, then the user can simply unprotect the sheet so it’s always a good idea to do this.
Alan Gurney is AAT Comment’s Excel tips writer.