How Do You Create a Drop Down List in Excel

Last Updated on May 4, 2024 by sunjava22

How Do You Create a Drop Down List in Excel

Guide to Creating a Drop-down List in Microsoft Excel

Enhance efficiency in worksheets by incorporating drop-down lists in cells. This feature enables users to select items from a pre-defined list that you create in Microsoft Excel.

1. Enter the desired entries for your drop-down list in a new worksheet. It’s recommended to organize your list items in an Excel table. If you haven’t done so, swiftly convert your list to a table by selecting any cell in the range and pressing Ctrl+T.

• Utilize an Excel Table as the source for Data Validation lists.
  • Why opt for a table? When your data resides in a table, any modifications made to the list—whether adding or removing items—will automatically update any drop-downs linked to that table. No additional steps are required.
• Now, ensure your data is sorted in the range or table intended for your drop-down list.

1. Select the cell in the worksheet where you wish to incorporate the drop-down list.
2. Navigate to the Data tab on the Ribbon and locate Data Validation.
   • Note: If Data Validation isn’t selectable, the worksheet may be protected or shared. Unlock specific areas of a protected workbook or cease sharing the worksheet, then retry step 2.
3. Within the Settings tab, in the Allow box, opt for List.
4. Choose the Source box, then select your list range. For instance, our list is located on a sheet named “Cities,” spanning from A2 to A9. Omit the header row to exclude it as a selection option.

   

5. If it’s permissible for users to leave the cell empty, check the Ignore blank box.
6. Confirm the In-cell dropdown box.
7. Proceed to the Input Message tab.
   • To display a message upon selecting the cell, check the Show input message when a cell is selected box. Input a title and message (up to 225 characters). If no message is desired, clear the checkbox.

     

8. Head to the Error Alert tab.
   • If you wish to trigger a message when a user inputs data not in the list, check the Show error alert after invalid data is entered box. Choose an option from the Style box, and specify a title and message. If no message is required, clear the checkbox.

     

   • Unsure about the Style box options?
     • To display a non-intrusive message allowing users to enter data not in the drop-down list, select Information or Warning. Information displays a message with an information icon while Warning displays a message with a warning icon.
     • To restrict users from inputting data not in the drop-down list, select Stop.