How to Use IF Formula in Google Sheets

What Is the IF Formula and Why You Need It

The IF formula is one of the most powerful tools in Google Sheets. At its core, it allows your spreadsheet to make decisions based on conditions you set. Instead of manually reviewing data and typing responses, the IF formula automates this process. When you use IF correctly, you unlock the ability to create spreadsheets that respond intelligently to your data.

Imagine a sales tracker where you need to flag which numbers exceeded targets. Without IF, you would manually check each row and type “Hit” or “Miss”. With IF, the spreadsheet does it for you instantly. The same principle applies to grade books, attendance records, inventory alerts, and budget trackers. Any situation where you need your spreadsheet to evaluate data and respond with different outcomes is a perfect use case for IF.

Google Sheets makes the IF function straightforward to learn, and once you master the basics, you can combine it with other functions to handle complex logic. This guide walks you through every stage, from your first simple IF formula to advanced nested structures that handle multiple conditions simultaneously.

Understanding IF Formula Syntax

The IF formula follows a consistent structure that never changes. Learning this structure is the foundation for everything else you will build with IF.

The syntax is: =IF(logical_test, value_if_true, value_if_false)

Breaking this down into its three components makes it much easier to understand. The logical_test is the condition you want to evaluate. This is a question your spreadsheet asks about the data. You write it using comparison operators like equals, greater than, or less than. The value_if_true is what the formula returns when your condition is true. This can be text, a number, or another formula. The value_if_false is what appears when the condition is false.

Let’s use a concrete example from a classroom setting. Say you have a student’s test score in cell A1. You want to display “Pass” if the score is 60 or higher, and “Fail” if it is below 60. Your formula would be: =IF(A1>=60, “Pass”, “Fail”)

Here, A1>=60 is the logical test. The spreadsheet checks if the value in A1 is greater than or equal to 60. If it is true, the formula returns “Pass”. If it is false, it returns “Fail”. Notice that text values in IF formulas are wrapped in quotation marks, while cell references like A1 are not.

Another example uses numeric outputs instead of text. If you want to give bonus points, you might write: =IF(B2>90, 5, 0) This awards 5 bonus points if the score in B2 exceeds 90, otherwise 0 bonus points. The formula works the same way, but your outputs are numbers instead of words.

Writing Your First IF Formula

Now that you understand the syntax, let’s walk through a real example from start to finish. You are tracking sales performance, and you want to know which sales representatives hit their monthly target of 50 deals.

In Column A, you have the sales rep’s name. In Column B, you have the number of deals they closed. You want Column C to show “Target Hit” or “Target Missed” depending on whether they reached 50 deals.

Click on cell C1 first. Type the formula: =IF(B1>=50, “Target Hit”, “Target Missed”)

Press Enter. If B1 contains a number less than 50, the cell displays “Target Missed”. If B1 contains 50 or more, it displays “Target Hit”.

Now copy this formula down to all your other rows. Click on C1 again, then drag the small square at the bottom right corner of the cell down to the last row with data. Google Sheets automatically adjusts the cell references, so C2 will check B2, C3 will check B3, and so on. This is called a relative reference, and it saves you from typing the formula over and over.

Your spreadsheet now automatically categorizes every sales rep without you lifting a finger. If someone brings in new data tomorrow, you simply extend the formula to the new row, and the categorization happens instantly. This is the power of IF in action.

Nesting IF Statements for Multiple Conditions

Some situations require more than two outcomes. A simple IF handles true or false, but what if you need to categorize grades into A, B, C, D, or F based on different score ranges? This is where nesting comes in.

Nested IF means putting one IF formula inside another. The value_if_false part of your first IF becomes another IF formula. This creates a chain of conditions that your spreadsheet evaluates from the first to the last.

Here is a grading system example. Score 90 or above equals A. Score 80 to 89 equals B. Score 70 to 79 equals C. Score 60 to 69 equals D. Below 60 equals F. Your nested IF formula looks like this:

=IF(A1>=90, “A”, IF(A1>=80, “B”, IF(A1>=70, “C”, IF(A1>=60, “D”, “F”))))

Reading this formula from inside out helps you understand the logic. The innermost IF checks if A1 is below 60, returning “F”. If that is false, it moves to the next layer up, checking if A1 is at least 60 for a “D”. This continues upward until it finds a condition that is true. Once a condition is true, the formula returns that grade and stops evaluating the rest.

