Creating an Excel drop down list

If you have ever seen a drop down menu in Excel and wondered how to set one up, you are in the right place. Creating an Excel drop down list is a simple way to improve accuracy, speed and consistency in your worksheets. In this post I will show you exactly how to create, edit and manage drop down lists so your data entry becomes cleaner and consistent.

Why use an Excel drop down list?

Creating an Excel drop down list is useful when you want to control how information is entered. A drop down menu:

• Helps users enter information quickly
• Prevents inconsistent spelling or random abbreviations
• Improves the reliability of your sorting and filtering
• Creates a clean and user friendly interface

In the example below, you can see inconsistent entries in the Source and Course Name columns. These inconsistencies make future data analysis more difficult. Creating an Excel drop down list ensures everyone selects from the same set of options.

Let’s look at how we can create a drop down list to allow users to select from a pre-defined list of options. In my example, I want to have a column in my worksheet where users must select from a list of specific Locations (column A) and I would also do the same thing for the Source (column G) and Course Name (column H) columns.

Create the list items for the Drop Down list

To create an Excel drop down list we will use the Data Validation feature. Data Validation essentially allows you to specify the type of information you wish to have entered into a cell, row, or column. You can be very specific with the requirements where you can choose between a specific number of characters and even specify only dates or numbers can be entered.

To create a list of items:

1. Open Microsoft Excel.
2. If you wish to work on an existing file then press Ctrl + F12 to display the Open dialog. Locate the file and open it.
3. If you want to start from scratch you can work directly from the new blank workbook displayed.
4. We will use the first blank worksheet (Sheet1) as our main data entry sheet. We will need a second worksheet to store the options we want to be displayed in the drop down list.
5. Click the New sheet button at the bottom of the window:

6. A new blank worksheet will be added.
7. Click on the new worksheet to open it, this will hold the options to be used in our drop down list.
8. In cell A1 type the heading Location.
9. In cell A2 type the first location e.g. Sydney.
10. Press Enter and continue to add approx 6-8 different locations into the list.
11. You can format the heading as needed and resize the column if you prefer. You can even use the Sort function to put the list into alphabetical order:

Display the Excel drop down list in your worksheet

Now that you have created the list of options you want included in the drop down menu, you can choose where the Excel drop down list will appear in the worksheet. You can apply data validation to individual cells, groups of cells or an entire column or row.

1. Return to Sheet1.
2. Add any column headings you want to use or copy the example below.
3. Highlight all of column A by clicking once on the column heading:

4. Select the Data tab from the Ribbon and locate the Data Tools group of commands.
5. Click the Data Validation button and choose Data Validation from the drop-down menu:

6. The Data Validation dialog box will appear:

7. From the Allow drop-down, select that you want to allow a List.
8. You now need to select the cells which contain the list of options. This is located on the second worksheet.
9. Click the Collapse button on the right side of the Source field:

10. Now you will need to highlight the cells which hold the list items you want displayed in your drop down list.
11. Select Sheet2 and highlight the cells containing each location, do not include the heading (I’ve highlighted A2:A9):

12. Click the Expand button to bring the Data Validation dialog box back:

13. Click OK.
14. Place your cursor in any cell in column A and you will see a drop-down arrow appear on the right side of the cell:

15. Click the drop-down arrow and choose an option from the list.
16. Repeat this process to create drop-down lists in any other columns within the worksheet.

How to edit your Excel drop down list

If you need to add additional options to the drop down list, or you’ve made a spelling error, you can easily edit the list within the worksheet.

To edit drop down list items:

1. Open the second worksheet which contains all the list items you have created.
2. Edit the entries as normal.
3. The edited entries will automatically be available via the drop down lists on your data entry worksheet.

To add more options to a drop down list:

1. Open the second worksheet which contains all the list items you have created.
2. Add in any additional options you wish to include.
3. Go back to your data entry worksheet.
4. Highlight the column containing the corresponding drop down list.
5. Select the Data tab from the Ribbon.
6. Click the Data Validation button and choose Data Validation from the options:

7. Edit the Source field by clicking the Collapse button:

8. Highlight the new range of cells containing your list options.
9. Expand the dialog box again and click OK.
10. Your new list items should be available through the drop down list in your data entry worksheet.

Allow values that are not in the drop down list

You may want users to have flexibility to type their own values rather than only use the pre-set list.

1. Highlight the cell(s) or entire column with the existing drop down list.
2. Select the Data tab from the Ribbon.
3. Click the Data Validation button and choose Data Validation from the options.
4. Select the Error Alert tab:

5. Untick the Show error alert after invalid data is entered checkbox.
6. Click OK.
7. This will allow users to enter alternative options to those displayed in the drop down list.

How to delete a drop down list

If you no longer need a drop down list, you can remove it.

1. Highlight the column containing the drop down list.
2. Select the Data tab from the Ribbon.
3. Click the Data Validation button and choose Data Validation from the options.
4. Change the Allow field to Any value:

5. Click OK.
6. The column will now allow any type of value to be entered.

I hope this helps you to begin creating an Excel drop down list in your worksheet. Ready to master more Excel features? Explore my collection of Excel tutorials and tips to boost your spreadsheet skills and productivity! Feel free to comment below with any questions.