This post will show you everything there is to know about dropdown lists in Microsoft Excel.

If you are creating an Excel spreadsheet for other users to input data, then dropdown lists are very useful to control what data they are entering.

This way you can ensure that they will not enter incorrect data which will produce errors in your spreadsheet when calculations are made based on the user input.

Dropdown lists should be familiar as you will frequently find them on the web or while working in other applications.

They enhance the user experience as they make choice selection easy and help to standardize data entry.

This post is going to cover everything about dropdown lists in Microsoft Excel.

Are you ready for the ultimate resource guide to dropdown lists in Microsoft Excel? Get your copy of the example workbook and follow along!

Example Dataset

All the examples in this post will use the above standard set of data within Excel.

How to Create a Dropdown List

There are several ways to populate list items when you create a dropdown list within your spreadsheet.

Use Comma Separated List of Values for List Items

The first method is the most basic where all items are entered in the Data Validation menu as a comma-separated list.

1. Go to the Data tab and click on the Data Validation button in the Data Tools group.

2. This will open the Data Validation menu. Go to the Settings tab and select List from the Allow dropdown.
3. In the Source input box, enter your delimited list using commas as the delimiter between items.
4. Click OK button to create your dropdown list.

📝 Note: Keep the In-cell dropdown option checked as this is what will create the dropdown.

Your selected cell will now have a dropdown arrow to the right of it. Click the arrow, and your list will now show as separate items based on the comma delimiters that you entered.

📝 Note: If you use a comma and space to delimit your list items, Excel will remove the leading space from each item in your dropdown.

The advantage is that the list can be created in a very straightforward manner. All you need to do is to type the list in, or even paste it in from elsewhere.

The disadvantage is that it is hardcoded and is not dynamic. There is no way to change the list based on data entered in the spreadsheet.

Any changes to list items need to be done in the Data Validation menu. If you want to use the same list elsewhere in the spreadsheet, then you either need to copy and paste the list or set up the list from scratch.

Use a Range Reference for List Items

This is the most obvious choice for your list items as the Data Validation menu has a button to select a range from the grid.

From the Data Validation menu click on the Select button found on the right side of the Source input field. This will allow you to select the required range from the grid.

Use a Named Range for List Items

Another way to enter list items in your dropdown is by entering them in a named range, and then referencing the named range in the Data Validation menu.

Follow these steps to create a named range.

1. Select the range of cells to use for the range name for the list of data that you want to use. This must be a single column range.

2. Go to the Formula tab and click on the Define Name command in the Defined Names group of the ribbon. You can edit the range name afterward by clicking on Name Manager in the same group.

3. This will open up the New Name menu. Enter a name for the range in the Name field. This is how you will refer to the range when creating a dropdown list.
4. The cells you selected should be listed in the Refers to field, so check this is correct and update it if needed.
5. Press the OK button.

This will create the name and when you select the range, you will see the name displayed in the Name Box.

You can also use the Name Box to skip the Define Name menu and quickly create a named range. Simply select the range of cells to name and then type the name into the Name Box and press Enter.

Now you can use the named range to create your dropdown list.

1. Select the cell for your dropdown list.
2. Go to the Data tab in the ribbon.
3. Click on the Data Validation button in the Data Tools group.

4. This will open up the Data Validation menu on the Settings tab. In the Allow dropdown, select List from the options.
5. In the Source input box, enter the name of your named range for the list source. Precede it with an equal sign (=). You can also use the Up Arrow selector icon to select the range from the sheet. When you select the full named range, Excel will display the name as your selection.
6. Press the OK button.

Your selected cell will now have a dropdown arrow to the right of it and will show all the items within your named range.

The advantage is that you can use this range name as a single source for many data validation lists. You can easily edit values in the named range, and that will reflect in all the dropdown lists that use that range.

Also, if the range name is moved to another location within the spreadsheet, it will still act as a valid source for all the dropdowns that use it as the source for list items.

