How to Make a Drop-down List in Excel

You can improve your work efficiently by using a drop-down list for data collection. The drop-down list can significantly minimize the data entry errors such as the names, address. Please see below how to make a drop-down list:

Step 1: Prepare a list you want to appear in the drop-down list in a new blank worksheet;

Step 2: Select the cell in the worksheet where you want the drop-down list (e.g., A1 in a new worksheet);

Step 3: Click the "Data" tab from the Ribbon;

Step 4: Click "Data Validation" in the Data Tools section;

Step 5: On the "Settings" tab, in the "Allow" box, click "List";

Step 6: Click the "Source" box, and select the list you just prepared in step 1;

Step 7: Input Message tab: You can show a message for reminder or interpretation when the cell is selected.

1. Check the "Show input message when cell is selected" box;

2. Type a title and message in the boxes;

3. If you don’t want a message to show up, clear the check box;

4. The message you just typed in will appear when you click the cell.

Step 8: Error Alert tab: The message will display when you enter a wrong message. By default, when you enter something that is not in your list, a message below will pop up.

Step 9: Change Error Alert Tab: If you want to have a different message pop up, please follow the steps below:

1. Click Error Alert tab;

2. Check the "Show error alert after invalid data is entered" box;

3. Select an option from the Style box (stop, warning or information);

– "Stop": To stop people from entering data that isn’t in the drop-down list;

– "Warning": To display a warning message. People can enter data that is not from the drop-down list;

– "Information": To display a information message. People can enter data that is not from the drop-down list;

4. Type the title (e.g., Drop Down) and the message (e.g., please use the drop-down list only). If you don’t want a message to show up, clear the check box.

5. Click "OK" at the bottom

Step 10: When you enter a message that is not from the drop-down list, the following message will display.

Leave a Reply