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 7

Information & Arrays

89 TRANSPOSE =TRANSPOSE(array) Returns a vertical range of cells as a horizontal range, or vice versa. Flips the rows and columns of an array. Information & Arrays Intermediate Classic
Syntax
```
=TRANSPOSE(array)
```
Arguments

array

An array or range of cells on a worksheet that you want to transpose.
Sample
```
=TRANSPOSE(A2:D2)
```
Result Vertical Array
Sample data
Download

Pro tip
In modern Microsoft 365, dynamic arrays spill automatically. In legacy Excel, select destination and press Ctrl + Shift + Enter.

Common pitfall
Destination area must have enough empty cells to avoid a #SPILL! error.
90 ISBLANK =ISBLANK(value) Returns TRUE if the value refers to an empty cell. Essential for verifying form submissions. Information & Arrays Beginner Classic
Syntax
```
=ISBLANK(value)
```
Arguments

value

The cell you want to test.
Sample
```
=ISBLANK(B2)
```
Result FALSE
Sample data
Download

A B C D
2 Rohan Sharma 9811122233 FALSE Filled
3 Kabir Verma TRUE Missing Contact!
4 Meena Gupta 9822233344 FALSE Filled

Pro tip
A cell with a formula returning "" is NOT blank according to ISBLANK (it returns FALSE). Use A2="" if you want to test for empty display.

Common pitfall
Invisible space characters make a cell appear empty to the eye, but ISBLANK returns FALSE.
91 ISERROR =ISERROR(value) Returns TRUE if the value is any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). Information & Arrays Beginner Classic
Syntax
```
=ISERROR(value)
```
Arguments

value

The value or cell reference to test for an error condition.
Sample
```
=ISERROR(B2)
```
Result FALSE
Sample data
Download

A B C D
2 100 / 2 50 FALSE Clean number
3 100 / 0 #DIV/0! TRUE Division by zero!
4 VLOOKUP missing #N/A TRUE Key not found

Pro tip
ISERROR catches all errors including #N/A, whereas ISERR catches all errors EXCEPT #N/A.

Common pitfall
ISERROR only identifies IF an error exists (TRUE/FALSE); use IFERROR if you want to replace the error with another value.
92 ISNA =ISNA(value) Returns TRUE if the value refers to the #N/A error value specifically. Pinpoints missing lookup matches. Information & Arrays Beginner Classic
Syntax
```
=ISNA(value)
```
Arguments

value

The value or cell to test for #N/A.
Sample
```
=ISNA(B2)
```
Result TRUE
Sample data
Download

A B C D
2 SKU-999 #N/A TRUE Item missing in catalog
3 SKU-101 350.00 FALSE Valid price retrieved
4 SKU-102 #DIV/0! FALSE Mathematical error, not #N/A

Pro tip
Pair with IF: =IF(ISNA(VLOOKUP(...)), "Add to Inventory", "In Stock").

Common pitfall
ISNA ignores other errors like #VALUE! or #REF!; it only responds to #N/A.
93 ISNUMBER =ISNUMBER(value) Returns TRUE if the value is a number, date, or time serial; returns FALSE otherwise. Information & Arrays Beginner Classic
Syntax
```
=ISNUMBER(value)
```
Arguments

value

The value to test.
Sample
```
=ISNUMBER(B2)
```
Result TRUE
Sample data
Download

A B C D
2 Sales 350 TRUE Genuine number
3 Date 10-Apr-2025 TRUE Dates are serial numbers!
4 Text ID "E-101" FALSE String text

Pro tip
Because SEARCH returns a number when text is found, =ISNUMBER(SEARCH("word", A1)) is the gold-standard test for "cell contains word".

Common pitfall
Numbers enclosed in quotes like "123" return FALSE because Excel considers them text.
94 ISTEXT =ISTEXT(value) Returns TRUE if the value is text. Useful for catching entries where text was typed into numeric columns. Information & Arrays Beginner Classic
Syntax
```
=ISTEXT(value)
```
Arguments

value

The value to test.
Sample
```
=ISTEXT(B2)
```
Result TRUE
Sample data
Download

A B C D
2 Name Rohan Sharma TRUE String
3 Sales 350 FALSE Number
4 Date 10-Apr-2025 FALSE Date (Numeric serial)

Pro tip
Formulas returning "" (empty string) return TRUE for ISTEXT.

Common pitfall
A date formatted as text returns TRUE, while a true Excel serial date returns FALSE.
95 ISLOGICAL =ISLOGICAL(value) Returns TRUE if the value is a logical boolean expression (TRUE or FALSE); otherwise returns FALSE. Information & Arrays Beginner Classic
Syntax
```
=ISLOGICAL(value)
```
Arguments

value

The value you want to test.
Sample
```
=ISLOGICAL(B2)
```
Result TRUE
Sample data
Download

A B C D
2 Is Active TRUE TRUE Boolean value
3 Is Expired FALSE TRUE Boolean value
4 Status Text "TRUE" FALSE Text in quotes (not boolean)

Pro tip
1 and 0 are numbers, not booleans, so ISLOGICAL(1) is FALSE.

Common pitfall
Typing "TRUE" with quotes is text; ISLOGICAL("TRUE") is FALSE.
96 ISFORMULA =ISFORMULA(reference) Returns TRUE if there is a reference to a cell that contains a formula. Audits financial models for unauthorized hardcoding. Information & Arrays Beginner Classic
Syntax
```
=ISFORMULA(reference)
```
Arguments

reference

A reference to the cell you want to test.
Sample
```
=ISFORMULA(B2)
```
Result TRUE
Sample data
Download

