Yes—you can create a Yes/No column in Excel with a dropdown list. Select the cells, go to Data > Data Validation, choose List, and enter Yes,No as the source; each selected cell will then offer those two choices. A dropdown makes responses more consistent than typing them, though it does not prevent every invalid entry if someone pastes data into a cell.
What a Yes/No Column Is For
A Yes/No column records a simple decision or status for each row in a table. For example, a project tracker might use a column called Approved, a mailing list might use Contacted, and an inventory sheet might use In stock. The consistent two-choice format makes it easier to scan records, filter a list, and summarize answers.
Excel cells can hold ordinary text such as Yes and No without any special setup. The dropdown is a data-validation feature: it helps people choose from an approved list rather than entering different spellings or variations. It is useful when several people update the same workbook or when the answers will later be counted or filtered.
Decide what the two answers mean before building the column. A blank cell can mean not answered yet, while No means the question was answered and the answer is negative. Keeping those meanings separate is often important. If you need more than two states—for example, Yes, No, and Pending—use a three-option list rather than treating a blank as a third answer.
Create the Dropdown in Excel for Windows
These steps apply to Excel for Microsoft 365 on Windows. To add a Yes/No dropdown to a column without a header, such as B2:B100, use the following process:
- Select the cells that should contain the choices, such as B2:B100. Do not include the header cell if it contains a heading such as Approved.
- Open the Data tab on the ribbon and select Data Validation. In the dialog, stay on the Settings tab.
- Set Allow to List. Leave In-cell dropdown selected so the arrow appears when a validated cell is selected.
- In Source, enter
Yes,No, then select OK. - Test a cell in the range by selecting it and choosing Yes or No from the arrow.
The order in the Source box determines the order shown in the dropdown. You could enter No,Yes if No should appear first. If you enter the values directly, do not add spaces around the comma unless you want those spaces included in the list items.
For an existing Excel table, you can select the data cells in the relevant table column and apply the same validation. A table may extend when new rows are added, but do not assume every workbook setup will automatically extend the validation rule in the way you expect. Add a test row and confirm that its cell has the dropdown. If necessary, copy a validated cell into new rows or apply validation to a larger intended range.
Use a Cell Range as the List Source
Typing Yes,No directly into Source is quick for a short list. A separate source range is often easier to maintain, especially if the choices might change or the workbook uses regional settings with a different list separator.
For example, put Yes in E1 and No in E2. Select the destination cells, open Data > Data Validation, set Allow to List, and enter =$E$1:$E$2 in Source. The dollar signs keep the source reference fixed if the validation is copied. You can keep the source values on a separate worksheet and hide that sheet if appropriate, but hiding it is only an organizational choice, not a security measure.
In some regional configurations, a comma typed between list items may not be interpreted as the separator Excel expects. A cell-range source avoids that particular issue. If a directly typed list is not behaving as expected, try a source range or check the list-separator settings used by your system.
Set Blanks and Error Messages
In the Data Validation dialog, Ignore blank controls whether an empty cell is permitted by the validation rule. Leave it selected if people may not have an answer yet. Clear it if each cell must contain a choice, then test the behavior in your workbook. A required response may still need follow-up checks: validation alone is not a full audit of whether every intended record has been completed.
The Error Alert tab lets you choose what Excel displays when someone enters a value that does not meet the rule. A Stop alert blocks an invalid value entered directly and shows your message. You might use a short prompt such as “Choose Yes or No from the list.” Warning and Information alerts are less restrictive because they allow the user to continue after confirmation.
Data validation is a helpful input control, not a guarantee that a column contains only approved answers forever. Pasting content into validated cells can replace the rule or introduce values that do not match it. Imports, copied worksheets, and edits from other applications can also complicate consistency. For important records, periodically inspect the column and consider protecting worksheet structure or using a controlled data-entry process.
Count and Review the Answers
Once responses are consistent, you can count them with COUNTIF. With answers in B2:B100, this formula returns the number of cells containing Yes. If 37 cells contain Yes, the result is 37:
=COUNTIF(B2:B100,"Yes")
With the same answer range, this formula counts No responses. If 12 cells contain No, it returns 12:
=COUNTIF(B2:B100,"No")
These two counts need not add up to 99, because some cells may be blank or contain other text. That can be useful for finding incomplete data. To review entries visually, use the filter arrows on a table or turn on filtering from Data > Filter, then filter the Yes/No column for one answer at a time.
For a quick visual distinction, you can apply conditional formatting to the answer cells. Select the range and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then create a rule for Yes and another for No. Choose readable formatting and make sure the colors are not the only way the result is conveyed; the words should remain visible for people who cannot distinguish the colors easily.
Common Problems and Fixes
- No dropdown arrow appears: Select a cell that should have the rule and open Data > Data Validation to confirm Allow is set to List and In-cell dropdown is selected. The arrow may appear only while the cell is selected.
- The list accepts unexpected text: Check the Error Alert tab and use the Stop style. Also check for pasted values, because pasting can bypass the normal entry experience or overwrite validation.
- Some answers do not count: Look for extra spaces, misspellings, or different text such as Y, TRUE, or Not yet. A dropdown improves consistency, but existing data is not automatically converted to the approved choices.
- The rule is missing in new rows: Apply validation to the newly added cells or copy a correctly validated cell. Verify the rule after extending a table or pasting rows.
- The typed source is treated as one item: Your regional list separator may differ. Put Yes and No in separate cells and use those cells as the source instead.
If you find inconsistent historical entries, correct them deliberately before relying on totals. For instance, replacing Y with Yes may be appropriate if the column clearly represents a Yes/No answer, but do not make a bulk replacement until you have checked what the existing values mean.
Dropdowns Compared With Checkboxes and Text
A Yes/No dropdown is a good fit when the answer should be stored as readable text and users may need to choose either option. It works naturally with filters, COUNTIF, and reports. It also allows a blank response when the validation settings and workflow permit one.
Excel checkboxes can be useful when a task is simply complete or incomplete. In supported Microsoft 365 versions, in-cell checkboxes represent a checked or unchecked state, which may suit a checklist. However, they do not display the words Yes and No in the same way as a dropdown. Choose based on how the data will be read and analyzed, and confirm that the checkbox feature is available in the Excel version used by everyone who edits the workbook.
Free-text cells are the least restrictive option. They may be appropriate for comments or answers that need explanation, but they are a poor substitute for a controlled binary field when you want dependable counts. If users need to explain a No answer, consider keeping the Yes/No dropdown and adding a separate Notes column rather than mixing explanations into the answer itself.
Excel for Mac and Excel for the Web
In Excel for Mac, the feature is also called Data Validation and is available from the Data tab. The dialog labels and layout can differ slightly from Windows, but the essential setup is the same: select the cells, choose a list rule, provide the source, and enable the in-cell dropdown. Mac keyboard shortcuts can differ from Windows shortcuts, so using the ribbon is a dependable way to find the command.
Excel for the web also supports data validation, although some dialog details and workbook behaviors can vary with the current version and file. If you cannot find a setting or need to manage a complex workbook, open it in the desktop application and confirm the rule there. When a workbook is shared, test the dropdown in the version and editing environment that the intended users will use.
Frequently Asked Questions
How do I make a Yes/No dropdown in Excel?
Select the cells, choose Data > Data Validation, set Allow to List, and enter Yes,No in Source. Keep In-cell dropdown selected and choose OK.
Can I make a Yes/No dropdown for an entire column?
Yes. Select the cells beneath the heading where answers will go, or select the column if the header and other cells should also receive the rule. Applying it to a practical range such as B2:B1000 can be easier to manage than including a header.
Can I leave a Yes/No cell blank?
Yes, if your validation setup and workflow allow it. A blank can mean unanswered, so keep it distinct from No when reviewing results. You can use counts or filters to identify blanks that still need attention.
Why can someone paste an invalid answer into the column?
Data validation is primarily an entry rule, and pasting can bypass the dropdown or replace cell settings. Use a Stop error alert, check the data after imports or pastes, and protect the workbook or control editing when stronger consistency is needed.
Can I count how many Yes answers I have?
Yes. If the answers are in B2:B100, use =COUNTIF(B2:B100,"Yes"). The formula counts cells containing the text Yes; check for blanks or inconsistent existing values when comparing the result with the number of records.
Related
- How to Run Solver in Excel: Setup and Optimization
- K-Means Clustering in Excel: Solver and Easier Methods
- Excel Solver Two-Variable Example and Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Duplicate Entries: Find, Highlight, RemoveTo find duplicate entries in Google Sheets, use Format > Conditional formatting to highlight repeats, or choose Data…
- Change View in New Outlook: Layout and Reading PaneTo change how email looks in new Outlook, open View > View settings, or select the Settings gear…
- Excel: How to Consolidate Multiple Rows Into One CellIf your goal is to combine values from matching rows into one cell, put the matching key in…
- Splinter Cell Split Jump: How the Wall Move Works in the GamesA Splinter Cell split jump is a move in which Sam Fisher jumps up in a narrow passage…
- How to Add a Subject Line to a Word Mail Merge EmailTo add a subject line to a Word email merge, finish preparing your message, then choose Mailings >…
- Excel Advanced Goal Seek: Solver, Limits, and ExamplesExcel has no separate feature called Advanced Goal Seek: the built-in Goal Seek tool finds a value for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.