Exploring Office 2007: Error-Free Worksheets in Excel 2007 Avoiding the Four Types of Excel Spreadsheet Errors Helen Bradley
In business, spreadsheets are most often used to make financial calculations. Financial worksheets that contain errors could cause financial embarrassment to the business, but unfortunately spreadsheets are susceptible to errors simply by their inherent design — information is hidden in formulas where it cannot easily be seen or checked for accuracy.
While a mistake in a Word document may amount to no more than an embarrassing typo, a mistake in an Excel worksheet could result in a financial loss to the business. Because of this, it's critical to understand the problems that might occur in your Excel worksheets and to take precautions to ensure that your worksheets are as error free as possible.
»
Types of Errors
There are four types of errors that you may encounter in your Excel worksheets. The first of these are syntax errors where you use incorrect syntax for an Excel formula.
For example, instead of using the calculation XIRR to calculate the compound annual growth rate of an investment you attempt to type CAGR as the formula name. Luckily most Excel syntax errors will be picked up by Excel and instead of performing the calculation, it will display #NAME? in the cell indicating it doesn't recognize what you're trying to enter. Of all the errors that you make, syntax errors are generally the easiest to locate if only because Excel will refuse to proceed further when you create them.
A logic error in a worksheet produces a result but not the result that you would expect to receive. The error is generally caused by someone writing an incorrect formula but one that is still valid. Excel calculates the result and displays it, but the result isn't the intended one.
An example of this is in a financial calculation in which you are calculating the periodic payments on a loan where interest and principle are calculated monthly. In this situation the interest rate should be divided by 12 to reduce it to a periodic rate, but if you don't do this, instead of 0.5% you'll be using 6% as the interest rate.
The calculation will still be made but the result won't be what you expect or seek. Logic errors can be difficult to recognize and locate, particularly when they do not result in an obviously incorrect result. The closer the formula's result to what you expect it to be, the less likely it will be that you are aware that the formula is incorrectly written for your purposes.
The third kind of error is particularly pervasive and often almost impossible to locate. Runtime errors occur only in certain circumstances where a series of situations are met.
For example, a runtime error will occur when a cell is divided by zero. This may occur only once in every few hundred (or thousand) calculations when a specific set of circumstances conspire to return a value of zero, which is then used as the divisor in a formula. Notwithstanding that this only happens very occasionally, runtime errors typically cause the program to fail.
The final type of error is user error. This can occur, for example, when a user, whether accidentally or maliciously makes a change to a worksheet which makes it produce incorrect results. This can be done by typing over a formula in a worksheet with a fixed value with the result that the formula no longer calculates and the worksheet produces incorrect results either now or when used again in the future.
A user error may also occur when a user enters an incorrect value into a cell — even though the worksheet will still calculate a result, the incorrect value ensures that the result itself is not correct. Excel provides some handy tools you can use to help prevent user errors from occurring.
»
Accept the Problem
All that said, the primary tool that you have for avoiding errors in your Excel worksheets exists between your ears. It is the knowledge and understanding that Excel worksheets are prone to error that will help you create more accurate worksheets.
It's important to develop a healthy distrust of worksheets to the extent that you check things carefully and design worksheets so that they can be easily understood and so that problems can be easily identified and fixed.
If you are interested in learning more about spreadsheet errors that have been encountered in the past and in reading horror stories that surround this topic, visit the website of Ray Panko from the University of Hawaii. Panko has been researching spreadsheet errors for over ten years and he has some very interesting research that reinforces the need to be particularly careful when designing spreadsheets and relying on the data that they produce.
»
Use Data Areas
One method of designing better worksheets is to extract variable values and constants from the formulas and place them in a separate area on the worksheet. You then refer to these cells in your formulas.
For example, if you have an interest rate of 6%, instead of burying it inside a formula as a hardwired figure, place the 6% in a separate data area and label the cell Interest Rate. Then, whenever you use the interest rate in a formula, do so using a reference to the cell containing that value.
Doing this not only makes it easy to locate cells that rely on this value by clicking the cell and choosing the Trace Dependents option, it also makes it easy to change the value of the interest rate if, for example, the interest rate that you are paying changes. Instead of having to locate every formula in the worksheet that uses the 6% value and change them, all you need do is to change one cell.
»
Protect Your Formulas
Protecting your formulas and values is a way to avoid some user errors. Once your worksheet has been created and is ready for use, select and unlock each cell that contains data that the user should be able to change — by default all cells are locked when a worksheet is protected so you must unlock those that a user may alter.
To do this, select the cells that can be altered and choose Format from the Home tab in Excel 2007 and next choose Lock Cell. It's not easy to see, but the option is enabled by default and when you select it, you're disabling it. For earlier versions of Excel, select the cells, choose Format, Cells, the Protection tab, and then disable the Locked checkbox.
Once this is done, protect the worksheet by selecting the Home tab and choose Format, Protect sheet in Excel 2007, or Tools, Protection, Protect Sheet in earlier versions. From the Protect Sheet dialog type a password if desired (you don't have to use one), set any of the settings you particularly want to enable or disable and click OK.
Once this is done, if a user attempts to make a change to a cell which is locked and which contains, for example, a formula, they will be prevented from doing so. Use this feature to protect formulas and constants that should not be able to be altered by users.
