IF checks whether something is true, and shows one answer if it is and another if it isn’t. It’s how a sheet makes a decision on every row.
D2 = Met Imani’s sales are above the target, so the answer is yes, and IF shows its second argument.
Say you have a list of sales reps, each with their sales and a target. IF looks at every row and writes Met or Missed beside it, so nobody reads down the list comparing numbers by hand. The same formula marks a test score Pass or Fail, flags an invoice as Overdue, or works out a bonus only for the people who earned one.
It’s one of the first functions an Excel test for job applicants asks about, and its mistakes are quiet: the formula runs and the column fills, with one row wrong. Below you build one step by step, then see it taken apart one piece at a time, each with a small sheet where you can make the classic mistakes yourself.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
IF takes three arguments: a question with a yes or no answer, what to show for yes, and what to show for no. It works out the question first, then shows one of the two answers and ignores the other.
D2 = Met Imani sold 48200 against a target of 45000. The test is true, so IF shows value_if_true and never looks at value_if_false.
The question is usually a comparison: = equal to, <> not equal to, > and < more and less than, >= and <= at least and at most. Two of them are easy to mix up, because they agree on every
row but one.
D4 = Met Sofia’s sales equal the target, and >= counts equal as reaching it.
Whether a value exactly on the line counts is a business rule, not an Excel one. Read the rule (“reaches”, “at least”, “over”), pick the operator that says it, and test the formula on a row that sits exactly on the line.
The two answers can be words, numbers, or calculations of their own. Words need double quotes, so Excel knows they’re text. Numbers and calculations must not have them.
D2 = Met In quotes, Met is text, and IF shows it as written.
A quoted number is the quiet version of this mistake: a bonus column of "500" looks
right, but it’s text, so a total over it comes to 0 without any error.
“Reached target and rated 4 or more” sounds like two IFs, but it’s one question with two parts. Put
them in AND when both must be true, or OR when either will do, and the
whole thing goes where the single comparison went.
E2 = No Sofia reached target but is rated 3. AND needs both, so the answer is No. Still one question, so still one IF.
When there are more than two answers, such as Star, Met and Missed, put a second IF where the first one’s “no” answer goes. Excel asks the questions in order and stops at the first yes, so the order matters.
D2 = Met Priya isn’t 20% over, so the first IF passes the question to the second, which finds the target reached: Met.
In Microsoft 365, Excel 2019 and later and Google Sheets, IFS does the same without the nesting: =IFS(B2>=C2*1.2, "Star", B2>=C2, "Met", TRUE, "Missed"). It lists question,
answer, question, answer, and also stops at the first yes. It has no “otherwise”, so the last
question is TRUE, which is always yes. Past three or four answers, a lookup table
usually reads better than either: VLOOKUP practice shows how.
To carry on in your own Excel, download the workbook: 150 employees with departments, start dates and salaries, and a sheet of IF tasks to work through, such as salary bands, tenure flags and department rules.
150 employees across departments and offices, with start dates and salaries. Practice lookups by ID, IF logic on salary bands, sorting, filtering, and date arithmetic.
150 rows · Employee ID · Full Name · Department · Office · Start Date · Annual Salary
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches IF: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →