How to Create a Drop-Down List in Excel Sheet? 7 Easy Steps

In this article, you will learn a practical way of how to create a drop-down list in Excel sheet. A dropdown list, also known as a drop-down menu, makes it an easy way to input data by offering predefined options within a particular cell. With the help of a dropdown menu list, we are utilizing the “Data Validation” feature in your Excel spreadsheet. It allows you to identify a range of data values or a specific data list for users to choose data from. This works to streamline data input and helps to work more efficiently in an Excel spreadsheet.

In Excel, the drop-down list is beneficial when you work to bound data entry choices, reduce errors, and ensure reliability in your Excel workbook. It enhances your Excel’s skill and the way or working with datasets.

How to Create a Drop-Down List in Excel Spreadsheet?

Follow the below steps to easily create a dropdown menu list in an Excel spreadsheet. It empowers you to efficiently manage and organize your data usability.

1. Open Your Excel Spreadsheet:
   Start Microsoft Excel in a computer or laptop and open the workbook in which you want to create a drop-down menu list. For example, you want to create a drop-down list in Sheet1.
2. Create the Source Items for the Dropdown Menu List:
   First, you may create a source item for the drop-down menu list in Sheet2 (e.g country names, city names, fruit names, etc.), as you can see in the above screenshot.
3. Select the Cell or Range:
   Now go to Sheet1 and click on the cell (e.g. B2). B2 is the cell where you want to create the dropdown list, as you can see in the above screenshot.
4. Go to the “Data” Tab:
   Choose the “Data” tab in the Excel ribbon located at the top of the screen (highlighted in the above screenshot).
5. Go to “Data Tool” Group:
   In the “Data Tools” group, find and choose the “Data Validation” option. A dialog box will appear showing three tabs. (a. Setting, b. Input Message, c. Error Alert)
   a. Setting:
   Click on the “Allow” dropdown arrow and select “List”. Check both boxes of the Ignore Blank and In-Cell Dropdown.
   In the “Source” field, either type the values you want in the dropdown list separated by commas or select a range of cells in your workbook that contains the list, e.g. A2:A8 (you can see in the above screenshot).
   b. Input Message:
   If you want to show a pop-up message on the screen when you select the cell, check the Show input message when cell is selected box and type, e.g. “Country” in the Title box and “Please select a country name from the list” in the Input Message.
   c. Error Aler:
   If you want to show a pop-up message when you enter data in the specific cell that’s not in your pre-defined list. Check the  Show error alert after invalid data is entered box. Choose “Stop” in the Style box, type “Invalid Entrie” in a title, and type “Please select a country name from the list” in the Error Message box.
6. Click “OK”:
   Once you’ve specified all the above, click “OK” in the Data Validation dialog box to apply the dropdown list to the selected cell or cells.
7. Test the Dropdown List:
   Click on the cell with the dropdown list, and you will see a small arrow appear. Click on the arrow to reveal and select from the predefined options.
8. Edit or Update the Dropdown List (Optional):
   To modify the dropdown list, return to the “Data Validation” dialog box by selecting the cell, clicking on “Data Validation,” and adjusting the source values as needed.
9. Save Your Workbook:
   Save your Excel workbook to retain the changes made to the dropdown list.

By following the above steps, you can easily create dropdown lists in your Excel spreadsheet, providing a convenient and controlled way for users to input data and promoting consistency in your spreadsheet. This feature proves invaluable for data validation and efficient data entry in various Excel data sheets.

Why are we using a drop-down list in an Excel spreadsheet?

1. Data Entry Efficiency:
   Speeds up the process by allowing users to select data from the predefined list instead of typing manually.
2. Accuracy and Consistency:
   A drop-down list helps the user to enter data correctly and decrease typing errors or irrelevant data in the Excel sheet.
3. Data Integrity:
   It improves data integrity and ensures that only specific values are entered in the cell.
4. Data Validation:
   Helps in validating without inconsistencies to avoid duplicate or misspelled entries.
5. Time-saving:
   It saves a lot of our time we consumed to repeatedly type the same values.

How to Add More Items to a Drop-Down List in an Excel Sheet?

Here I am exploring the methods that you can add more items to your existing drop-down list (menu) in an Excel spreadsheet. Different methods are used, as we described below.

Method 1: Edit manually the dropdown list:

1. Select the Cell:
   Click on the cell containing the drop-down menu list.
2. Open Data Validation:
   On the ribbon, go to the Data tab and click on Data Validation in the Data Tools group.
3. Modify the List:
   In the Settings tab, under Source, add new items by separating them with commas (e.g., “Item1, Item2,…”).
4. Click OK.
   Click on “OK” and the new items will now add and appear in the drop-down list.

Method 2: Expanding a Range-based Drop-down List:

1. Find the Source List:
   If your drop-down list is based on a range of cells, locate that range in the worksheet.
2. Add New Items:
   Insert the new items into the range where the original list is stored.
3. Adjust the Range (If Needed):
   If the list doesn’t automatically update, go to Data Validation, click Source, and extend the range to include the new items.
4. Click OK:
   Click on “Ok”. The drop-down will now include and appear in the items.

Method 3: Using a Dynamic Named Range:

This method is used for the dianomic and frequent updates.

1. Go to the Formulas Tab:
   Click Name Manager and locate the existing named range for the drop-down list.
2. Edit the Range Reference:
   Update the range to include additional rows.
3. Use an Excel Table:
   Convert the list range into an Excel Table (Ctrl + T) so that any new entries are automatically included.
4. Click Close & Apply:
   The drop-down will now update dynamically when new items are added.