The disadvantage is that you will need to set up the range name first of all, but if you have many dropdowns within the spreadsheet using this same source, then it is a very small overhead.

Use a Table for List Items

You can also use an Excel table as the source for your dropdown list.

Check out his post to find out everything about Excel tables if you haven’t seen them before.

Tables are great because it’s easy to add new data to the table. Just type in the row directly below the table and it will absorb the new data into the table.

New entries in the table will then appear in any dropdown lists with the table as a list item source.

Follow these steps to convert your range into a table.

1. Select your data for the table including the header row.

2. Go to the Insert tab and click on the Table button in the Tables group of the ribbon.

3. This will open up the Create Table menu with the range selected. Make sure the My table has headers option is checked if your range had a column heading included. Press the OK button after you’ve checked everything is correct.

4. Select the table go to the Table Design tab and give your new table a name. Type over the generic Table1 name with the new name and press Enter.

Now you will be able to create a dropdown list based on this table.

1. Select the cell for your dropdown list and click on the Data Validation button in the Data tab.

2. Select List in the Allow field.

= INDIRECT ( "Cars[Model]" )

3. Enter the above formula into the Source box. This assumes that your table is called Cars, and that Model is a column header in that table.
4. Press the OK button.

Your selected cell will now have a dropdown list based on the Model column from your table.

Using a defined table has huge advantages over the previous methods described.

You can use a source that has multiple columns, and you can easily select which column you want to use by changing the header name within the source formula.

If you require separate dropdowns for both columns in the table, all you need to do is copy and paste the cell with the validation into another location, and alter the column name in the source formula.

This is easier than creating a separate single-column range name for each column of the data!

It is also easier for you to follow if you have several dropdowns all being driven off of one table. The table is also dynamic and can be easily changed or updated with new data that will automatically flow into the dropdown list.

📝 Note: If you change the table name, you will need to update the formula used in the Data Validation Source input to reflect the new name. This is because the name is referenced by a hard-coded text string.

Use a Dynamic Array Reference for Dropdown List Items

This is the most flexible method for adding list items in a dropdown list.

Start by adding a table containing your dropdown list of items. In an adjacent cell, insert a formula that references the entire column from the table.

=Cars[Model]

In this example, the above formula has been entered in cell D3. You can see this creates an array that is the exact same as the table column that it references.

If you add, edit or delete any items in the table, the array will update accordingly to match.

You can then reference this dynamic array inside the Data Validation menu as =$D$3# the Source input. The hashtag means it will reference the entire array.

When you add, edit, or delete items in the table, items in the array will update. Because the dropdown list references the array, it will also update with the same changes.

How to Add Items to an Existing DropDown List

Once you have created dropdown lists you will probably need to make changes, such as adding, editing, or deleting items in the list.

In the example of cars that are used here, new models are frequently added, or older models are retired and might need to be removed.

Editing Dropdown List Items from a Comma Separated List

You can change the items in a dropdown list with the following steps.

1. Select the cell which contains the dropdown list to edit.
2. Go to the Data tab.
3. Click on the Data Validation button in the Data Tools group of the ribbon.
4. This will up the Data Validation menu and you can add, remove, or edit the list items in the Source input field.
5. Press the OK button.

📝 Note: This will only update the items in the selected dropdown list. Other similar dropdown lists in the workbook will remain unchanged.

Editing Dropdown List Items from a Named Range

The data in the single column named range can be changed easily and will reflect through to any dropdown that uses that named range.

Adding a new item is a bit more involved as you will need to extend the named range. You can do this from the Name Manager.

1. Go to the Formulas tab.
2. Click on the Name Manager button in the Defined Names section.

3. Select the named range to update.
4. Update the range reference in the Refers to field to add more cells to the range.
5. Click on the checkmark to the left of the Refers to field.
6. Press the Close button.

The Named range will now include the cells which you added to the range reference and you can enter the new list items there.

