102 Useful Excel Formulas Cheat Sheet PDF (Free Download Sheet)

If you want to be a power user of MS Excel, you must master the most useful Excel formulas of Excel. To be frank, it is not an easy task for all as the functions are a lot in numbers.

One trick can help you!

Let me share the trick that I used and still use to master the formulas: I used to revise 5-10 Excel formulas every day before start working anything with Excel. This revision makes a permanent image of the formulas in my brain. Then wherever I see the name of an Excel formula, I can quickly remember its syntax and uses. This helps me a lot while I am trying to solve an Excel problem with formulas. You can use this trick to master anything complex, not only Excel formulas.

In this Excel Formulas Tutorial, I am sharing here the most useful 102+ Excel formulas cheat sheet and downloadable free PDF. You can download the PDF and print it to use it anywhere, but for personal use. You cannot use this PDF for any kind of commercial use.

B. N.: I did not include here the specialized formulas for Engineering, Statistical, Web, etc. uses.

Useful Excel Formulas Cheat Sheet PDF. Free Download Excel Sheet

Table of Contents hide

Download Excel Formulas Cheat Sheet PDF

Click on the button below to download the PDF with 102 Excel Functions. I have documented every Excel formula with its syntax and a good number of examples.

Click here to download the PDF

Excel Formulas with Examples in an Excel Sheet (Free Download .xlsx File)

I have documented all the above Excel formulas in a single Excel sheet so that you can tweak the formulas to understand and practice better.

Click here to download the .xlsx file

102 Most Useful Excel Formulas with Examples

A. IS FUNCTIONS

1. ISBLANK

=ISBLANK(value)

If a cell is blank, it returns TRUE. If a cell is not blank, it returns FALSE.

isblank function excel syntax and examples

2. ISERR

=ISERR(value)

Checks whether a value is an error (#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) excluding #N/A, and returns TRUE or FALSE

iserr function excel syntax and examples

3. ISERROR

=ISERROR(value)

Checks whether a value is an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!), and returns TRUE or FALSE

iserror function excel syntax and examples

4. ISEVEN

=ISEVEN(value)

Returns TRUE if the number is even

iseven function excel syntax and examples

5. ISODD

=ISODD(value)

Returns TRUE if the number is odd

isodd function excel syntax and examples

6. ISFORMULA

=ISFORMULA(value)

Checks whether a reference is to a cell containing a formula, and returns TRUE or FALSE

isformula function excel syntax and examples

7. ISLOGICAL

=ISLOGICAL(value)

Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE

islogical function excel syntax and examples

8. ISNA

=ISNA(value)

Checks whether a value is #N/A, and returns TRUE or FALSE

isna function excel syntax and examples

9. ISNUMBER

=ISNUMBER(value)

Checks whether a value is a number, and returns TRUE or FALSE

isnumber function excel syntax and examples

10. ISREF

=ISREF(value)

Checks whether a value is a reference, and returns TRUE or FALSE

isref function excel syntax and examples

11. ISTEXT

=ISTEXT(value)

Checks whether a value is text, and returns TRUE or FALSE

istext function excel syntax and examples

12. ISNONTEXT

=ISNONTEXT(value)

Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE

isnontext function excel syntax and examples

B. CONDITIONAL FUNCTIONS

13. AVERAGEIF

=AVERAGEIF(range, criteria, [average_range])

Finds average (arithmetic mean) for the cells specified by a given condition or criteria

averageif function excel syntax and examples

14. SUMIF

=SUMIF(range, criteria, [sum_range])

Adds the cells specified by a given condition or criteria

sumif function excel syntax and examples

15. COUNTIF

=COUNTIF(range, criteria)

Counts the number of cells within a range that meet the given condition

countif function excel syntax and examples

16. AVERAGEIFS

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

Finds average (arithmetic mean) for the cells specified by a given set of conditions or criteria

averageifs function excel syntax and examples

17. SUMIFS

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

