How To Create Excel Drop Down List – Step by Step

By |2019-02-14T23:42:35+00:00February 9th, 2019|

Excel drop down list illustrated

Excel drop-down list is a perfect way to reduce data entry errors.

Also, it speeds up data entry.

It lets you create some valid choices in a drop-down box that appears when your spreadsheet user moves into that cell.

You can limit the choices of your drop-down list so that users cannot enter data you didn’t define.

For example, Excel yes/no drop-down limits users to only select one of the two choices.

Likewise, you can also allow users to enter data that’s not part of the drop-down list.

For example, with Excel yes/no drop-down list, users can be given the chance to enter values other than the two drop-down options.

If you are looking for a comprehensive guide on how to create drop down list in Excel, and make it behave the way you want, then this guide is for you.

Download the excercise file and Keep sliding down the page to learn how to perform all these drop-down techniques.

Download Excercise File

How to create an Excel Drop Down List

In this section, I’ll show you how you can create a drop-down list to contain all the 12 months (January to December).

Without further ado, let’s get to work:

  • Type 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:

Excel drop down list source
  • Select the cell or cells that will contain the drop-down list.

You can create the drop-down box in 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:

Excel drop down box cell
  • Go to Data → Data Tools → Data Validation.

Excel drop down: Navigate to Data validation

The action in this step will bring out the Data Validation dialog box.

Excel drop down list: Data validation dialog box
  • Under the Settings tab, click the Allow: drop-down box and select the List option from the list.

The Allow drop down field
  • In the Source: field, specify the range of cells that contains your list and click OK.

In our example, the range of cells that contains the month’s names is A1:A12 – that’s the January to February list we defined in the first step.

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.

Excel drop down list source range

If you follow the above steps very carefully, you can also create Excel drop-down lists in your worksheets.

For more advanced and real-world examples, keep reading.

How to create Yes/No drop-down list

In the beginning-steps to create an Excel drop-down list, we used the cell range containing the 12 months as the source.

However, if your list has few items, like two items as in Yes/No kind of situation, you can enter the items directly in the source field without referring to a cell range.

Therefore, to create a Yes/No dropdown list in Excel, follow the steps below:

  • Select the cell or cells that will contain the 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.

See screenshot:

yes no drop down list

As seen in the above screenshot, the comma between the Yes and No indicates that there are two different items in the list.

The same way, 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:

Excel drop down: comma seperated values

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:

Excel drop down list illustrated

This invoice template is retrieving both the product names and prices from data on a different sheet which is shown below:

Drop down from another sheet

The list contains 16 products. Since we used this products 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 InsertTablesTable ( or pressing Ctrl+T).

This way, Excel will automatically update the list of options when you remove or add new products to the products list.

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 to the wrong data entry gods with a polite 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 months 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 for months.

  • Go to Data → Data Tools → Data Validation.
Excel drop down: Navigate to 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
Excel drop down list: Error Alert Tab
  • Turn on the “Show error alert after invalid data is entered”

This 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.

Excel drop down list: turn on alert checkbox
  • 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 an error message you select.

Excel drop down list: error style

Input the message title in the “Title” field. For example, you could write Invalid Month in the title field.

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:

Excel drop down list: error message

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 wrong data. It displays an error message with three buttons – the Retry button, Cancel Button and the Help button. 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.
Excel drop down list: stop error alert
  • Warning: The warning style gives the person a Yes and No options to decide whether to go ahead with their input. The Yes button makes Excel 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 the user can retype the correct value.
Excel drop down list: warning message

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 (but invalid) data. And when you click the Cancel button, Excel will reject the new (but invalid) data and reverse the change to the previous value that was in the cell.

Excel drop down list information message box

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 user enters a wrong data.

How to Edit Excel drop-down list

You can edit the 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 official Excel table will allow your drop down 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 (as in yes/no drop down), 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.

What is Excel drop down list?

With the above practical guide to create a 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 this, you’ll want to know what drop-down lists are 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 cells to select a student’s name and course to retrieve a student’s scores for the selected course.

See screenshot:

What is Excel drop down list

As illustrated above, 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 accept that value, or 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:

  1. Your spreadsheet user can fill cells with the mouse, which is much faster and easier than typing.
  2. It prevents users from making spelling mistakes since the user didn’t type the response herself.
  3. It presents your spreadsheet users with possible responses beforehand.

User response can be integrated into formulas as seen in the invoice template above.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I Know You Better!
Subscribe To My Newsletter
Be the first to get latest Excel updates and
exclusive content straight to your email inbox.
Yes, I want to receive updates
No Thanks! I Don't Want to Learn Excel.
close-link

Not Sure What to Learn in Excel?

Sign Up For My Actionable Excel Techniques You Can Use Right Away!
Send me Updates
You'll Get One Email Every Week!
close-link