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 Spreadsheet to enhance your experience and the usability of your Excel spreadsheets. A dropdown list, also called a drop-down menu, makes an easy way to input data by offering predefined options within a selected cell. This feature is useful when you want to limit data entry choices, reduce errors, and ensure consistency in your Excel workbook.

Creating a dropdown list in Excel sheet involves utilizing the “Data Validation” feature, allowing you to specify a range of values or a list for users to choose from. This simplifies data input helps to work more efficiently in an Excel spreadsheet.

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

Follow the below steps to easily create a dropdown list in an Excel spreadsheet, empowering you to efficiently manage and organize your data.

1. Open Your Excel Spreadsheet:
   Start Microsoft Excel and open the workbook in which you want to create a drop-down list. For example, you want to create a drop-down list in Sheet 1.
2. Create the Source Itmes of the Dropdown List:
   First, you may create a source item (e.g country names, city names, fruit names, etc.) in Sheet 2, as you can see it in the above screenshot.
3. Select the Cell or Range:
   Now go to Sheet 1 and click on the cell or range of cells where you want to create the dropdown list, e.g. B2 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 We Use a Drop-down List in an Excel Spreadsheet:

1. Data Entry Efficiency:
   Speeds up the process by allowing users to select predefined options from the list instead of typing manually.
2. Accuracy and Consistency:
   Reduces typing errors and ensures uniform data entry across the Excel spreadsheet.
3. Help the User:
   A drop-down list helps to simplify data input, making it easier for users to navigate and select relevant information.
4. Standardized Responses:
   Ensures that only specific values are entered, improving data integrity.
5. Error Prevention:
   With the help of a list, minimize incorrect or irrelevant data entries by limiting choices.
6. Data Validation: Helps in maintaining structured and validated data without inconsistencies.
7. Improved Organization:
   Keeps records clean and manageable by avoiding duplicate or misspelled entries.
8. Enhanced Filtering & Sorting:
   Makes data analysis more efficient by allowing structured categorization.
9. Time-saving:
   Eliminates the need to repeatedly type common values, improving productivity.
10. Professional Appearance:
    Creates a polished and organized look, especially in reports and dashboards.

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

You can add more items to an existing drop-down list in an Excel spreadsheet using different methods based on how the list was created.

Method 1: Editing a Manually Entered List:

1. Select the Cell:
   Click on the cell containing the drop-down menu.
2. Open Data Validation:
   Go to the Data tab and click 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.