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.

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.

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.

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

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.