How to Edit, Copy and Delete Drop Down List in Excel

Excel is a versatile tool used globally for various data management tasks, including the use of drop-down lists to simplify data entry and ensure consistency. However, there might be times when you need to modify these lists to keep up with changes in data or requirements. These essential techniques ensure your data remains consistent and accurate. This guide will walk you through how to edit, copy, and delete drop-down lists in Excel efficiently. Imagine quickly modifying Excel drop down list settings, effortlessly duplicating drop down list in Excel, and efficiently removing drop down list in Excel entries you no longer need. This guide will walk you through each step, making it simple to manage your Excel data validation list and customize drop down list in Excel to suit your need.

Before exploring this article you can check how to create a dropdown in excel if don't already know.

How to Edit Drop-Down List in Excel

Based on the method that you used to create a drop-down list, you can edit a drop-down list in Excel in different ways. Let us look at them one by one.

How to Modify a Comma-Separated Drop-Down List

If you simply want to add or remove the list items of a drop-down, then follow these steps-

Step 1: Select the cell with the desired drop-down

Select the cell where the desired dropdown is present.

Note that if you wish to select multiple cells with the same drop-down, then use the shortcut Ctrl key(On Windows) or Cmd key(On Mac) while making a selection.

Or,

You can select a single drop-down and use the checkbox option to apply the changes to all the drop-downs with the same content. You will see this later in this section.

Step 2: Go to the Data tab and Select the Data Validation option

Go over to the data tab and select the data validation option from the data tools group.

Step 3: Add or Remove the list items in the source box

In the dialog box that opens, you will see a box titled source. You can remove or add the list items in this box as desired.

Note that you can manually type new values as well as select the values from the spreadsheet. Also, in this section, we are adding a new item but you can delete any value present in the source box as desired.

Step 4: Tick the checkbox to apply changes to all similar drop-downs

Check the 'apply these changes to all other cells with the same settings' box in order to reflect the changes in the list items of all such dropdowns.

Step 5: Save changes

Click on ok to save the changes. You can see that now the drop-down list has 3 items.

How to Edit Drop-Down Menu Based on a Range of Cells

Follow these steps to edit a drop-down list that is based on a cell range-

Step 1: Select the cells with the desired drop-down list

Select the cells containing the drop-down list. In order to select multiple cells, use the Ctrl key(On Windows) or Cmd key(On Mac) while making a selection.

Step 2: Select the data validation option

Go over to the Data tab and select the Data Validation option from the Data Tools tab.

Step 3: Make changes in the source box

In the dialog box that opens, change the range in the source box. You can now exclude or include the desired items.

Step 4: Save changes

Click ok to save the changes.

How to Change Drop-Down List Based on a Named Range

Follow these steps to edit a drop-down list that is based on a named range-

Step 1: Select the cell with the desired dropdown

Select the cell with the drop-down that you want to edit.

Step 2: Go to the Formulas tab >> Choose the name manager option

Go over to the Formulas tab and click on the Name Manager option from the Defined Names drop-down.

Step 3: Select the desired named range

In the dialog box that opens, you will see the list of all name ranges. Select the named range you wish to edit.

In other words, you have to select the named range that forms the dropdown that you wish to edit.

Step 4: Make changes

A new dialog box appears. Add or remove the cells as needed in the 'Refers to' box.

Step 5: Save the changes

Click ok followed by the close option to save the changes.

Step 6: Click on the Close Tab

Note that the changes made will be reflected in all the drop-downs that are based on a given named range.

How to Edit Drop-Down Menu Created From Table

Excel tables are dynamic in nature. Thus, in order to edit a drop-down made out of an Excel table, all you have to do is make changes in the table as needed. These changes will be reflected in all the corresponding drop-downs automatically. How to Edit Drop-Down Menu Created From Table

How to Quickly Add or Remove Items in Drop-Down List

Learn how to efficiently add or remove items from a drop-down list in your spreadsheet. Whether using Excel, Google Sheets, or another platform, discover quick steps to update your drop-down menu by editing the underlying data source, ensuring seamless customization for enhanced data management and user interaction Here is how you can quickly add or remove a certain item from a drop-down list in Excel without having to use the data validation option.