Order matters in nested IF formulas. Always check the highest values first. If you checked for A1>=60 before checking A1>=90, every score of 90 or above would return “D” because the first condition would be true. The spreadsheet returns the result from the first true condition it encounters.

Nesting can go deeper, but readability suffers once you use more than three or four levels. If you find yourself nesting beyond that, consider using the IFS function instead, which was designed specifically for this situation.

Combining Logical Operators: AND and OR

The logical_test in IF can be simple, like checking a single cell against a value. But often you need to check multiple conditions at once. The AND and OR operators let you do this.

AND means both conditions must be true. If you want to pass only students who scored at least 70 on the test AND attended at least 80 percent of classes, you would write:

=IF(AND(A1>=70, B1>=80), “Pass”, “Fail”)

Here, A1>=70 is the first condition, and B1>=80 is the second. Both must be true for the formula to return “Pass”. If either condition is false, it returns “Fail”.

OR means at least one condition must be true. Suppose you want to flag inventory items for reorder if they are either below 10 units OR approaching expiration within 30 days. Your formula would be:

=IF(OR(A1<10, B1<=30), "Reorder Soon", "In Stock")

If the quantity in A1 is less than 10, the result is “Reorder Soon”. If the days until expiration in B1 is 30 or less, the result is also “Reorder Soon”. Only if both conditions are false does it show “In Stock”.

You can nest AND and OR together for even more complex logic. For example, a promotion might apply only if the customer spent over $100 AND has been with you for at least one year OR is a VIP member regardless of tenure. This demonstrates why combining these operators is so powerful.

Using Comparison Operators in IF

Comparison operators are the building blocks of every logical_test in your IF formula. Understanding each one ensures you write the correct condition every time.

The equals sign (=) checks if two values are exactly the same. =IF(A1=100, “Perfect Score”, “Not Perfect”) returns “Perfect Score” only if A1 is exactly 100, not 99.9 or 100.1.

The greater than sign (>) checks if the first value is larger. =IF(A1>50, “Above Average”, “Average or Below”) returns true only if A1 is strictly greater than 50. The value 50 itself would return false.

The less than sign (<) does the opposite. =IF(A1<50, "Below Threshold", "At or Above Threshold") works when you need to detect values below a cutoff.

Greater than or equal to (>=) and less than or equal to (<=) include the boundary value. =IF(A1>=50, “Pass”, “Fail”) returns “Pass” if A1 is 50 or any number higher. This is different from > and < alone.

The not equal operator (<>) catches everything except one specific value. =IF(A1<>“Pending”, “Resolved”, “Still Waiting”) returns “Resolved” for any status except “Pending”. This operator prevents a specific case from proceeding.

Selecting the right operator is critical. A mistake here changes the behavior of your entire formula. Review your conditions carefully to ensure they evaluate the data the way you intend.

Using IFS as a Cleaner Alternative

Google Sheets offers an alternative function called IFS that simplifies multiple conditions. While IF requires nesting to handle more than two outcomes, IFS lets you list conditions in sequence without deep nesting.

The IFS syntax is: =IFS(condition1, value1, condition2, value2, condition3, value3, …)

Returning to the grading example, instead of nested IF, you could write:

=IFS(A1>=90, “A”, A1>=80, “B”, A1>=70, “C”, A1>=60, “D”, TRUE, “F”)

This reads much more clearly. Each condition paired with its result is right next to each other. Notice the final TRUE at the end, which acts as a catch-all for any values that do not match the previous conditions.

The advantage of IFS is readability, especially as your conditions grow more numerous. A nested IF with six or seven levels becomes hard to follow. IFS keeps the same logic clear and organized. Google Sheets evaluates the conditions in order and returns the value from the first condition that is true, then stops.

However, IF is more universally compatible across all spreadsheet applications. If you work with colleagues who use Excel or other platforms, they may not be familiar with IFS. Understanding both functions makes you flexible depending on your audience and situation.

Working with Text Values in IF Formulas

When your logical_test compares text instead of numbers, a few rules apply. Text comparisons are case insensitive by default in IF. This means “apple” and “APPLE” are treated as the same value.

In a customer service tracker, you might write: =IF(A1=”resolved”, “Done”, “Pending”)

This returns “Done” whether A1 contains “resolved”, “Resolved”, or “RESOLVED”. Google Sheets does not distinguish between these cases in a standard IF formula.

