How to change drop down list in Excel

The Data Validation feature in Excel 2019 offers a List option to confine selections in a cell to a preconfigured series of values. This selection can further influence the values in another list by presenting options that relate to the first choice.To begin with, the first drop-down list's Items list might present A list of product categories that produces a secondary list of product models.For this function to work, the first drop-down list must be based on relevant constraints for cycle ranges set up in Excel.

If you need to change the drop-down list in Excel, it's easy to do. Simply highlight the entire drop-down list, and then use the shortcut keys (Ctrl+A or Cmd+A) to select all of the text. Next, click on the Home tab and click on the Text Box Properties button. This will open the Text Box Properties dialog box.

In Excel, you can change the dropdown list by highlighting the text and then pressing F2 (or using the keyboard shortcut Ctrl+F2).

Drop down lists are a staple of spreadsheet applications and can be used for a variety of tasks. In this tutorial, we will show you how to change the drop down list in Excel.

Method 1: How to Edit a Drop-Down List from a Table

Using Excel may seem complicated to many beginners. Before you give up, there remember that it gets easier with practice. Once you learn how to edit a drop-down list in Excel, it gets easier from there on.

Here are some steps that can help in editing a drop-down list from a table.

Add an Item

1. Visit the last Row in the Column of your list

2. Press Enter/Return in the cell

3. Enter a New List, to begin with

4. Press Enter/Return another time

Remove an Item

1. Visit the item in the list

2. Press Right Click on the needed item

3. Choose “Delete” from the List

4. Press Table Rows

How to Create and Use a Table in Microsoft Excel

1. Open MS Excel on your PC by searching on the window search option

2. Select the Range of Cells to Create a Table by clicking on one cell and dragging the cursor

3. Open the “Home” tab in Excel

4. Click “Format as Table”

Pros

The best part about learning how to edit drop-down lists in Excel tables is that you do not have to fill in every little change by hand. Any addition or removal will update the contents of your table.

Hence, it is ideal for use by people who deal with long lists, and these lists need alteration often. You can have the peace of mind that everything does not need to be done manually in Excel.

Cons

Using tables while learning how to edit drop-down lists in Excel also has a downside. Many people have to face the trouble of facing black lines. These lines can affect the view of your tables.

Therefore, you will need to adjust a few things before proceeding with drop-down lists on Excel. It is the only way you can avoid the lines on the table.

Method 2: How to Edit a Drop-Down List from a Cell Range

There are many paths that you can choose to edit a drop-down list. Cell Range is one such path where you can learn how to edit Drop-Down lists in Excel by following the mentioned steps.

Add/Delete the Item

1. Open Your Worksheet by looking from the start bar

2. Go to List Items in Your Sheet to access the entries

3. Add/Delete the Item of Need as per your preferences

Use Data Validation

1. Select the Cell with Drop-Down List

2. Go to the “Data” section in the bar above

3. Click “Data Validation” to select “Custom.”

4. Press OK

Try Cell Range

1. Go to the “Settings” Tab

2. Look for “Source”

3. Adjust the Cell Range as per your preference

4. Include/Exclude your Entries as needed

5. Press OK.

Pros

Learning how to edit a drop-down list in Excel from a cell range is one of the most effective ways to reduce errors in your work. This feature can be especially useful for users who work with MS Excel on a daily basis in a professional as well as personal environment. This, in turn, will increase your efficiency in office work, and you will be able to meet deadlines.

Of course, efficiency matters in every workplace. Hence, you can get your work done faster without the chances of major errors that can put you days behind on your progress.

Cons

Although you can reduce the chances of errors with this approach how editing drop-down lists in Excel can also be time-taking. This problem can be easy to deal with for individuals but not in offices.

Of course, offices deal with loads of data and lists on a daily basis. You cannot give hours to a single list. At one point or another, one needs to take the next step and ensure that their time is used effectively.

Method 3: How to Edit a Drop-Down List Manually

Some people are more comfortable with taking matters into their own hands. For such an audience, learning how to edit drop-down lists in Excel manually can be the best approach. These approaches can help you in learning how to edit drop-down lists in Excel by yourself.

Data Validation

1. Visit the “Data” Tab in the bar at the top

2. Click “Data Validation”

3. Sort the Lists Manually

4. Click “OK” to complete the task

Separate by Comma

1. Go to the “More Number Formats” Tab in the ribbon above

2. Search for “Source”

3. Add/Delete List Items as per your need

4. Separate the entries with a Comma

5. Press the OK button to save your progress

Pros

By learning how to edit drop-down lists in Excel manually, you can restrict specific cells. This facility allows you to use properly formatted data only. Hence, you will not need to worry about any entries being lost on the way.

Of course, data is important for every office. One cannot afford to lose important data on the way. So, you can lock the important entries and allow changes only to those that need alteration in the given circumstances.

Cons

Handling drop-down lists manually can limit the input of content in your drop-down lists. This restriction can be frustrating for some users. Of course, the drop-down lists in every situation are not of the same size. The lists can be big or small, depending on the need. Therefore, these restrictions can be very frustrating.

Reasons for Using WPS Office - Microsoft Free Alternative

Millions of people worldwide are using WPS for presentations. There are many reasons to back up this preference. The best thing about using WPS is that it does not have any restrictions for different platforms. It is compatible with Microsoft Word, Windows, and Mac, all free of cost.

Although Microsoft 365 has gained popularity worldwide over the last decades, there are always better options for the ones seeking to add productivity and efficiency to their work and personal life.

In addition, the WPS interface is extremely easy to use. It can be accessed easily by users of every age for everyday use. It is similar to Microsoft but a lot easier; hence you can follow the same tutorials.

Trustpilot4.8

Recommend WPS Office

• Use Word, Excel, and PPT for FREE, No Ads.
• Edit PDF files with the powerful PDF toolkit.
• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.
• Microsoft-like interface. Easy to learn. 100% Compatibility.

Free Download

5,820,008 User

Algirdas Jasaitis

FAQs

How can I make sure that the drop-down list items are always sorted in alphabetical order?

Many people want their lists in Excel to be sorted alphabetically. It can make sorting easier and ensure that there is no trouble in finding the needed items. Here are some steps that can ensure that your data is always sorted alphabetically while you understand how to edit drop-down lists in Excel.

1. Click “Home” in your Excel file

2. Choose the “Sort & Filter” option from the menu

3. Choose “Sort A to Z” or “Sort Z to A” as per Your Preference

4. Select “A-Z” or “Z-A” on the data tab

What is the maximum number of items that can be added to a drop-down list in Excel?

Many people are concerned about the limit of drop-down lists in Excel. After all, Excel is not limitless. The list can show 32,767 items. The limit of the data validation box is 256 characters (including commas).

The limitations may not be a problem for people who use MS Excel to create small files. However, an individual or organization with bigger needs might find these restrictions to be problematic.

Summary

The above-mentioned methods can be your best guide to understanding how to edit drop-down lists in Excel. All these methods are extremely useful according to the preference of the user and the situation at hand.

However, Microsoft Excel can be a bit overwhelming, especially for new users. That is why more than 500 million users trust WPS Office. WPS is compatible with all systems,s including Mac, Android, iOS, Linux, and Windows. It is best not to miss out on this amazing platform and give it a try today.