Note: Unfortunately, typing new items at the bottom of your named range won’t automatically extend the named range. You need to update the range reference manually to ensure new items are included in your dropdown lists.

Editing Dropdown List Items from a Table

Because tables are dynamic, it is far easier to add, edit, or delete list items.

When you add a new item at the bottom of your table, the table will automatically expand to include the new row. This means your new item will appear in the dropdown list.

Editing or deleting an item is just as easy!

Type over any item in the table to edit it.

Right-click on an item and select Delete ➜ Table Rows. This will delete the entire row in the table and remove the item from the dropdown list.

Editing Dropdown List Items from a Dynamic Array

This is the exact same process as editing a dropdown list from a table.

Changes you make to the table will propagate to the dynamic array which drives the dropdown list.

How to Remove a Dropdown List

If you no longer require a particular dropdown list within your spreadsheet, it is very easily removed. Follow these steps to remove a dropdown list.

1. Select the cell with the dropdown list to remove.
2. Go to the Data tab.
3. Click on Data Validation in the Data Tools group.
4. Press the Clear All button in the Data Validation menu.
5. Press the OK button.

This will only remove the dropdown list from the selected cell and not any other copies of the dropdown list.

Copy and Paste a Dropdown List

You may want to use your dropdown list elsewhere in the workbook and this can easily be done by copying and pasting the cell to a new location.

You can use the Paste Special command to paste in only the data validation in the cell.

Use Ctrl + C to copy the cell which contains the dropdown list.

Select the cell that you want to copy the dropdown to and then right-click on the cell, and choose Paste Special from the options.

You can also use the Ctrl + Alt + V keyboard shortcut to open the Paste Special menu.

The Paste Special menu will appear and you can select the Validation option and click on OK.

You will now have an exact copy of the dropdown list in your new cell and it will use the same source for its list items.

Create a Dropdown List from Another Sheet

If you want to copy and paste or cut and paste a dropdown list into a new sheet, you might run into a problem when the list items were created using a range reference.

If the range reference was originally created within the same sheet, then it won’t contain a reference to the sheet name.

='My Sheet'!$B$3:$B$12

You will need to update the range reference with the sheet name like in the above example.

Search a Dropdown List in Excel Online

The online version of Excel has a handy feature that allows you to search the dropdown list by just typing in a few characters.

This will narrow down the available list of options to choose from in the dropdown. This is extremely useful when dealing with a long list of items!

For example, using the list of car models, you can type Ac into the cell and that will display all entries in the list beginning with Ac. In this case, it will display Accord and Accent.

In the case of a very long list, this could pull out several entries beginning with F.

As you enter more letters, the number of entries in the search list will decrease to list items with a partial match. You can then click on a value in the search list dropdown to select it.

Case Sensitive Dropdown List Items

You can make your dropdown list case sensitive by entering your list options as a comma separated text string.

If a user enters a value that does not correspond to an item in the list in both value and case, then an error message will appear.

This will ensure that the final value in the validation cell matches the case of the list items.

📝 Note: Any other source used for your list items will allow any variation of case to be entered into the cell.

Remove Duplicates from List Items

When you select data for list items, you may find that there are duplicates within that data.

Duplicates that are in the source data for the list items will show up in the list and there is no option in the Data Validation menu to remove them.

If you include them in the dropdown list, this can cause confusion for the user when they have multiple choices which are the same. It’s best to remove any duplicate values from the list of items.

How you remove the duplicate values will depend on whether your version of Excel has dynamic arrays.

Using a Dynamic Array

When you have dynamic arrays, getting a list of unique items is easy. You can use the UNIQUE function to return the items with the duplicates removed.

= UNIQUE ( Cars[Make] )

You can use the above formula which references a table named Cars that contains a column named Make. This column contains a few repeated items.

Notice the UNIQUE function returns all the items from the table but does not repeat any item of them.

Now you can reference this dynamic array as the list of items for the dropdown list.

Sort List Items in a Dropdown List

