How to Add Yes or No Answers in Excel

The fastest way to add yes or no answers in Excel

The simplest method is to type "Yes" or "No" directly into a cell, the same way you would type any other text. Excel treats it like regular data — no special setup needed. If you want to move faster, use a dropdown list so you can click to select from just those two options instead of typing each time. This takes about 30 seconds to set up and saves time if you are filling in the same column repeatedly.

For more complex work — like counting how many "Yes" answers you have, or hiding rows based on whether the answer is "Yes" — you can use formulas. But start with the dropdown if you are new to Excel. It handles most situations without requiring you to learn formula syntax.

Key Takeaways

Type "Yes" or "No" directly into cells, or use a dropdown list to select from those two options with a single click.
A dropdown list takes about 30 seconds to create and prevents typos like "YES" or "yeah" that would break formulas later.
Use the COUNTIF function to count how many cells contain "Yes" or "No" in a column.
Conditional formatting can automatically highlight or color rows based on whether the answer is "Yes" or "No".

Creating a dropdown list for yes or no answers

Open the spreadsheet and click on the cell where you want the dropdown to appear. If you want the dropdown in multiple cells in the same column, select the entire range instead — for example, click on cell B2, then hold Shift and click on B100 to select all cells from B2 to B100.

Go to the Data menu at the top and click Data Validation (in Google Sheets it is called "Data validation"; in Excel for Mac it may say "Validity"). A dialog box opens. Under the dropdown that says "Allow", select List. In the box labeled "Source" or "List", type Yes,No with no spaces. Click OK.

Now when you click on any cell in that range, a small arrow appears on the right side of the cell. Click the arrow to see "Yes" and "No" as your only two options. This prevents typos and makes the data consistent, which matters if you later write a formula that counts or filters based on these answers.

Counting yes or no answers with a formula

If you have a column of yes or no answers and want to know how many of each you have, use the COUNTIF function. Click on an empty cell where you want the count to appear. Type =COUNTIF(B2:B100,"Yes") and press Enter. Replace B2:B100 with the actual range of cells you want to count. The formula returns the number of cells containing exactly "Yes".

To count the "No" answers, use the same formula but replace "Yes" with "No": =COUNTIF(B2:B100,"No"). If you want to count both at once and see what percentage are "Yes", you can divide: =COUNTIF(B2:B100,"Yes")/COUNTA(B2:B100). This gives you a decimal (0.75 means 75 percent). Multiply by 100 if you want it displayed as a whole number.

Highlighting rows based on yes or no answers

Conditional formatting automatically colors or highlights cells based on their content. Select the range of cells containing your yes or no answers. Go to Format in the menu, then Conditional Formatting. A panel opens on the right side (in Excel) or a dialog appears (in Google Sheets).

Choose Format rules or Format only if, then select Cell is equal to or Text is exactly. Type "Yes" in the text box. Click the color or fill button and choose the color you want — green is common for "Yes". Click Done. Repeat the process for "No" if you want a different color for that answer.

Now every cell containing "Yes" appears in your chosen color automatically. If you change a cell from "No" to "Yes", the color updates when ready. This makes it straightforward to scan a long list and see the pattern at a glance.

Using yes or no answers in larger formulas

Once you have yes or no answers in a column, you can use them to control what other formulas do. For example, if column B contains "Yes" or "No" and column C contains dollar amounts, you can sum only the amounts where the answer is "Yes": =SUMIF(B2:B100,"Yes",C2:C100). This adds up all the values in column C where the corresponding cell in column B says "Yes".

You can also use IF statements to create new columns based on the yes or no answer. Type =IF(B2="Yes",100,0) to put 100 in the cell if B2 is "Yes", or 0 if it is anything else. Copy this formula down the column and it applies to every row. This is useful when you need to assign points, calculate bonuses, or flag items for further action based on a yes or no decision.

Common mistakes when working with yes or no answers

The most common error is inconsistent capitalization or spelling. If some cells say "Yes", others say "YES", and others say "yes", a formula looking for "Yes" will miss the other two. This is why the dropdown list is so valuable — it forces every entry to be identical. If you inherit a spreadsheet with mixed capitalization, use Find and Replace to fix it before writing formulas.

Another mistake is leaving cells blank instead of choosing "Yes" or "No". A blank cell is not the same as "No" in a formula. If you want blank cells to count as "No", you have to write the formula to handle that: =COUNTIF(B2:B100,"Yes") counts only "Yes", but =COUNTIF(B2:B100,"<>Yes") counts everything that is not "Yes", including blanks. Be clear about what blank means in your spreadsheet before you start.

Frequently Asked Questions

Can I use TRUE or FALSE instead of Yes or No?

Yes. Excel and Google Sheets treat TRUE and FALSE as special values that work in formulas slightly differently than text. If you use TRUE/FALSE, formulas like =COUNTIF(B2:B100,TRUE) work the same way. TRUE and FALSE take up less space and are faster in very large spreadsheets, but "Yes" and "No" are easier to read when you are scanning the data by eye.

What if I want to add yes or no answers to a cell that already has other data?

You cannot put two separate pieces of data in one cell and keep them independent. Create a new column instead. If you need to track multiple yes or no questions about the same item, use one column per question — for example, column B for "Approved", column C for "Paid", column D for "Shipped".

How do I copy a dropdown list to other cells?

Select the cell with the dropdown you created. Copy it (Ctrl+C or Cmd+C). Select the range where you want the dropdown to appear and paste (Ctrl+V or Cmd+V). The dropdown settings copy along with the cell. This is faster than creating the validation rule from scratch each time.

Can I sort or filter by yes or no answers?

Yes. Click any cell in your data, then go to Data and click Sort or Filter. You can sort so all "Yes" answers appear together, or filter to show only rows where the answer is "Yes". This is useful for grouping related items or hiding rows you do not need to see right now.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.