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 1

Math & Arithmetic

01 SUM =SUM(number1, [number2], ...) Adds all numbers in a range of cells, multiple non-contiguous ranges, or individual numeric constants. Ideal for aggregating financial rows, totals, and sales volumes. Math & Arithmetic Beginner Classic
Syntax
```
=SUM(number1, [number2], ...)
```
Arguments

number1

The first number, cell reference, or continuous range to sum.

[number2, ...]

Optional additional numbers, cell references, or disjoint ranges (up to 255 total arguments).
Sample
```
=SUM(C2:C5)
```
Result 1450
Sample data
Download

A B C D
2 E-101 HR 350 Active
3 E-102 IT 400 Active
4 E-103 Sales 450 Active
5 E-104 Finance 250 Active

Pro tip
Press Alt + = on Windows (or Cmd + Shift + T on Mac) for instant AutoSum on currently selected rows or columns.

Common pitfall
Text strings inside ranges are silently ignored, but typing text directly as an argument like =SUM("test", 10) throws a #VALUE! error.
02 SUMIF =SUMIF(range, criteria, [sum_range]) Calculates the sum of values in a range that satisfy a single condition. If sum_range is omitted, the cells in range itself are totaled. Math & Arithmetic Intermediate Classic
Syntax
```
=SUMIF(range, criteria, [sum_range])
```
Arguments

range

The range of cells you want evaluated by criteria.

criteria

The condition in the form of a number, expression, cell reference, text, or wildcard.

[sum_range]

The actual cells to sum if they meet criteria. If omitted, range is summed directly.
Sample
```
=SUMIF(B2:B5, "HR", C2:C5)
```
Result 600
Sample data
Download

A B C D
2 E-101 HR 350 Evaluated (Match)
3 E-102 IT 400 Skipped
4 E-103 HR 250 Evaluated (Match)
5 E-104 Sales 450 Skipped

Pro tip
Wildcards like * (any sequence of characters) and ? (any single character) can be used in criteria strings (e.g., "*North*").

Common pitfall
Range and sum_range should be the exact same size; size mismatches can yield misaligned calculations or silent errors.
03 SUMIFS =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) Adds cells in a range that meet multiple criteria. Unlike SUMIF, the sum_range is placed FIRST to support multiple criteria pairs seamlessly. Math & Arithmetic Intermediate Classic
Syntax
```
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
```
Arguments

sum_range

The specific range of numeric cells you intend to calculate totals for.

criteria_range1

The first range to evaluate with criteria1.

criteria1

Condition 1 that determines which cells will be included.

[criteria_range2, criteria2]

Subsequent ranges and conditions (up to 127 condition pairs).
Sample
```
=SUMIFS(C2:C5, B2:B5, "HR", D2:D5, "01-Jan")
```
Result 350
Sample data
Download

A B C D
2 E-101 HR 350 01-Jan
3 E-102 IT 400 01-Jan
4 E-103 HR 250 02-Jan
5 E-104 Sales 450 02-Jan

Pro tip
Remember that SUMIFS puts sum_range FIRST, whereas legacy SUMIF puts sum_range LAST.

Common pitfall
All criteria ranges must have the exact same number of rows and columns as sum_range, or Excel returns #VALUE!.
04 ROUND =ROUND(number, num_digits) Rounds a number to a specified precision. If num_digits is greater than 0, rounds to decimals; if 0, rounds to nearest whole integer; if less than 0, rounds to tens, hundreds, or thousands. Math & Arithmetic Beginner Classic
Syntax
```
=ROUND(number, num_digits)
```
Arguments

number

The numeric value or cell containing the decimal number you want to round.

num_digits

Number of decimal places to round to. 0 rounds to whole integer, negative values round to tens/hundreds.
Sample
```
=ROUND(B2, 2)
```
Result 34.5
Sample data
Download

A B C D
2 Item 1 34.498 34.50 Rounds up from 8
3 Item 2 125.642 125.64 Rounds down from 2
4 Item 3 89.555 89.56 5 rounds upward

Pro tip
Use num_digits = -1 to round to the nearest 10, or -2 for the nearest 100 (e.g. ROUND(1234, -2) yields 1,200).

