How to Add Item to Drop-Down List in Excel (5 Methods)

Drop-down list is an interesting feature of Microsoft Excel. Using this tool, we can choose items from a given list, without typing any value. It restricts us from inputting unnecessary data in Excel. Normally the items on the drop-down list are predefined. But sometimes we may need to add a new item to the drop-down list. So in this article, we will discuss how to add items to the drop-down list in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Methods to Add Item to Drop-Down List in Excel

We will apply 5 methods to add items to the drop-down list in Excel. Adding items will depend on how a drop-down list is created. We will consider the following dataset for this purpose.


1. Add Item to Drop-Down List by Adding Item to Existing Data Range in Excel

We will show how to add items to Range based drop-down list. There are two options to add items to a range-based drop-down list.


1.1 Add Item within the Range Using Insert Feature

We can add items using the insert feature in the range-based drop-down list.

Step 1:

  • Move to Cell E4.
  • Select the Data Tools group from the Data tab.
  • Now, choose the Data Validation option.

Add Item to Drop-Down List Based on Range of Cells in Excel

Step 2:

  • Choose List from the Allow field.
  • Now, choose the desired range in the Source field and then press OK.

Add Item to Drop-Down List Based on Range of Cells in Excel

Look at the dataset. The drop-down list is visible at Cell E4.

We will use the Insert feature to add items at any position of the selected range.

Step 3:

  • Move the cursor to any position of the Fruit List column.
  • Press the right button of the mouse.
  • Choose Insert from the menu.

Add Item to Drop-Down List Based on Range of Cells in Excel

Step 4:

  • Select Shift cells down from the Insert window.

Add Item to Drop-Down List Based on Range of Cells in Excel

Step 5:

  • Now, press OK and look at the dataset.

A new cell is added to the range.

Step 6:

  • Now, write a Mango on Cell B9 and click on the drop-down list of Cell E4.

Mango is added to the drop-down list.


1.2 Add Item at the Bottom of Range

We will add new items at the bottom of the data range by changing the source range.

Step 1:

  • Add a new item at the bottom of the Fruit List column.

Step 2:

  • Go to the Data Validation field by following the steps shown before.
  • Now, modify the Source by selecting the range from the dataset.
  • Mark the option Apply these changes to all other cells with the same settings and press OK.

Add Item to Drop-Down List Based on Range of Cells in Excel

Step 3:

  • Now, move to Cell E4 and click on the down arrow to check the drop-down list.

The newly added item is shown on the list.

Read More: How to Create List from Range in Excel (3 Methods)


2. Add Item to Drop-Down List by Editing a Named Range

We can form a drop-down list using Named Range in Excel. New items will be added to the drop-down list by changing this Named Range.

Step 1:

  • Select the cells of the Fruit List column.
  • Select Define Name group from the Formulas tab.
  • In the Refers to field select the range for Named Range. Then, press OK.

Add Item Named Range Based Drop-Down List by Editing the Range

Step 2:

  • Move the cursor to Cell E4.
  • Go to the Data Tools group from the Data tab.
  • Then, select Data Validation from the options.

Step 3:

  • The Data Validation window will appear. Put the Named Range title on the Source box.
  • Then click OK.

Add Item Named Range Based Drop-Down List by Editing the Range

Look at the dataset now.

The drop-down list is visible at Cell E4.

We will add new items at the bottom of the dataset and modify the Named Range and that will reflect on the drop-down list.

Step 4:

  • We added a new item at the bottom of the dataset.
  • Go to Name Manager from the Formulas tab.

Add Item Named Range Based Drop-Down List by Editing the Range

Step 5:

  • In the Name Manager window, go to Refers to select the updated range with newly added data.
  • Then click Close.

Add Item Named Range Based Drop-Down List by Editing the Range

Step 6:

  • A new dialog box will appear for permission. Choose Yes.

Again, go to Cell E4 and click on the down arrow sign.

The newly added item is shown on the list.

We can apply the Named Range in another simple way. Just select the cells, go to the name bar, and put your desired name.

Add Item Named Range Based Drop-Down List by Editing the Range

Read More: How to Edit Drop-Down List in Excel (4 Basic Approaches)


3. Create a Table-Based Drop-Down List and Add New Item

We will form a drop-down list based on Excel Table and add new items from the bottom of the table.

Step 1:

  • Select all the cells of the Fruit List Then, press Ctrl+T.
  • The Create Table window will appear. The selected range will show here.
  • Mark the box of My table has headers and then press OK.

Form a Table Based Drop-Down List and Add New Item

Step 2:

  • Move the cursor to Cell E4.
  • Now, go to the Data Tools group from the Data tab.
  • Click on the Data Validation option.

Step 3:

  • Select the range on the Source field and press OK.

Step 4:

  • Click on Cell E4 and press the down arrow sign.

We can see that the drop-down list is visible in the dataset.

Step 5:

  • Now, go to the last cell of the Table. Add a new item and press the Enter button.

Form a Table Based Drop-Down List and Add New Item

We can see that the new item is showing on the drop-down list.

Read More: Create Excel Drop Down List from Table (5 Examples)


Similar Readings


4. Add Item Manually in an Excel Drop-Down List

We will manually input the items to form a drop-down list in Excel.

Step 1:

  • Go to Cell E4.
  • Go to the Data Tools group from the Data.
  • Choose the Data Validation option.

Add Item Manually in an Excel Drop-Down ListStep 2:

  • Put 5 items manually on the Source field of the Data Validation option.
  • Then press OK.

Add Item Manually in an Excel Drop-Down List

Click on the down arrow of Cell E4.

We can see 5 items visible on the drop-down list.

Step 3:

  • Again, go to the Data Validation option and add a new item to the Source box.
  • Mark on Apply these changes to all other cells with the same settings option and then press OK.

Step 4:

  • Go to Cell E4.

Add Item Manually in an Excel Drop-Down List

We can see that the new item is added to the drop-down list.

Read More: How to Make a Drop Down List in Excel (Independent and Dependent)


5. Add Item by Utilizing Dynamic Drop-Down List in Excel

We can utilize the Excel Dynamic Drop-Down list to add new items.

Step 1:

  • Select Cell E4 first.
  • Choose Data Tools from the Data tab.
  • Select the Data Validation option.

Add Item by Utilizing Dynamic Drop-Down List in Excel

Step 2:

  • Put the following formula on the Source option.
  • Then, press OK.
=OFFSET($B$5,0,0,COUNTA($B:$B),1)

Add Item by Utilizing Dynamic Drop-Down List in Excel

Now, go to Cell E4 of the dataset. The drop-down list is visible here.

Step 3:

  • Now, add a new item on the bottom cell of the Fruit List column and press Enter.

Add Item by Utilizing Dynamic Drop-Down List in Excel

Again, go to Cell E4 and see that the new item is visible here.

Read More: How to Create Dynamic Dependent Drop Down List in Excel


Conclusion

In this article, we described how to add new items to the drop-down list in Excel. Go through the methods and choose your desired method. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo