Drop down lists

Drop down boxes are a great way to restrict data entry into a particular cell or range of cells.

The use of dropdown boxes also provides a user friendly interface which helps improve the aesthetics of a spread sheet designed for non-proficient excel users.

How to setup a drop down boxes

In order to setup a drop down box we need to identify a list from which the selections can be drawn.

Using the list below we can make a drop down box

Favourite animal


Best practise is to create a named range for the data to be included in the drop down menu.

dropdown 1

Highlight the data to be included then alter the name in the white drop down box to the left of the function box and name it something relevant, in this example “animal”.

The name “animal” has now been applied to this range of cells.

In order to create a drop down box select the required cell and navigate to data validation.

dropdown 2

We are then presented with the following pop up menu

dropdown 3

In the “Allow” drop down select the “List” option and set the source to the range we have just created above.

dropdown 4

The only input that is now allowed in cell B7 are the options from the drop down box, in this instance “Cat”, “Dog” and “Bird”.