To add a dropdown list in Excel for Microsoft 365 on Windows, put your choices in a worksheet range, select the cells where people will make a choice, then choose Data > Data Validation. Set Allow to List and select the choices as the Source. Excel will show a dropdown arrow in the selected cells, helping people enter consistent values instead of typing them differently.
What You’ll Need
You need an Excel workbook and the choices you want users to select. For a simple list, you can type the options directly into the Data Validation dialog. For a list that is easier to update or that will be used in many cells, keep the options in worksheet cells and point the dropdown to that range.
- A list of the permitted entries, such as Pending, In progress, and Complete.
- A destination range for the dropdown, such as cells B2:B100.
- If the choices are on a different worksheet, a named range for those choices.
These instructions are for Excel for Microsoft 365 on Windows. Excel for Mac has the same core Data Validation settings, though the dialog layout can differ; open Data > Data Validation. Excel for the web also has Data Validation under the Data tab, but its controls may be arranged differently.
Step-by-Step
- Choose where the dropdown will go. Decide which cells should accept a choice. For example, if column B records a task status, you might apply the dropdown to B2:B100 and leave B1 as the column heading. Applying validation to a range in advance means new rows can use the dropdown without repeating the setup. You can select one cell, a continuous range, or multiple nonadjacent cells by holding Ctrl while selecting them.
- Prepare the choices in cells. Enter one option per cell, with no heading included in the list range. For example, put Pending, In progress, and Complete in H2:H4. Keep the options together, without blank cells between them, so the list is easy to select and maintain. You can put the source list in an unused area of the current sheet, or keep it on a separate worksheet if you want the main data-entry sheet to look uncluttered.
- Select the destination cells. Click the first cell or select the entire range where users should choose an option. Check the selection before opening the dialog: Excel applies the rule to the cells that are selected at that moment. If the range includes a heading or unrelated cells, those cells will get dropdowns too.
- Open Data Validation. On the ribbon, choose Data > Data Validation, then choose Data Validation from the menu if it appears. In the dialog, use the Settings tab. The Data Validation command is in Excel’s Data Tools area; if the ribbon is collapsed, expand the Data tab or use the ribbon search box to find Data Validation.
- Set the rule to a list. In the Settings tab, open the Allow menu and choose List. Leave Ignore blank selected if users are allowed to leave the cell empty. Keep In-cell dropdown selected so the arrow appears when a destination cell is active. If you clear that checkbox, the validation rule can still exist, but users will not see the arrow to open the choices.
- Set the Source. Click in the Source box, then select the cells containing the choices. For the example list in H2:H4, the source should be
=$H$2:$H$4. The dollar signs keep the range fixed when the rule is applied to other cells. If you prefer, type that reference directly. Do not include the heading or cells outside the list. Click OK to apply the rule. - Optionally add a prompt and an error message. Reopen Data > Data Validation for the selected cells. On the Input Message tab, you can enter a short title and instruction, such as “Choose a status from the list.” On the Error Alert tab, make sure Show error alert after invalid data is entered is selected. Choose Stop to block entries that are not on the list, or select another alert style if you want to warn users while still allowing an exception. Click OK when finished.
- Test the dropdown. Click a destination cell. An arrow should appear at its right edge; select it and choose one of the listed options. Then try typing a value that is not on the list. With a Stop alert enabled, Excel should reject that entry and offer a chance to correct it. Testing both actions confirms that the dropdown is visible and that the error rule behaves as intended.
- Use a list stored on another worksheet, if needed. Excel’s list Source box generally cannot use a direct reference to a range on a different sheet. Instead, select the source cells on that sheet, open Formulas > Define Name, give the range a simple name such as StatusOptions, and confirm that Refers to points to the intended cells. Then return to the validation dialog and enter
=StatusOptionsas the Source. Use a name without spaces, and check that its scope is Workbook if the dropdown will be used on multiple sheets.
To change the choices later, edit the source cells. If you add another choice outside the original source range, update the Source in Data Validation as well. A named range also needs to include any added cells unless you have set it up to expand. After making changes, test a destination cell again.
Common Mistakes
- Selecting the wrong range. The rule applies only to the cells selected when you configure it. If you set it up for B2 instead of B2:B100, the other rows will not get the dropdown. Select the intended destination range first, or copy a validated cell and use Paste Special > Validation on the remaining cells.
- Including a heading in the source. If the source range includes a label such as “Status,” the label will appear as a selectable option. Select only the actual choices, not the heading above them.
- Forgetting In-cell dropdown. A list rule can be present even when the arrow is hidden. Reopen Data Validation and check In-cell dropdown on the Settings tab.
- Expecting pasted data to be blocked. Typing an invalid value may trigger the error alert, but pasted content can sometimes bypass validation or replace a cell’s rule. Treat validation as a useful guardrail, not a complete security control. Check imported or pasted data separately.
- Typing several options with the wrong separator. A manually entered list may use a separator that depends on your regional settings. A cell range avoids this issue and is simpler to maintain, especially when choices contain punctuation.
- Adding choices beyond the source range. A dropdown based on H2:H4 will not automatically include a new choice entered in H5. Expand the source reference or named range to include it.
Tips
- Keep the source list easy to maintain. Store choices together in a clearly labeled area or on a dedicated lookup sheet. This makes it easier to review the permitted entries and spot accidental blanks or duplicates.
- Apply validation to likely future rows. If users will add records, selecting a reasonable range such as B2:B500 can prevent repeated setup. Avoid applying the rule to an entire column without considering whether the extra cells will make the workbook harder to manage.
- Use descriptive prompts. An input message can explain what the field means or what to do when no option applies. Keep the instruction brief so it is helpful rather than distracting.
- Choose the right alert style. Stop is the strictest option and prevents a typed value that is not in the list. Warning lets users confirm an exception, while Information displays a message without enforcing the list. Use the option that fits how the workbook will be used.
- Consider who needs to edit the source. If other people maintain the choices, make the source cells easy to find and document where they are. If the list is on another sheet, you can hide that sheet for a cleaner workbook, but remember that someone with permission to edit the workbook may need access to update it.
- Check existing values before adding a rule. Applying validation does not automatically remove or repair values that were already in the cells. Review existing entries and correct any that do not match the new choices.
Troubleshooting
The arrow does not appear: Select a cell that should have the rule, open Data > Data Validation, and check that Allow is set to List and In-cell dropdown is checked. Also confirm that the cell is inside the destination range. The arrow normally appears when the validated cell is selected, rather than remaining visible all the time.
The list is empty or shows the wrong choices: Reopen the validation settings and inspect Source. Make sure the reference points to the cells that contain the options, and that those cells are not blank. If you used a named range, check Formulas > Name Manager to confirm the name exists and refers to the right range. For a list on another sheet, use the named range in Source rather than a direct cross-sheet address.
Excel says the source evaluates to an error: Check for a misspelled range name, an invalid reference, or a deleted source cell. A named range used as a list source should begin with an equals sign in the Source box, such as =StatusOptions. If you entered choices directly, try putting them in worksheet cells and referencing that range instead.
An invalid value still appears in a cell: Check the Error Alert tab and make sure the alert is enabled with the intended style. Remember that pasted or imported values may not be stopped by validation. Use Data > Data Validation to inspect the rule, and review questionable entries manually or with Excel’s validation checking tools where available.
A new option is missing: The source range probably does not include the cell where you added it. Expand the Source reference or edit the named range so it covers the additional option, then test the dropdown again. If the list has moved, update the reference to its new location.
Frequently Asked Questions
Can I type the dropdown options directly into Excel?
Yes. Choose List in Data Validation and type the options into Source, separated by the list separator Excel expects for your regional settings. For a list you may edit later, a worksheet range is usually more dependable and easier to maintain.
Can I use a dropdown list from another sheet?
Yes. Create a named range for the source cells with Formulas > Define Name, then enter the name with an equals sign in the Data Validation Source box. This avoids using a direct reference to another worksheet, which the list source may reject.
Can users enter a value that is not in the dropdown?
It depends on the Error Alert setting. A Stop alert blocks invalid entries typed into a validated cell; Warning and Information alerts can allow them. Pasting data may bypass the rule, so check pasted or imported entries separately.
How do I remove a dropdown?
Select the validated cells, choose Data > Data Validation, and click Clear All in the dialog, then click OK. This removes the validation rule from the selected cells; it does not delete their existing contents.
Will the dropdown update when I add another choice?
Only if the source reference includes the new cell. With a fixed range such as H2:H4, adding an option in H5 requires expanding the Source. If you use a named range, update its reference too unless you configured it to expand automatically.
Related
- Switch Excel Columns From Letters to Numbers?
- Choose an Excel Data Range: Methods, Tips, and Shortcuts
- Excel Letter Count: Count Letters and Characters in Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Merge Two Word Documents to Compare ChangesTo compare two Word documents, open Word for Microsoft 365 on Windows and choose Review > Compare >…
- How to Make Columns the Same Width in a Word TableTo make every column in a Word table the same width, select the whole table, then choose Layout…
- Data Validation Named Range in Excel: Setup and FixesA data validation named range is a named cell range used as the source for an Excel drop-down…
- Project Tracker Excel: Examples, Setup, and Practical TipsYes—Excel can make a useful project tracker for tasks, owners, due dates, priorities, and progress. Start with one…
- How to Create a Signature Line in Microsoft OutlookTo create an email signature line in Outlook, open the signature settings for your version of Outlook, write…
- Insert Rows in Excel Multiple Times: A Step-by-Step GuideTo insert several rows in Excel at once, select the same number of row headers as the number…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.