Wondering how to add a drop-down list in MS Excel to save your efforts on typing? The drop-down list in Excel does impress your boss and coworker as it is an excellent time-saving feature for all the customizable Excel sheets.
The drop-down acts like a menu list in Microsoft Excel which has a list of options. Once the drop-down list is created, you or your co-workers can later select the options from the Excel field.
Creating a drop-down list in Microsoft Excel can also be very useful in web forums, polls and survey as they have limited choices to answer or fill from the existing options.
It comes in handy for most of the wholesale and retail companies to manage their product lines. The drop-down lists in Excel also help banks, hospitals, and professional employees, as it reduces typing and avoids any error.
So how to create the drop-down list in the Microsoft Excel spreadsheet? Here’s a step-by-step guide.
How to Create a Drop-down list in Microsoft Excel?
For those who don’t know, one of the best features of Microsoft Excel is that you can make a drop-down list that can be further added to your spreadsheet.
Now, follow the step by step process to create a drop-down list in Excel.
1. Set the List of Contents
The first and foremost thing to do is to set the contents you want to add to add in the drop-down list. For that:
- You have to open a new Excel sheet.
- Now, in the left-most section, add the various contents you wish to see in your drop-down list
Note: Each entry should only use one cell and ensure that there are no empty cells between them and all are present in the same section.
- Once you have made your list, select all the entries and right-click on them
- Now, you can see a menu, choose the Define Name option
- A window comes up with the New Name title. Set your preferred title for your drop-down list
Note: Ensure that you do not enter any space to the drop-down list title.
- Hit on OK to save the changes
You have now successfully set your content to add to the drop-down list in your Excel.
2. Attach the Drop-down List to a Spreadsheet
Once you have arranged the contents of the list, let’s proceed further to add these contents of the drop-down list to your worksheet. To do so,
- Open a new sheet or an existing worksheet where you want to add the drop-down list
- Select the cell/s where you want the list to appear and go to the menu bar (on the top of the sheet)
- Now, select Data and click on Data Validation
- A small drop-down with three tabs appears- Input Message, Settings, Error Alert
- However, the Settings tab opens by default, and again a drop-down appears with two options.
- Now, hit on the Allow option and select List. You can see a menu at the bottom of the window named Source
- Click on the Source menu and enter the “=” sign followed by the title of your list
For example: =beverages
- Ensure that the In-cell drop-down box is checked and also tick the Ignore blank box, allowing other people to leave the field blank if they don’t want to choose any of your preset options
Now as we are done with the settings tab. Let us go on to the “Input Message” tab
3. Input Message tab
This tab creates a message which can appear on your screen if you click on the drop-down cell. However, this tab is usually not required or used. But is very useful to add hints or subtitles or complicated questions you post. Let us see how it works:
- Add a title in the tab and enter the message or description in the Input Message box
- And if you don’t require this feature, you can just untick the Show Input Message when cell is selected menu
With that completed, let us now move to the last tab the “Error Alert.”
4. Error Alert
This feature is very similar to the Input Message with that it also informs you if you have input an invalid response. Here you can add a title and a description, but also has an additional feature to set Style to them. Let’s see more of its functions below:
- You will see three icons when you click on the Error Alert option- Stop, Warning, and information. Choose any of them that suits your choice. Just untick the show error alert after invalid data is entered box to remove this feature
- Click on OK to save the changes. You can now see an arrow in the selected cell for the drop-down list. When you click on it, your list of answers appears immediately.
- You can see the drop-down list only if you click on the cell.
- You can copy-and-paste this drop-down cell to any other cells in your spreadsheet, and you can create as many different drop-downs.
- Now, your users can only choose one of the options in the drop-down. They’ll receive an error message if they try to input their data.
That’s all! You have now created your own drop-down list and made it immensely easier for you and your co-workers to avoid any errors and get to choose from the preset options or contents. Let us know if this guide helped you.