Want to create a drop-down list in Excel cell to work more efficiently in worksheets? Learn how to create drop down list in Excel 2016 / 2013 / 2010.
Sometimes limiting the choices is very useful in controlling the flow and decision-making process of the users. This can be done with the help of drop-down lists. Applications of a drop-down list are drastically varied and it essentially helps users choose correctly, faster.
Did you know you can create drop-down list in Excel cell?
No? I don’t blame you. Well, the process of creating drop down menus in Excel seems little difficult and makes you jump through a few hoops before you can accomplish your task but don’t worry as we are here to help.
In this Microsoft Excel drop down list creation guide, we will be taking a look at how to create a drop down list in Excel 2016 / 2013 / 2010 and some important tips to keep in mind while adding drop down list in excel cells.
How to Create a Drop Down List in Excel 2010 / 2013 / 2016
A drop-down list is the most convenient way to provide the user an option to select from a pre-defined list. Using the drop-down list saves lots of time and effort because it’s always easy to select from the list instead of typing in each particular cell manually.
If you want to add Excel drop down list then you can easily use the data validation feature to create drop-down menus in Excel cells. All you need for this would be a list of data and a data entry cell.
Follow the steps shown below and learn how to add drop down list in Excel 2016 / 2013 / 2010:
Step (1): To create drop down list in Excel cell, first of all, list down the entries you want to appear in the drop-down menu either in a single row from A1 to A4 or A1 to D1.
For the example: Here, I have arranged a list of teams I want to display in the drop-down menu.
Step (2): Now select the Excel cell where you wish to add the drop-down menu in and then go to the Data tab and click on the Data Validation option.
Step (3): In the Data Validation dialog box, go to the Settings tab and then select List from the Allow drop-down list.
Step (4): Next, click on the Source box and then highlight/select the list of entries (which you have arranged in Step 1) using the mouse.
Just after highlighting/selecting the list of entries, Excel sheet formula for the drop-down list will appear in the Source box.
Step (5): Before clicking on OK, check the option “Ignore Blank” to ignore cells with no entry. Also, ensure the option “In-cell dropdown” is checked.
Step (6): Finally, click on the OK button and now you will be able to see a drop-down list in the Excel cell which you have selected before clicking on the Data validation option.
Thus, you can see how simple is to create a drop-down list in Excel 2016, Excel 2013 or Excel 2010.
Addition to this, Microsoft Excel also allows you to show input message or show error alert in the Excel drop-down list.
Display an Input Message in Excel
If you want to specify a message pop up when the drop-down list in Excel cell is clicked then you have to do the following:
- Go to the Input Message tab and then check the Show input message when cell is selected box.
- Next, type a Title and Input message in the boxes (up to 225 characters) and then click on the OK button.
Now, whenever the drop-down list in Excel cell will be clicked, the user will see a message pop up like this:
Display an Error Alert in Excel
If you want to display an error message when someone enters something that is not included in the drop-down menu then you have to do the following:
- Go to the Error Alert tab and then check the Show error alert after invalid data is entered box.
- Next, select a Style, add a Title and Error message in the boxes and then click on the OK button.
Now when someone enters something that’s not in the drop-down list, the user will see an error message pop up like this:
In the Error Alert tab, you can choose from the multiple styles, Information would show the blue I Symbol which is the standard one whereas Warning will show the Exclamation point (!) in the yellow triangle. And if you don’t want to allow anyone to input any additional data in the excel drop-down list, select Stop style.
Some Quick Tips to Keep in Mind
- Drop Down menus can only be seen if someone clicks the Excel cell.
- Drop Down cells can be copied and pasted so you don’t have to create them individually every time.
- You can create a data validation drop down list that uses data from another workbook as a source.
So this was our guide on how to create drop down list in Excel 2016, we hope you learned every step clearly. By following the exact same method, you can create a drop-down list in Excel 2010 and add drop down list in Excel 2013.
If you have any question regarding how to create a drop-down list in Excel cells do let us know below in the comments and we will try to help as quickly as possible.
You may also be interested in checking out:
- How to Remove Red Underline in Word 2013 / 2010 / 2007
- How to Change Orientation of One Page in Word 2016 / 2013 / 2010
- How to Fix Something went wrong Error 1058-13 in Office 2013
- How to Disable Automatic Updates in Office 2016
- How to Automatically Encrypt Your Office Document in Windows 10
- How to Increase PST File Size Limit in Outlook 2007 / 2010 / 2013 / 2016