TIPS for CLICKS: Using drop-down lists for data entry

This is an excellent helper for data entry in Excel. When we create worksheets with tables of data or databases, in many cases there are text entries, expressions or phrases that we have to enter repeatedly many times. In such situations we can create lists of entries, like people names, phone numbers, cities, days of the week, months, whatever is included in tables, and then create drop-down lists based on the entries. Later, instead of typing the entries one by one in our table we just select the entries from the drop-down list.

Here is a simple example, based on the list of fruits. Follow the steps below to create the list.

First, create (somewhere in the vicinity of your planned table, e.g. in $X$1:$X$24 range) the list of potential entries and sort them A to Z for easy selection. Create table by selecting the range of listed values and pressing CTRL+T:

Select the first data cell in your table column designated for the names of fruits, e.g. cell C2, and click Data tab on the ribbon, then select Validation>Settings>Allow>List>Source: $X$2:$X$24 . Click OK.

Your drop-down list is created in the cell C2. Now extend (copy) the C2 selection down the column as far as you expect your table size will need. From now on you won't need to type any entry in the column; you'll just select appropriate item from the list.
 

There is one small problem with this approach. The standard Excel drop-down list size is limited to eight lines. What if your list contains more entries? You have to use vertical scroll bar to select items listed below the eight line. This isn't convenient and takes additional time, so we need to find a remedy for that inconvenience.

To display more than eight lines in the drop-down list we can use a Combo Box from Form Cotrols. Click on Developer tab in the ribbon, then select Insert>Form Controls>Combo Box. While pressing ALT key (in order to fit the box into the cell size) draw a Combo Box in cell C2 (in our example).

Right-click on the box and select Format Control>Control>Input Range, then:
enter the range with your values to be displayed ($X$2:$X$24 in our case) in the drop-down list, and enter also the number of lines you want to display in the drop-down list (23 in our case) in the 'Drop down lines' box, as shown below. Click OK.

Now extend (copy) the C2 selection down the column as originally intended above. From now on you will see the whole list of fruits for selection in cells beneath cell C2. You'll just select appropriate item from the list without any scrolling.

This way you can prepare and use drop-down lists for any column in your table, wherever you need to enter the same text in some of the cells; without the limitation to eight lines.