Yes—Excel can use Data Validation to limit a cell to Yes or No, usually with a dropdown list. In Microsoft 365 for Windows, select the cells and choose Data > Data Validation > Data Validation, then set Allow to List and enter Yes,No as the source. This helps standardize entries, but it does not prevent every invalid value from being pasted into a cell.
What Yes-or-No Data Validation Means
Excel does not have a dedicated Yes/No validation setting. Instead, you create a list rule whose allowed entries are the two text values Yes and No. With the in-cell dropdown enabled, a user can select either option; the rule can also accept someone typing an allowed entry directly.
This is useful when a column records a simple decision or status, such as whether an order has shipped, a task is complete, or a customer has given permission. A controlled list reduces variations such as “Y,” “yep,” “Nope,” and “N,” which can make filtering, counting, and reporting less reliable.
The values in a list are text, not special logical TRUE and FALSE values. That distinction matters if another formula, export, or system expects Boolean values or numeric codes. For ordinary tracking tables, the words Yes and No are often clearest to people reading the sheet.
How to Create the Dropdown in Excel
For a short list, enter the two choices directly in the validation rule. In Excel for Microsoft 365 on Windows:
- Select the cell or range where people should enter an answer, such as C2:C100.
- Open Data > Data Validation > Data Validation.
- On the Settings tab, set Allow to List.
- In Source, type Yes,No. Keep the choices separated by the list separator Excel expects in your regional settings; in many installations that is a comma.
- Make sure In-cell dropdown is selected, then choose OK.
Click a validated cell to see its dropdown arrow. Selecting Yes or No enters that value into the cell. You can still type an allowed value yourself, so the dropdown is a convenience rather than the only way to enter data.
To provide guidance, open the Input Message tab before choosing OK and add a short instruction, such as “Choose Yes if the item has been reviewed.” On the Error Alert tab, keep Show error alert selected and use the Stop style if you want Excel to reject disallowed entries entered normally. A useful message explains what the user should do instead of merely saying the value is invalid.
Choosing Between a Typed List and a Source Range
Typing Yes,No in Source is quick for a fixed two-choice list. It is suitable when the choices are unlikely to change and the workbook is used in a consistent regional setup. In some locales, the separator expected between list items differs, so a typed list may not behave as expected when a workbook moves between computers with different settings.
A source range is easier to maintain in a shared or longer-lived workbook. Put Yes and No in two cells, for example on a Lists worksheet, and use that range as the validation source. Since Excel validation lists have restrictions on direct references to another worksheet, a common approach is to define a named range for those cells and use the name as the Source. Create or manage names from Formulas > Name Manager. If the available controls differ in Excel for the web, set up or adjust the rule in the desktop app.
A range also makes later changes simpler: editing the source cells updates the choices without reopening every rule. For a simple Yes/No field, avoid making the setup more elaborate than the workbook needs. A two-item typed list is usually sufficient for a personal sheet; a named source is a more maintainable choice for a template used by a team.
What the Rule Does—and What It Cannot Guarantee
Data Validation guides entry and checks values when a user enters them through normal cell editing. With a Stop alert, typing an unlisted value usually prompts Excel to reject it or ask the user to correct it. The rule can also show an input message when a validated cell is selected.
Validation is not a security boundary. Copying and pasting data into a validated cell can introduce a value that does not meet the rule, and some fill or import workflows can also leave unexpected content. Pasting can additionally replace the validation rule itself, depending on how the paste is performed. Therefore, do not treat a dropdown as proof that every value in a large or shared workbook is valid.
For data that matters, periodically check the column for unexpected entries. You can use filters to inspect distinct values, or apply a separate check in another column. Protecting the worksheet can reduce accidental changes to formulas or setup, but sheet protection is not a substitute for auditing data, and it does not turn validation into access control.
Using Yes and No Reliably in Formulas and Reports
Consistent wording makes summaries easier. For example, a table with a Status column containing only Yes and No can be filtered by either value. PivotTables can summarize the categories, and a simple count can show how many records are marked Yes. Keep the exact labels consistent across worksheets if you plan to combine or compare data.
Think about blanks before applying the rule. The Ignore blank option allows empty cells to remain empty; it does not force a user to answer. That is often appropriate while a form is being completed. If every record must eventually have an answer, use a separate completion check or review process rather than assuming a dropdown makes the field mandatory.
Also decide what Yes means. A column headed “Approved?” is clearer than a generic “Answer” column, and a short input message can define the condition. Clear labels prevent a standardized response from being interpreted differently by different people.
Common Problems and How to Fix Them
- The dropdown arrow is missing: Select the cell and reopen Data > Data Validation > Data Validation. Confirm Allow is List and In-cell dropdown is checked. The arrow normally appears when the cell is selected.
- Excel rejects a choice or shows one combined item: Check the Source separator and regional settings. If the workbook is shared across locales, use a source range rather than a typed comma-separated list.
- An invalid value appears anyway: It may have been pasted or brought in by another workflow. Reapply the intended validation if the rule was overwritten, then inspect existing cell contents separately.
- Some cells work and others do not: The rule may have been applied to only part of the intended range, or different cells may have different settings. Select the full target range and review the validation rule before applying it consistently.
- Users can leave the cell empty: Check Ignore blank and remember that a list rule does not make an answer compulsory. Add a separate completeness check if blanks need follow-up.
Data Validation Compared with Checkboxes
A Yes/No dropdown and a checkbox both offer a way to record a choice, but they suit different layouts. A dropdown stores the visible text Yes or No directly in the cell. It is compact, easy to filter, and clear in a table with many rows.
A checkbox is a visual control that represents a checked or unchecked state. Depending on the checkbox feature and workbook setup, its value may be a Boolean value rather than the words Yes and No. That can be handy for a compact checklist, but it is not automatically interchangeable with a text list for formulas, exports, or existing reports. Choose based on how the data will be read and used, not just on appearance.
Frequently Asked Questions
Can I make a Yes-or-No dropdown in Excel?
Yes. Select the cells, open Data > Data Validation > Data Validation, choose List for Allow, and set the source to the two choices. Enable In-cell dropdown if you want users to select an item from an arrow.
Does Excel have a built-in Yes/No validation option?
No dedicated Yes/No preset is needed. Use the List option and supply Yes and No as the allowed entries. This creates the same practical choice without requiring a special validation type.
Can users type Yes or No instead of using the dropdown?
Yes. A list dropdown does not require clicking the arrow; users can type an allowed value. The error alert can reject other entries during ordinary editing, but pasting can bypass the check.
How do I make the answer required?
A list rule alone does not require a completed answer. Ignore blank controls whether blanks are allowed during entry, but it is not a dependable way to enforce completion in every workflow. Use a separate review or completeness check to find unanswered records.
Why did an invalid value get into a validated cell?
It may have been pasted, imported, or entered after the validation rule was removed or changed. Check both the cell contents and the rule. Reapply validation to the intended range and audit existing entries rather than assuming the rule corrected them automatically.
Related
- Excel Text to Columns: How to Split Data
- Excel Slicer: Change a Vertical List to Horizontal
- How to Protect a Sheet in Excel and Set Permissions
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Why Is Excel Not Letting Me Transpose Data?Excel usually will not transpose data because the source was not copied as a normal cell range, or…
- Budget Spreadsheet App: How to Choose and Use It WellA budget spreadsheet app is a spreadsheet program you use to plan income, track spending, and see how…
- How to Add a Header in Microsoft WordTo add a header in Microsoft Word, open the document, select Insert > Header, choose a built-in layout,…
- Can You Merge Cells in Excel? Options and LimitsYes, you can merge cells in Excel for Microsoft 365 on Windows: select the cells, then choose Home…
- Excel Named Range Shortcut: Create, Edit, and Use NamesIn Excel for Microsoft 365 on Windows, press Ctrl+F3 to open Name Manager and manage named ranges. To…
- How to Insert a Line Above a Table in WordTo add a line of text or a blank paragraph above a table in Word, put the cursor…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.