To create a drop-down list manually you can set up a list of data validation rules using the Data Validation dialogue box. Data validation lets you specify how data entered into a cell should be validated before it is used in a formula.

For example, you might use data validation to restrict the values allowed in a text box or to require that an entry be numeric.

Mouse” width=”800″ height=”450″ />

Drag the mouse pointer over the entire range of cells containing all of the list items that you want to include.

Select the down arrow icon to expand the Data Validation Window again.

You’ll see that the range you’ve selected now appears in the source field.

Click OK to accept the settings. When you create a dropdown list using a range, each item within that range will be used as the option value. So if you use a range that includes 5 cells, then you will end up with 5 different options.

In order to get around this limitation, we recommend creating an array formula that contains the values you want to include in your dropdowns.

For example, if you wanted to create a dropdown containing the names of the months, you could do something like this: {“January”, “February”, “March”, “April”, “May”}.

Then, if you were to enter a date such as 1/1/2016 into a cell, the drop-down would display January, February, March, April, or May depending on what month was being entered.

To insert a blank cell, select the cell you want to insert a blank cell after, then press Ctrl+Shift+Down Arrow. You can now type anything you want in that cell.

This method allows you to add a new item to your drop-down list. However, it requires some extra steps.

To use this method, you must first configure the data validation to your drop-down range. Then enter the new item into the list box, and select the Add New Item button.

Finally, you must set the value property of the List item element to the index position of the new item.

In order to do this, you first select the name you want to modify and then click the cell-range button at the bottom of the Name Manager dialogue box.

Next, select the new range you want to apply and click the cell-range buttons again.

When you create a new cell range, you should always set the number format to General This ensures that the cells will be formatted correctly when you copy them over to other worksheets or workbooks.