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:

In the Allow dropdown, choose List

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

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:

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:

Suzanne