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

Suzanne

Comments

  • Anonymous
    February 06, 2012
    I just have done this . It’s Pleasure to understand your weblog. The above content article is very impressive, and I genuinely enjoyed reading your weblog and points that you simply expressed. I enjoy coming back on the typical basis, post a lot more around the subject. Thanks

  • Anonymous
    April 05, 2013
    Hello, Further to the above description I am looking for a further restriction by which user should not be allowed to enter any value (even if its present in allowed list) manually in the cell. He must only use the drop down menu to select any value? The reason being I have some event based VBA codes which will be triggered when a value is selected in drop down and then action would be taken based on selection in the 'Active Cell'. However if user enter any value manually in a particular cell and press enter it changes the ActiveCell to next cell and then triggers the even. Could you please let me know if you have any solution for this? Regards, Kunal

  • Anonymous
    July 03, 2013
    I tried this and it is working fine. I will surely come back for more articles. Thanks

  • Anonymous
    October 14, 2013
    Hi, does anyone knows how to restrict those cells as well please. I have created drop down list on few cells but i Only want user to select from that list than typing anything else. any ideas?