Excel drop-down list is a feature in Microsoft Excel, similar to a list box, that allows the user to choose one value from a list of values.
It lets you create some valid choices in a drop-down box that appears when anyone using your spreadsheet moves into that cell.
If you are looking for a comprehensive guide on how to create or edit drop-down list in Excel, and make it behave the way you want, then this guide is for you.
Without further ado, below are several ways you can create and edit drop-down list in Excel with Examples.
How to create Yes/No drop-down list
In this first approach, we are going to create a Yes or No drop-down list that allows users to select between either yes or no in the cell.
Therefore, to create a Yes/No dropdown list in Excel, follow the steps below:
- Select the cell or cells that will contain the Yes/No list.
- Go to Data > Data Tools > Data Validation.
- Select List from the Allow: drop-down box.
- Type in your list and separate each item with a comma.
- Click OK to apply changes.
As seen in the above screenshot, the comma between the Yes and No indicates that they are two different items in the list.
Likewise, if your list will contain days of the week, you can enter them in the source field and make sure to separate each item with a comma.
See screenshot:
This is how you may create a drop-down list in Excel.
For more advanced and real-world examples, keep reading.
How to Create a Drop-Down List in Excel – January – December
In this section, I’ll show you how to create a drop-down list to contain all the 12 months (January to December).
Without further ado, let’s get started:
- Define your list items into a row or column.
These are the items that will show when a user clicks on the drop-down box.
Since we want to display the 12 months in the drop-down box, let’s enter all the months’ names in column A starting from A1.
See screenshot:
- Select the cell or cells that will contain the drop-down list.
You can create the drop-down list to several cells at once. To do that, instead of selecting one cell, select all the cells that will contain the list.
In our example, let’s select cell C4.
See screenshot:
- Go to Data→Data Tools →Data Validation.
The action in this step will bring out the Data Validation dialog box.
- Under the Settings tab, click the Allow: drop-down box and select the List option from the list.
- In the Source: field, specify the range of cells that contains your list and click OK.
In our example, A1:A12 is the range of cells that contains the months’ names.
However, to specify the range, do not type it manually because you can easily mistype something.
Instead, click in the Source: field, move to the worksheet and select the range containing the list.
If you follow the above steps very carefully, you can also create Excel drop-down lists in your worksheets.
Excel Drop Down List from Table
Let’s say you want to create an invoice template that has drop-down lists in the products column.
This way, users can enter items using the mouse to click-in products. This prevents the spreadsheet user from having to type out full product names.
Take the following invoice template for example:
This invoice template is retrieving both the product names and prices from data on a different sheet which is shown below:
The list contains 16 products. Since we used this product list as the drop-down for the invoice, it’ll also contain 16 items when you click on the drop-down menu.
However, if you add more products to the list, your invoice will not automatically adjust to the change. You’ll have to go to the data validation settings to update the source criteria.
If you want to make the invoice drop-down to automatically adjust the list of choices as you update your product catalog, then you’ll have to put the list in a table by going to Insert → Tables → Table.
This way, Excel will automatically update the list of options when you remove or add new products to the products list.
How to Edit the Excel drop-down list
You can edit any Excel drop-down list by adding more items to the list, removing some items or delete the drop-down box altogether.
If your source of the list is from a range of cells, converting the range to an official Excel table will allow your dropdown to automatically update the list as items are being added or deleted from the table (This technique is explained in the preceding section).
However, if you are using comma-separated values for the source field, you’ll have to manually edit the list.
To edit Excel drop-down list, follow the steps below:
- Select the cell or cells that contain the list you want to edit.
- Go to Data → Data Tools → Data Validation.
- Select List from the Allow: drop-down box.
- Update the old list with the new list, and separate each item with a comma.
- Then click OK to apply changes.
For instance, in the Yes/No drop-down list, if you want to change the order of the list so that the No will come first before the Yes.
Just follow the above steps and when you reach the source field, edit the list to bring the No first before the yes. Excel will update your drop-down box with newly defined list items.
Controlling the behavior of Excel drop-down cells.
Despite your painstaking effort to prevent bad data entry by employing the drop-down technique, someone somewhere might still input a wrong information into the cell.
You need to address this issue by responding wrong data entry gods with polite a message explaining the problem.
Using Error Alert, you can prevent users from entering data you didn’t define in the drop-down list.
Meanwhile, you can also display a message addressing the user that she has entered an incorrect value.
To do so, below are the steps:
- Select the cell or cells you wish to apply these settings to.
Let’s use the month’s drop-down list for this illustration. Thus, if anyone enters any value that is not in the months list, Excel will stop that person with an error message.
So, select the cell that has the drop-down box.
- Go to Data → Data Tools → Data Validation.
The action in this step will bring out the Data Validation dialog box.
Perform all the necessary drop-down list settings under the settings tab.
- Click the Error Alert tab in the Data Validation Window
- Turn on the “Show error alert after invalid data is entered”
This tell tells Excel to lookup out for invalid information. If anyone enters the wrong information (like misspell a month), the error window appears with an error message.
- Select your error message style and input your message and its title.
The style drop-down field contains a list of error message styles. And Excel will display a button depending on the style of the error message you select.
Input the message title in the “Title:” field. For example, you could write: Invalid Month.
Also, input the error message in the “Error message:” field. For example, you could write: Please the month you entered does not exist. Use the drop-down box to select a month.
See screenshot:
Know that different error styles have different effects. The various styles are briefly explained below:
- Stop: The stop option completely prevents the person from entering invalid data. It displays an error message with three buttons – Retry, Cancel and Help. The Retry button will return the cell back into edit mode for the user to retype the correct value. The Cancel button will reverse the change by returning the cell to the previous value. And the Help button will take you to Microsoft’s website where you can get more information about the error.
- Warning: The warning style gives the person Yes and No options to decide whether to go ahead with their input. The Yes button makes Excel to accept the input, even if it may violate the validation rules. The No button will reject the input and return back to the cell in edit mode so that you can retype the correct input.
- Information: The information error message style also presents you with two choices – OK (to accept) and Cancel (to reject). When you click the OK button, Excel will accept the new (invalid) data. And when you click the Cancel button, Excel will reject the new (invalid) data and reverse the change to the previous value that was in the cell.
Note: Even though you may set all the validation rules, if the “Show error alert” checkbox is turned off, no error message will display when the wrong data is entered.
What is Excel drop-down list?
With the above practical guide on how to create drop-down list in Excel, you now know how the whole thing works.
However, if you are not yet satisfied, join me to rant on some theory.
For those who are new to Excel drop-down list, you’ll want to know what it is in the first place. The following screenshot illustrates what it is all about.
Below is a spreadsheet I created for my Excel VLOOKUP Examples article. It uses two drop-down lists to select a student’s name and course to retrieve the student’s scores for the selected course.
See screenshot:
As illustrated above, Excel drop-down list is one of the most common data validation features that allows you to provide options for users to choose from.
This way, you can restrict your spreadsheet users to select one of the options you’ve defined in your list.
You can also enter values manually. However, if you enter a value that doesn’t match one of the drop-down values you defined, Excel assumes that your entry is invalid.
Through data validation Error Alert settings, there are several ways you could make your drop-down cells behave after a user entered an incorrect value.
For example, when a user types a value that doesn’t match the data validation restrictions you defined, you can make Excel to accept that value, or you can reject it with a message that she has entered an incorrect value.
Why should you use Drop-Down list in Excel
Using a drop-down list in Excel has a lot of advantages over manually entering data:
- Your spreadsheet user can fill cells with the mouse, which is much faster and easier than typing.
- It prevents users from making spelling mistakes since the user didn’t type the response herself.
- It presents your spreadsheet users with possible responses beforehand.
- User response can be integrated into formulas as seen in the invoice template above.