Common pitfall
Changing cell display formatting only hides decimals visually; ROUND actually changes the underlying numerical value.
05 ROUNDUP =ROUNDUP(number, num_digits) Behaves like ROUND, except that it always rounds numbers upward away from zero, essential for calculating container counts, shipping crates, and packaging minimums. Math & Arithmetic Beginner Classic
Syntax
```
=ROUNDUP(number, num_digits)
```
Arguments

number

Any real number that you want rounded upward.

num_digits

The number of digits to which you want to round the number.
Sample
```
=ROUNDUP(B2, 0)
```
Result 35
Sample data
Download

A B C D
2 BX-01 34.12 35 Pushed up to 35
3 BX-02 12.01 13 Even 0.01 pushes up
4 BX-03 99.90 100 Pushed up to 100

Pro tip
Use ROUNDUP(val, 0) whenever you need ceiling integers (e.g. calculating billable hours or shipping boxes).

Common pitfall
For negative numbers, ROUNDUP rounds away from zero (-3.1 becomes -4, not -3).
06 ROUNDDOWN =ROUNDDOWN(number, num_digits) Behaves like ROUND, except that it always rounds numbers downward toward zero. Truncates digits past the designated decimal point. Math & Arithmetic Beginner Classic
Syntax
```
=ROUNDDOWN(number, num_digits)
```
Arguments

number

The real number you want rounded downward.

num_digits

Number of decimal digits to keep without rounding up.
Sample
```
=ROUNDDOWN(B2, 2)
```
Result 34.89
Sample data
Download

A B C D
2 Widget A 34.899 34.89 Leaves exact .89
3 Widget B 120.954 120.95 Drops trailing digits
4 Widget C 45.678 45.67 Truncates downward

Pro tip
ROUNDDOWN is identical in behavior to TRUNC when num_digits is positive.

Common pitfall
For negative numbers, ROUNDDOWN rounds toward zero (-3.8 becomes -3).
07 INT =INT(number) Rounds a real number down to the nearest integer. Extremely useful for extracting days from date-time timestamps or whole completed units. Math & Arithmetic Beginner Classic
Syntax
```
=INT(number)
```
Arguments

number

The real number you want to round down to a whole integer.
Sample
```
=INT(B2)
```
Result 12
Sample data
Download

A B C D
2 Product X 12.85 12 Decimals dropped
3 Product Y 145.12 145 Integer portion
4 Product Z 99.99 99 Rounds down

Pro tip
To extract just the time fraction from a combined date-time cell: =A2 - INT(A2).

Common pitfall
With negative numbers, INT rounds DOWN away from zero: INT(-4.2) is -5, whereas TRUNC(-4.2) is -4.
08 ODD =ODD(number) Rounds a positive number up to the nearest odd integer and a negative number down to the nearest negative odd integer. Math & Arithmetic Beginner Classic
Syntax
```
=ODD(number)
```
Arguments

number

The value to round up to the nearest odd number.
Sample
```
=ODD(A2)
```
Result 3
Sample data
Download

A B C D
2 1 1 Odd match Already odd, stays 1
3 2 3 Rounded Even 2 rounds up to 3
4 3 3 Odd match Already odd, stays 3

Pro tip
If the input number is already an odd integer, ODD returns the number itself without modification.

Common pitfall
Non-numeric text passed into ODD triggers an immediate #VALUE! error.
09 EVEN =EVEN(number) Rounds a positive number up to the nearest even integer and a negative number down to the nearest negative even integer. Math & Arithmetic Beginner Classic
Syntax
```
=EVEN(number)
```
Arguments

number

The value to round up to the nearest even integer.
Sample
```
=EVEN(A2)
```
Result 2
Sample data
Download

A B C D
2 1 2 Even Target Odd 1 rounds up to 2
3 2 2 Even Match Already even, stays 2
4 3 4 Even Target Odd 3 rounds up to 4

Pro tip
Use EVEN when items must be ordered in pairs (e.g. dual channels, socket pairs, or twin tires).

Common pitfall
0 is already considered an even number by Excel, so EVEN(0) returns 0.
10 ABS =ABS(number) Returns the absolute value of a number without regard to its sign. Crucial for financial variances, audit discrepancies, and distance metrics. Math & Arithmetic Beginner Classic
Syntax
```
=ABS(number)
```
Arguments

number

The real number of which you want the absolute value.
Sample
```
=ABS(B2)
```
Result 1500
Sample data
Download

