IF Rules in Google Sheets: How Formulas and Rules Work

In Google Sheets, an IF rule is a formula that checks a condition and returns one result when it is true and another when it is false. Enter =IF(A2>=60,"Pass","Retry") in a cell to test whether the value in A2 is at least 60; this returns Pass for 60 or higher and Retry otherwise. If you mean color or style changes, those are set up separately with conditional formatting.

What people mean by IF rules

The phrase IF rules can refer to two related but distinct features. The IF function is spreadsheet logic: it evaluates a test and places a result in the formula cell. A conditional formatting rule changes how a cell looks when a condition is met; it does not, by itself, replace the cell’s value. Knowing which outcome you want—data or appearance—makes it easier to choose the right feature.

For example, a teacher might use IF to display Pass or Retry beside each score. They might also use conditional formatting to fill passing scores green and lower scores red. The formula creates readable text that can be used in later calculations or filters, while formatting makes a value easier to spot. You can use either feature alone or combine them.

How the IF function works

Google Sheets uses this structure: =IF(logical_expression, value_if_true, value_if_false). The logical expression is a test that resolves to TRUE or FALSE. The two remaining arguments tell Sheets what to return for each outcome. Text results need double quotation marks; cell references and numeric results generally do not.

With the score in A2, =IF(A2>=60,"Pass","Retry") returns Pass when A2 is 60, 72, or 100, and Retry when it is below 60. The comparison operators you will commonly use are = for equal to, > for greater than, < for less than, >= for greater than or equal to, <= for less than or equal to, and <> for not equal to.

The third argument is optional. If you leave it out, Sheets returns FALSE when the test is false, which is often less useful than an explicit result. With a numeric value in A2, =IF(A2>=60,"Pass") displays Pass for a score of at least 60 and FALSE for a lower score. Supplying a clear alternative such as Retry or a blank string makes the output more intentional.

Enter an IF formula and apply it to rows

To add a basic IF formula in Google Sheets in a web browser:

Select the cell where you want the result, such as C2.
Type an equals sign and the IF formula, using the relevant cell references and results.
Press Enter to calculate it, then check both a true case and a false case if possible.
To use the same logic for more records, select the formula cell and drag its small blue fill handle down the column, or copy and paste it into the other rows.

With scores in B2:B20 and the result starting in C2, enter =IF(B2>=60,"Pass","Retry") in C2. If B2 is 72, C2 displays Pass. When the formula is filled down, the reference changes to B3, B4, and so on, so each row checks its own score. This is relative referencing; it is why copying the formula down usually works without editing every row.

For an empty score cell, the basic formula treats the blank as zero in a numeric comparison, so it may show Retry before anyone has entered a score. With the score in B2 and the output in C2, =IF(B2="","",IF(B2>=60,"Pass","Retry")) leaves C2 visually blank when B2 is empty, then checks the score once one is entered. This nested version is useful for unfinished lists, although it is important not to confuse a blank-looking result with a truly empty cell: the formula still exists in C2.

Use IF with several outcomes

One IF function chooses between two outcomes. If you need several bands, you can nest IF functions, placing another IF in the false-result position. With a score of 84 in A2, =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","Needs review"))) returns B. The tests are evaluated from left to right, and the first true condition determines the answer. Order the thresholds carefully: a higher threshold should generally come before a lower one, or a broad condition may capture values too early.

Nested formulas can become difficult to read as the number of outcomes grows. Google Sheets also supports IFS, which checks a sequence of conditions and returns the result associated with the first true condition. With the same score of 84 in A2, =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"Needs review") returns B. The final TRUE acts as a catch-all for values that did not meet any earlier test, much like the final false result in a nested IF. Without a condition that catches the remaining cases, IFS can return an error if none of its tests is true.

Combine conditions with AND and OR

Use AND when every required test must be true, and OR when at least one test is enough. These functions are often placed inside IF’s logical expression. For instance, suppose A2 contains an order status and B2 contains its amount. With Approved in A2 and 125 in B2, =IF(AND(A2="Approved",B2>=100),"Release","Hold") returns Release. If either the status is not Approved or the amount is under 100, the result is Hold.

With a task priority in A2 and its due date in B2, =IF(OR(A2="Urgent",B2<TODAY()),"Review","Normal") returns Review if the task is marked Urgent or its due date is before today. TODAY updates as the date changes, so a formula using it can produce a different result on a later day. Consider whether that changing behavior suits your tracking sheet; for a fixed reporting date, use a date stored in a cell instead of relying on today’s date.

