Excel formula library

Excel Tutorial

Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.

Start with first formula Browse formulas

127 Formulas

11 Categories

3 Skill levels

Live Search & filters

Formula list

Find any Excel formula

Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.

Chapter 3

Logical & Conditional

28 IF =IF(logical_test, value_if_true, [value_if_false]) Checks whether a condition is met, and returns one value if true, and another value if false. Foundation of decision logic in spreadsheets. Logical & Conditional Beginner Classic
Syntax
```
=IF(logical_test, value_if_true, [value_if_false])
```
Arguments

logical_test

Any value or expression that can be evaluated to TRUE or FALSE.

value_if_true

The value that is returned if logical_test is TRUE.

[value_if_false]

The value that is returned if logical_test is FALSE. If omitted, returns FALSE.
Sample
```
=IF(B2="HR", C2, "")
```
Result 350
Sample data
Download

A B C D
2 E-101 HR 350 350
3 E-102 IT 400 "" (Blank)
4 E-103 Sales 450 "" (Blank)
5 E-104 HR 280 280

Pro tip
Nest IF statements or use IFS (Excel 2019+) for multi-tier grading: =IFS(A2>90, "A", A2>80, "B", TRUE, "C").

Common pitfall
Forgetting quotation marks around text outputs produces a #NAME? error (always write "Pass", never Pass).
29 OR =OR(logical1, [logical2], ...) Returns TRUE if any of its arguments evaluate to TRUE, and returns FALSE only if every argument is FALSE. Logical & Conditional Intermediate Classic
Syntax
```
=OR(logical1, [logical2], ...)
```
Arguments

logical1, [logical2, ...]

1 to 255 conditions that evaluate to TRUE or FALSE.
Sample
```
=OR(B2="HR", C2="Sharma")
```
Result TRUE
Sample data
Download

A B C D
2 101 HR Verma TRUE (HR matched)
3 102 IT Sharma TRUE (Sharma matched)
4 103 Sales Gupta FALSE (Neither matched)
5 104 Admin Kumar FALSE (Neither matched)

Pro tip
Wrap inside an IF statement: =IF(OR(A2="Red", A2="Blue"), "Primary", "Other").

Common pitfall
OR returns boolean TRUE/FALSE. Do not write OR(...) = "TRUE"; simply use IF(OR(...), ...).
30 AND =AND(logical1, [logical2], ...) Returns TRUE if all of its arguments evaluate to TRUE, and returns FALSE if one or more arguments evaluate to FALSE. Logical & Conditional Intermediate Classic
Syntax
```
=AND(logical1, [logical2], ...)
```
Arguments

logical1, [logical2, ...]

1 to 255 conditions to test. All must be true for overall TRUE.
Sample
```
=AND(B2="HR", C2="Sharma")
```
Result FALSE
Sample data
Download

A B C D
2 101 HR Verma FALSE (Surname not Sharma)
3 102 IT Sharma FALSE (Dept not HR)
4 103 Sales Gupta FALSE (Both failed)
5 104 HR Sharma TRUE (Both passed)

Pro tip
Combine with IF for commission gates: =IF(AND(B2>=75, C2>=75), "Distinction", "Pass").

Common pitfall
Ensure types match: comparing a numeric cell to "50" (in quotation marks) fails because numbers do not equal text.
31 IFERROR =IFERROR(value, value_if_error) Returns a value you specify if a formula evaluates to an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!); otherwise returns the result of the formula. Logical & Conditional Beginner Classic
Syntax
```
=IFERROR(value, value_if_error)
```
Arguments

value

The argument that is checked for an error (typically another formula or division).

value_if_error

The value to return if the formula evaluates to an error.
Sample
```
=IFERROR(B2/C2, 0)
```
Result 0.25
Sample data
Download

A B C D
2 Campaign 1 250 1000 25.0%
3 Campaign 2 0 0 0.0% (Caught #DIV/0!)
4 Campaign 3 400 1200 33.3%
5 Campaign 4 10 50 20.0%

Pro tip
Always wrap legacy lookups: =IFERROR(VLOOKUP(A2, Data, 2, 0), "Item Not Found").

Common pitfall
IFERROR catches ALL errors, including formula typos like #NAME?. Use IFNA if you only want to catch missing lookup values.
32 TRUE =TRUE() Returns the logical value TRUE. Provided primarily for compatibility with other spreadsheet systems. Logical & Conditional Beginner Classic
Syntax
```
=TRUE()
```
Arguments

No arguments

Takes no arguments.
Sample
```
=TRUE()
```
Result TRUE
Sample data
Download

A B C D
2 ENABLE_TAX Apply GST/VAT TRUE Boolean
3 AUTO_SAVE Background Save TRUE Boolean
4 DEBUG_LOG Detailed logging FALSE Boolean

Pro tip
In mathematical calculations, TRUE automatically coerces to the numeric value 1 (e.g. TRUE + 5 equals 6).

Common pitfall
Typing "TRUE" inside quotes creates a text string, not a true logical boolean.
33 FALSE =FALSE() Returns the logical value FALSE. You can also type the word FALSE directly into the worksheet or formula. Logical & Conditional Beginner Classic
Syntax
```
=FALSE()
```
Arguments

No arguments

Takes no arguments.
Sample
```
=FALSE()
```
Result FALSE
Sample data
Download

A B C D
2 Module A Inventory Sync FALSE 0
3 Module B Payment Gateway TRUE 1
4 Module C SMS Alerts FALSE 0

Pro tip
In mathematical formulas, FALSE evaluates to 0 (e.g. FALSE * 100 equals 0).

Common pitfall
Do not put quotes around FALSE in formulas like =IF(A2=FALSE, ...).

	A	B	C	D
2	E-101	HR	350	350
3	E-102	IT	400	"" (Blank)
4	E-103	Sales	450	"" (Blank)
5	E-104	HR	280	280

	A	B	C	D
2	101	HR	Verma	TRUE (HR matched)
3	102	IT	Sharma	TRUE (Sharma matched)
4	103	Sales	Gupta	FALSE (Neither matched)
5	104	Admin	Kumar	FALSE (Neither matched)

	A	B	C	D
2	101	HR	Verma	FALSE (Surname not Sharma)
3	102	IT	Sharma	FALSE (Dept not HR)
4	103	Sales	Gupta	FALSE (Both failed)
5	104	HR	Sharma	TRUE (Both passed)

	A	B	C	D
2	Campaign 1	250	1000	25.0%
3	Campaign 2	0	0	0.0% (Caught #DIV/0!)
4	Campaign 3	400	1200	33.3%
5	Campaign 4	10	50	20.0%

	A	B	C	D
2	ENABLE_TAX	Apply GST/VAT	TRUE	Boolean
3	AUTO_SAVE	Background Save	TRUE	Boolean
4	DEBUG_LOG	Detailed logging	FALSE	Boolean

	A	B	C	D
2	Module A	Inventory Sync	FALSE	0
3	Module B	Payment Gateway	TRUE	1
4	Module C	SMS Alerts	FALSE	0