People rely on Microsoft Word and Excel for work. There are many useful tools provided for users and some of them are less used but indeed powerful and productive. In this article on MiniTool Website, we will teach you one excellent function – Excel drop-down list – to help you work more effectively and better.

With the help of an Excel drop-down list, you can easily allow users to choose an item from a pre-defined list and that can be a great way to ensure that only valid data is entered into a cell. Drop-down lists help you to organize your data and limit the number of entries people can make to each cell.

Microsoft Excel 2019 Download Free for Windows/Mac/Android/iOS
Microsoft Excel 2019 Download Free for Windows/Mac/Android/iOS

In this post, we will show you how to free download Microsoft Excel 2019 for Windows 11/10/8/7, macOS, Android, and iOS devices.

Read More

Create Excel Drop-Down List

To create Excel drop-down list, you can use a cell range which is the most common way.

Step 1: First of all, you need to create an original sheet that contains the source data. Then create a destination sheet where you would like the drop-down list to be. Choose one cell that is going to have the drop-down list.

Step 2: Switch to the Data tab on the ribbon and in the Data Tools section, choose Data Validation.

Step 3: In the pop-up window, under the Settings tab, choose List in the Allow drop-down menu and check Ignore blank and In-cell dropdown.

Step 4: Then click on the Source box and go to the original sheet and simply select the range of cells by dragging the cursor from the first cell down to wherever your last cell is located. The data will be automatically selected and click OK to save the change.

click OK

You will now have a drop-down list menu located in the cell you have chosen.

Manage Excel Drop-Down List

To manage a drop-down list in Excel, you can allow other entries, and add or remove items to Excel drop-down list. Here are specific steps.

Allow Other Entries

If you add a value that is not in the list, Excel will show you an error that tells you the value doesn’t match. To add other values to the drop-down list, you can do it as follows.

Step 1: Go to the Data tab and in the Data Tools section, choose Data Validation.

Step 2: Go to the Error Alert tab in the pop-up window and uncheck the option of Show error alert after invalid data is entered and click OK.

uncheck the option

At last, you can enter the value not included in the list.

Add/Remove Items to Excel Drop-Down List

You can insert new items into the list without opening the Data Validation dialog box and changing the range reference.

Step 1: Locate the cell and choose one item from the list.

Step 2: Right-click on the item and choose Insert.

Step 3: Check the option of Shift cells down and click OK.

check Shift cells down

Step 4: Then you can type what you want to add in the cell and that can be added successfully.


Tip: Excel automatically changed the range reference and you can check it via Data Validation.

If you want to remove the item, you can right-click on the item and click Delete from the drop-down menu. Then select Shift cells up and click OK.

Remove Excel Drop-Down List

To remove the entire Excel drop-down list, you can follow the next steps.

Step 1: Locate the drop-down menu cell and choose Data Validation from Data Tools.

Step 2: In Settings from the pop-up window, click Clear All and then OK.


Note: If you want to remove all other drop-down lists with the same settings, check the option of Apply these changes to all other cells with the same settings.

How to Activate Microsoft Excel to Access All Features – 4 Ways
How to Activate Microsoft Excel to Access All Features – 4 Ways

This post teaches you how to activate Microsoft Excel app. 4 ways are provided with detailed explanations.

Read More

Bottom Line:

There is a detailed introduction to Excel drop-down list. Multiple types of Excel drop-down lists have been cleared. More functions and features will be listed by Minitool and you can expect that. Hope this article can help resolve your concerns.

  • linkedin
  • reddit