Error-Free Worksheets in Excel 2007: Page 2

(Page 2 of 2)

» 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.

The Data Validation dialog can be used to describe data which can be entered into a cell or range

» 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.

Excel can alert you to a range of potential errors in your worksheet if you configure it to do so

» 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.

Using the Trace Dependants (shown here) and Trace Precedents tools you can view where data comes from and goes to on your worksheets

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.