The Data Validation menu gives no option to sort the list items into alphabetical order.

Sorting the list of items will help make finding an item in a long list much easier.

Fortunately, sorting list items can be done outside of the Data Validation menu and is a fairly easy implementation.

Using a Dynamic Array

With dynamic arrays, sorting is also quite easy. You can use the SORT function to sort your list items for the dropdown list.

= SORT ( Cars[Model] )

The above formula can be used to sort a column in alphabetical order and the results can then be references in your dropdown list source input.

Edit All Dropdown Lists

When your spreadsheet has many exact copies of the same dropdown list, you may need to update them all when adding, editing, or deleting list items.

This is especially true when you are using drop-down lists with comma-separated list items.

Thankfully, there is an easy option to update all your dropdown lists at the same time.

Follow these steps to update all your dropdown lists that use the same settings.

1. Select one of the dropdown lists to edit.
2. Go to the Data tab.
3. Select the Data Validation command in the Data Tools section.
4. Make any changes to the Source list.
5. Check the Apply these changes to all other cells with the same settings option.
6. Press the OK button.

When you check this option in the Data Validation menu, you will see all dropdowns with the same settings will get selected in your sheet. When you press the OK button, the changes are made to all these cells.

Note: This will only affect dropdown lists in the current sheet! If you have dropdowns using the same settings but located in other sheets, then you will need to update those sheets separately.

Error Alerts for Dropdown Lists

The best thing about drop-down lists is they force users to input data correctly.

If a user tries to skip selection from the dropdown list and instead enter their own data, Excel will show a warning and entry will be prevented.

Data Validation for lists gives you the flexibility to change the default error alert message and also to change the icon used in the error message.

You can customize the error message in the Error Alert tab of the Data Validation menu.

1. Make sure the Show error alert after invalid data is entered option is checked. It should be enabled by default.
2. Select the Style of alert.
   • Stop will prevent the user from entering any value not in the list.
   • Warning will alert the user the item is not in the list, but will let them decide if they still want to enter the value or not.
   • Information will only alert the user the item is not in the list but will keep the value entered.
3. Add a Title for the alert.
4. Add an Error message for the alert.

Press the OK button once you’ve adjusted the error alert settings to your liking.

Now when a user tries to enter a value into the cell which is not in the list, a pop-up alert will show with your custom message. The above example shows a Warning alert that gives the user the Yes or No option to continue with the entry.

Input Messages for Dropdown Lists

You can create an input message for your dropdown list. This will appear when the user selects the cell containing the dropdown list.

Click on the Input Message tab in the Data Validation pop-up window, and enter a title (optional) and a message for the user to see.

You can create an input message from the Input Message tab of the Data Validation menu.

1. Make sure you check the option to Show input message when cell is selected. This will allow the pop-up to display when the cell is selected.
2. Add a Title for the pop up message.
3. Add the Input message to be displayed in the pop up.

Press the OK button to save the pop-up message on the dropdown list.

Now when you select the cell with the dropdown list, a pop-up will show with your custom message. This is a great way to add any required instructions for the spreadsheet user as it doesn’t even require the use of a dropdown.

Allow Entries Not in the Dropdown List Items

You may have a situation where you are using a dropdown in a cell, but you want to allow the user to enter values outside of the dropdown list.

This can be done from the Error Alert tab in the Data Validation menu.

Uncheck the Show error alert after invalid data is entered option.

This will give the user the option to use the dropdown list to select a value, but will not require it.

If a user does not pick from the list it will suppress the error message and allow any value to be entered in the cell.

Create a Dropdown List with VBA

Sub CreateDropdown()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes, No, Maybe"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

You can use VBA to create a dropdown list.

The above VBA code can be used to create a basic dropdown from a comma separated list of items.

