Drop-downs allow people to pick an item from a list that you create. Want to be walked through this process? Try our new online tutorial for drop-down lists beta. In a new worksheet, type the entries you want to appear in your drop-down list. Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list , any drop-downs you based on that table will automatically update. You don't need to do anything else.
Now is a good time to Sort data in a range or table in your drop-down list. Go to the Data tab on the Ribbon, then Data Validation. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again.
On the Settings tab, in the Allow box, click List. Click in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:. If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes up to characters.
If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. Information will show a message with this icon and Warning will show a message with this icon. Note: If you don't add a title or text, the title 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. After you create your drop-down list, make sure it works the way you want.
For example, you might want to check to see if Change the column width and row height to show all your entries. If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them. If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.
To delete a drop-down list, see Remove a drop-down list. You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples. If you prefer you can have the values in your drop-down list tied to something else in your spreadsheet, so that you can change them if you want.
Select a cell on the spreadsheet and enter your values. This time instead of typing the values into the "Source" field, you will select them from the spreadsheet. The "Source" field contains a button that allows you to go back to the spreadsheet to select your values.
Selecting the entire column will place a formula in the "Data Validation" window that shows the entire column is selected. Open the "Data Validation" window and click OK.
You enter data by clicking the arrow and then selecting the desired entry. You can set up a drop down box in just a few minutes, and greatly improve your data entry speeds. Log in Social login does not work in incognito and private browsers. Please log in with your username or email to continue. No account yet? Create an account. Edit this Article. We use cookies to make wikiHow great. By using our site, you agree to our cookie policy.
Cookie Settings. Learn why people trust wikiHow. Download Article Explore this Article Steps. Tips and Warnings. Related Articles. Open the spreadsheet in which you want to add the drop-down box es. Create a list of the items to appear in the drop-down list. Type the data in the order it will be displayed in the list.
The entries should be typed in a single column or row and should not contain any blank cells. To create the list of the desired items on a separate worksheet, click the tab of the worksheet in which to enter the data. Type and then highlight the data to appear in the list. Right-click the highlighted range of cells and click "Name a Range" from the list that appears. Type the name for the range in the "Name" box and click "OK. Click the "Data Validation" button from the "Data Tools" group.
The "Data Validation" dialog box appears. Click the "Settings" tab and then click "List" from the "Allow" drop-down list box. Click the toggle button at the end of the "Source" box. Select the list of items you want to have in your drop-down box. If you created a range name, in the "Source" box, type an equal sign and then type the name of the range. Select or clear the "Ignore blank" check box, depending on whether you allow the cell containing the drop-down box to be left blank.
Make certain the "In-cell dropdown" check box is selected. Click the "Input Message" tab to display a message box when the cell of the drop-down box is clicked.
0コメント