Create a Drop Down List in a Cell

Create a Drop Down List in a Cell

To create a drop down list in Excel, you can name a list of items, based on a named Excel table. Then, use that list as the source for the Data Validation drop down list. If you don't want to create a named table, you can follow the instructions in the named range section below.

Watch the steps in this short video, and the written instructions are below the video

How to Create a Drop Down List

With Data Validation, you can create a dropdown list of options in a cell. There are 3 easy steps:

1. Create a Table of Items OR Create a List

2. Name the List

3. Create the Drop Down

Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Clear > Clear All, on the Ribbon's Home tab.

1. Create a Table of Items

The easiest way to create and maintain the list of options, is to type them on a worksheet. You can do this on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists.

NOTE: A data validation list can show up to show 32,767 items from a list on the worksheet.

1. Type a heading for the list -- Employees in this example
2. Immediately below the heading cell, in single column, type the entries you want to see in the drop down list. Do not leave blank cells between the entries.

   

3. Select a cell in the list, and on the Ribbon's Insert tab, click Table

   

4. Add a check mark in "My table has Headers" and click OK

   

The table is now a Named Excel Table.

2. Name the List

Next, you will create a named range that does not include the heading cell in the table. This named range will be dynamic -- it will adjust automatically if items are added to or deleted from the list.

The Name Box method is quicker, but you can't add a comment.

Use the Name Manager

This is the method shown in the video. You can set a name, scope and comments.

1. Select one of the cells in the list of employee names.
2. On the Ribbon, click the Formulas tab, and in the Defined Names group, click Define Name



3. In the New Name dialog box, type a one-word name for the selected range, e.g. EmpNames
4. Leave the Scope as Workbook



5. Click in the Refers to box (On the worksheet, the cell that is currently listed in the Refers to box will be highlighted)
6. To select all of the names in the list, point to the top of the heading cell (B1), and when a black arrow appears, click, to select all the names, without the heading cell. (Be sure to click when the arrow is in cell B1, not in the Column B heading button)



7. The table name and column name will appear in the Refers to box: =Table1[Employees]
8. Click OK, to complete the name.

Use the Name Box

This is a quick way to name a range of cells.

1. Click at the top of the heading cell, to select all the cells in the list (the heading will not be selected).

   

2. Click in the Name box, to the left of the formula bar
3. Type a one-word name for the selected range, e.g. EmpNames, and then press the Enter key, to complete the name.

• NOTE: After you press Enter, the name will disappear, and the Table name will appear in the name box.

3. Create the Drop Down List

Now that you have created a named range, you can use that to create a drop down list in one or more cells

1. Select the cells in which you want the drop down list



2. On the Ribbon's Data tab, click Data Validation.

   

3. From the Allow drop-down list, choose List

   

4. Click in the Source box, and type an equal sign, and the list name, for example:
   =EmpNames
   OR, press the F3 key, to see a list of names, click on a name, and click OK

   

5. Click OK to close the Data Validation dialog box.
6. Click on one of the cells, and click the drop down arrow

   

7. Click on an item in the drop down list, to enter it into the cell.

If you have any query or want any help related to excel feel free to join our Telegram channel and message us..

Channel link :

https://t.me/msexcelmaster101