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.

Editor's Picks

Free Newsletters, In your Inbox