Return Yes or No in Excel With IF

A yes no formula in Excel puts a test inside IF and returns the word Yes or No: =IF(B2>=100,"Yes","No"). It is the simplest way to answer questions like "did this rep hit target?" or "is this invoice overdue?" in a column people can read at a glance. This guide shows the basic pattern and the most common tests: numbers, blanks, text, dates and two conditions at once.

How to return Yes or No with IF#

IF checks a condition and returns one value if it is TRUE and another if it is FALSE. For a yes/no answer, those two values are the words "Yes" and "No":

=IF(logical_test, "Yes", "No")

Here is the sales table used in the first examples. The target is 100:

1. Click C2.
2. Type =IF(B2>=100,"Yes","No") and press Enter. Ana sold 120, so C2 shows Yes.
3. Fill the formula down to C6 (Windows: Ctrl+D; Mac: ⌘D).

Ana, Cara and Eve get Yes. Ben and Dan get No. Cara's 100 counts because >= means "greater than or equal to". The guide to the Excel IF function explains each argument, and IF greater than covers the other number comparisons.

Why not just use TRUE and FALSE?#

A comparison on its own already gives a yes/no answer, just in different words. Type =B2>=100 in a cell and Excel shows TRUE for Ana and FALSE for Ben. These are logical values: Excel's built-in way of saying yes and no.

TRUE and FALSE are handy inside other formulas, but Yes and No read better in a report. IF is simply the step that swaps one pair of words for the other. You can return any labels you like, such as "Met" and "Missed" or "Late" and "On time".

Yes or No for blank and filled cells#

To check whether something was entered, compare the cell to "", which means empty text. This returns Yes when B2 has any value:

=IF(B2<>"","Yes","No")

<> means "not equal to", so the test reads "B2 is not empty". To flag missing entries instead, flip it: =IF(B2="","Yes","No") answers "Is it missing?". A cell that holds only a space is not empty to Excel, so it counts as filled.

Yes or No when a cell contains certain text#

For an exact match, compare the cell to the word: =IF(A2="Paid","Yes","No"). The comparison is not case-sensitive, so "paid" and "PAID" also give Yes. The article on IF with text covers text tests in depth.

To find a word anywhere inside a longer cell, such as "urgent" in "Call back - urgent", use SEARCH with ISNUMBER:

=IF(ISNUMBER(SEARCH("urgent",A2)),"Yes","No")

SEARCH returns the position of the word if it finds it, and an error if it doesn't. ISNUMBER turns that into TRUE or FALSE, and IF turns it into Yes or No. Wildcards like "*urgent*" don't work in a plain = comparison, which is why this pattern uses SEARCH.

Yes or No for dates#

Dates in Excel are numbers, so you compare them like numbers. To flag invoices whose due date in B2 has passed:

=IF(B2<TODAY(),"Yes","No")

TODAY returns the current date, so this answer can change from day to day. For a fixed cut-off, use DATE: =IF(B2<DATE(2024,6,30),"Yes","No") returns Yes for any date before 30 June 2024. Typing the date as text, like "30/06/2024", depends on your regional settings and can give the wrong result.

Yes or No with two conditions#

When the answer depends on two things, put AND or OR inside IF. A rep gets a bonus only if sales are 100 or more and returns are below 5:

=IF(AND(B2>=100,C2<5),"Yes","No")

AND returns TRUE only when both tests are TRUE. Use OR when either one is enough. The guide to IF with AND has more examples, and the article on IF THEN formulas shows how to read any IF as an "if this, then that" sentence.

How to count and use Yes and No answers#

Once a column holds Yes and No, COUNTIF counts each answer. On the sales table:

To turn the answers into numbers you can add, use =IF(C2="Yes",1,0). The guide to counting Yes, No and ticks covers more ways to count answers.

If people type Yes and No by hand, add a drop-down list so the spelling stays consistent. Select the cells, go to Data > Data Validation, choose List, and type Yes,No as the source. Typos like "Yse" or "yes " (with a space) are a common reason a count comes out wrong.

Yes or No formulas in a job test#

Screening tests often phrase this task as "add a column that shows Yes if the rep hit the target". A few habits help you get it right under time pressure:

1. Read the rule carefully. "At least 100" means >=; "more than 100" means >.
2. Copy the exact labels. If the task asks for "Y" and "N", don't type Yes and No. Automatic grading often compares the text.
3. Check the edge row. Find a value that sits exactly on the limit, like Cara's 100, and make sure it gets the right answer.
4. Use the limit cell if there is one. Refer to it with $ signs, like $F$1, so the formula still works when the limit changes.

To check yourself on tasks like this, try the Excel screening quiz.

Practise it#

The yes/no pattern is the heart of every IF formula, and it appears in nearly every Excel screening test. Practise the IF function in a live spreadsheet: you type the formula and it checks it instantly. To get comfortable with the tests themselves, work through TRUE and FALSE comparisons, and keep the IF function guide handy for the syntax.