Implementing a drop-down list in Excel is a quick and efficient way to choose predefined data. In the process, you’re able to save time from manually entering such data into a given spreadsheet. Drop-down lists are perfect for several purposes, such as inputting information into a form.
Here’s how to create a drop-down list in Microsoft Excel.
Difficulty
Easy
Duration
5 minutes
What You Need
-
Microsoft Excel
-
PC
Contents
- Create a drop-down list by using a range of cells
- Create a drop-down list by manually inputting data into Source field
- Displaying a message when the drop-down list cell is selected
- Displaying an error alert
- Protecting your drop-down list
Create a drop-down list by using a range of cells
The most common way to create a drop-down list in Excel with multiple selections is by using a range, which relies on using data from other cells.
Step 1: Choose a column where you want to include the data that will be shown in the associated drop-down list. This can be from the same spreadsheet where the drop-down list will be located, but you can also include them in a separate spreadsheet (add a new spreadsheet at the bottom). The latter option will naturally make your primary spreadsheet look more tidy, professional, and less cluttered.
Step 2: Type in all the data entries into the column, with each entry having its own cell.
Step 3: Click the cell where you want the drop-down list to appear.
Step 4: Click the Data tab at the top and then select the Data Validation button.
Step 5: Within the Allow menu, select List. An arrow will be displayed on the edge of the Source field. Click that arrow.
Step 6: Once the aforementioned arrow has been selected, you’ll be taken back to your spreadsheet’s main view. From here, simply head to the column where you entered the data. Now, select the range of cells in question by dragging the cursor from the first cell down to wherever your last cell is located.
Step 7: In the Data Validation mini pop-up window, click the arrow button. The Source bar will include the entire cell range you select in the above steps. Click OK.
You will now have a drop-down list menu located in the cell you chose from step 3.
Create a drop-down list by manually inputting data into Source field
There are a few ways to create a drop-down list on Excel. This specific method is the most straightforward and is particularly effective for a Microsoft Excel spreadsheet beginner and for lists that won’t require constant updates.
Step 1: Select the cell in the column where you want to input a drop-down list.
Step 2: Click the Data button from the top row of menus and then choose the Data Validation button.
Step 3: Click the Allow menu from the subsequent window that pops up. Now select List.
Step 4: Within the Source field, enter exactly what you want to be included in the drop-down list. Be sure to apply a comma after each word(s) or number.
Step 5: Click OK.
The cell you initially selected from Step 2 will now have a functioning drop-down list consisting of all the information you inputted in Step 5.
Displaying a message when the drop-down list cell is selected
Once you’ve created your drop-down list, you can make it more accessible by adding an Input Message.
Step 1: Select the cell where the drop-down list is located. Then click the Data Validation button.
Step 2: Select the Input Message option. Enter a relevant title and the text you want to be displayed when clicking the drop-down list itself in the Input message box. The text added here is limited to 225 characters.
Displaying an error alert
Similar to how you can insert a message that describes the purpose of the drop-down list, you can also display an error alert, which can emerge when text or data that’s not found within the list is entered.
Step 1: Select the cell where you created your drop-down. Click the Error Alert tab within the Data Validation window and then tick the Show error alert after invalid data is entered box.
Enter a custom title and a message. Should the title or text fields be left empty, the title will automatically be set to Microsoft Excel, while the message will read: “The value you entered is not valid. A user has restricted values that can be entered into this cell.”
Step 2: Choose a style from the ones provided such as the Stop (X) sign. Click OK.
Alternatively, if you want a message to pop up that doesn’t prevent individuals from entering invalid data not included within the drop-down list, select either Information or Warning from the Style menu. Both options sport their own design.
Protecting your drop-down list
To prevent someone from viewing or tampering with the source of the drop-down list’s data, you can lock those cells.
Step 1: Go to the column where your data for the drop-down list is entered. Now select the cells you wish to lock in order to prevent changes from unauthorized sources.
After you’ve selected the area you want to lock, click the Home tab. Within the Alignment section, click the small arrow at the bottom right. This will open the Format Cells window. Head to the Protection tab, click the Locked check box, and then click OK.
Step 2: Click the Review tab. Select Protect Sheet or Protect Workbook. Make any adjustments as per your requirements and click OK.
