Top 50 Excel Functions cheat sheet

1. SUM Function

The SUM function adds up a range of numbers.

Syntax:

=SUM(number1, [number2], ...)

Example:

Cell	Value
A1	10
A2	20
A3	30
A4	40
A5	50

Formula:

=SUM(A1:A5)

Calculation:

• Add the values: 10 + 20 + 30 + 40 + 50.

Result:

150

2. AVERAGE Function

The AVERAGE function calculates the mean (average) of a range of numbers.

Syntax:

=AVERAGE(number1, [number2], ...)

Example:

Cell	Value
B1	10
B2	20
B3	30
B4	40
B5	50

Formula:

=AVERAGE(B1:B5)

Calculation:

• Sum the values: 10 + 20 + 30 + 40 + 50 = 150.
• Divide by the count of numbers: 150 ÷ 5.

Result:

30

---

3. COUNT Function

The COUNT function counts the number of cells containing numeric values.

Syntax:

=COUNT(value1, [value2], ...)

Example:

Cell	Value
C1	10
C2	Hello
C3	30
C4	(Blank)
C5	50

Formula:

=COUNT(C1:C5)

Calculation:

• Numeric values: 10, 30, 50.
• Excluded: Text (\”Hello\”) and blank cells.

Result:

3

---

4. COUNTA Function

The COUNTA function counts the number of non-empty cells.

Syntax:

=COUNTA(value1, [value2], ...)

Example:

Cell	Value
D1	10
D2	Hello
D3	30
D4	(Blank)
D5	World

Formula:

=COUNTA(D1:D5)

Calculation:

• Non-empty cells: 10, Hello, 30, World.

Result:

4

---

5. IF Function

The IF function performs a logical test and returns a value based on the result.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Example:

Cell	Value
E1	15

Formula:

