Google Sheets IF Function: Syntax, Examples & Tips

In Google Sheets, the IF function checks a condition and returns one value when it is true and another when it is false. Its basic form is =IF(condition, value_if_true, value_if_false); for example, =IF(B2>=70,"Pass","Review") returns Pass when B2 is 70 or higher and Review otherwise.

What the IF Function Does

IF is a logical function: it lets a spreadsheet make a choice based on the contents of a cell or the result of an expression. Instead of manually labeling each row, you can write a rule once and fill it down a column. This is useful for grading, checking whether orders qualify for a discount, flagging overdue dates, and displaying a message when a value crosses a threshold.

A simple condition has two possible outcomes. In =IF(C2>0,"In stock","Out of stock"), Google Sheets checks whether the value in C2 is greater than zero. If it is, the formula returns the text In stock; if not, it returns Out of stock. The formula does not change C2 or apply formatting to it. It places a result in the cell containing the formula.

IF is not limited to displaying words. Its outcomes can be numbers, dates, cell references, calculations, or another formula. For example, it can return a shipping fee for qualifying orders and a different fee for others. Both outcomes should be chosen to make sense for the condition you are testing.

IF Syntax and How Evaluation Works

The Google Sheets syntax is IF(logical_expression, value_if_true, value_if_false). The first argument is the test, the second is returned if the test evaluates as true, and the third is returned if it evaluates as false. In a cell formula, begin with an equals sign, as in =IF(A2="Yes",1,0).

The test can compare values using operators such as =, <> (not equal), >, <, >=, and <=. For example, with a score in B2, the test B2>=70 is true when the score is 70 or more. Text values in formulas need straight double quotation marks, while numbers generally do not: compare A2="Complete" with A2>=100.

Google Sheets allows the third argument to be omitted; if the test is false, the result is then blank. For clarity, especially in shared spreadsheets, it is usually better to state the false result explicitly. Use commas between arguments in the examples here. Spreadsheet locale settings can use different separators, so if Sheets rejects a formula that otherwise looks correct, check the file’s locale and the separator it expects.

Practical IF Formula Examples

With a score in B2, =IF(B2>=70,"Pass","Review") returns Pass for 70 and Review for 69. With an order total in C2, =IF(C2>=50,"Free shipping","Shipping applies") returns Free shipping at 50 or above. These are illustrative rules; use the actual thresholds and wording that suit your sheet.

IF can return a calculation too. If quantity is in B2 and unit price is in C2, =IF(B2>=10,B2*C2*0.9,B2*C2) applies a 10% reduction when the quantity is at least 10. For a quantity of 10 at a unit price of 20, the formula returns 180: 10 × 20 × 0.9. This example only calculates a result; it does not alter the source quantity or price.

To use a formula for multiple rows, enter it in the first result row and use the fill handle to copy it down. Cell references such as B2 normally adjust to B3, B4, and so on. If a rule must always refer to one fixed cell, use an absolute reference. For instance, with a threshold stored in F1, =IF(B2>=$F$1,"Pass","Review") keeps F1 fixed as you fill the formula down.

Handling Blank Cells and Text

A common pattern is to keep a result cell visually empty until its input is entered. With a value in A2, =IF(A2="","",A2*2) displays a blank when A2 is empty and otherwise doubles its value. The two pairs of quotation marks in the middle represent an empty text string. This is different from returning zero, which displays a numeric 0 and may affect later calculations differently.

Be deliberate about what counts as missing. A cell containing a space is not the same as a truly empty cell, and a formula that returns an empty text string may look blank while still containing a formula. If your data may include accidental spaces, clean or validate the input rather than assuming every visually blank cell is genuinely empty. Also check that an intended numeric input is stored as a number; text that looks like a number can behave differently in comparisons and calculations.

Text comparisons in Sheets are generally not case-sensitive: a comparison such as A2="ready" can match READY as well as ready. If capitalization itself matters, use the EXACT function in the condition, for example =IF(EXACT(A2,"Ready"),"Match","Check"). This returns Match only when the text matches with the same capitalization.

Testing More Than One Condition

Use AND or OR inside IF when a decision depends on multiple tests. With an order total in B2 and a customer type in C2, =IF(AND(B2>=100,C2="Member"),"Eligible","Not eligible") returns Eligible only if both conditions are true. AND is useful when every requirement must be met.

