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.
Let’s 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:
| =INDIRECT(E4) |
The result is displayed like this
If I don’t 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 info@exceltip.com
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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I left a blank space on my drop down menu so I could hand type something into the cell. When I do, the information won't save and I get an error message. Is there any way to add info on a drop down list and have it save?