Create a Dropdown List with Office Scripts

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();

	selectedSheet.getRange("B2").getDataValidation().setRule({ list: { inCellDropDown: true, source: "Yes, No, Maybe" }});
	selectedSheet.getRange("B2").getDataValidation().setPrompt({ showPrompt: true, message: "", title: "" });
	selectedSheet.getRange("B2").getDataValidation().setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.stop, message: "", title: "" });
	selectedSheet.getRange("B2").getDataValidation().setIgnoreBlanks(true);
}

You can use Office Scripts to create a dropdown list.

The above TypeScript code can be used to create dropdown list based on a comma separated set of list items.

Keyboard Shortcuts for Using Dropdown Lists

There are useful keyboard shortcuts that you can use in conjunction with a Data Validation dropdown list.

• Alt + Down Arrow will activate the dropdown list and is the same as clicking the down arrow on the control.
• Up and Down Arrow keys will allow you to navigate and move up and down the dropdown list during selection.
• Enter will choose the item value that is highlighted in the dropdown list.
• Alt + A, V, V will take you straight to the Data Validation menu.

You can customize the QAT by adding your favorite commands so they are easily accessible at all times.

This will allow you to create your own keyboard shortcuts because every command you add to the QAT will get its own keyboard shortcut based on its position.

For example, the second command in your QAT can be used by pressing Alt + 2 on your keyboard.

This means you can add the Data Validation command to the QAT and access it with a customized keyboard shortcut.

Go to the Data tab and right-click on the Data Validation command. Select Add to Quick Access Toolbar from the menu and the command will be added to your QAT.

In this example, the data validation command is the third item in the QAT so you can press Alt + 3 to access it with the keyboard.

When you press the Alt key, the hotkey labels will show you what key to press next in order to access the commands.

Create a Dropdown List from Data Above the Current Cell

A useful feature in Excel is the ability to create a dropdown list from the data directly above the current cell.

1. Select the cell directly below a column of data values.
2. Right-click on the cell and select Pick From Drop-down List.

A dropdown will be instantly created in that cell based on the values above. The nice thing about this feature is it will only show a list of unique values and they will be sorted in alphabetical order.

The downside is that the dropdown list is not permanent, and the user has to right-click the cell again each time they want to use it.

Find All Dropdown Lists in a Sheet with Go To Special

Data Validation dropdown lists are hard to find within an Excel workbook. They remain invisible until the cell is selected, and the selector key appears to the right of the cell.

There is a way of highlighting all data validation cells on a spreadsheet.

1. Select a cell that contains the dropdown list you want to find.

2. Go to the Home tab.
3. Select Find & Select from the Editing section.
4. Select Go To Special to open up the Go To Special menu.

You can also press F5 and the Go To window will open, then you can press the Special button to open the Go To Special menu.

5. Select the Data Validation option.
6. Select Same from the Data Validation options.
7. Press the OK button.

This will select all the cells in the sheet with the exact same data validation. This means it will differentiate between lists with slightly different list items!

📝 Note: The All option will find and select other types of data validations in the sheet and not just lists.

Dropdown List Template Tutorial

Dropdowns are so important and so widely used in Excel, that there is a dedicated tutorial template for dropdown lists which can be accessed from the File menu.

Go to the File tab and click on the Drop-down tutorial template then click on the Create button.

This template will take you on a guided and interactive tour of dropdown lists.

You can also download the template here.

Conclusions

Very often the wrong input can lead to errors in your spreadsheets.

Data Validation dropdown lists are very useful, for guiding or restricting a user as to what input they can use in certain cells to help avoid errors.

There are many ways of constructing dropdown lists, including from a comma-separated list, a range, a named range, a table, or a dynamic array.

A simple dropdown list is usually all that is required in most cases, but advanced setups such as dependent lists can be achieved with a bit of effort.

Advanced options are also available with your dropdown lists such as input messages and error alerts.

These all make dropdowns a versatile tool you need to start using in your spreadsheet solutions.

Are you using dropdown lists in your Excel workbooks? Do you have any special dropdown list tips I missed? Let me know in the comments below!