This article will show you how to add/create a dropdown control in Excel so that the user can select only predefined value from custom list.
What we want to accomplish
We want a user to select a color in cell B1 from the predefined list of colors as shown on the picture.
Typical scenario when describing items in stock in any store.
Click DATA -> Data Validation -> Data Validation
For lower resolution Excel window
For maximized Excel window
On Data Validation choose List in Allow drop-down.
Make sure that In-cell dropdown is selected (should be by default).
Select range for Source that will be shown and used for validation in the dropdown.
You can select named range or use mouse to select data like shown in the pictures below.
You can use data in other spreadsheets – for example if you have one sheet that contains only definition data.
At the end
You can enable Input Message.
It will be shown when user clicks on the cell with dropdown defined as shown in the screenshot below.
In Error Alert tab you can specify what will be shown if user types or selects invalid data.
There are 3 styles or messages (based on priority):
Table below shows how each type will be presented to the user if selected value is invalid.
|Definition||Result for user|
Click OK button on Data Validation window.
That’s it! You have now created a dropdown control that lets user choose value from custom list.