Note The instructions in this article apply to Excel 2019, 2016, 2013, and 2010; Excel for Mac 2019, Excel for Mac 2016, and Excel for Mac 2011. Enter the Data The first step to creating a drop-down list in Excel is to enter the data.
Note: If you can’t click Data Validation, the worksheet might be protected or shared. 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 it’s OK for people to leave the cell empty, check the Ignore blank box.
Check the In-cell dropdown box. Click the Input Message tab. 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 225 characters). If you don’t want a message to show up, clear the check box. Click the Error Alert tab. 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. If you don’t want a message to show up, clear the check box.
Not sure which option to pick in the Style box?. To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, click Information or Warning. Information will show a message with this icon and Warning will show a message with this icon. To stop people from entering data that isn’t in the drop-down list, click Stop. Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.
Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data. If the list is not large, you can easily refer to it and type the entries directly into the data validation tool. Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells. Select the cells that you want to restrict data 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. You cannot change data validation settings if your workbook is shared or your sheet is protected.
For more information about workbook protection, see. Click the Settings tab, and then in the Allow pop-up menu, click List. Click in the Source box, and then on your sheet, select your list of valid entries. The dialog box minimizes to make the sheet easier to see. Press RETURN or click the Expand button to restore the dialog box, and then click OK.
Notes:. Why should you put your data in a table? When your data is in a table, then as you, 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 in your drop-down list. Select the cell in the worksheet where you want the drop-down list.
Go to the Data tab on the Ribbon, then click Data Validation. On the Settings tab, in the Allow box, click List. If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell. Just include the cells that should appear in the drop-down. You can also just type a list of entries in the Source box, separated by a comma like this: Fruit,Vegetables,Grains,Dairy,Snacks.
If it’s OK for people to leave the cell empty, check the Ignore blank box. Check the In-cell dropdown box.
Click the Input Message tab. If you want 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 you don’t want a message to show up, clear the check box. Click the Error Alert tab. If you want a message to pop up when someone enters something that's not in your list, check the Show Alert checkbox, pick an option in Type, and type a title and message.
If you don’t want a message to show up, clear the check box. 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 to show all your entries. If you decide you want to change the options in your drop-down list, see. To delete a drop-down list, see. Need more help?
You can always ask an expert in the, get support in the, or suggest a new feature or improvement on.
. PLEASE FULLY READ THE SIDE-BAR BEFORE SUBMITTING A POST! SUBMISSION GUIDELINES FAQ PROBLEM SOLVED? OPs can reply to any solutions with: This will award the user a and change the flair to SOLVED. TO APPLY CODE FORMATTING Use 4 spaces to start each line You can select code in your VBA window, press Tab, then copy and paste into your thread or comment. PROVIDE YOUR DATA!
Include a or use the website to generate the reddit table markup from your spreadsheet. Recent ClippyPoint Milestones! Congratulations and thank you to these contributors Date User CP 10 25 10 10 10 10 10 1 25 500 A community since March 11, 2009 Download the official to convert Excel cells into a reddit table. This probably isn't what you're looking for if you want a full calendar to show up on dropdown, but you can use data validation to make three separate selectors for day, month, and year without resorting to a developer pack.
If you make a list of months on another tab, you can choose Data Data Validation and then Allow a list, which you set equal to the range where you have your months written out. You could do the same for years, or you could only allow whole numbers between a set range (1,31 for days, for example) and a custom error message if the user enters a bad value. Let's say you've got they enter their day in A1, month in B1, and year in C1.
This formula will turn it into a date: =TEXT(A1&B1&C1, 'dd/mm/yyyy').