IF Function

Excel Guides / Logical

Logical

Test a condition and return one result when it is true and another when it is false.

Updated 12 Oct 2026 Ā· 4 min read Ā· By Sky Dashboards

IF is how a spreadsheet makes decisions. It checks whether something is true, such as stock falling below its reorder level, and returns one result if it is and another if it is not. Almost every working model has an IF somewhere, from status labels to commission rules.

IF syntax

=IF(logical_test, value_if_true, [value_if_false])

Argument Required? What it does
logical_test Required A comparison, or anything else that gives TRUE or FALSE, such as B2>=100.
value_if_true Required What to return when the test is TRUE: a number, text in double quotes, a cell reference or another formula.
value_if_false Optional What to return when the test is FALSE. Leave it out and IF returns the logical value FALSE.
=IF(B2>=100,"Free shipping","Standard")

If the order total in B2 is 100 or more, show Free shipping. Otherwise show Standard.

A number also works as a test on its own: 0 counts as FALSE and every other number as TRUE. So =IF(C2,"Paid","Due") shows Paid whenever C2 holds a non-zero amount, and Due when it is 0 or empty.

Comparison operators

Most tests compare two values with one of these operators:

Operator Meaning Example
= Equal to B2="Paid"
<> Not equal to B2<>"Cancelled"
> Greater than C2>0
< Less than C2<D2
>= Greater than or equal to C2>=500
<= Less than or equal to B2<=C2

Text comparisons ignore case, so "paid" matches Paid and PAID. They do not support wildcards: B2="*gift*" looks for that literal text, asterisks included. To test whether a cell contains a word, use ISNUMBER(SEARCH("gift",B2)) as the test.

Return text, numbers or blanks

  • Text goes in double quotes: "Reorder".
  • Numbers go in without quotes: 250. Put quotes around a number and Excel returns text that looks like a number but is ignored by SUM.
  • Calculations work in either slot, such as B2*5%.
  • A blank is two double quotes with nothing between them: "". The cell looks empty but holds empty text, so ISBLANK returns FALSE for it and COUNTA counts it.
=IF(D2="","",D2-C2)

Leaves the cell blank until a finish date goes into D2, then shows the number of days since the start date in C2.

Empty text is not an empty cell

Formulas further down the line can trip over "". To test for a blank, use B2="" rather than ISBLANK(B2). The first is TRUE for a truly empty cell and for empty text, while ISBLANK is TRUE only for a truly empty cell.

Example: stock status with a nested IF

A nested IF puts a second IF in the value_if_false slot, so you can return more than two results. This shop flags each product as Out of stock, Reorder or OK:

D4fx=IF(B4=0,"Out of stock",IF(B4<=C4,"Reorder","OK"))
A B C D
1 Product In stock Reorder at Status
2 Soy candle 38 20 OK
3 Wax melt 12 30 Reorder
4 Reed diffuser 0 10 Out of stock
5 Gift set 15 15 Reorder
6 Room spray 42 25 OK

The first test catches zero stock. Only rows that pass it reach the second test, which compares stock with the reorder level.

Excel works through the tests in order and stops at the first TRUE, so the order matters. Test for zero first. If the reorder check came first, a product with nothing in stock would show Reorder, because 0 is also below its reorder level.

Tip

Excel allows 64 nested IFs, but anything past three or four is hard to read and easy to break. For long chains, use IFS or a lookup table with XLOOKUP.

Combine IF with AND, OR and NOT

AND, OR and NOT let one test check several conditions. Each returns TRUE or FALSE, so it slots straight into the logical_test argument.

  • AND is TRUE only when every condition is true.
  • OR is TRUE when at least one condition is true.
  • NOT flips TRUE to FALSE and FALSE to TRUE.

A commission example

A design studio pays its sales reps 5% on monthly sales of $10,000 or more and 3% below that:

=IF(B2>=10000,B2*5%,B2*3%)

Sales of $12,000 in B2 return $600. Sales of $8,000 return $240.

Add a quality condition with AND, so the higher rate also needs a refund rate in C2 of 2% or less:

=IF(AND(B2>=10000,C2<=2%),B2*5%,B2*3%)

OR and NOT

=IF(OR(C2="Overdue",D2>30),"Chase","")

Flags an invoice to chase when it is marked Overdue or is more than 30 days old.

=IF(NOT(C2="Cancelled"),B2,0)

Counts the order value unless the order was cancelled. Writing the test as C2<>"Cancelled" does the same job.

Common IF mistakes

  • Text without quotes. =IF(B2=Paid,...) returns #NAME?, because Excel reads Paid as a name it cannot find. Write "Paid".
  • Numbers in quotes in the test. =IF(B2>"100",...) compares a number with text. Excel ranks all text above all numbers, so the test is FALSE for every number in B2.
  • Numbers stored as text in the data. The reverse problem. If B2 holds the text "250", B2>100 is TRUE, but so is the text "50". Convert the column to real numbers first. Imported data is the usual source.
  • Numbers in quotes in the result. =IF(B2>=C2,"250","0") returns text, so a SUM of the column comes to 0. Drop the quotes.
  • Tests in the wrong order. In a nested IF on bands, test the highest threshold first when you use >=. Otherwise the first test catches everything.

Note

To check a test on its own, type it in a spare cell without the IF, such as =B4<=C4. Excel shows TRUE or FALSE, which tells you which branch the IF will take.

Frequently asked questions

How many IF functions can you nest in Excel?

64. In practice, switch to IFS or a lookup table well before that.

How do I make IF return a blank cell?

Use two double quotes as the result, as in =IF(B2=0,"",B2). The cell looks blank but holds empty text, so ISBLANK treats it as not blank.

Can IF check whether a cell contains certain text?

Not with wildcards. Use =IF(ISNUMBER(SEARCH("gift",A2)),"Yes","No"). SEARCH ignores case. Use FIND instead for a case-sensitive check.

Is IF case-sensitive?

No. =IF(A2="paid",...) matches Paid and PAID. For a case-sensitive test, use EXACT: =IF(EXACT(A2,"Paid"),...).

Want the finished dashboard instead?

My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.

Browse templates ā†’

Related guides

All 32 Excel guides