Yes—Excel can use Data Validation to create a Yes/No dropdown in a cell. Select the cells, open Data > Data Validation, set Allow to List, and enter Yes and No as the list source; users can then choose one of those entries instead of typing freely.
Quick steps to add a Yes/No dropdown
In Excel for Microsoft 365 on Windows, you can add the list to one cell or a whole range. To apply it to several cells, select the range first; for example, select B2:B100 if those cells will record answers.
- Select the cell or range that should contain the answer.
- Go to Data > Data Tools > Data Validation.
- On the Settings tab, set Allow to List.
- In Source, enter Yes,No. In some regional settings, Excel uses a semicolon instead of a comma as the list separator; if the entry is not accepted as two choices, try Yes;No.
- Leave In-cell dropdown selected, then select OK.
Click a validated cell to open its dropdown and choose Yes or No. You can also type a permitted answer directly into the cell. Data Validation is a restriction on what Excel accepts, not a requirement to use the dropdown arrow.
How the list works
Data Validation checks entries against rules you set for a cell. With Allow set to List, the rule provides a set of permitted values. A Yes/No list is useful for recording a binary response, such as whether a task is complete, whether a document has been received, or whether a customer agreed to be contacted.
The displayed options are ordinary text values. Choosing Yes does not automatically make Excel treat the cell as the logical value TRUE, and choosing No does not make it FALSE. That distinction matters if you later use the column in formulas, filters, charts, or an export to another system. If another tool expects TRUE/FALSE, use those values as the list choices or convert the text deliberately as part of your workflow.
The dropdown also does not make an answer mandatory by itself. A blank can remain blank, depending on the rule settings and how the cell is used. Validation is most helpful when people enter data through the worksheet; pasted or imported data can require separate checking.
Choosing between typed values and a cell range
For a simple list used in one place, typing the choices in Source is quick. In the standard English-language setup, enter Yes,No. If your Excel installation uses a different regional list separator, the separator may differ. A separator problem can make Excel treat the entry as one choice rather than two, so test the dropdown after creating the rule.
A range-based source is often easier to maintain, especially when the same choices are used in multiple worksheets. Put Yes and No in two cells, such as H1 and H2, then use those cells as the list source. If the source is on another worksheet, a named range is a convenient way to refer to it, because Excel generally expects a direct list source to point to cells on the same worksheet. You can create a name through Formulas > Define Name, then use that name as the validation source.
- Use a typed source when the list is short and unlikely to change.
- Use a range when you want to reuse or centrally edit the choices.
- Use a named range when the source cells are on another sheet or you want a clearer reference.
With only two choices, either method is practical. The range option becomes more valuable when the list grows or several input areas need the same approved values.
Set helpful prompts and error messages
The Data Validation dialog includes options beyond the list itself. On the Input Message tab, you can show a short instruction when someone selects the cell. For example, a message could say, Select Yes if the inspection is complete. This is useful when the meaning of the question is not obvious from the column heading.
On the Error Alert tab, choose whether Excel should show a warning when someone enters a value that is not on the list. A Stop alert blocks an invalid typed entry while the rule is active. Warning and Information alerts allow the user to proceed after seeing a message, so they are less strict. Add a clear title and message, such as Enter Yes or No, to explain how to fix the entry.
The Ignore blank option affects whether an empty value is permitted by the validation rule. If blank answers are acceptable—for example, because a question has not yet been reviewed—keep that in mind when setting the rule. A dropdown alone cannot distinguish between No and unanswered, so do not use No to mean both if those states matter to your report.
Practical examples and reporting choices
Imagine a project tracker with a header in B1 called Approved and entries in B2:B50. Applying the Yes/No list to B2:B50 gives each reviewer a consistent pair of choices. A separate Status or Notes column can capture context, such as why an item was not approved, without expanding the dropdown into a long list.
For a quality checklist, a Yes/No choice may be too restrictive if some items are not relevant or have not been checked. Consider whether the actual answers should be Yes, No, and Not applicable, or Yes, No, and Pending. The list should reflect the real states users need to record; otherwise, people may put explanations in the wrong column or choose an inaccurate answer just to complete the row.
Consistent entries also make filtering and counting more dependable. A column containing Yes, yes, Y, and a blank may be understandable to a person but harder to summarize consistently. A dropdown reduces accidental variations, while a clear question and an agreed meaning for each option help prevent different users from interpreting the same answer differently.
Common problems and how to fix them
If the dropdown arrow does not appear, select the cell and revisit Data > Data Tools > Data Validation. Confirm that Allow is set to List and In-cell dropdown is checked. Also check that you selected the cell with the rule rather than a neighboring cell. The arrow is normally shown when the validated cell is selected.
If Excel offers only one combined choice, the source separator may not match your regional settings. Reopen the dialog and try the appropriate list separator, or use a source range containing one choice per cell. If a range source is used, make sure the referenced cells contain the exact options and are not blank unintentionally.
If a user can enter an unexpected value, check the Error Alert tab. A warning or information alert can allow an invalid entry to be accepted, unlike Stop. Also remember that pasting data into a cell may bypass the usual interactive validation prompt. To inspect entries that are already present, select the relevant cells and look for the Circle Invalid Data command under Data Validation. Correct any marked values and consider checking the column again after importing or pasting data.
Finally, avoid assuming the rule has been applied to every row. When extending a table or copying cells, confirm that new input cells have the intended validation. A quick test is to select a few cells at the beginning, middle, and end of the range and check that each shows the expected choices.
Windows, Mac, and Excel for the web
The steps above use Excel for Microsoft 365 on Windows. In Excel for Mac, select the cells and open Data > Data Validation; the dialog and available controls can be arranged somewhat differently, but the list rule uses the same basic settings. Menu wording and separator behavior can depend on the installed version and regional settings.
Excel for the web also supports dropdown lists and Data Validation, though the command placement and editing experience may differ from the Windows desktop app. If a workbook was created in another version, or an option is unavailable in the browser, open it in the desktop app to review the rule. When collaborating, test the dropdown in the version your users will actually use, particularly if the workbook is shared across platforms.
When a checkbox or another option is better
A Yes/No dropdown is compact and makes both choices visible when opened, but it is not the only way to collect a binary answer. Excel for Microsoft 365 has an in-cell checkbox feature in supported builds. A checkbox is useful when a blank box naturally means not completed and a checked box means completed. Its underlying value is TRUE or FALSE, rather than the text Yes or No, so check that this fits the workbook’s formulas and reporting needs.
Checkboxes are less suitable when the distinction between No and unanswered must be explicit. A dropdown can show both text choices, while an empty cell remains a separate state if your process allows it. For a simple response column, the dropdown is usually easier to scan and filter; for a completion tracker where checked means done, a checkbox may be more intuitive.
Frequently Asked Questions
Can I make a Yes/No dropdown in Excel without a formula?
Yes. Use Data > Data Tools > Data Validation, choose List, and enter the two options as the source. A formula is not required to create the dropdown.
Can users type Yes or No instead of clicking the arrow?
Yes. They can type a permitted value into a validated cell. The error alert determines what happens if they type something that is not on the list.
How do I add the dropdown to an entire column?
Select the intended input range in the column, such as B2:B500, before creating the validation rule. Applying it to a defined range rather than the whole worksheet column can keep the workbook easier to manage.
Why does my Yes/No list show one option?
The list separator may be wrong for your regional settings. Try the other common separator or put each choice in its own cell and use that range as the source.
Does Data Validation stop invalid values from being pasted?
Not reliably in every paste or import workflow. Validation is useful for normal data entry, but review imported or pasted values separately and use Circle Invalid Data to identify entries that do not meet the rule.
Related
- How to Combine Excel Files: Methods and Common Issues
- Combine Multiple XML Files Into One Excel Workbook
- Combine Excel Files: Practical Methods and Steps
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation List Auto Update: How to Set It UpYes—an Excel data validation dropdown can update automatically when its source list changes. For a list you keep…
- Excel Cell Number Format: How It Works and How to Use ItAn Excel cell number format controls how a value looks in a worksheet, not the value Excel stores…
- Excel Conditional Formatting Between Dates: How to Do ItTo highlight dates between two values in Excel, select the date cells and choose Home > Conditional Formatting…
- Google Sheets IF Blank: Formulas, Examples, and FixesIn Google Sheets, use =IF(A2="","Blank","Not blank") to test whether A2 is empty or returns an empty string; replace…
- Excel Protect Sheet: Allow Filtering and Sorting SafelyYes. In Excel for Microsoft 365 on Windows, turn on the filter for your data before protecting the…
- How to Clear Track Changes in WordTo clear tracked edits in Word, review them and choose Review > Accept > Accept All Changes to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.