OR returns true when at least one of its conditions is true. With a status in A2, =IF(OR(A2="Urgent",A2="High"),"Prioritize","Standard") prioritizes either Urgent or High items. You can combine AND and OR when a rule has groups of requirements, but add parentheses carefully so that the intended groups are clear.

When outcomes depend on several thresholds, you can nest IF functions. With a score in B2, =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","Needs improvement"))) checks the highest threshold first. A score of 85 returns B; a score of 65 returns Needs improvement. The order matters: once a condition is true, Sheets returns that result and does not continue to the remaining branches.

For a longer list of distinct conditions, IFS can be easier to read than many nested IFs. With a score in B2, =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"Needs improvement") returns the first matching result. The final TRUE acts as a catch-all. Without a condition that matches, IFS can return an error, so include a fallback when every possible input should have an outcome.

Common Errors and How to Fix Them

A parse error often points to a missing comma, unmatched parenthesis, or quotation mark that was typed incorrectly. Compare the number of opening and closing parentheses, use straight double quotes around text, and check whether the spreadsheet’s locale expects commas or another argument separator. When building a formula, enter the test and its outcomes in stages rather than adding several nested tests at once.

A formula can also be syntactically valid but produce an unexpected result because its condition does not match the data. Check for extra spaces, inconsistent labels, dates stored as text, or numbers stored as text. Test the condition by itself in a spare cell, such as =B2>=70; Sheets will show TRUE or FALSE, helping you separate a data issue from an outcome issue.

Use IFERROR only when you have decided what an error should mean. For example, with a numerator in A2 and denominator in B2, =IFERROR(A2/B2,"Check values") displays Check values if the division produces an error, such as when B2 is zero. IFERROR catches errors broadly, so it can also hide problems unrelated to division by zero. If you need to distinguish a specific condition, handle that condition directly rather than masking every error.

Applying IF to a Whole Column

For a short or fixed dataset, filling a formula down is straightforward. Google Sheets also supports ARRAYFORMULA to apply an expression to ranges. If quantities are in A2:A and prices are in B2:B, =ARRAYFORMULA(IF(A2:A="","",A2:A*B2:B)) returns a blank for rows with no quantity and multiplies quantity by price for populated rows. For example, a quantity of 3 and price of 4 produce 12.

Enter this formula once in the first output cell, with the source data starting in row 2, and make sure the cells below it are available for the results to expand. A value already occupying an output cell can prevent the array from displaying fully. ARRAYFORMULA is a Google Sheets feature; Excel has different ways to work with dynamic arrays, so do not assume that every Sheets formula will transfer unchanged to Excel.

When to Use IF, Conditional Formatting, or Another Function

Use IF when you want a cell to contain a result based on a rule. Use conditional formatting when the goal is to change how cells look, such as coloring scores below 70 red without replacing their values. In Google Sheets, select the relevant cells and choose Format > Conditional formatting. A custom formula can define the formatting condition, but conditional formatting is not a substitute for a formula that produces a label or calculation.

For a single yes-or-no test, IF is usually the clearest choice. For several ordered condition-and-result pairs, IFS can reduce nesting. AND and OR combine tests, while IFERROR supplies an alternate result when a formula encounters an error. In Microsoft Excel, the basic IF syntax and many of these logical patterns are similar, but Google Sheets-specific features such as ARRAYFORMULA may not work the same way. Check compatibility before copying a spreadsheet formula between the two products.

Frequently Asked Questions

Does Google Sheets require a false result in IF?
No. The third argument can be omitted, and Sheets returns a blank when the condition is false. Including an explicit result such as "No" or 0 usually makes the formula’s behavior clearer to readers.

How do I write “not equal to” in Google Sheets?
Use <> in the condition. For example, =IF(A2<>"Paid","Follow up","Complete") returns Follow up when A2 is anything other than Paid.

Can IF return a number instead of text?
Yes. The true and false results can be numbers or calculations. For example, =IF(B2>=10,5,0) returns 5 when B2 is at least 10 and 0 otherwise.

Why does my IF formula show FALSE?
Usually the false result was omitted, or the formula’s test is not matching the data as expected. Add a third argument if you want a specific false result, then check the referenced cells, comparison operator, data types, and any extra spaces in text.

Can I use IF with dates?
Yes. Dates can be compared as date values. For example, if A2 contains a due date, =IF(A2<TODAY(),"Overdue","Not overdue") marks dates before today as Overdue. Confirm that the cell contains a real date value rather than text that merely looks like a date.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.