Adds the cells specified by a given set of conditions or criteria

sumifs function excel syntax and examples

18. COUNTIFS

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

Counts the number of cells specified by a given set of conditions or criteria

countifs function excel syntax and examples

19. 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 is FALSE

if function excel syntax and examples

20. IFERROR

=IFERROR(value, value_if_error)

Returns value_if_error if the expression is an error and the value of the expression itself otherwise

iferror function excel syntax and examples

21. IFNA

=IFNA(value, value_if_na)

Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression

ifna function excel syntax and examples

C. MATHEMATICAL FUNCTIONS

22. SUM

=SUM(number1, [number2], [number3], [number4], …)

Adds all the numbers in a range of cells

sum function excel syntax and examples

23. AVERAGE

=AVERAGE(number1, [number2], [number3], [number4], …)

Returns the average (arithmetic means) of its arguments, which can be numbers or names, arrays, or references that contain numbers

average function excel syntax and examples

24. AVERAGEA

=AVERAGEA(value1, [value2], [value3], [value4], …)

Returns the average (arithmetic means) of its arguments, evaluating text and FALSE in arguments as 0;  TRUE evaluates as 1. Arguments can be numbers, names, arrays, or references.

averagea function excel syntax and examples

25. COUNT

=COUNT(value1, [value2], [value3], …)

Count the number of cells in a range that contain numbers

count function excel syntax and examples

26. COUNTA

=COUNTA(value1, [value2], [value3], …)

Counts the number of cells in a range that are not empty

counta function excel syntax and examples

27. MEDIAN

=MEDIAN(number1, [number2], [number3], …)

Returns the median, or the number in the middle of the set of given numbers

median function excel syntax and examples

28. SUMPRODUCT

=SUMPRODUCT(array1, [array2], [array3], …)

Returns the sum of the products of corresponding ranges or arrays

sumproduct function excel syntax and examples

29. SUMSQ

=SUMSQ(number1, [number2], [number3], …)

Returns the sum of the squares of the arguments. The arguments can be numbers, arrays, names, or references to cells that contain numbers

sumsq function excel syntax and examples

30. COUNTBLANK

=COUNTBLANK(range)

Counts the number of empty cells in a range

countblank function excel syntax and examples

31. EVEN

=EVEN(number)

Rounds a positive number up and negative number down to the nearest even integer

even function excel syntax and examples

32. ODD

=ODD(number)

Rounds a positive number up and negative number down to the nearest odd integer.

odd function excel syntax and examples

33. INT

=INT(number)

Rounds a number down to the nearest integer

int function excel syntax and examples

34. LARGE

=LARGE(array, k)

Returns the k-th largest value in a data set. For example, the fifth-largest number

large function excel syntax and examples

35. SMALL

=SMALL(array, k)

Returns the k-th smallest value in a data set. For example, the fifth smallest number

small function excel syntax and examples

36. MAX & MAXA

=MAX(number1, [number2], [number3], [number4], …)

Returns the largest value in a set of values. Ignores logical values and text

=MAXA(value1, [value2], [value3], [value4], …)

Returns the largest value in a set of values. Do not ignore logical values and text. MAXA function evaluates TRUE as 1, FALSE as 0, and any Text value as 0. Empty cells are ignored

max and maxa functions excel syntax and examples

37. MIN & MINA

=MIN(number1, [number2], [number3], [number4], …)

Returns the smallest number in a set of values. Ignores logical values and text

=MINA(value1, [value2], [value3], [value4], …)

Returns the smallest value in a set of values. Do not ignore logical values and text. MAXA function evaluates TRUE as 1, FALSE as 0, and any Text value as 0. Empty cells are ignored

min and mina functions excel syntax and examples

38. MOD

=MOD(number, divisor)

Returns the remainder after a number is divided by a divisor

mod function excel syntax and examples

39. RAND

=RAND()

Returns a random number greater than or equal to 0 and less than 1, evenly distributed (changes on recalculation)

