A great way to ensure accuracy when users are entering data into a Microsoft Excel spreadsheet is to have them choose options from a list. Learn how to create a list for controlling data entry using a State field as an example.
Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.
A list box makes it easier for Microsoft Excel users to avoid errors. For example, a State field requires users to know the two-letter abbreviation for each state. To ensure accuracy, let users select from a list.
To create a list for the State field cells, follow these steps:
- Select all the cells in the State column.
- Go to Data | Validation.
- On the Settings tab, select List from the Allow drop-down list.
- In the Source text box, enter the allowable entries separated by commas as follows: NJ, NY, PA, DE, etc.
- Select the In-cell Dropdown check box.
- Click OK.
When users move to a cell in the State column, Excel displays a drop-down arrow. Clicking the arrow reveals a list of allowable states users can select for that cell. If users attempt to type an entry that's not on the list, Excel displays an error message.
