SPREADSHEETS - 09.07.2010

Data validation

The data validation option

Beyond protection. You are probably familiar with cell protection to prevent users from overwriting key cells in a spreadsheet. However, Excel also offers a data validation (DV) facility to allow you to place restrictions on what actual data can or should be entered in a particular cell. For example, you can use DV to restrict: (1) data to pre-defined items in a drop down list; (2) numbers outside a specified range; (3) dates outside a certain time frame; and (4) the number of text characters.

Pop-up messages. You can configure DV to stop users from entering data that is not valid. However, it may be better to allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell and instructions to help users correct any errors.

Tip. In your Excel workbook, select the cell(s) you want to validate. On the Datatab, click Validation(or Data Validationin Data Toolsin Excel 2007). You will then have a window where you can add your validation criteria and input messages.

Use formulas or values in other cells

You can use DV to calculate the maximum allowed value in a cell, based on a value elsewhere in the workbook, and then create pop-up messages to help the user.

For example. You’ve given the sales team a spreadsheet template to provide details of their sales budget. The commission policy is 6% of sales so the budgeted commission figure can’t exceed 6% of the projected sales figure. To validate the commissions input cell (D8 in our example): Select cell D8/Data Validation/Whole number. Enter the minimum value as zero with the maximum value as =D6*0.06 where D6 is the projected sales figure. (See The next step.)

Alert or error message. There are three types of icon that you can choose from to appear as an error/alert message, i.e. a Stop sign, a Warning triangle or an Information symbol (see The next step).

Other practical points

Trap. After you share your workbook, you won’t be able to change the validation settings.

Tip. If you plan to share your workbook, only do so after you have finished specifying data validation and protection settings. Test the data validation to make sure that it’s working correctly. Try entering both valid and invalid data to make sure that your settings are working as you intended and your messages are appearing when you expect.

Trap. Data validation is designed to show messages and prevent invalid entries only when users type directly in a cell. When data is copied or filled, the messages do not appear.

Tip. To prevent users from copying and filling data by dragging and dropping cells, uncheck the box titled enable fill handle and cell drag and drop, which can be found at File or MS Office button/Excel Options/Advanced.

For a spreadsheet showing an example of data validation in action, visit http://financialcontroller.indicator.co.uk(FC 02.10.03).