Create A Drop Down List Manually Or Using A Range of Cells

Drop down boxes are a neat way to limit what can be entered into a spreadsheet. If you know how, you can format cells so that you get what you need from your users as well as making it easier for them to get right.

Drop_Down_List_1

 

Excel has put the drop-down functionality in a tool called Data Validation. It can be found in the Data Tab under the Data Tools section. It’s called data validation because the main function is to validate the values that are being entered into your chosen cells. One validation is that the value appears in a pre-defined list (which can be neatly displayed in a drop down list! yay).

Drop_Down_List_2

 

Select the cell (or cells) that you’d like your drop down list to appear in and then click the Data Validation option in the ribbon. For a drop down box you need to select List in the Allow: option box (you’ll see that there are more options in the list but more on that later).

There are 2 tick boxes which are quite self explanatory but you need to make sure that the In-cell dropdown option is ticked.

Finally, in the Source: box you need tell Excel what you want in your drop down list. You have a few option…

Write out the option, separating them with a comma…

Drop_Down_List_3

 

Select a range of cells that list out your options. Notice that you can find your range of cells and use your mouse to select them and excel will fill in the source box for you…

Drop_Down_List_4

And if you are really clever, you can create a named range for your options and simply type =YourNamedRange in the source box…

Drop_Down_List_5

 

Below you can see the list of other option for validating entered values into cells. Each one requires different inputs once you’ve selected them. Worth having a play with to see what they all do…

Drop_Down_List_6

There are 2 further tabs in the Data Validation box (highlighted above). Simply put, the Input Message tab allows you to show a message box like this one below… (we have a post covering this here)

Drop_Down_List_7

…and the Error Alert tab allows you to throw up a clever message box telling your users that they’re a bit rubbish!

Drop_Down_List_8

 

And that’s it. Easily done and very helpful in many situations. If you have any questions or need further info just leave a comment in the box below. Someone will get back to you.

No ratings yet.

Please rate this