Software training expert Karen Roem looks at how to restrict cell entries in a Microsoft Excel list .
Tame your computer: England, United Kingdom, UK, Great Britain, or GB?
Excel has all the right tools you need to transform the information in your worksheets into powerful charts, graphs, and reports without having to know how to write complicated code. But what if the data you want to analyse isn't consistent? For example, if data entry is a team effort you are facing the 'what do we call it?' problem, such as do we enter England, United Kingdom, UK, Great Britain, or GB?
Well, Excel lets you restrict the entries that are allowed in a particular range of cells. This is accomplished by specifying a predefined drop-down list an entry must match in order to be considered valid. This way you can build a solid, consistent database that's easy to use and prevents incorrect entries. (After all, we're only human!).
1. Type the list of valid entries down a single column or across a single row in an out-of-the-way range of cells in your worksheet. Do not include blank cells in the list.
2. Select the cells in which the restriction is to apply. This can be either a single cell or a larger range of cells, or even an entire column.
3. Choose the Data, Validation command.
4. On the Settings panel of the Data Validation dialogue box, which is subsequently displayed, expand the Allow box, and click the List option.
5. Click in the Source box. Then, in the worksheet, select the range in which the list of valid entries appears. (The selected entries are automatically entered into the Source box.)
6. Optionally, enter information to display a message that prompts for entry into the selected cell (Input Message tab) or displays an error message when incorrect data is entered (Error Alert tab).
7. Click the OK button.
When you select a cell from the area you selected in step 2, a drop-down arrow will appear. If you try to manually enter anything other than these values, a stop message appears and will not allow the cell to keep the invalid entry. The only options will be to Retry or Cancel, unless you specified in step 6 to have a warning or information style error alert. (A warning box will discourage the entry of the invalid data, whereas an information box will announce the category you typed is not in the list.)
If the list of valid entries is short, you can type them directly in the Source box on the Data Validation dialogue box (separated by commas), instead of typing the entries on a worksheet. For example, you could type Miss, Mrs, Ms, Mr, Dr, Prof in the Source box instead of entering the six short entries on the worksheet, as described in step 1.
Now that you can rely on your data to be consistent, you can take full advantage of Excel's powerful tools to retrieve, filter, analyse and manipulate your data.
Unless stated otherwise, these tips were written for Microsoft Office 2000. Most of what is covered however will also apply to earlier and later versions.
10 February 2005
Karen Roem offers software training and support through her company Roem Ltd.
Contact her by email Karen@roem.co.uk or visit her website at www.roem.co.uk.