Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
»
Ensure Valid Data
The Excel Data Validation tool is another way to prevent a user from entering invalid data. This lets you specify what data can and cannot be entered into a cell so you can trap user errors before they occur.
To set up a data validation rule for an Excel range, select the cells that should contain a certain type of data or data within a specific range. In Excel 2007 choose the Data tab and then Data Validation, and in earlier versions, choose Data, Validation.
When the Data Validation dialog appears, click the Settings tab, and as an example, to limit data to a range such as between 18 and 70, from the Allow dropdown list select Whole Number, from the Data list select Between, and then type 18 as the Minimum value and 70 as the Maximum. Click the Input Message tab and configure a message that will appear as a tool tip for your user and that describes the data they should enter into the cell.
Click the Error Alert tab and select a type of error that will be displayed to the user if incorrect data is entered. The Style you use — whether it be Stop, Warning, or Information — will determine the result for the user.
To prevent the user from entering data that isn’t within the specified range use the Stop style and the user will be presented with the options: Retry or Cancel and they will be unable to enter invalid data into the cells. To warn the user that they are about to enter incorrect data but allow them to still do this, use the Warning or Information styles as these warn but still allow invalid data to be entered. Click OK and you are done.
Configuring data validation rules for cells where it is possible to describe the type of data that is allowed to be entered lets you test the data entered by the user and, if desired, prevent invalid entries.
»
Excel Error Checking
When you are creating a complex worksheet you can have Excel monitor the worksheet to ensure that the formulae, for example, look correct. Excel has a series of built-in error checking routines that you can enable or disable as desired. To see what is available in Excel 2007, click the Microsoft Office button and choose Excel Options > Formulas, and in earlier versions choose Tools > Options >Error Checking tab.
In the Error Checking Rules area you can select one or more of nine individual error-checking options that Excel will check for you. If it locates a problem Excel displays a small green indicator in the top left of the cell indicating this cell has a problem worth checking.
For example, if you select the Formulas inconsistent with other formulas in the region option it will trap a cell in a series of cells, all of which include the same formulas if that cell is different to the rest.
So where you have created a series of SUM formulas by hand and misjudged one of the sets of values you are summing, this error will be identified. Of course, when you create a series of SUM formulas across a row or down a column, they should be created by copying where possible to minimize the chance of errors.
»
Find Problem Cells
Excel provides some other features that help you locate errors. So, for example, if you suspect that a user has hardwired a value into a worksheet and replaced a formula with a number, you can locate the problem by selecting an area that you think should contain formulas and click the Home tab, choose Find & Select > Go To Special, and click Formulas (in earlier versions, choose Edit > Go To > Special > Formulas). Excel highlights all the cells containing formulas so you can check to see if any that should be highlighted are not — these likely contain hardwired values.
Excel also includes some auditing tools that you can use to check cells. In Excel 2007 click the Formulas tab and use the Trace Precedents and Trace Dependants options to view cells that either provide data to the current cell or cells that get their data from the current cell.
Click once on either button to show the first level Precedents or Dependants as a series of blue arrows. Click again to show another level of precedents/dependants. When you are done, click the Remove Arrows option to remove the arrows from the worksheet. In earlier versions of Excel these tools are located on the Formula Auditing toolbar — choose View, Toolbars, Formula Auditing to display it.
Another handy option on Excel 2007’s Formulas tab is the Show Formulas button. Click it once to show formulas on the worksheet and click it again to hide the formulas.
Displaying and hiding formulas can help you locate where a formula is missing or a place where a formula does not follow the pattern of other formulas around it. In earlier versions of Excel this feature is accessed using Tools, Options, View tab and select or deselect the Formulas checkbox.
»
Test Your Work
As you develop your worksheets, check the values that the worksheet returns against what you expect the result to be. In some circumstances you will be able to use data that you have created previously to compare with the Excel provided results.
In other cases you may have to perform the same calculation manually or using another program to check that the Excel value is correct. It is vital that you check your worksheet formula results thoroughly before distributing the worksheet.
Whether you are designing worksheets for others to use or worksheets for your own use, ensuring the accuracy of the data in it is critical. However attractively formatted your worksheet is, it’s of little value if the results it returns are incorrect.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.
This article was first published on WinPlanet.com.