A B C D
2 Q1 Total =SUM(C2:C5) TRUE Dynamic Formula
3 Bonus Amount 5000 FALSE Hardcoded Constant!
4 Net Margin =B5/B1 TRUE Dynamic Formula

Pro tip
Apply conditional formatting across entire financial models with =NOT(ISFORMULA(A1)) to immediately catch rogue hardcoded numbers.

Common pitfall
ISFORMULA only takes a single cell reference, not a multi-cell range.
97 ISEVEN =ISEVEN(number) Returns TRUE if the number is even, or FALSE if the number is odd. Commonly used for zebra shading alternating rows. Information & Arrays Beginner Classic
Syntax
```
=ISEVEN(number)
```
Arguments

number

The value to test.
Sample
```
=ISEVEN(A2)
```
Result FALSE
Sample data
Download

A B C D
2 1 FALSE White row Odd
3 2 TRUE Gray row Even
4 3 FALSE White row Odd

Pro tip
Use =ISEVEN(ROW()) as a conditional formatting formula to create clean alternating shaded table rows.

Common pitfall
Non-numeric text arguments cause a #VALUE! error.
98 ISODD =ISODD(number) Returns TRUE if the number is odd, or FALSE if number is even. Information & Arrays Beginner Classic
Syntax
```
=ISODD(number)
```
Arguments

number

The value to test.
Sample
```
=ISODD(A2)
```
Result TRUE
Sample data
Download

A B C D
2 1 TRUE 1 Odd Number
3 2 FALSE 0 Even Number
4 3 TRUE 1 Odd Number

Pro tip
Zero is considered an even number by Excel, so ISODD(0) returns FALSE.

Common pitfall
Decimals are truncated toward zero before testing (e.g. ISODD(3.8) evaluates as 3 and returns TRUE).
99 TYPE =TYPE(value) Returns the type of value. 1 = number, 2 = text, 4 = logical value, 16 = error value, 64 = array. Information & Arrays Intermediate Classic
Syntax
```
=TYPE(value)
```
Arguments

value

Any Excel value, such as a number, text, or error.
Sample
```
=TYPE(B2)
```
Result 2 (Text)
Sample data
Download

A B C D
2 Cell A "Rohan" 2 Text / String
3 Cell B 350 1 Number
4 Cell C TRUE 4 Logical Boolean

Pro tip
Use TYPE inside IF to route processing: =IF(TYPE(A1)=1, A1*1.18, "Invalid Price").

Common pitfall
TYPE cannot distinguish between a formula and its result; it only evaluates the resulting output type.
100 HYPERLINK =HYPERLINK(link_location, [friendly_name]) Creates a shortcut or jump that opens a document stored on your hard drive, a network server, or on the internet. Information & Arrays Beginner Classic
Syntax
```
=HYPERLINK(link_location, [friendly_name])
```
Arguments

link_location

Path and file name to the document to be opened, or web URL.

[friendly_name]

The jump text or numeric value that is displayed in the cell.
Sample
```
=HYPERLINK("https://excel.com", "Excel Automation")
```
Result Excel Automation (Clickable)
Sample data
Download

A B C D
2 Portal https://excel.com Excel Automation Opens website in browser
3 Support mailto:hr@corp.com Email HR Dept Opens email client
4 Sheet Jump #Summary!A1 Jump to Summary Navigates to Summary sheet

Pro tip
To jump to a cell within the current workbook, start link_location with a hash #: =HYPERLINK("#'Sheet2'!A1", "Go to Sheet 2").

Common pitfall
Both link_location and friendly_name must be enclosed in quotation marks if entered as literal text strings.

	A	B	C	D
2	Rohan Sharma	9811122233	FALSE	Filled
3	Kabir Verma		TRUE	Missing Contact!
4	Meena Gupta	9822233344	FALSE	Filled

	A	B	C	D
2	100 / 2	50	FALSE	Clean number
3	100 / 0	#DIV/0!	TRUE	Division by zero!
4	VLOOKUP missing	#N/A	TRUE	Key not found

	A	B	C	D
2	SKU-999	#N/A	TRUE	Item missing in catalog
3	SKU-101	350.00	FALSE	Valid price retrieved
4	SKU-102	#DIV/0!	FALSE	Mathematical error, not #N/A

	A	B	C	D
2	Sales	350	TRUE	Genuine number
3	Date	10-Apr-2025	TRUE	Dates are serial numbers!
4	Text ID	"E-101"	FALSE	String text

	A	B	C	D
2	Name	Rohan Sharma	TRUE	String
3	Sales	350	FALSE	Number
4	Date	10-Apr-2025	FALSE	Date (Numeric serial)

	A	B	C	D
2	Is Active	TRUE	TRUE	Boolean value
3	Is Expired	FALSE	TRUE	Boolean value
4	Status Text	"TRUE"	FALSE	Text in quotes (not boolean)

	A	B	C	D
2	Q1 Total	=SUM(C2:C5)	TRUE	Dynamic Formula
3	Bonus Amount	5000	FALSE	Hardcoded Constant!
4	Net Margin	=B5/B1	TRUE	Dynamic Formula

	A	B	C	D
2	Cell A	"Rohan"	2	Text / String
3	Cell B	350	1	Number
4	Cell C	TRUE	4	Logical Boolean

	A	B	C	D
2	Portal	https://excel.com	Excel Automation	Opens website in browser
3	Support	mailto:hr@corp.com	Email HR Dept	Opens email client
4	Sheet Jump	#Summary!A1	Jump to Summary	Navigates to Summary sheet

	A	B	C	D
2	1	FALSE	White row	Odd
3	2	TRUE	Gray row	Even
4	3	FALSE	White row	Odd