Implementing a dropdown list in Excel is a fast and efficient way to choose predefined data. In the process, you can save time by manually entering such data into a given spreadsheet. Dropdown lists are perfect for a number of purposes, such as entering information into a form.
Here’s how to create a dropdown list in Microsoft Excel.
Create a dropdown list by selecting a range Cell Range
The most common way to create a drop-down list in Excel with multiple selections is by using a range, which is based on using data from other cells.
Step 1: Choose a column where you want to include the data to be displayed in the associated dropdown list. This can be from the same spreadsheet where the dropdown will be located, but you can also use a separate spreadsheet (add a new spreadsheet at the bottom). The latter option will naturally make your main spreadsheet look neater, professional, and less cluttered.
Step 2: Write all the data entries in the column, with each entry having its own cell.
Step 3: Select the cell where you want the drop-down list to appear.
Step 4: Go to the Data tab and select the Data Validation button or select Data Validation from the dropdown menu.
Step 5: Within the Allow menu, select List. An arrow will be displayed at the edge of the Font field. Select that arrow.
Step 6: You will return to the main view of your spreadsheet. From here, simply select the range of cells by dragging your cursor from the first cell to where the last cell is.
Step 7: In the Data Validation mini-pop-up, select the arrow button. The Font bar will include the entire range of cells you selected in the previous steps. Choose OK.
You will now have a drop-down menu located in the cell you chose in step 3.
Create a drop-down list by manually entering data
There are a few ways to create a drop-down list in Excel. This specific method is the simplest and is particularly effective for a beginner to Microsoft Excel spreadsheets and for lists that won’t require constant updating.
Step 1: Select the cell in the column where you want to enter a dropdown list.
Step 2: Go to the Data tab and select the Data Validation button or choose Data Validation from the dropdown menu.
Step 3: Choose the Allow menu in the next window that appears and select List.
Step 4: Inside the field Font, enter exactly what you want included in the dropdown list. Be sure to apply a comma after each list item.
Step 5: Select OK.
The cell you initially selected in step 1 will now have a functional drop-down list consisting of all the information that you entered in step 4.
Display a message when dropdown list is selected
Once you’ve created your dropdown list, you can make it more accessible by adding an input message.
Step 1: Choose the cell where the dropdown list is located. Then open the Data Validation popup once more.
Step 2: Select the Input Message tab. Enter a relevant title and the text you want to display when clicked from the dropdown list. Text added here is limited to 225 characters.
Step 3: Choose “OK” to apply the message.
Display an error alert
Similar to how you can insert a message describing the purpose of the dropdown list, you can also display an error alert, which can occur when you enter text or data not found in the list.
Step 1: Select the cell you created your dropdown in. Reopen the Data Validation window, select the Error Alert tab, and check the Show error alert after invalid data entry box.
Enter a custom title and message. If the title or text fields are left empty, Excel will apply a default message.
Pa so 2: Choose a style from the ones provided, such as Stop (X), and select OK.
Alternatively, if you want a popup that doesn’t prevent people from entering invalid data , select Information or Warning from the Style menu. Both options sport their own design.
Protect your dropdown list
To prevent someone from seeing or tampering with the source of your dropdown list data, you can lock those cells.
Step 1: Go to the column where you enter your data for the dropdown list. Now select the cells you want to lock to prevent changes from unauthorized sources.
After selecting the area you want to lock, go to the Home tab.
Step 2: Within the Alignment section, select the small arrow at the bottom right to open the Format Cells window. Go to the Protection tab, check the Locked checkbox, and select OK.
Step 3: Go to the Review tab and select Protect Sheet or Protect Workbook. Make the settings as per your requirements and choose OK.
A drop-down list in Excel gives you a convenient way to enter data. To learn more, take a look at these additional Excel tips and tricks.
Editors’ Recommendations
- ChatGPT: How to Use the Viral AI Chatbot Everyone’s About the world talks
- Bing Chat: How to use Microsoft’s own version of ChatGPT
- PowerPoint will use ChatGPT to create entire slideshows for you
- How to cancel Spotify Premium on your desktop or iOS device
- Finally, you’ll soon be able to use 3D avatars in Teams calls
.