If you want to work more efficiently in Excel, drop-down menus are very useful. These are list boxes, with which you can make one choice from several predetermined options. How to create such list box or dropdown menu in Excel? Fortunately, you don’t need extensive knowledge of Excel to make a selection list.
Separate entry options
Suppose you have your own company and you want to gain insight into how customers want to be approached. In the example below we have created a list of names and a column that is intended to indicate how customers prefer to be approached.
Behind every name there must be a selection list to indicate whether someone prefers to be approached by mail, telephone or text message. It is useful if you put these options somewhere in the Excel document: it does not matter where. In our example, we put the three options in cells E1, E2, and E3.
Data Validation
You must first select the cells that should have a drop-down menu. In our example, these are cells B2 through B8. Then go in the menu bar of Excel then to Facts and choose in the Ribbon for the option Data Validation. You can find this option at Data Tools. A window will then appear. Choose under Allow in front of List.
Bee Source you can now specify the different choices. In our example, enter the cell range here E1:E3 and press OK. If all goes well, Excel will convert this into a working formula. Isn’t that the case? add Source then enter the formula as follows: =$E$1:$E$3.
Input message
In the dialog box, under the tab Input message enter text to be displayed when you select a cell. So here you can enter something like Choose an option.
Customize Error Message
If you enter an option other than the choices from the list, you will receive the error message This value does not match the data validation restrictions defined for this cell. This is a somewhat vague error message, especially if others have to fill in your Excel document. Therefore you can edit the error message.
You also do this in the window Data Validation at the tab Error Message. Enter a title for the dialog box that appears when an incorrect entry is made, for example Wrong entry. Bee error message can you put something like Choose an option from the list.
It is now a lot clearer if someone has made an invalid choice.
Do you want to learn more about Excel? Thanks to the Tech Academy course bundle Excel in practice you learn all the important skills.
.