rand function excel syntax and examples

40. RANDBETWEEN

=RANDBETWEEN(bottom, top)

Returns a random number between the numbers you specify

randbetween function excel syntax and examples

41. SQRT

=SQRT(number)

Returns the square root of a number

sqrt function excel syntax and examples

42. SUBTOTAL

=SUBTOTAL(function_num, ref1, [ref2], [ref3], …)

Returns a subtotal in a list or database

subtotal function excel syntax and examples

D. FIND & SEARCH FUNCTIONS

43. FIND

=FIND(find_text, within_text, [start_num])

Returns the starting position of one text string within another text string. FIND is case-sensitive

find function excel syntax and examples

44. SEARCH

=SEARCH(find_text, within_text, [start_num])

Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive)

search function excel syntax and examples

45. SUBSTITUTE

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Replaces existing text with new text in a text string

substitute function excel syntax and examples

46. REPLACE

=REPLACE(old_text, start_num, num_chars, new_text)

Replaces part of a text string with a different text string

replace function excel syntax and examples

E. LOOKUP FUNCTIONS

47. MATCH

=MATCH(lookup_value, lookup_array, [match_type])

Returns the relative position of an item in an array that matches a specified value in a specified order

match function excel syntax and examples

48. LOOKUP

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Looks up a value either from a one-row or one-column range or from an array. Provided for backward compatibility

lookup function excel syntax and examples

49. HLOOKUP

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

Looks for a value in the top row of a table or array of values and return the value in the same column from a row you specify

hlookup function excel syntax and examples

50. VLOOKUP

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

Looks for a value in the leftmost column in a table, then return a value in the same row from a column you specify. By default, the table must be sorted in an ascending order

vlookup function excel syntax and examples

F. REFERENCE FUNCTIONS

51. ADDRESS

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Creates a cell reference as text, given specified row and column numbers

address function excel syntax and examples

52. CHOOSE

=CHOOSE(index_num, value1, [value2], [value3], …)

Chooses a value or action to perform from a list of values, based on an index number

choose function excel syntax and examples

53. INDEX

Array Form: =INDEX(array, row_num, [column_num])

Return the value of a specified cell or array of cells

index function excel (array form) syntax and examples

Reference Form: =INDEX(reference, row_num, [column_num], [area_num])

Returns a reference to specified cells

index function excel (reference form) syntax and examples

54. INDIRECT

=INDIRECT(ref_text, [a1])

Returns the reference specified by a text string

indirect function excel syntax and examples

55. OFFSET

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

Returns a reference to a range that is a given number of rows and columns from a given reference

offset function excel syntax and examples

G. DATE & TIME FUNCTIONS

56. DATE

=DATE(year, month, day)

Returns the number that represents the date in Microsoft Excel date-time code

date function excel syntax and examples

57. DATEVALUE

=DATEVALUE(date_text)

Converts a date in the form of text to a number that represents the date in the Microsoft Excel date-time code

datevalue function excel syntax and examples

58. TIME

=TIME(hour, minute, second)

Converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format

time function excel syntax and examples

59. TIMEVALUE

=TIMEVALUE(time_text)

Converts a text time to an Excel serial number for a time, a number from 0 (12:00:00 AM) to 0.999988424 (11:59:59 PM). Format the number with a time format after entering the formula

timevalue function excel syntax and examples

60. NOW

=NOW()

Returns the current date and time formatted as a date and time

now function excel syntax and examples

61. TODAY

=TODAY()

Returns the current date formatted as a date

today function excel syntax and examples

62. YEAR(),  MONTH(),  DAY(), HOUR(), MINUTE(), SECOND()

YEAR(), MONTH(), DAY(), HOUR(), MINUTE() and SECOND() Functions

All these functions take one argument: serial_number

year month day hour minute second functions excel syntax and examples

63. WEEKDAY

=WEEKDAY(serial_number, [return_type])