=IF(E1>10, \"Yes\", \"No\")

Logic:

• If E1 is greater than 10, return \”Yes\”.
• Otherwise, return \”No\”.

Result:

Yes

---

6. VLOOKUP Function

The VLOOKUP function searches for a value in the first column of a range and returns a value from another column.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

Product ID	Price
101	500
102	600
103	700

Formula:

=VLOOKUP(102, A1:B3, 2, FALSE)

Logic:

• Look for 102 in column A.
• Return the value from column B in the same row.

Result:

600

---

7. HLOOKUP Function

The HLOOKUP function searches for a value in the first row of a range and returns a value from another row.

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example:

A	B	C
ID	101	102
Price	500	600

Formula:

=HLOOKUP(102, A1:C2, 2, FALSE)

Logic:

• Look for 102 in row 1.
• Return the value from row 2.

Result:

600

---

8. INDEX Function

The INDEX function returns the value of a cell in a specified row and column.

Syntax:

=INDEX(array, row_num, [column_num])

Example:

A	B	C
10	20	30
40	50	60

Formula:

=INDEX(A1:C2, 2, 3)

Logic:

• From the range A1:C2, return the value in row 2, column 3.

Result:

60

---

9. MATCH Function

The MATCH function returns the position of a value within a range.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

Example:

A
Apple
Banana
Cherry

Formula:

=MATCH(\"Banana\", A1:A3, 0)

Logic:

• Look for \”Banana\” in the range A1:A3.

Result:

2

---

This format provides clarity and emphasizes key formulas for practical use. By using code-style formatting, it’s easier to distinguish formulas from explanations.

---

10. SUMIF

The SUMIF function adds values in a range that meet a specific condition.

Syntax:

=SUMIF(range, criteria, [sum_range])

Example:

A	B
5	100
15	200
25	300
10	400
20	500

Formula:

=SUMIF(A1:A5, \">10\", B1:B5)

Logic:

• Add values in column B where corresponding values in column A are greater than 10: 200 + 300 + 500.

Result:

1000

---

11. SUMIFS

The SUMIFS function adds values based on multiple conditions.

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

A	B	C
5	100	15
15	200	25
25	300	20
10	400	30
20	500	10

Formula:

=SUMIFS(B1:B5, A1:A5, \">10\", C1:C5, \"<20\")

Logic:

• Add values in column B where:
  • A > 10 (200, 300, 500) and
  • C < 20 (200, 500).

Result:

700

---

12. AVERAGEIF

The AVERAGEIF function calculates the average of values based on a condition.

Syntax:

=AVERAGEIF(range, criteria, [average_range])

Example:

A	B
5	10
15	20
25	30
10	40
20	50

Formula:

=AVERAGEIF(A1:A5, \">10\", B1:B5)

Logic:

• Average values in column B where A > 10 (20, 30, 50).
• Calculation: (20 + 30 + 50) ÷ 3.

Result:

33.33

---

13. AVERAGEIFS

The AVERAGEIFS function calculates the average of values based on multiple conditions.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

A	B	C
5	10	15
15	20	25
25	30	20
10	40	30
20	50	10

Formula:

=AVERAGEIFS(B1:B5, A1:A5, \">10\", C1:C5, \"<20\")

Logic:

• Average values in column B where:
  • A > 10 (20, 30, 50) and
  • C < 20 (20, 50).
• Calculation: (20 + 50) ÷ 2.

Result:

35

---

14. COUNTIF

The COUNTIF function counts the number of cells that meet a specific condition.

Syntax:

=COUNTIF(range, criteria)

Example:

A
5
15
25
10
20

Formula:

=COUNTIF(A1:A5, \">10\")

Logic:

• Count values greater than 10: 15, 25, 20.

Result:

3

---

15. COUNTIFS

The COUNTIFS function counts the number of cells that meet multiple conditions.

Syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

A	B
5	10
15	20
25	30
10	40
20	50

Formula:

=COUNTIFS(A1:A5, \">10\", B1:B5, \"<40\")

Logic:

• Count rows where:
  • A > 10 (15, 25, 20) and
  • B < 40 (20, 30).

Result:

2

---

16. LEFT

The LEFT function extracts characters from the beginning of a text string.

Syntax:

=LEFT(text, num_chars)

Example:

A
Excel Function

Formula:

=LEFT(A1, 5)

Logic:

• Extract the first 5 characters: \”Excel\”.

Result:

Excel

---

17. RIGHT

The RIGHT function extracts characters from the end of a text string.

Syntax:

=RIGHT(text, num_chars)

Example:

A
Excel Function

Formula:

=RIGHT(A1, 8)

Logic:

• Extract the last 8 characters: \”Function\”.

Result:

Function

---

18. MID

The MID function extracts a substring from the middle of a text string.

Syntax:

=MID(text, start_num, num_chars)

Example:

A
Excel Function

Formula:

=MID(A1, 7, 8)

Logic:

• Start at character 7 and extract 8 characters: \”Function\”.

Result:

Function

---

19. LEN

The LEN function returns the length of a text string.

Syntax:

=LEN(text)

Example:

A
Excel Function

Formula:

=LEN(A1)

Logic:

• Count characters (including spaces): 15.

Result:

15

---

20. TRIM

The TRIM function removes extra spaces from a text string, leaving only single spaces between words.

Syntax:

=TRIM(text)

Example:

A
\” Excel Tips \”

Formula:

=TRIM(A1)

Logic:

• Remove extra spaces: \”Excel Tips\”.

Result:

Excel Tips

---

---

21. CONCATENATE

The CONCATENATE function joins multiple text strings into one.

Syntax:

=CONCATENATE(text1, [text2], ...)

Example:

A	B
Hello	World

Formula:

=CONCATENATE(A1, \" \", B1)

Logic:

• Joins \”Hello\” and \”World\” with a space in between.

Result:

Hello World

---

22. TEXT

The TEXT function formats a number or date into text.

Syntax:

=TEXT(value, format_text)

Example:

A
12/15/2024

Formula:

=TEXT(A1, \"MM/DD/YYYY\")

Logic:

• Converts the date in A1 into the \”MM/DD/YYYY\” format.

Result:

12/15/2024

---

23. DATE

The DATE function creates a date from year, month, and day.

Syntax:

=DATE(year, month, day)

Example:

Formula:

=DATE(2024, 12, 25)

Logic:

• Creates the date December 25, 2024.

Result:

12/25/2024

---

24. NOW

The NOW function returns the current date and time.

Syntax:

=NOW()

Example:

Formula:

=NOW()

Logic:

• Displays the current date and time.

Result (example):

12/15/2024 12:30 PM

---

25. TODAY

The TODAY function returns the current date.

Syntax:

=TODAY()

Example:

Formula:

=TODAY()

Logic:

• Displays the current date without the time.

Result (example):

12/15/2024

---

26. DAY

The DAY function returns the day of the month from a date.

Syntax:

=DAY(date)

Example:

A
12/15/2024

Formula:

=DAY(A1)

Logic:

• Extracts the day part from the date.

Result:

15

---

27. MONTH

The MONTH function returns the month from a date.

Syntax:

=MONTH(date)

Example:

A
12/15/2024

Formula:

=MONTH(A1)

Logic:

• Extracts the month part from the date.

Result:

12

---

28. YEAR

The YEAR function returns the year from a date.

Syntax:

=YEAR(date)

Example:

A
12/15/2024

Formula:

=YEAR(A1)

Logic:

• Extracts the year part from the date.

Result:

2024

---

29. TEXTJOIN

The TEXTJOIN function joins multiple text strings with a specified delimiter.

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Example:

A	B	C	D
Apple	Banana	Cherry

Formula:

=TEXTJOIN(\", \", TRUE, A1:D1)

Logic:

• Joins non-empty cells in A1:D1 with a comma.

Result:

Apple, Banana, Cherry

---

30. INDIRECT

The INDIRECT function returns the reference specified by a text string.

Syntax:

=INDIRECT(ref_text, [a1])

Example:

A	B
10

Formula:

=INDIRECT(\"A1\")

Logic:

• Refers to the value in cell A1.

Result:

10

---

31. OFFSET

The OFFSET function returns a reference to a range that is offset from a starting cell.

Syntax:

=OFFSET(reference, rows, cols, [height], [width])

Example:

A	B	C
10	20	30
40	50	60

Formula:

=OFFSET(A1, 1, 2)

Logic:

• Moves 1 row down and 2 columns right from A1 (points to C2).

Result:

60

---

32. CHOOSE

The CHOOSE function selects a value based on an index number.

Syntax:

=CHOOSE(index_num, value1, [value2], ...)

Example:

Formula:

=CHOOSE(2, \"Red\", \"Green\", \"Blue\")

Logic:

• Returns the 2nd value: Green.

Result:

Green

---

33. PMT

The PMT function calculates the payment for a loan.

Syntax:

=PMT(rate, nper, pv, [fv], [type])

Example:

Formula:

=PMT(5%/12, 12, -1000)

Logic:

• Monthly payment for a loan of 1000 at 5% annual interest for 12 months.

Result:

-85.61

---

34. FV

The FV function calculates the future value of an investment.

Syntax:

=FV(rate, nper, pmt, [pv], [type])

Example:

Formula:

=FV(5%/12, 12, -100)

Logic:

• Future value of monthly payments of 100 at 5% annual interest for 12 months.

Result:

1233.00

---

35. RAND

The RAND function generates a random number between 0 and 1.

Syntax:

=RAND()

Result:

0.72645

---

36. RANDBETWEEN

The RANDBETWEEN function generates a random number between two specified values.

Syntax:

=RANDBETWEEN(bottom, top)

Formula:

=RANDBETWEEN(1, 100)

Result:

57

---

37. ROUND

The ROUND function rounds a number to a specified number of digits.

Syntax:

=ROUND(number, num_digits)

Example:

A
12.34567

Formula:

=ROUND(A1, 2)

Result:

12.35

---

38. IRR

The IRR function calculates the internal rate of return for a series of cash flows.

Syntax:

=IRR(values, [guess])

Example:

A
-500
200
200
200
200

Formula:

=IRR(A1:A5)

Logic:

• Finds the rate of return such that the net present value (NPV) of the cash flows is zero.

Result:

14.87% (approx.)

---

39. RAND

The RAND function generates a random number between 0 and 1.

Syntax:

=RAND()

Example: Formula:

=RAND()

Result:

0.52734 (example; output changes every time)

---

40. RANDBETWEEN

The RANDBETWEEN function generates a random number between two specified integers.

Syntax:

=RANDBETWEEN(bottom, top)

Example:

Formula:

=RANDBETWEEN(1, 100)

Result:

42 (example; output changes every time)

---

---

41. ROUNDUP

The ROUNDUP function rounds a number up, away from zero.

Syntax:

=ROUNDUP(number, num_digits)

Example:

A
12.34567

Formula:

=ROUNDUP(A1, 2)

Logic:

• Rounds 12.34567 up to 2 decimal places.

Result:

12.35

---

42. ROUNDDOWN

The ROUNDDOWN function rounds a number down, towards zero.

Syntax:

=ROUNDDOWN(number, num_digits)

Example:

A
12.34567

Formula:

=ROUNDDOWN(A1, 2)

Logic:

• Rounds 12.34567 down to 2 decimal places.

Result:

12.34

---

43. MOD

The MOD function returns the remainder after division.

Syntax:

=MOD(number, divisor)

Example:

A
10

Formula:

=MOD(A1, 3)

Logic:

• Divides 10 by 3 and returns the remainder.

Result:

1

---

44. AND

The AND function returns TRUE if all conditions are true.

Syntax:

=AND(logical1, [logical2], ...)

Example:

A	B
15	3

Formula:

=AND(A1 > 10, B1 < 5)

Logic:

• Both conditions are true: A1 > 10 and B1 < 5.

Result:

TRUE

---

45. OR

The OR function returns TRUE if any condition is true.

Syntax:

=OR(logical1, [logical2], ...)

Example:

A	B
8	6

Formula:

=OR(A1 > 10, B1 < 5)

Logic:

• One condition (B1 < 5) is true.

Result:

TRUE

---

46. NOT

The NOT function reverses the logical value of its argument.

Syntax:

=NOT(logical)

Example:

A
15

Formula:

=NOT(A1 > 10)

Logic:

• A1 > 10 is TRUE, so NOT makes it FALSE.

Result:

FALSE

---

47. ISBLANK

The ISBLANK function checks if a cell is empty.

Syntax:

=ISBLANK(value)

Example:

A

Formula:

=ISBLANK(A1)

Logic:

• Checks if A1 is empty.

Result:

TRUE

---

48. ISNUMBER

The ISNUMBER function checks if a cell contains a number.

Syntax:

=ISNUMBER(value)

Example:

A
123

Formula:

=ISNUMBER(A1)

Logic:

• A1 contains a number.

Result:

TRUE

---

49. ISERROR

The ISERROR function checks if a cell contains an error.

Syntax:

=ISERROR(value)

Example:

A
#DIV/0!

Formula:

=ISERROR(A1)

Logic:

• A1 contains an error (#DIV/0!).

Result:

TRUE

---

50. ISNA

The ISNA function checks if a cell contains the #N/A error.

Syntax:

=ISNA(value)

Example:

A
#N/A

Formula:

=ISNA(A1)

Logic:

• A1 contains the #N/A error.

Result:

TRUE

These functions will help you perform a variety of tasks in Excel and are great for improving both data analysis and everyday productivity!