Use this Excel tool to guide spreadsheet users

Sometimes when you build an Excel model, you want to “guide” what an end user may enter in a particular cell (eg, choose a day of the week, a month of the year, on/off, member/nonmember). You can achieve this in various ways. One very easy approach is to use data validation — which few modellers seem to know. This article will shed some light on data validation.

Data validation is simply the process that permits only certain content to be entered in a cell (“the contents must be validated”). By default, Excel allows you to type anything in a cell, subject to software constraints (eg, length issues, certain characters not supported). However, you may change this and force users to pick certain numbers, certain dates, items from a list, or data that meet certain criteria.

To access data validation, from any cell in Excel:

• Go to the Data tab on the Ribbon and click on the Data Validation icon in the Data Tools group.
• Excel 2007 suggests the keyboard shortcut Alt+A+V+V.
• A much simpler keyboard shortcut is the old pre-Excel 2007 one, Alt+D+L, which still works.

No matter the method selected, the following dialog box pops up:

From the screenshot above, you can see clearly that the default setting for all cells in Excel is to allow any value. This may be changed in the Allow dropdown box. It may be modified to any of the following:

Most of these criteria do exactly what they say: By choosing Decimal, the input must be a number, whereas Whole Number allows for integers only. However, making a selection from the Allow dropdown box is only the first part of the data validation process.

Once a selection has been made (eg, I will use Whole Number), the dialog box changes appearance, as shown below:

The Ignore blank check box is no longer greyed out. This allows blank cells to be “valid” regardless of the criteria selected. The remainder of the dialog box is governed by the Data dropdown box. Various selections may be made:

Depending upon the choice made, the box will prompt for values (eg, Minimum: and Maximum: in the illustration above) that can be typed in, or else the values can refer to cell references directly or indirectly via range names.

Once the choices have been made, you might wish to use the other two tabs of the Data Validation dialog box:

With the Show input message when cell is selected box checked, if the end user selects the data-validated cell, the message typed in here will appear. This can make data inputs in a model much simpler, as end users are “spoon-fed” with a pop-up box detailing what to do. This is great for documentation or trying to make users follow instructions. For example, the Input Restrictions comment appears only when the cell is selected:

The third tab selects what to do if invalid data are entered in the cell:

This alerts the end user when an invalid entry has been made (eg, typing “dog” when a number is expected) — as long as the Show error alert after invalid data is entered check box is ticked.

Three styles are available:

The three styles provide differing treatment of invalid data:

• Stop: The value will not be accepted, and the end user will be prompted to retry;
• Warning: The end user will be warned that the data are invalid but will be asked whether it is OK to continue;
• Information: The end user will be advised that the data are invalid but that the data have been accepted.

If the Show error alert after invalid data is entered check box is not ticked, no prompt will occur and invalid data will be accepted in the cell without any warning.

Other types of data validation

Whole Number, Decimal, Date, Time, and Text Length are all relatively straightforward, albeit very similar in nature. This leaves just two remaining categories to consider.

List: This functionality allows the end user to select from a list.

With List selected, the dialog box prompts for a source for the list. In the illustration, the entries have been typed in, separated by a comma. However, the data can use cell references that are in a column or a row. For most versions of Excel still in use, these references may even be on another worksheet.

For lists, I strongly recommend using the In-cell dropdown, which provides a dropdown list of valid entries once the cell has been selected.

Custom: As you become more experienced, you may find the functionality limiting. This is where the final Allow category comes in useful as you design your own data validation. Examples are included in this Excel file.

Word to the wise: Data validation will not solve all of your data-entry problems. If data have already been entered into a cell and data validation is applied retrospectively such that the contents of the cell would be deemed invalid, no warning will ensue. Similarly, if the contents of a list are altered, any cells that selected the changed value will not update automatically.

To counter these issues, invalid data may be identified on a worksheet as follows:

• On the Data tab of the Ribbon, go to the Data Tools group and click the dropdown menu next to the Data Validation icon.
• Select Circle Invalid Data (Alt+A+V+I).
  

This will circle all invalid data on the worksheet.

One other issue is locating cells that have been data-validated in the first place (ie, no longer allow “any value”). The simplest way to do this is, through the Go To dialog box (F5), click on the Special… button and then select Data Validation (either all data-validated cells or those validated similarly to the cell presently selected):

Though they don’t always solve every data-entry problem, data validation techniques can help spreadsheet modellers guide end users along the proper path to the desired results. And they also help to facilitate the use of other Excel approaches, as we’ll explore in future instalments of this column.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of An Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.