A B C D
2 Budget A -1500 1,500 Negative converted to +
3 Budget B 2300 2,300 Positive remains unchanged
4 Budget C -450 450 Discrepancy magnitude

Pro tip
Commonly paired with SUM or AVERAGE to compute Mean Absolute Deviation (MAD) in forecasting models.

Common pitfall
Cannot process text representations of numbers or dates formatted as unparsed strings directly.
11 RAND =RAND() Returns a random real number between 0 and 1. A new random number is generated every single time worksheet calculations occur. Math & Arithmetic Beginner Classic
Syntax
```
=RAND()
```
Arguments

No arguments

Takes zero arguments. Empty parentheses are mandatory.
Sample
```
=RAND()
```
Result 0.742819
Sample data
Download

A B C D
2 Trial 1 0.742819 74.28 Volatile
3 Trial 2 0.219503 21.95 Volatile
4 Trial 3 0.894121 89.41 Volatile

Pro tip
To generate a random decimal between A and B: =A + RAND() * (B - A).

Common pitfall
RAND is a volatile function that recalculates on every single worksheet edit. Copy and Paste-as-Values to freeze numbers.
12 RANDBETWEEN =RANDBETWEEN(bottom, top) Generates a random whole integer between two boundary values. Ideal for creating test dummy data, student scores, or sample IDs. Math & Arithmetic Beginner Classic
Syntax
```
=RANDBETWEEN(bottom, top)
```
Arguments

bottom

The smallest integer RANDBETWEEN can return.

top

The largest integer RANDBETWEEN can return.
Sample
```
=RANDBETWEEN(10, 100)
```
Result 68
Sample data
Download

A B C D
2 Student 1 10 68 100
3 Student 2 10 42 100
4 Student 3 10 95 100

Pro tip
Copy and Paste as Values (Ctrl + Alt + V -> V) immediately after generating mock test data so values stop recalculating.

Common pitfall
Bottom must be less than or equal to Top, otherwise Excel returns a #NUM! error.

	A	B	C	D
2	E-101	HR	350	Active
3	E-102	IT	400	Active
4	E-103	Sales	450	Active
5	E-104	Finance	250	Active

	A	B	C	D
2	E-101	HR	350	Evaluated (Match)
3	E-102	IT	400	Skipped
4	E-103	HR	250	Evaluated (Match)
5	E-104	Sales	450	Skipped

	A	B	C	D
2	E-101	HR	350	01-Jan
3	E-102	IT	400	01-Jan
4	E-103	HR	250	02-Jan
5	E-104	Sales	450	02-Jan

	A	B	C	D
2	Item 1	34.498	34.50	Rounds up from 8
3	Item 2	125.642	125.64	Rounds down from 2
4	Item 3	89.555	89.56	5 rounds upward

	A	B	C	D
2	BX-01	34.12	35	Pushed up to 35
3	BX-02	12.01	13	Even 0.01 pushes up
4	BX-03	99.90	100	Pushed up to 100

	A	B	C	D
2	Widget A	34.899	34.89	Leaves exact .89
3	Widget B	120.954	120.95	Drops trailing digits
4	Widget C	45.678	45.67	Truncates downward

	A	B	C	D
2	Product X	12.85	12	Decimals dropped
3	Product Y	145.12	145	Integer portion
4	Product Z	99.99	99	Rounds down

	A	B	C	D
2	1	1	Odd match	Already odd, stays 1
3	2	3	Rounded	Even 2 rounds up to 3
4	3	3	Odd match	Already odd, stays 3

	A	B	C	D
2	1	2	Even Target	Odd 1 rounds up to 2
3	2	2	Even Match	Already even, stays 2
4	3	4	Even Target	Odd 3 rounds up to 4

	A	B	C	D
2	Budget A	-1500	1,500	Negative converted to +
3	Budget B	2300	2,300	Positive remains unchanged
4	Budget C	-450	450	Discrepancy magnitude

	A	B	C	D
2	Trial 1	0.742819	74.28	Volatile
3	Trial 2	0.219503	21.95	Volatile
4	Trial 3	0.894121	89.41	Volatile

	A	B	C	D
2	Student 1	10	68	100
3	Student 2	10	42	100
4	Student 3	10	95	100