Edit Drop-Down List in Excel

Before we get to editing drop-down lists in excel, we must know the basics of what a list in excel is. In simplified terms lists in, excels are columns in excel. But in columns, we do not have any dropdowns. We simply enter values manually in it or paste the data from any other source. But if we are creating surveys or asking any other user to enter data and want to give some specific options to choose from, dropdowns in excel come in handy.

As explained above, dropdowns in excel help guide a user to manually enter values in a cell with some specific values to choose from. Like in surveys, if there is a question for the gender of a person, if we ask every user to enter values for that question, then data will not be in order. Some people will write answers in uppercase, some in lowercase, or some may make some spelling mistakes. But if we give users two values to choose from, either Male or Female, our data would be in the exact order we want. This is done by creating drop-down lists in excel.

There are various ways of editing drop-downs lists in excel; they are:

  1. Giving Drop Down Values Manually and using data validation.
  2. Giving Drop Down Ranges and using Data Validation.
  3. Creating a Table and using Data Validation.

Data Validation is an option under the Data Tab in the Data Tools section.

How to Edit Drop-Down List in Excel?

There are three methods explained on how to edit the drop-down list in excel:

Let us learn to make drop-down lists with some examples and learn every process to edit the drop-down list in Excel.

You can download this Edit Drop-Down List Excel Template here – Edit Drop-Down List Excel Template

Example #1 – Giving Drop Down Values Manually and Using Data Validation.

For the step, we need to have the drop-down values ready to enter. Suppose we want to have the values to enter Shoe Brand to choose from. Now we need to select a cell where we are going to insert the dropdown.

  1. In cell B2, we are going to enter our drop-down list.


    edit drop down list example 1.1

  2. In Data Tab under the Data Tools section, click on Data Validation.


    edit drop down list example 1.2

  3. Again Click on Data Validation in ExcelData Validation In ExcelData validation in Excel is used to limit a user's inputs to specified cells or input ranges by offering pre-defined inputs for selection to avoid multiple redundant data inputs.read more, and a dialog box appears,


    edit drop down list example 1.3

  4. In Settings under Allow list, select Lists.


    edit drop down list example 1.4

  5. In the source section, Manually enter the values for the drop-down options.


    edit drop down list example 1.5

  6. When we click on the OK, we have our drop-down created.


    edit drop down list example 1.6

    The above method is the easiest way to make and edit a drop-down list in excel, but if we have to enter more values for the choice of shoes, we have to redo the whole process.

Example #2 – Giving Drop Down Ranges and Using Data Validation.

For Example, I am a teacher, and I want a response from my students whether they have completed their projects or not. For the survey, I want to give them just three options: Completed, Pending, Not Started.

Yes, I can use the above process, but it can be changed by the user as he/she can go to the data validation tab and change the values. In this process, we select a range of values and hide the columns so that the other user cannot edit the validation or the dropdown.

  • In a list or column, copy values for dropdowns or write them down.
edit drop down list example 2.1
  • In the cell we want to enter the validation, we will select the cell, i.e., cell B2
edit drop down list example 2.2
  • Under Data Tab in the data tools section, click on Data Validation.
edit drop down list example 1.2
  • Again Click on Data Validation; a wizard box appears.
edit drop down list example 1.3
  • In the settings, under Allow, click on Lists.
edit drop down list example 1.4
  • In the source tab, select the range of data for the drop-down list.
edit drop down list example 2.3
  • When we click on ok, we have a drop-down in cell B2.
edit drop down list example 2.4
  • Copy the validation to all the cells (up to cell B6). Now we have to drop down a list of all the cells we want.
edit drop down list example 2.5

Now even if we hide our cell range, which was the source of the drop-down, any user cannot edit the validation. The above process also has the same disadvantage as the first example because if I have to insert another option of Half Completed, then I have to redo the process all over again. I have to insert the new option to the source and enter a new validation.

Example #3 – Creating a Data Table and Using Data Validation.

In this method, we will create a data table and use data validation as before. But the benefit of using this method will be explained later on. Let us say I have a restaurant and have some dishes to select for customers. I have inserted the data in a column as below.

edit drop down list example 3.1
  • The first step is to create a table, select the data, and in the Insert tab, click on Tables.
edit drop down list example 3.2
  • The following window will open and when we click ok we have created our table in column A.
edit drop down list example 3.3
edit drop down list example 3.4
  • Let us name this table as “Restaurant.” In the left corner, we can see there is an option to rename the table.
edit drop down list example 3.5
  • Rename the table as Restaurant”.
edit drop down list example 3.6
  • Now select the cell where we want to insert the drop-down list.
edit drop down list example 3.7
  • Under the Data tab, click on Data Validation.
edit drop down list example 1.2
  • In the allow Tab, select Lists.
edit drop down list example 1.4
  • Now in Source, type the following,
edit drop down list example 3.8
  • When we click on OK we can see that drop-down has been inserted to the data.
edit drop down list example 3.9
  • Now, if I have another menu to add, suppose Soups.
edit drop down list example 3.10
  • We can see that the new entry in the menu tab is also being displayed in our dropdown.
edit drop down list example 3.11

The above process has solved our problem where if a new entry has to be created and dropdowns needed to be made all over again.

Explanation of Edit Drop-Down List in Excel

I have already explained above why we need drop-down lists in our data. It helps guide a user to manually enter values in a cell with some specific values to choose from.

While asking users to choose some specific options to choose from dropdowns in excel, then making and editing drop-down lists come in handy as a user can enter wrong values, which hampers the data.

Things to Remember While Editing Drop-Down List in Excel

  1. If we enter to drop down values manually or set ranges, then any newer entry needs to be inserted with a new drop-down list.
  2. In tables, we can insert a new entry, and it gets updated in the dropdown.
  3. To view or edit the drop-down list, we need to click on the cell.

Recommended Articles

This has been a guide to Edit Drop-Down List in Excel. Here we discuss how to Edit Drop-Down List in Excel along with examples and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *