A Yes or No drop-down list in Excel is built with Data Validation using the List source type, and entering the literal value Yes,No in the Source field gives you a clickable two-option picker in any cell. The drop-down enforces consistent answers, removes typo risk like "yeS" or "no ", and works in both classic Excel and Excel 365 without formulas or VBA. You can source the list from a comma-separated string, a cell range, or a named range so future edits stay in one place. Excel stores every selected answer permanently in the cell, which is the right behavior when you need a record, but the wrong tool when you only want a quick casual 50/50 prompt. For those cases, the Yes or No Generator produces an unbiased binary answer in your browser using Web Crypto, with an optional question and a 20-entry history, instead of writing anything to a cell.
What a Yes or No Drop-Down List Does in Excel
A Data Validation drop-down turns a plain cell into a controlled picker. The user clicks the arrow, sees exactly the values you allowed, and picks one. Anything outside that list can be blocked outright or warned against, depending on how strict you want to be. For a Yes or No worksheet, this means a column of answers will always read "Yes" or "No" with the capitalization and spelling you defined.
The List source type is the simplest way to create a binary drop-down. You do not need a lookup table, a helper column, or a named range to get a working picker, although those become useful once you scale the list across many cells or sheets. The drop-down arrow only appears when the cell is selected, so a printed or shared copy of the sheet still reads normally while the editing experience stays controlled.
Create the Yes or No Drop-Down in Excel
These steps use the built-in Data Validation dialog and apply to Excel 365, Excel 2021, Excel 2019, and earlier desktop versions.
- Select the cell or range where the Yes or No picker should appear. A single column such as B2:B100 is a common choice for survey or checklist sheets.
- Open the Data tab on the ribbon and click Data Validation in the Data Tools group.
- In the Settings tab of the dialog, set Allow to List.
- Click inside the Source box and type exactly Yes,No with no spaces around the comma. Excel reads this as a literal two-item list.
- Leave In-cell dropdown checked so the arrow appears in the cell, and leave Ignore blank checked so empty cells stay editable.
- Switch to the Input Message tab if you want a tooltip like "Pick Yes or No" when the cell is selected. The title and message are optional.
- Switch to the Error Alert tab and choose Stop under Style to block entries that are not Yes or No. Use Warning or Information if you want users to be able to override the rule.
- Click OK. The arrow appears immediately in every cell of the selected range.
If you would rather pull the list from cells, put Yes in one cell and No in the cell directly below it, select both, and enter a name like YesNoList in the Name Box. Then set the Source to =YesNoList so editing those two cells updates every drop-down in the workbook.
Make the Drop-Down Work Better in Real Worksheets
A few small touches turn the basic Yes or No picker into a sheet that holds up under real use. First, lock the sheet or the input cells so users cannot delete the data validation rule itself by right-clicking and choosing Delete. Reviewers can still pick from the list, but the rule stays intact.
Second, count answers with COUNTIF(range, "Yes") and COUNTIF(range, "No") so the sheet reports its own totals. A simple percentage column such as =COUNTIF(B2:B100,"Yes")/COUNTA(B2:B100) makes the result visual without adding complexity.
Third, if you need a default value for new rows, use IF(A2="","",IF(A2="yes","Yes",IF(A2="no","No",""))) in an adjacent column. This keeps user input permissive while the official column stays locked to Yes or No only.
| Data Validation option | Effect on a Yes or No list |
|---|---|
| List source = Yes,No | Two-item picker with no setup cost |
| List source = named range | Centralized list editing across the workbook |
| Error Alert style = Stop | Blocks any value other than Yes or No |
| Error Alert style = Warning | Allows override with a confirmation prompt |
| Ignore blank = On | Empty cells remain valid and editable |
| Input Message enabled | Tooltip guides the user when the cell is active |
When a Standalone Random Yes or No Tool Fits Better
Not every Yes or No question belongs in a spreadsheet. If the goal is a quick tiebreaker for a board game, a casual choice between two restaurants, or a prompt for the next topic in a classroom discussion, opening Excel feels heavy. The Yes or No Generator handles that lightweight case in a single browser tab, with no file, no account, and no upload.
The generator is built around a 50/50 outcome that does not depend on the question, the previous answer, or any clock-driven seed. Each generation draws a single 32-bit unsigned integer from Crypto.getRandomValues, the standard browser API described in MDN's Crypto.getRandomValues reference, and maps it deterministically to Yes or No. Because the source range of 2^32 is exactly divisible by two, every valid value is accepted and even numbers map to Yes while odd numbers map to No, which keeps the split perfectly balanced.
For a different but related lightweight decision, the guide Flip a Coin for Yes or No Decisions Instantly walks through a virtual coin flip with the same casual-decision framing, useful when you want a visible image of the coin as well as the answer.
How the Yes or No Generator Produces a 50/50 Answer
The generator runs entirely in the current tab and stores nothing remotely. The verified flow has three steps.
- Optionally type a low-stakes question of no more than 1,000 UTF-16 code units into the question field, or leave it empty. Empty text is valid because the question is a label, not an input that drives the answer.
- Click the Generate button. The tool asks the browser to fill a one-element Uint32Array with Crypto.getRandomValues. Even values produce Yes and odd values produce No, so the mapping is exact.
- Read the current answer, then use the newest-first history if you want to compare recent results. The history holds at most the newest 20 entries. After the twenty-first generation the oldest entry is removed and the cumulative count of discarded entries is shown, so nothing disappears silently. Use the clear control to wipe history and the current answer when you are done.
If Web Crypto is missing, blocked, or throws, the tool reports a clear failure and produces no answer. There is no fallback to Math.random because silently changing the random source would contradict the displayed methodology and make failures harder to detect. Editing the question clears the current answer and any prior error so an answer associated with older wording is not mislabeled, but it does not touch the history.
Excel Drop-Down Versus the Yes or No Generator
Both approaches answer a binary question, but they serve different jobs. The table below summarizes the practical differences so you can pick the right tool for the moment.
| Aspect | Excel Data Validation list | Yes or No Generator |
|---|---|---|
| Where the answer lives | Written into the worksheet cell, saved with the file | Displayed in the browser tab, kept only locally |
| Randomness source | Not random; user picks the value | One Uint32 from Crypto.getRandomValues per generation |
| History | Every previous answer stays in the column | Newest-first, capped at 20 with a visible discarded count |
| Privacy | Stored wherever the .xlsx file is stored | Never uploaded, never written to an account |
| Best for | Recorded checklists, surveys, attendance, approvals | Casual tiebreakers, quick prompts, classroom polls |
| Limitations | Requires Excel; users can paste over the rule if the sheet is unprotected | Not suitable for high-stakes decisions of any kind |
Safety Notes for Either Method
Data Validation in Excel is excellent for clean data and consistent records, but it is not a safeguard against people who paste over the validation or unprotect the sheet. Treat the drop-down as a polite guardrail rather than a security boundary. If the Yes or No question drives a real-world outcome such as a hire, a refund, a medical step, or a safety check, replace the random tool with documented criteria and a human reviewer.
The Yes or No Generator is similarly scoped. It is described as a casual prompt, not advice or a decision authority. A mathematically balanced answer can still be unsuitable, unlucky, or harmful when applied outside low-stakes situations. Independent 50/50 results can form streaks, and the visible history is descriptive only; it never modifies the probability to chase a streak. Use the tool for what it is good at and stop there.