Returns a number from 1 to 7 identifying the day of the week from a date

weekday function excel syntax and examples

64. DAYS

=DAYS(end_date, start_date)

Returns the number of days between the two dates

days function excel syntax and examples

65. NETWORKDAYS

=NETWORKDAYS(start_date, end_date, [holidays])

Returns the number of whole workdays between two dates

networkdays function excel syntax and examples

66. WORKDAY

=WORKDAY(start_date, days, [holidays])

Returns the serial number of the date before or after a specified number of workdays

workday function excel syntax and examples

H. MISCELLANEOUS FUNCTIONS

67. AREAS

=AREAS(reference)

Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell

areas function excel syntax and examples

68. CHAR

=CHAR(number)

Returns the character specified by the code number from the character set for your computer

char function excel syntax and examples

69. CODE

=CODE(text)

Returns a numeric code for the first character in a text string, in the character set used by your computer

code function excel syntax and examples

70. CLEAN

=CLEAN(text)

Removes all non-printable characters from text. Examples of Non-Printable Characters are Tab, New Line characters. Their codes are 9 and 10.

clean function excel syntax and examples

71. TRIM

=TRIM(text)

Removes all spaces from a text string except for single spaces between words

trim function excel syntax and examples

72. LEN

=LEN(text)

Returns the number of characters in a text string

len function excel syntax and examples

73. COLUMN() & ROW() Functions

=COLUMN([reference])

Returns the column number of a reference

=ROW([reference])

Returns the row number of a reference

column and row functions excel syntax and examples

74. EXACT

=EXACT(text1, text2)

Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive

exact function excel syntax and examples

75. FORMULATEXT

=FORMULATEXT(reference)

Returns a formula as a string

formulatext function excel syntax and examples

76. LEFT(), RIGHT(), and MID() Functions

=LEFT(text, [num_chars])

Returns the specified number of characters from the start of a text string

=MID(text, start_num, num_chars)

Returns the characters from the middle of a text string, given a starting position and length

=RIGHT(text, [num_chars])

Returns the specified number of characters from the end of a text string

left, mid and right functions excel syntax and examples

77. LOWER(), PROPER(), and UPPER() Functions

=LOWER(text)

Converts all letters in a text string to lowercase

=PROPER(text)

Converts a text string to proper case; the first letter in each word in uppercase, and all other letters to lowercase

=UPPER(text)

Converts a text string to all uppercase letters

lower, proper, and upper functions excel with syntax and examples

78. REPT

=REPT(text, number_times)

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string

rept function excel syntax and examples

79. SHEET

=SHEET([value])

Returns the sheet number of the referenced sheet

sheet function excel syntax and examples

80. SHEETS

=SHEETS([reference])

Returns the number of sheets in a reference

sheets function excel syntax and examples

81. TRANSPOSE

=TRANSPOSE(array)

Converts a vertical range of cells to a horizontal range, or vice versa

transpose function excel syntax and examples

82. TYPE

=TYPE(value)

Returns an integer represnting the data type of a value: number = 1, text = 2; logical value = 4, error value = 16; array = 64

type function excel syntax and examples

83. VALUE

=VALUE(text)

Converts a text string that represents a number to a number

value function excel syntax and examples

I. RANK FUNCTIONS

84. RANK

=RANK(number, ref, [order])

This function is available for compatibility with Excel 2007 and others.

Returns the rank of a number in a list of numbers: its size relative to other values in the list

rank function excel syntax and examples

85. RANK.AVG

=RANK.AVG(number, ref, [order])

Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned

rank.avg function excel syntax and examples

86. RANK.EQ

=RANK.EQ(number, ref, [order])

Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned

rank.eq function excel syntax and examples

J. LOGICAL FUNCTIONS

87. AND

=AND(logical1, [logical2], [logical3], [logical4], …)

Checks whether all arguments are TRUE, and returns TRUE when all arguments are TRUE

