Create a list box in Excel to ensure data entry accuracy

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:

  1. Select all the cells in the State column.
  2. Go to Data | Validation.
  3. On the Settings tab, select List from the Allow drop-down list.
  4. In the Source text box, enter the allowable entries separated by commas as follows: NJ, NY, PA, DE, etc.
  5. Select the In-cell Dropdown check box.
  6. 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.