This wikiHow teaches you how to create a drop-down list in a Microsoft Excel spreadsheet on your computer. With this feature, you can create a list of selectable entries and add a drop-down selector to an empty box on a worksheet. This feature is only available on the desktop version of Excel.
Step
Part 1 of 2: Making a List
Step 1. Open the Excel spreadsheet file that you want to edit
You can find and double-click a file stored on your computer or open Microsoft Excel and create a new worksheet.
Step 2. Enter the drop-down list entries in one column
Make sure you enter each entry in a separate box sequentially in the same column.
For example, if you want to create a drop-down list that includes "Jakarta", "Bandung", and "Ambon", type "Jakarta" in box A1, "Bandung" in box A2, and "Ambon" in box A3
Step 3. Click the empty box where you want to add a drop-down menu
You can add a drop-down list to any box on the worksheet.
Step 4. Click the Data tab on the toolbar ribbon
You'll find this button above the toolbar, at the top of the spreadsheet. The data tools will be opened after that.
Step 5. Click the Data Validation button on the "Data" toolbar
This button looks like two separate boxes with a green tick and a red stop sign. A new pop-up window will open after that.
Step 6. Click the Allow drop-down menu in the "Data Validation" pop-up window
You can find this menu in the "Settings" pop-up tab.
The “Data Validation” pop-up window will automatically open the “Settings” tab
Step 7. Select List from the "Allow" drop-down menu
With this option, you can create a list on the selected blank box.
Step 8. Mark options
In-cell dropdown.
Once marked, you can create a drop-down list in the selected box on the worksheet.
Step 9. Mark options
Ignore blank (optional).
When flagged, users can clear the drop-down list without getting an error message.
If the drop-down list you create is mandatory, make sure you don't check this box. If it's not a mandatory option, you can mark it
Step 10. Click the column below the "Source" text in the pop-up window
You can select the data set or entries that you want to include in the drop-down list.
Step 11. Select the data set/entry drop-down list on the worksheet
Use the cursor to select the data set that you want to add to the drop-down list on the sheet.
- For example, if you type the entries "Jakarta", "Bandung", and "Ambon" in boxes A1, A2, and A3, make sure you select a box range from A1 to A3.
- Alternatively, you can type the data range of the drop-down list manually in the "Source" field. For this option, make sure you separate each entry with a comma.
Part 2 of 2: Modifying List Properties
Step 1. Click the Input Message tab on the "Data Validation" pop-up window
It's a tab at the top of the window. On this tab, you can create a pop-up message to display next to the drop-down list.
Step 2. Mark options
Show input messages….
With this option, you can display a small pop-up message when a list entry is selected.
If you don't want to show a pop-up message, just clear the box
Step 3. Enter text in the "Title" and "Input Message" fields
You can use these columns to describe, describe, or provide more information about the drop-down list.
The title and input message you type will appear in a small yellow pop-up window next to the drop-down list when the box is selected
Step 4. Click the Error Alert tab at the top of the pop-up window
On this tab, you can display an error pop-up message whenever invalid data is entered into the drop-down box.
Step 5. Mark options
Show error alerts….
When flagged, an error message will be displayed when the user types invalid data in the drop-down box.
If the error message does not need to be displayed, simply clear the box
Step 6. Select an error message style in the Style drop-down menu
You can select options such as “Stop”, “Warning”, and “Information” from this menu.
- Option " Stop ” will display an error pop-up window with a message that you can modify, as well as restrain the user from entering data that is not available in the drop-down list.
-
Option " Warning " and " Information ” will not prevent the user from entering invalid data, but may display an error message with the icon " !
"yellow or" i" is blue.
Step 7. Enter your own "Title" and "Error message" entries (optional)
You can enter your own title and error message to display when invalid data is typed in the drop-down box.
- You can leave these columns blank. If left blank, the title and the default error message of the Microsoft Excel common error template will be used.
- The program's default error template is titled " Microsoft Excel " and contains the message " The value you entered is not valid. A user has restricted values that can be entered into this cell ".
Step 8. Click the OK button on the "Data Validation" window
A drop-down list will be created and added to the selected box.
Tips
- When you have finished creating the drop-down list, open the list to make sure all entries or data are displayed correctly. Sometimes, you need to widen the box so that all entries or data are displayed in full.
- When entering a data set for the drop-down list, type the data in the order you want them to appear in the drop-down menu. For example, you can type entries alphabetically so that users can find specific data/values more easily.