and function excel syntax and examples

88. NOT

=NOT(logical)

Changes FALSE to TRUE, or TRUE to FALSE

not function excel syntax and examples

89. OR

=OR(logical1, [logical2], [logical3], [logical4], …)

Checks whether any of the arguments is TRUE, and returns TRUE or FALSE. Returns FALSE only when all arguments are FALSE

or function excel syntax and examples

90. XOR

=XOR(logical1, [logical2], [logical3], …)

Returns a logical ‘Exclusive Or’ of all arguments

xor function excel syntax and examples

Thanks for reading our blog. Are this Excel functions list helpful? If you find this article helpful, share this with your friends and colleagues. Do you have any suggestions that can make this list better? Let us know in the comment box. Or email us at [email protected]demy.com.

Read More

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

56 Comments
  1. Dear Kawser
    If is wonderful to have you share your love for Excel.
    When I tried to download your file it only brings me to your mailing list signup page which I’ve already filled out. Could you please send me either the file or a link.

    Thanks.

  2. Dear Kawser Bhai,

    When I tried to download your file it only brings me to your mailing list signup page which I’ve already filled out. Could you please send me either the file or a link?

  3. Although I have been already subscribed, I cannot download the Macro ebook and the Functions. It always be required an email adress. If I insert it, I get an error that I’m already subscribed.
    Thanks

  4. Wonderful resource, unfortunately experiencing the exact same problem as the users above.
    Can you please send me a direct download link. Thank you.

  5. Yep subscribed but no email link!

    • Borb,
      I tried to reach you to your email address but it is showing the mail cannot be reached.
      How can I help you?
      Regards
      Kawser

  6. Already subscribed; would like the Formula Cheat Sheet. Please send link, thanks!!

  7. Hi Kawser,

    Already subscribed, can you share the Formula Cheat Sheet. Thanks

  8. I clicked on the link, but nothing downloaded. Is there another option?

  9. I had the same problem as everyone else. Embarrassing since I shared the link on LinkedIn. :O\

    • Hey Tricia,
      I checked several times the link. There is no problem with the link 🙂 This is not a direct download link. You have to subscribe to get the cheat sheet. I hope you should not be embarrassed with the shared link.
      Best regards
      Kawser

  10. i’m always subcribed, but there is no email with a download-link :/

  11. subscribed but did not find any downlaod link?

  12. I have subscribed and waiting for my download link. Kindly send the link to my email. I have an interview later in the day. Thank you, Kawser.

  13. Hello,

    I just subscribed and would like the download link as well.

    Thanks,
    Kaz

  14. Hello Kawser

    I am facing issue on page script error. Please send me 100+Excel Formulas Cheat Sheet on my email id.

    Thanks

  15. Excellent support from this site.

  16. Please send me the excel!

  17. I subscribed! Please send me 100+Excel Formulas Cheat Sheet to my email

  18. Hello! I have subscribed to your newsletter, but am not able to find the link for the Excel Formulas Cheat Sheet. Please can you send? THANK YOU!

  19. July 2017 and I’m having the same issue, no link to download the cheat sheet in my email

  20. I did not receive the download. 🙁

  21. cannot find correct download link.

  22. Hi Kawser
    I’m having the same issue, send cheat sheet on my Email address

  23. Having same issues as everyone else, could you please send me the download link\

    Thanks.

  24. Cannot download example file. It keeps directing me to mailchimp and then I’m seeing error because I’m already subscribed.

  25. No link please email I already subscribed. also like to say thank you very much for this in advace

  26. Hello! thank you for your hard work … I have the same problem … I did register but wouldn’t allow me to download … could you please send me the link? My email is : [email protected]

  27. Can u send me link please

  28. can you please share the direct link..?

  29. hlo sir can you please share me advance excel formulas list.

  30. i dont download your cheat sheet why i m subscribe already

  31. Can u send me link, please

Leave a reply

ExcelDemy
Logo