How to Create Drop-down List from a Range of Cells in Your Excel

Sometimes, you may want to create a drop-down list from a range of cells instead of inputting the values manually. Here we will talk about the details of the drop-down list.

Many of you have already known how to create a drop-down list in Excel cells. You can also refer to our previous article How to Create Multiple Choice Questions in an Excel Form to learn about the method. Except for inputting the options into the “Source” text box directly, you can also refer to a range in Excel worksheet. And below we will talk about the usage in detail.

Refer to a Cell Range

If the options are in a range in the current workbook, you can also use the cell range in the source. And the cell range should be in one column or in one row.

  1. Click the target cell where you need to create a drop-down list.
  2. And then click the tab “Data” in the ribbon.
  3. Next click the button “Data Validation” in the toolbar.Data Validation
  4. After that, choose “List” in the “Allow” text box.Choose List
  5. Now click in the text box of “Source”.
  6. In this step, you need to input the range reference into the text box. If the list is in the same worksheet, you can now directly select this range.Range in Same Worksheet

On the other have, if the range is in another worksheet, you can also click the tab of that worksheet and then select the list.Other Worksheet

  1. After that, click the button “OK” in the “Data Validation” window.

Therefore, the drop-down list has been created.

The Drop Down List

Refer to a Defined Name

Instead of refer to a cell range in the workbook, you can also input a defined name into the “Source” text box. And before you use the name, you should create one in this workbook.

  1. Select the range in the target worksheet.
  2. And then right click the range.
  3. After that, click the option “Define Name” in the new menu.Define Name
  4. In the “New Name” window, input a designated name into the first text box. Here we give a name “list_source”.
  5. If the range is in another worksheet, make sure that the scope is “Workbook” or the worksheet where you need to create the drop-down list.Set Name
  6. After that, click “OK” in the dialog.
  7. And then repeat the first 5 steps in the previous part.
  8. Next, input the name into the text box of “Source”. You need to input a “=” before the name.Input Name
  9. After that, click “OK” in the window. Now you have also created a drop-down list for the cell.

The Drop Down List

Change of the Source Range

When you need to change the source range, the drop-down list will also be affected. And there are several different conditions.

  1. If you directly delete the cell range or the defined name in the workbook, the small arrow for the drop-down list will be invalid. Therefore, you need to pay attention if you will delete the source range.
  2. Sometimes, you need to add items into the source range. When the source changes, the drop-down list will also change. If you also need to show the new items in the drop-down list, you can insert cells within the range and then input the items into those new cells. When you add them before the first cell or after the last cell, those items will not appear in the drop-down list.
  3. On the other hand, if you clear an item in the source range, there will also be a blank item in the list. Here we clear one cell in the source range.Clear One Cell

And in the drop-down list, you can also see a blank item.Blank Item

Therefore, if you don’t want to show the blank item in the list, delete the original cell instead of just clearing the content in it.

  1. When you move the source range to other column or row or other worksheet, the drop-down list in the cell will not change. Besides, the source will change accordingly. For example, the source reference is “=’Product list’!$A$2:$A$27” in the Refer to a Cell Range When you move the range to another column, i.e. here we move it to column G, you can check the list in the data validation.Source Change

Here you can see that the source has also change. Thus, move columns will not affect the data validation. In addition, the defined name will also not change if you move the original range.

  1. If you need to sort the source range, the rank in the drop-down list will also change at the same time. Now we have sort from A to Z for the column.Sort Source

And you can check the drop-down list. You will find that the rank has also changed.New Rank

Except for the above 5 conditions, there may also exist other cases. But whenever you need to modify the source range, you need to check the changes in the drop-down list. Otherwise the worksheet will be in a mess, and you need to spend additional time to solve problems.

Pay Attention to Error Messages

When you are working with Excel, you will sometimes see error messages. Most people will just ignore them, which turn out to be a bad habit. Some messages will contain important information. If you ignore them, you will have a great possibility to suffer from Excel corruption. And once the data accident happens, you need to spend more time and energy to fix those errors. At this moment, you can repair xlsx file corruption by using our powerful tool. With this tool at hand, all the data and information can be retrieved easily.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc data error and outlook repair software products. For more information visit

Comments are closed.