How to Quickly Add Items in Drop-Down List

Follow these steps to quickly add new items to a drop-down list-

Note that for the sake of this example, we are taking a drop-down based on a cell range containing names as shown below-

Step 1: Right-click on the Cell and Select Insert a new cell in the cell range

To insert a new cell in the cell range, click on the cell below the position where you want to insert the cell. Right-click the cell and click on Insert.

This will open a dialog box.

Step 2: Select the Shift cells down from the options and Click ok

In the dialog box that opens, select the shift cells down option and click ok.

This will create a new empty cell just below the first item.

Step 3: Add the new item

In the new cell just created, simply type the item you want to add.

You will see that the new item is added to the drop-down list automatically.

To add more items, simply repeat the above three steps.

How to Quickly Remove Items from a Drop-Down List

Follow these steps to quickly remove items from a drop-down list-

Step 1: Right Click on the Cell, and Select delete the desired item from the cell range

In the cell range, simply click on the item you want to remove and right-click to delete.

Step 2: Select the Shift cells up from the Options

In the dialog box that opens, click on the shift cells up option and press ok.

You will see that the item is deleted from the cell range as well as from the drop-down.

How to Copy Drop-Down List in Excel

Once you have created a drop-down in Excel, you might wish it to appear in multiple cells without having to create it manually each time. In order to do this, you can simply copy a drop-down list in Excel.

Copy and Paste

Hold and drag the fill handle and drag it through the cells where you want the drop-down to appear. Alternatively, you can simply copy the drop-down using Ctrl + C and paste it using Ctrl + V.

Note that this copies the drop-down along with the current selection. Thus, either use this method when nothing has been selected in the drop-down or follow these steps to copy a drop-down without the current selection-

Step 1: Copy the drop-down

Select the cell where the drop-down is present and copy it using Ctrl + C. A dotted border will appear around the cell post selection.

Step 2: Select cells

Select the cells where you want to copy this drop-down.

Step 3: Paste the dropdown

Right-click in the selected area and choose the 'Paste Special' option from the Paste Special dropdown.

Step 4: Select the validation option

Select the 'Validation' option in the dialog box that opens and click OK to save the changes.

How to Delete Drop-Down List in Excel

Depending on your need, you might want to remove the drop-down from selected cells or from all the cells in a given Excel sheet. Here is how you can do both-

How to Remove the Drop-Down Menu from Selected Cells

Step 1: Select the cells with the desired drop-down

Select the cell/cells from where you wish to remove the drop-down.

Step 2: Choose data validation

Go over to the data tab and select the 'Data Validation' option from the data tools tab.

Step 3: Clear All

In the dialog box that opens, select the 'Clear All' button and click ok.

Note that the drop-down will be removed but the selected value still remains in the cells. You can simply delete them like you normally would using the delete key.

How to Delete Drop-Down List from all Cells of the Current Sheet

Streamline your Excel worksheet by removing unnecessary drop-down lists with ease. Whether you're cleaning up data or preparing a polished report, learning how to delete drop-down lists from all cells in the current sheet is crucial. This guide will show you the steps to efficiently remove drop-down lists in Excel, enhancing your workflow and ensuring a professional and user-friendly document.

Step 1: Select any one cell with the desired drop-down

Select any one cell where the required drop-down is present.

Step 2: Choose the data validation option

Go over to the data tab and select the 'Data Validation' option from the data tools tab.

Step 3: Check the apply same settings box

In the dialog box that appears, check the 'Apply these changes to all other cells with the same settings' box.

Step 4: Clear all and save changes

Now click 'Clear all' followed by 'OK' to save all changes.

This is essentially how you can remove drop-downs from an Excel sheet.

Conclusion

In this article, we looked at how we can edit, copy, or delete a drop-down in Excel. Dropdowns are quite useful when it comes to data entry and collaborations in Excel and it is important to use effective methods to manage them like the ones we saw in this post. To improve your Excel skills further, you might want to look at this topic next - Use Custom Data Validation Function To Limit Number of Digits In Excel