Table of Contents

• Edit Drop-Down List in Excel
• How to Edit Drop-Down List in Excel?

Editing Drop-Down List in Excel

Before we get to editing drop-down lists in Excel, we must know the basics what a list in excel is. In simplified terms lists in, excels are columns in excel. But in columns, we do not have any drop downs. 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 drop downs 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 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 a option under Data Tab in Data tools section.

How to Edit Drop-Down List in Excel?

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

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

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 drop down.

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

• In Data Tab under Data tools section click on Data Validation.

• Again Click on Data Validation and a dialog box appears,

• In Settings under Allow list select Lists.

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

• When we click on OK we have our drop down created.

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 drop down.

• In a list or column, copy values for drop downs or write them down.

• In the cell we want to enter the validation we will select the cell, i.e. cell B2

• Under Data Tab in the data tools section click on Data Validation.

• Again Click on Data Validation a wizard box appears.

• In the settings, under Allow click on Lists.

• In the source tab, select the range of data for the drop-down list.

• When we click on ok we have a drop down in cell B2.

• Copy the validation to all the cells (up to cell B6). Now we have to drop down a list to all the cells we want.

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 has also 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.

• First Step is to create a table, Select the data and in the Insert tab, click on Tables.

• The following window will open and when we click ok we have created our table in column A.

• Let us name this table as “Restaurant”. In the left corner, we can see there is an option to rename the table.

• Rename the table as Restaurant”.

• Now select the cell where we want to insert the drop-down list.

• Under the data tab, click on Data Validation.

• In the allow Tab select Lists.

• Now in Source, type the following,

• When we click on OK we can see that drop down has been inserted to the data.

• Now if I have another menu to add suppose Soups.

• We can see that the new entry in the menu tab is also being displayed in our drop down.

The above process has solved our problem where if a new entry has to be created and drop downs 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 drop down.
3. To view or edit drop-down list we need to click on the cell.

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

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 –

• Excel Columns Group
• Create Checklist in Excel
• Create List Box in Excel VBA
• How to Randomize List in Excel?
• How to Search in Excel?

The post Edit Drop-Down List in Excel appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.