How to Add Drop-down List in Excel Cell

We all know that Excel is a commercial spreadsheet application produced and distributed by Microsoft for Windows and Mac OS X. Excel has the basic spreadsheet feature wherein you use a collection of cells arranged into rows and columns to organize data manipulation. In this article we will discuss how to add a drop-down list to an Excel cell. You can use the drop-down list to enter data in a spreadsheet from a predefined items list. The drop-down list helps limit the number of choices available to you and speeds up data entry in Excel.

Creating an Excel Drop-down List

Excel drop-down list can be based on:

• a list of comma-separated values,
• a named range, and
• a range of cells

The methods described in this section apply to most of the Excel versions, including 2013, 2010, 2007 and 2003.

Creating an Excel drop-down list based on Comma Separated Values

Select a cell or cells where you want the drop-down box to appear. It can be a single cell, a range of cells or the entire column as displayed in the illustration below. If you select the whole column, a drop-down menu will be created in each cell of that column. This turns out to be a time-saver for creating a questionnaire type of worksheet.

On the Excel menu bar click Data > Data Tools group and then click Data Validation.

1. In the Data Validation dialog box, click Settings tab

 

2. From the Allow drop-down box, select List.
3. In the Source box, type the items that you want to show in your drop-down menu separated by a comma (with or without spaces).
4. Select the In-cell dropdown
5. Select Ignore blanks if you want your users to leave the cell empty.
6. Click OK and your drop-down is list is created. Users can click the down arrow to display the list and select an item.

The above method is feasible if you have only few cells residing on the Worksheet. If you want the same list to appear in multiple cells, then this method should be avoided. Editing will be cumbersome as you will have to change every cell that references the Data Validation list. Locating each cell and editing it can be a painful process.

Here is a better way to do this.

Creating an Excel drop-down list based on a named range

This method, though a bit time consuming, but it saves time in the long run. For example, let’s create a drop-down list of book titles of your favorite authors.

1. In Worksheet 1 list out the author names in a column as under:

2. In Worksheet 2 create the book titles of each author in individual column. These values should be entered in a single column without any blank cells.
3. Select all the titles in the column of a specific author, right-click and from the context/popup menu select Name Range. Or you can click Name Manager on the Formulas tab or press Ctrl + F3. In the Name Manager dialog, click New.

Alternatively, you can select the cells and type the range name directly in the Name box as illustrated below. When finished, click Enter to save the newly created named range.

4. In the New Name dialog box, type a name in the Name Ensure there is no space in the name assigned. Do not change the option in the Scope field. Specify an easy to understand Comment in the field if required or leave it blank. The Refers to field is populated automatically and displays the range of cells that you selected. Click OK to save the range name that you created.
5. Switch to Worksheet1. Select cell against the author name.
6. On the Excel ribbon, click the Data > Data Validation.

 

7. In the Data Validation dialog, select the settings as described above and in the Source field type the range name that you defined and click OK. Ensure that the name in the source is prefixed with = (equal to) sign.
8. In the cell against the selected author name click the down arrow to display the list of book titles by the author.

You can create book drop-down list for each author. A drop-down list with more than eight items will have a scroll bar.

Note: You can create the list of items on the same or a different worksheet. For ease of understanding the example above uses two different worksheets.

Creating an Excel drop-down list based on a range of cells

This method is very similar to creating a drop-down list based on a named range except for a minor difference.

When configuring your drop-down list, instead of typing the range’s name, click on the range selection icon next to the Source box, and select all cells with the entries you want to include in your drop-down list. They may be in the same or in a different Worksheet. If the latter, you simply go to the other Worksheet and select a range using your mouse.

Designing a drop-down list in Excel is fairly easy and saves time. It is a clean method of displaying a large list of choices since only one choice is displayed initially until the user activates the drop-down box.

We hope that this article on creating drop-down list in Excel was useful for you. Should you have any question on this topic, please feel free to ask in the comments section. We at TechWelkin and our reader community will try to assist you. Thank you for using TechWelkin!