Restrict data entry in Excel with lists

I recently shared how you can restrict data input in Excel to certain number ranges, such as whole number percentages. But what if you're dealing with a strict set of values, like product names, and you don't want users to be able to create any new variations when entering data? That's easy to do too.

First, make a column that lists the values you want to choose from - each in a separate cell. I suggest using a different sheet of the same workbook, but you can put the values anywhere you want.

Now select the cells you want to restrict, go to the Data tab, and click Data Validation:

Data Validation

In the Allow dropdown, choose List

Allow List

Now click the cell range icon to the right of the Source input field:

Source cell range

This opens the cell range entry dialog box. Navigate to the cells where you entered your list data and select them. Excel converts your selection to the proper range formula:

Select your cell range for list values

Now click the range icon again to return to the Data Validation dialog box. Click OK and your cells will be constrained to the list, like so:

List selection dropdown