Excel Drop Down Selection and Free Form Entry

Flexible data entry

When creating a drop down selection you usually want to restrict the user to certain entries. There are cases however when you want to allow the user to choose an option or allow them to type in their own entry.

You can use Excel’s Data Validation feature to provide both a selection drop down and the ability to type their own entry. The secret is to use a named range for the entry list that includes a blank cell.

See the image below.

Sheet Layout

We have three comments listed in the range A2:A4. Cell D2 will contain the drop down.

To create the range name select the range A2:A5 (cell A5 is blank) and click in the Name Box (above column A and left of the formula bar) and type Comments and press Enter (see image below). After you press Enter the name will be centered in the Name Box.

Create range name

Select cell D2 and press, in sequence Alt a v v this is a shortcut that opens the Data Validation dialog. Click the Allow: drop down and choose List. Click in Source: box and press the F3 key (this shortcut opens the Paste Name dialog) and select Comments and click OK. See images below.

Data Validatio Select Name

Data Validation dialog

Click OK again and cell D2 is ready to use – see image below.

Drop down list

As well as choosing from the drop down options you can also type anything in cell D2. Because you have named the list it can be anywhere in the file. I tend to put all my lists in a dedicated sheet and typically name all my lists.

Free form entry

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “Excel Drop Down Selection and Free Form Entry