Navigating an Excel file can sometimes get confusing.
Drop-down menus, also called drop-down lists, are an effective way of helping users navigate a spreadsheet more efficiently.
Using drop-down menus in your Excel file lets you keep things more organized, improving your productivity.
Thus, whether you’re a business owner, a salesperson, or just need to manage and schedule things using Microsoft Excel, you’ll benefit from knowing about drop-down menus.
This article will show you how you can create drop-down menus in Excel.
What are drop-down menus in Excel?
The Benefits of Drop-Down Menus in Excel
How to Create Drop-Down Menus in Excel
How to Create Drop-Down Menus in Excel Manually
What are drop-down menus in Excel?
A drop-down menu in Excel is a data validation function that allows users to choose an option from multiple options. When created correctly, they help users to work more efficiently on a worksheet.
Due to their design, they let users select options from a created list. You can find a variation of drop-down menus nearly everywhere you look on the internet — from newspaper websites to online stores — as it helps users gain access to different content sections.
Whether on websites or Excel spreadsheets, drop-down lists improve the user experience and make everything look more organized.
The Benefits of Drop-Down Menus in Excel
As earlier pointed out, drop-down lists in Excel allow you to organize your data quickly. A drop-down menu also lets you decide the entries users can input in a cell.
You can minimize errors on the spreadsheets you create using a drop-down menu. It’s a useful tip to help you get the most from using Excel.
With these advantages in mind, let’s review how to create a drop-down menu in Excel.
How to Create Drop-Down Menus in Excel
Excel is a powerful and highly versatile tool that makes life easier for organizers.
In the example below, we show you how you can, step by step, create a drop-down list in Excel. The example spreadsheet lists people on the left and a column with their corresponding headphones of choice on the right. In the example, we’ll add drop-down lists for the Headphones column on the right.
Let’s get into it.
1. Create a list in an Excel table.
The first step in creating the drop-down menu is to create a list in an Excel Table. To do that, open a new sheet and type the entries you want to appear in the drop-down menu.
We have named the new sheet “Headphones.”
After typing the entries, you can convert the list into an Excel table by clicking CTRL + T. You can see the new table below.
2. Choose the cell where you want the drop-down list.
Next, go to the sheet where you want to include the drop-down list. In our example, this is the “Destination Sheet.”
Then, select all cells where you want the drop-down menu to appear. For example, you can see we have selected these cells below.
3. Select Data Validation.
The next step is to go to the Data tab on the ribbon, then select Data Validation. If Data Validation is grayed out, the worksheet is likely protected. And you’d need to unlock it before proceeding.
If you can click on Data Validation, continue with the steps below.
- When you select Data Validation, you’ll open the Settings tab. On the Settings tab, open the Allow drop-down menu, and click on List.
- After selecting List, click on Source to determine the list range. For example, we put ours in the “Headphones” sheet in range A2:A9.
Note that checking Ignore blank in the box above means an error won’t be generated if the selected cells are empty.
If you want a message to pop up when any cells are clicked, click on the Input message menu.
You can see the results in our new sheet below.
You can also use the Error Alert tab to display a message when a user enters a value not on your list. To do that, click the Error Alert tab, and choose an option from the Style box. And type in a title and error message.
In the Style box, choosing Information will only show an icon that a wrong value has been inputted — it doesn’t prevent the users from entering an incorrect value.
On the other hand, selecting Stop will prevent users from entering data that’s not on the list.
Next, click OK. You should have your drop-down menu ready to go.
How to Create Drop-Down Menus Manually
Using the Source box, you can manually enter source data for the drop-down menu. This is best if the values are small, e.g., yes or no.
Using our example, we’ll add a new column that requires a yes or no answer.
Let’s show you how to add a drop-down menu using manual means.
1. Select the cell.
Choose the cells you want to input into.
Then, click on Data Validation in the Data ribbon.
2. Enter each entry with a comma.
In the Source box, enter each value and separate them with a comma, as shown below.
Click OK. And that’s how to add a drop-down menu manually.
[Video: Excel Drop Down List Tutorial]
Getting Started
We’ve shown you the simple and fast way of creating a drop-down list or menu in Excel. Not only can you create a simple drop-down menu, but now, you can also add an error alert message.
Using this technique makes your spreadsheet look better and easier to navigate for anyone that uses it.
