Excel Yes/No Drop Down: add one in 20 seconds

A Yes/No column is the most common drop down there is. Twenty seconds.

Steps

1. Select the cells that should have the list — say C2:C100.
2. Data › Data Validation.
3. Allow: List. Source: type Yes,No. OK.

Click any cell in the range: an arrow appears with Yes and No.

Colour Yes green and No red

1. Select the same cells. Home › Conditional Formatting › Highlight Cells Rules › Equal To.
2. Type Yes, choose Green Fill. OK.
3. Repeat with No and Light Red Fill.

Count the answers

=COUNTIF(C:C, "Yes")
=COUNTIF(C:C, "No")

Add a third option

Open Data Validation again and change Source to Yes,No,Maybe.

Check it worked

Try typing yes in lower case — Excel accepts it (validation ignores case). Try typing Y — rejected.

Prefer a tick box?

Excel 365: Insert › Checkbox. Older Excel: Developer tab › Insert › Check Box. A drop down is simpler and works in every version.

More list types: drop down list.