how to edit drop-down list in excel macro

how to edit drop-down list in excel macro

In this article, we will learn how to edit the dropdown option on cells.

Data Validation is an Excel 2016 feature whose purpose is to restrict what users can input into a cell. It is essential to create drop-down lists or combo boxes that contain predefined options that limit user errors and allow for more consistent data entry.

In this article, we are going to learn how to edit dropdown list in excel. To do this, we will use the Name manager and Data Validation.

Lets understand this by taking an example.

We have some lists here as shown below.

First, we need to create a dropdown list for the Main Category and then we will proceed to Sub_Category.

Select Formula>Name Manager in Defined names OR use shortcut Ctrl + F3 to open Name manager where we will keep lists of the array with their names so that we can call them by there name whenever required.

Click New to create. Here Name will be Month and in Refers to option enter the list under Month as shown below.

The same we will do for Week_Days and it will show like

Click Close and now select the cell where we need to add dropdown list.

Then Click Data validation under Data bar. Choose list option is Allow and select the cells for main category names which in this case is at B2 and C2 cell Month and Week_Days

As we can see a drop down list is created which asks the user to choose from the given option.

Now select the cell under Sub_Category and just write the formula in Data validation and click OK.

Formula:

The result is displayed like this

If I dont want Month and Week_Days. Instead, I want Fruits_Name and vegetables_Name. We just need to edit our Name Manager list.

Press Ctrl + F3 to open Name manager anddelete the already inserted list and add new lists i.e. Fruits_Name and Vegetables_Name.

Instead of Month and Week_Days cell, we will insert Fruits_Name and Vegetables_Name in Data Validation and click OK

Now select the cell under Sub_category as shown in the snapshot below.

Instead of Month and Week_Days cell, we will use Fruits_Name and Vegetables_Name in Data Validation and click OK

As you can see the new list is added here.

This is the way we can edit in the dropdown list and change the list selection.

Below you can find more example:-

How to create drop down list in Excel?

How to delete drop down list in Excel?

How to apply Conditional Formatting in drop down list in Excel?

How to create dependent drop down list in Excel?

How to create multiple dropdown List without repetition using named ranges in Excel?

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at

Popular Articles:

50 Excel Shortcut to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel 2016

How to use the SUMIF Function in Excel

Video liên quan

admin