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 about. This article sheds some light on data validation.
Data validation is simply the process that permits only certain content to be entered in a cell (ie, "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 the Data Validation tool, 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, a dialog box pops up as shown in the screenshot "Data Validation Dialog Box".
Data Validation dialog box
In 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 choices shown in the screenshot "Allow Dropdown Box".
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.
Allow dropdown box
Once a selection has been made (eg, I will use Whole Number), the dialog box changes appearance, as shown in the screenshot "Data Validation: Whole Number Dialog Box".
Data Validation: Whole number dialog box
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, as shown in the screenshot "Data Dropdown Box Options".
Data dropdown box options
Depending upon the choice made, the box will prompt for values (eg, Minimum: and Maximum: in the screenshot "Data Validation: Whole Number Dialog Box") 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.
Data Validation dialog box: Input message
With the Show input message when cell is selected box checked (see the screenshot "Data Validation Dialog Box: Input Message"), 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, as shown in the screenshot "Input Restrictions Example".
Input restrictions example
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 as shown in the screenshot "Data Validation Dialog Box: Error Alert".
Data Validation dialog box: Error Alert
Three styles are available (see the screenshot "Error Alert Styles").
Error alert styles
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.
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 screenshot "Validation Criteria: List", 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.
Validation criteria: List
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. (Note: The link will automatically download the Excel file to your device.)
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) as shown in the "Circle Invalid Data" screenshot. This will circle all invalid data on the worksheet.
Circle Invalid Data
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). (See the screenshots "Select "˜Special...' on the Go To Dialog Box" and "Choose Data Validation".)
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.
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 firstname.lastname@example.org.
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.