If you need case sensitivity, use the EXACT function inside IF. The EXACT function compares text and returns true only if the cases match exactly. The formula would be: =IF(EXACT(A1, “Resolved”), “Match Found”, “No Match”)

Text values in IF formulas must be wrapped in quotation marks. Cell references do not get quotes. So =”apple” is text that returns the word apple, while =A1 checks what is in cell A1. Forgetting this is one of the most common errors when working with IF and text.

Handling Dates with IF Formulas

Dates in IF formulas work with comparison operators just like numbers do. You can check if a date is before, after, or equal to another date.

Project managers use this frequently. You might want to flag tasks due within the next week: =IF(A1<=TODAY()+7, "Due Soon", "Not Due Yet")

The TODAY() function returns the current date. Adding 7 to it gives you a date one week from now. The formula compares the deadline in A1 to this threshold. Any date on or before one week from today triggers “Due Soon”.

Another common pattern alerts when something is overdue: =IF(A1

If the deadline in A1 is before today’s date, the task is overdue. This helps you spot late deliverables instantly without manually reviewing dates.

Date comparisons are straightforward once you remember to use the correct comparison operator for your logic. Less than checks if something is in the past or sooner. Greater than checks if something is in the future or later. Combine this with TODAY() or specific dates you type in, and you have a powerful date checking system.

Real-World Use Cases for IF

Understanding the mechanics of IF is one thing. Seeing it applied to real problems helps you recognize where to use it in your own work. Here are four common scenarios where IF makes an immediate difference.

A budget tracker uses IF to highlight overspending. Each expense category has a budget limit in one column and actual spending in another. You write: =IF(B2>A2, “Over Budget”, “Within Budget”) to instantly flag categories that exceed their limit. When spending changes, the flag updates automatically, keeping your budget visible at a glance.

An attendance system uses IF to calculate pass or fail based on attendance percentage. Students need 80 percent attendance to pass. You write: =IF(A2>=80, “Pass”, “Fail”) where A2 contains the attendance percentage. The spreadsheet automatically determines who meets the requirement without manual review.

An inventory reorder system uses IF with AND to trigger alerts. Items need reordering if stock is below 20 units AND the last reorder was more than 30 days ago. You write: =IF(AND(A2<20, B2>30), “Reorder Now”, “OK”) to flag items needing immediate action.

A commission calculator uses nested IF to pay different rates based on sales tiers. Sales up to $10,000 earn 5 percent commission. Sales from $10,001 to $25,000 earn 7 percent. Sales above $25,000 earn 10 percent. You write: =IF(A2<=10000, A2*0.05, IF(A2<=25000, A2*0.07, A2*0.10)) to calculate the correct commission tier instantly.

Fixing Common Errors

Even experienced users encounter errors with IF formulas. Knowing how to spot and fix them saves time and frustration.

The #VALUE! error usually means you have a syntax problem. Check that all text is in quotation marks, all parentheses are matched, and all cell references are valid. Missing a closing parenthesis is the most common cause. Google Sheets helps by highlighting matching parentheses when you click inside them.

Circular reference errors occur when a formula refers to its own cell. For example, if C1 contains =IF(C1>10, “Yes”, “No”), the formula references itself, creating an endless loop. Google Sheets will not calculate this and displays an error instead. Check your cell references carefully to ensure you are not referencing the cell containing the formula.

In nested IF formulas, you may get unexpected results if your conditions are in the wrong order. Remember that the formula evaluates conditions from inside out and returns the first true result. If your conditions overlap or are in the wrong sequence, you get incorrect output. Test your formulas with sample data to verify they work as intended.

If your formula returns nothing or a blank cell when you expected a result, check whether your value_if_false is actually defined. Sometimes people write =IF(A1>50, “Yes”) and forget the false case. Google Sheets treats an undefined false case as an empty string, returning nothing.

Mastering these fixes prevents most issues. When in doubt, simplify your formula and test it with known values to understand what is happening at each step.

Frequently Asked Questions

If you found this guide helpful, explore how to use SUMIF in Google Sheets for advanced conditional calculations. You might also benefit from learning about conditional formatting in Google Sheets to visually highlight data based on conditions. For managing larger datasets, knowing how to remove duplicates in Google Sheets keeps your data clean. If you need to organize data further, check out our guide on how to sort by date in Google Sheets. To control which parts of your sheet others can edit when you share a Google Sheet, combine IF formulas with protected ranges for advanced access control.