Yes/No drop-down in Google Sheets: list, checkbox, COUNTIF

A Yes/No column is the smallest drop-down there is, and the one people build most often — for a checklist, an RSVP list, an audit. Here is the two-minute version for Google Sheets, the same in Excel, when a checkbox is the better choice, and the formulas that count the Yeses.

Contents

Google Sheets: Data → Data validation

Select the cells that should get the menu — a whole column of a table, say D2:D200.
Data → Data validation → Add rule.
Under Criteria choose Dropdown, type Yes in the first option and No in the second.
Pick a colour chip for each if you want the cell to turn green/red with the answer.
Done. Every cell in the range now shows a menu with the two choices.

Two settings worth touching: Display style (Chip, Arrow or Plain text — Chip is the one that shows the colour) and, under Advanced options, whether an invalid entry is rejected or just flagged. For a Yes/No column, reject: there is no third answer.

Excel: Data → Data Validation

Select the cells.
Data → Data Validation (Data Tools group).
Allow: List. Source: type Yes,No — the comma separates the items.
OK. Leave Ignore blank ticked so the cell can be cleared.

Excel has no colour chips; use conditional formatting if you want colour — Home → Conditional Formatting → Highlight Cells Rules → Text that Contains → No.

the source, in both apps

Yes,No              ← Excel: typed into the Source box
Yes  /  No          ← Google Sheets: one option per row in the rule

When a checkbox is better

If the question is really “done or not”, a checkbox reads faster than a menu, and both apps have one: Google Sheets Insert → Checkbox; Excel 365 Insert → Checkbox (older Excel doesn’t have it — see below). A checkbox holds TRUE/FALSE, so formulas read it directly:

reading a checkbox

=COUNTIF(D2:D200, TRUE)        how many are ticked
=IF(D2, "Done", "Open")         no ="Yes" needed — the cell is already a boolean

The catch is portability. A Google Sheets checkbox becomes plain TRUE/FALSE text when the file is opened in Excel 2016–2021, and Numbers turns it into its own checkbox format. That is why our own templates use a one-item drop-down with a tick character instead — it opens the same everywhere:

the ✓ that works in every app

Source:  ✓                       ← U+2713, one item; leave blank allowed
Count:   =COUNTIF(D2:D200,"<>")   any mark counts, including an x typed over it

Formulas that read a Yes/No column

count, test, and count with a condition

=COUNTIF(D2:D200, "Yes")                          how many said Yes
=COUNTIF(D2:D200, "No")                           how many said No
=COUNTA(D2:D200) - COUNTIF(D2:D200, "Yes") - COUNTIF(D2:D200, "No")   answered something else (shouldn't happen if invalid entries are rejected)
=IF(D2 = "Yes", B2, 0)                            a number that only counts when the answer is Yes
=COUNTIFS(D2:D200, "Yes", C2:C200, "Vegetarian")  Yes AND another column
=SUMIF(D2:D200, "Yes", E2:E200)                   add up a column for the Yes rows

COUNTIF is case-insensitive, so yes and Yes both count — but with the menu rejecting invalid entries you won’t get yes anyway. Don’t count blanks as No: an unanswered row is not a refusal, which is why the formulas above name each answer instead of subtracting.

Three variations

Yes / No / N/A — a third option for “doesn’t apply”. Add it to the list; count it separately so the Yes-rate is Yes ÷ (Yes + No), not Yes ÷ all.
A Yes/No that depends on another cell — you can’t make the menu itself conditional, but you can grey the cell: conditional formatting with a custom formula, =$C2="" to fade rows where the earlier answer is empty.
TRUE/FALSE as the list — avoid typing TRUE,FALSE as a text list in Excel: some locales turn the strings into booleans and some don’t, and ="TRUE" stops matching. Use Yes/No as words, or a real checkbox.

A typed Yes,No list is right for two options that will never change. For lists that will — statuses, categories, people — put the options on a Setup sheet and point the validation at a named range; that is how every workbook in our catalogue does it, and the full guide on drop-down lists in Excel and Google Sheets walks through it, including dependent menus.