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.



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).



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…



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…


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



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…


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)


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



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.

5/5 (1)

Please rate this