How Do I Make A Drop Down List In Excel
Create a drop-down list
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 More...Less
You lot tin can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs let people to choice an item from a list that you create.
Want to be walked through this procedure? Endeavor our new online tutorial for drop-down lists (beta).
-
In a new worksheet, type the entries you lot want to appear in your drop-downwards list. Ideally, you'll accept your list items in an Excel table. If you lot don't, then you can quickly catechumen your list to a tabular array by selecting whatsoever prison cell in the range, and pressing Ctrl+T.
-
Select the cell in the worksheet where you desire the drop-down listing.
-
Go to the Information tab on the Ribbon, so Information Validation.
Annotation:If you can't click Data Validation, the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet, and and then try step 3 once more.
-
On the Settings tab, in the Allow box, click List.
-
Click in the Source box, then select your list range. Nosotros put ours on a sheet chosen Cities, in range A2:A9. Note that we left out the header row, because nosotros don't want that to be a selection option:
-
If it'south OK for people to leave the jail cell empty, check the Ignore blank box.
-
Bank check the In-cell dropdown box.
-
Click the Input Bulletin tab.
-
If you want a message to pop up when the prison cell is clicked, cheque the Show input bulletin when prison cell is selected box, and type a title and message in the boxes (upward to 225 characters). If you don't want a bulletin to show up, articulate the check box.
-
-
Click the Error Alert tab.
-
If yous want a message to pop up when someone enters something that'southward not in your list, cheque the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don't want a message to show up, clear the check box.
-
-
Not certain which option to choice in the Style box?
-
To evidence a message that doesn't end people from entering data that isn't in the drop-down list, click Data or Warning. Information will show a message with this icon
and Alert will show a message with this icon
.
-
To end people from inbound information that isn't in the drop-downward list, click Stop.
Note:If you don't add a title or text, the championship defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
-
You lot can download an example workbook with multiple information validation examples like the one in this article. You lot tin can follow along, or create your ain data validation scenarios. Download Excel data validation examples.
Information entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.
Beginning by making a list of valid entries on a canvass, and sort or rearrange the entries so that they appear in the order you want. So you tin use the entries as the source for your drop-down list of data. If the list is not big, you lot tin easily refer to it and type the entries directly into the data validation tool.
-
Create a list of valid entries for the drop-down listing, typed on a sheet in a unmarried column or row without blank cells.
-
Select the cells that you want to restrict information entry in.
-
On the Data tab, under Tools, click Data Validation or Validate.
Note:If the validation command is unavailable, the sheet might be protected or the workbook may be shared. Yous cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
-
Click the Settings tab, and then in the Allow popular-up menu, click List.
-
Click in the Source box, and so on your sheet, select your list of valid entries.
The dialog box minimizes to brand the canvass easier to run into.
-
Press Render or click the Expand
button to restore the dialog box, and and then click OK.
Tips:
-
You can too blazon values directly into the Source box, separated by a comma.
-
To modify the list of valid entries, only change the values in the source listing or edit the range in the Source box.
-
You can specify your ain fault message to reply to invalid information inputs. On the Information tab, click Data Validation or Validate, and then click the Mistake Warning tab.
-
See also
Apply data validation to cells
-
In a new worksheet, type the entries y'all desire to announced in your drib-down list. Ideally, you'll accept your list items in an Excel tabular array.
-
Select the prison cell in the worksheet where y'all want the drop-down listing.
-
Become to the Data tab on the Ribbon, then click Information Validation.
-
On the Settings tab, in the Let box, click List.
-
If yous already made a table with the drop-downwards entries, click in the Source box, and then click and elevate the cells that contain those entries. All the same, practice not include the header cell. Just include the cells that should appear in the drib-downwardly. You can also just type a list of entries in the Source box, separated past a comma similar this:
Fruit,Vegetables,Grains,Dairy,Snacks
-
If it's OK for people to exit the prison cell empty, check the Ignore blank box.
-
Check the In-cell dropdown box.
-
Click the Input Message tab.
-
If you desire a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). If y'all don't want a message to show up, articulate the check box.
-
-
Click the Error Warning tab.
-
If you want a message to pop upwardly when someone enters something that'south not in your list, bank check the Show Alarm checkbox, pick an option in Type, and type a title and bulletin. If y'all don't want a message to show upwards, clear the check box.
-
-
Click OK.
Afterward you create your driblet-down listing, make sure it works the fashion you want. For case, you might want to check to run across if Modify the cavalcade width and row height to show all your entries. If y'all decide you want to change the options in your drop-down listing, see Add together or remove items from a drib-down list. To delete a drop-down list, come across Remove a drib-down list.
Demand more assist?
You tin always ask an skillful in the Excel Tech Customs or get support in the Answers community.
See also
Apply data validation to cells
All Excel functions (by category)
Creating drib-down lists (free preview)
Source: https://support.microsoft.com/en-us/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b
Posted by: morellitobt1957.blogspot.com
0 Response to "How Do I Make A Drop Down List In Excel"
Post a Comment