IF formulas versus conditional formatting

Choose IF when you want a value such as Pass, Overdue, or Needs review to appear in a cell. Choose conditional formatting when the value is already present and you only want to change its appearance. In Google Sheets, select the cells, then use Format > Conditional formatting. The sidebar lets you set an apply-to range, choose a condition, and specify the formatting style.

For example, if scores are in B2:B20, select that range and create a rule that formats cells greater than or equal to 60 with a green fill. This highlights passing scores but does not write Pass into another column. To display the word Pass, use an IF formula in a separate column; to have both a label and a color cue, use both methods. Conditional formatting can also use a custom formula. If the status is in column B and you want to highlight each full record from columns A to D when its status is Late, set the apply-to range to A2:D20 and use the custom formula =$B2="Late". The dollar sign fixes the status column while the row number adjusts for each record.

These steps and menu names refer to Google Sheets in a web browser, not Excel. Excel has its own IF function and conditional-formatting feature, but its menus and some behaviors differ. A formula written for Sheets may often look familiar in Excel, yet instructions for one program’s menus should not be assumed to apply to the other.

Common problems and ways to fix them

Text is not in quotation marks: Write a text result as "Pass", not Pass. Cell references such as A2 should not be quoted, because quoted references are treated as literal text.
The test checks the wrong boundary: >60 excludes 60, while >=60 includes it. Choose the operator that matches the rule you actually intend.
Blank rows show an unwanted result: Add a blank check before the main condition if empty input should produce no visible label. Check whether a cell contains a formula returning an empty string, since it can look blank while still containing a formula.
Copied formulas use the wrong cells: Relative references shift when copied. If a reference must stay fixed, use dollar signs, such as $B$2 for a fixed column and row. Mixed references such as $B2 fix only the column.
A formula shows an error: Check the parentheses, commas, quotation marks, and cell references. Some spreadsheet locales use semicolons instead of commas as formula separators. In Sheets, the locale can be reviewed under File > Settings; use the separators accepted by the sheet.
Formatting does not change the cell’s value: That is expected. Edit the conditional formatting rule if the visual condition is wrong, or use an IF formula if the sheet needs a calculated label or result.

Choosing a clear and maintainable rule

Before writing a formula, describe the rule in ordinary language and identify the cell or cells it should check. A statement such as if the score is at least 60, show Pass, otherwise show Retry translates directly to one IF. If there are multiple categories, write down the boundaries and the expected outcome at each boundary before choosing nested IF or IFS. Testing exact cutoff values—59, 60, and 61 in a pass/fail example—can uncover mistakes that a typical middle value will not reveal.

Use simple outputs when other formulas will rely on them. For instance, a text status is easy for a person to scan, but downstream calculations may be more straightforward if the formula returns a number or a consistent value. Avoid adding more branches than the real rule needs. If the conditions change often or involve a long list of categories, a small lookup table can be easier to maintain than a deeply nested formula; keep the category and result pairs in a clearly labeled range and choose a suitable lookup approach.

IF does not automatically validate the data entered in a cell. If users should choose only from an approved list, use Data > Data validation to create an input restriction, then use IF if you also need a calculated result from that input. Validation helps prevent some inconsistent entries; an IF formula handles the logic after the value is entered. The two features solve different parts of a spreadsheet workflow.

Frequently Asked Questions

Does Google Sheets have an IF function?
Yes. IF evaluates a logical test and returns one result when it is true and another when it is false. The standard structure is =IF(logical_expression,value_if_true,value_if_false).

Can an IF formula return a blank?
Yes. Use an empty text string, written as two quotation marks with nothing between them, as an outcome. For example, with a value in A2, =IF(A2="","","Entered") shows a blank when A2 is empty and Entered when it is not.

What is the difference between IF and conditional formatting?
IF calculates a result in a cell, such as a label or number. Conditional formatting changes a cell’s appearance when a rule is met. Use IF for calculated content and conditional formatting for visual cues; combine them if you need both.

Can I use more than one condition in IF?
Yes. Put AND inside IF when all tests must be true, or OR when any one of them is enough. For several different outcome bands, use nested IF functions or IFS, and include a final outcome for any values not covered by the earlier tests.

Why does my IF formula show FALSE?
If the false-result argument is omitted, Sheets returns FALSE when the test fails. Add a third argument, such as "Retry", 0, or "", to specify what should appear instead.

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.