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