Top 100 Excel Functions With Examples

Functions are one of the most important features in Excel and must to learn to make best out of this most popular spreadsheet tool on the planet.

Now the question is how can you learn any of these functions in the easiest way?

So my answer is, first of all, you need to know what's the purpose of a function. After that, syntax.

Next thing is to learn its usage through a simple example and in the end some important information about it.

And to help you on this, I have compiled a list of top most 100 Excel functions which you can use in your everyday work.

For example, if you want to learn about IF function, you have a quick intro, syntax description, some additional information and then an example. In the end, you have links to the relevant sources to learn further.

And all the functions are structured in a same way.

Text Functions


1. Excel LEFT Function: It returns specified (by you) characters from the left side of a text string. You can extract characters up to the total length of the text string.

2. Excel RIGHT Function: It returns specified (by you) characters from the right side of a text string. You can extract characters up to the total length of the text string.

3. Excel MID Function: It returns specific characters from a specific position in the text string. You can extract text up to the total length of the text string.

4. Excel LOWER Function: It converts a normal text into a lower case text where you have all the letters in small letters and numbers will be unchanged.

5. Excel UPPER Function: It converts a normal text into an upper case text where you have all the letters in capital letters and numbers will be unchanged.

6. Excel PROPER Function: It converts a normal text into a proper case text where you have the first letter of a word in capital letter and rests all in small and numbers will be unchanged.

7. Excel REPT Function: It repeats the text which you specify the number of times. You just need to specify the text and the repetition number. Its biggest benefit is for creating the in-cell chart.

8. Excel LEN Function: It returns the number of total characters a text string has. You can input a text string directly into the function or simply refer to a cell.

9. Excel FIND Function: It helps to find the starting position of a text string (Case Sensitive) from another text string. You just need to specify the text to find and a text from which you want to find.

10. Excel SEARCH Function: It helps to find the starting position of a text string (Non-Case Sensitive) from another text string. You just need to specify the text to find and a text from which you want to find.

Date Functions


11. Excel DATE Function: It creates a valid date according to Excel's date format. You need to provide day, month and year to create a date.

12. Excel DATEDIF Function: It returns the difference between the start date and end date. It has six different ways to show that difference.

13. Excel DATEVALUE Function: It converts a text date into a valid date according to Excel's date format. You can refer to a cell or you can also enter that text directly into the function.

14. Excel EDATE Function: It gives you a date which is a number of months before or after a specified date. In simple words, you'll get the same date in future or past month like the specific date.

15. Excel DAY Function: It returns the day of the month ranging from 1-31 from the date you specified.

16. Excel DAYS Function: It returns the difference between two dates using four different methods which you can specify.

17. Excel TODAY Function: It gives you current date/today's date as per your system settings. This is a volatile function which changes its values when you recalculate your worksheet.

18. Excel MONTH Function: It returns the month number ranging from 1-12 from the date you specified.

19. Excel EOMONTH Function: It gives you the last date of a month which is a number of months before or after a specified date. In simple words, you'll get the last date of a future month or past month.

20. Excel YEAR Function: It returns the year number from the date you have specified (It should be a valid date).

21. Excel WEEKDAY Function: It returns the day number from the date you have specified ranging from 1-7.

22. Excel WEEKNUM Function: It returns the week number of a specific date. For example, the week containing Jan 1 is the first week of the year and is numbered week 1.

23. Excel NETWORKDAYS Function: It returns the number of working days between the start date and end date which exclude weekends and any dates identified in holidays.

24. Excel NETWORKDAYS.INTL Function: It returns the number of workdays between the start date and end date which exclude weekends and any dates identified and specified holidays.

Time Functions


25. Excel TIME Function: If create a valid time according to Excel's time format. You need to provide hour, minutes, and seconds.

26. Excel TIMEVALUE Function: It converts a text time value into a valid time value according to Excel's time format. You can refer to a cell or you can also enter that text directly into the function.

27. Excel HOUR Function: It returns the hour value (ignores minutes and seconds) from a time value ranging from 0-23 hours.

28. Excel MINUTE Function: It returns the minute value (ignores hour and seconds) from a time value ranging from 0-59 minutes.

29. Excel SECOND Function: It returns the seconds' value (ignores hours and minutes) from a time value ranging from 0-59 seconds.

30. Excel NOW Function: It gives you current date and time as per your system settings. This is a volatile function which changes its values when you recalculate your worksheet.

Logical Functions


31. Excel IF Function: You can provide a condition to check and it returns a specific value if that condition is TRUE and another value if that condition is FALSE.

32. Excel IFERROR Function: It evaluates a value for an error and returns a specific value if an error occurs.

33. Excel IFNA Function: It evaluates a value for #N/A error and returns a specific value if an error occurs.

34. Excel OR Function: Test two or more conditions differently and return TRUE if any of those conditions is TRUE and FALSE if all those conditions are FALSE.

35. Excel AND Function: Test two or more conditions jointly and return TRUE if all of those conditions is TRUE and FALSE if any of those conditions is FALSE.

36. Excel NOT Function: It converts a TRUE into FALSE and FALSE into TRUE.

37. Excel FALSE Function: It returns the logical value TRUE in the cell where you insert it.

38. Excel TRUE Function: It returns the logical value FALSE in the cell where you insert it.

Maths Functions


39. Excel SUM Function: It adds values. It can add individual values, cell references or ranges or a mix of all three.

40. Excel SUMIF Function: It returns the sum of the numeric values of which meet a condition. You need to specify a condition and the range to check that condition.

41. Excel SUMIFS Function: It returns the sum of the numeric values of which meet multiple conditions. You need to specify conditions and range to check those conditions.

42. Excel SUMPRODUCT Function: It multiplies values from corresponding cells in the given arrays, and returns the sum of those products. It can take an array without using Ctrl + Shift + Enter.

43. Excel ABS Function: It converts a number into an absolute number. The absolute value of a number is the number without its sign.

44. Excel EVEN Function: It returns a number by rounding it to the nearest even number.

45. Excel INT Function: It returns a number by rounding it to the nearest to the nearest integer.

46. Excel MOD Function: It returns remainder after dividing two numbers, not the result of the division of two numbers.

47. Excel MROUND Function: It rounds a number to the nearest multiple of a number without considering that number is greater or lower than the original number.

48. Excel TRUNC Function: It truncates a number to an integer by removing the fractional part of the number. In simple word, it returns on integer part from a value.

49. Excel RAND Function: It returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

Statistical Function


50. Excel COUNT Function: It counts the number of cells which contain numbers, and counts the numbers which are specified in the arguments.

51. Excel COUNTA Function: It counts all the cells which are not empty. It doesn't matter which type of value is there in a cell, it counts all the cell with values in it.

52. Excel COUNTBLANK Function: It counts all the cells which are empty/blank, but if a cell has a blank space it will not count it.

53. Excel COUNTIF Function: It counts the number of cells which meet a criterion. You need to specify a criterion and a range of cell to check that criterion.

54. Excel COUNTIFS Function: It counts the number of cells which meet criteria. You need to specify criteria and ranges to check those criteria.

55. Excel AVERAGE Function: It returns the average (arithmetic mean) of the arguments. You can refer to a range of cells or insert numbers inside the function..

56. Excel AVERAGEA Function: It returns the average (arithmetic mean) of a group of numbers and text. You can refer to a range of cells or insert numbers inside the function.

57. Excel AVERAGEIF Function: It averages the number of cells which meet a criterion. You need to specify a criterion and a range of cell to check that criterion.

58. Excel AVERAGEIFS Function: It averages the number of cells which meet criteria. You need to specify criteria and ranges to check those criteria.

59. Excel MAX Function: It returns the largest value in a set of values. You can refer to a range or insert values directly into the function as well.

60. Excel MIN Function: It returns the smallest value in a set of values. You can refer to a range or insert values directly into the function as well.

Lookup Functions


61. Excel ADDRESS Function: It returns the address of a cell in a worksheet, given specified row and column numbers.

62. Excel AREAS Function: It returns the number of areas in a given reference. An area can be a range of contiguous cells or a single cell.

63. Excel CHOOSE Function: It returns a value from a list based on position given.

64. Excel COLUMN Function: It returns the column number of the given reference.

65. Excel COLUMNS Function: It returns the number of columns included in the given reference.

66. Excel FORMULATEXT Function: It returns a formula as a string from the referred cell.

67. Excel HLOOKUP Function: It searches for a value in the top row of a table or an array of values, and returns a value in the same column from a row you specify.

68. Excel HYPERLINK Function: It creates a hyperlink that opens a document stored on a network server, an intranet, or the Internet.

69. Excel INDEX Function: It returns a value from a column or a row based on the position of that value in the column or row.

70. Excel INDIRECT Function: It returns the reference specified by a text string. You need to mention the text and reference style.

71. Excel LOOKUP Function: It helps to lookup for a value from a single column or row.

72. Excel MATCH Function: It returns a number representing the position of a cell in an array. In simple words, you get the position of a cell in from a column or row.

73. Excel OFFSET Function: It creates a reference offset from given starting cell using height and width.

74. Excel ROW Function: It returns the row number of the given reference.

75. Excel ROWS Function: It returns the number of rows included in the given reference.

76. Excel TRANSPOSE Function: It switches row into column and column into rows using Ctrl + Shift + Enter.

77. Excel VLOOKUP Function: It looks up for a value in a column and returns the value from the right of the value that you have found.

Financial Functions


78. Excel FV Function: It calculates the future value of an investment which is based on periodic and constant payments and on a constant rate of interest.

79. Excel PMT Function: It calculates loan payment based on fixed monthly payments and constant rate of interest.

80. Excel PV Function: It helps you to determine that an investment is profitable or not. For using PV function, you need a constant interest rate, constant periodic payments.

Information Function


81. Excel CELL Function: It returns some specific information about a cell. You need to refer to a cell and type of information you need.

82. Excel ERROR.TYPE Function: It returns a number if the referred cell has an error. For each type of error, there is a different number it returns.

83. Excel INFO Function: It returns information about the current operating environment. You can select the type of information you need from the function.

84. Excel ISBLANK Function: It verifies a cell and return TRUE if that cell is blank (no value) and FALSE if that cell is not blank.

85. Excel ISERR Function: It verifies a cell and return TRUE if that cell has an error other than #N/A and FALSE if that cell has any other value.

86. Excel ISERROR Function: It verifies a cell and return TRUE if that cell has an error, otherwise it returns a FALSE.

87. Excel ISEVEN Function: It verifies a cell and return TRUE if that cell has an ever, otherwise it returns a FALSE.

88. Excel ISFORMULA Function: It verifies a cell and return TRUE if that cell has a formula, otherwise it returns a FALSE.

89. Excel ISLOGICAL Function: It verifies a cell and return TRUE if that cell has a logical value, otherwise it returns a FALSE.

90. Excel ISNA Function: It verifies a cell and returns TRUE if that cell has and #N/A error, otherwise it returns a FALSE.

91. Excel ISNONTEXT Function: It verifies a cell and return TRUE if that cell has a non-text value, otherwise it returns a FALSE.

92. Excel ISNUMBER Function: It verifies a cell and return TRUE if that cell has a numeric value, otherwise it returns a FALSE.

93. Excel ISODD Function: It verifies a cell and return TRUE if that cell has an odd number, otherwise it returns a FALSE.

94. Excel ISREF Function: It verifies a cell and return TRUE if that cell has a reference, otherwise it returns a FALSE.

95. Excel ISTEXT Function: It verifies a cell and return TRUE if that cell has a text value, otherwise it returns a FALSE.

96. Excel N Function: It converts a logical value into a number. 1 for TRUE and 0 for FALSE.

97. Excel NA Function: It returns an #N/A in the cell where you enter it.

98. Excel SHEET Function: It returns the worksheet number of the reference.

99. Excel SHEETS Function: It returns the number of sheets in a reference.

100. Excel TYPE Function: It returns a number representing a value type. When you refer a cell in this function, it verifies the value and returns a number to present it.

  • Inet Kemp

    Great to have such a summary available

    • Puneet Gogia

      Thanks Inet

  • Rohit Sharma

    Nice to see the whole functions list..would appreciate if you let us know the complete descriptions of all functions.

    • Puneet Gogia

      Hello Rohit,

      List will be updated before next week. Please Subscribe to our newsletter to get the alert for that. You can also download 100 Keyboard Shortcuts, 40 Useful Excel Tips & 40 ready to use macros.

      http://www.excelchamps.com/newsletter.html

  • Gautam Bhardwaj

    very helpful, i really appreciate. keep it up Bro

    • Puneet Gogia

      Thanks Gautam

  • Eanna

    Hi, Can you add the sumproduct function. I want to know how you use it to bring back sums based on 3 or conditions. Thanks eannablack@gmail.com

    • Puneet Gogia

      Hello Eanna,

      Already Added.

      Thanks for your comment. You’ll get your E-book on 30 Apr

  • Torstein S Johnsen

    i would like to Get a good description of the match function and/or the choose function. Thanks Torstein (sejohnse@yahoo.no)

    • Puneet Gogia

      Hey Torstein,

      You will get the both.

      Thanks for words.

      You’ll get your E-book on 30 Apr

  • Brenda G

    Hello again, Puneet (and congratulations on your 1st 1,000 subscribers)! I would like to see the IFERROR function covered. I am just learning about it, so all lessons are greatly appreciated. Thank you for asking!

    • Puneet Gogia

      Thanks Brenda. I will for sure.

      You’ll get your E-book on 30 Apr

  • Diana

    Hi Puneet, I saw someone at work using both the INDEX and MATCH functions. I see that someone has already requested MATCH so if possible, I would like to see INDEX added to the list. Thank you!

    • Puneet Gogia

      Alright Diana. Thanks for your reply.

  • Srinivasan Venkatraman

    Offset plays a vital role

    • Puneet Gogia

      Thanks Srinivasan

  • Angel Gonzalez

    I would really like to have a better understanding of the Indirect with Row and/or Columns function. Could we include these please.

    • Puneet Gogia

      Thanks Angel

  • Asif Hafeez

    I would love others to learn these functions “Indirect, Offset, Match, Index, Vlookup, Mid, Find, Replace, Substitute, Sumif, Sumifs, SumProduct mainly and of course array functions as well.

    • Puneet Gogia

      Thanks Asif.

  • Peter Wilson

    Index or match

    • Puneet Gogia

      Thanks Peter

  • iyke

    OFFSET/MATCH AND ROW/COLUMN

    • Puneet Gogia

      Thanks Iyke.

  • iyke

    Punnet thanks, May I suggest this, an average subscriber fairly uses excel and functions but in isolation. Every one knows match, offset etc what most need is the combination of these functions to generate more potent practical problem solving forces is the next step I will like you to do after these functions in isolation.

    • Puneet Gogia

      Yeah Iyek. I am trying to give two different examples. one is solo & other is combined with some other function.

      • iyke

        Great! Puneet. I am looking forward to this.

  • Mohammed Mubin Sayyed

    OFFSET and exact use of Array with detailed example.

    • Puneet Gogia

      Thanks Mohammed Mubin Sayyed

  • Dan Kordelski

    GETPIVOTDATA, I haven’t nailed that one yet…dan.kordelski@gmaildotcom

    • Puneet Gogia

      Thanks Dan.

  • Heather

    SUBTOTAL – which I find is a powerful function, but often overlooked – particularly with the different function numbers that formula uses.

    • Puneet Gogia

      Fully Agree With You. Thanks

  • Bj Sutton

    HI, is there any way to work out how long there is between 2 times? ie. 09:42 and 10:01 (that is, I want it to show 19 minutes)

    • Puneet Gogia

      Try,
      =TEXT(10:01-09:42,”HH:MM:SS”)

  • rathanak

    I’d love it.but it will be nice if you combine it in a one PDF file 🙂

  • ratanak

    Hi Puneet I think it is great if you combine these formulas in one store in PDF it is really awesome

  • Binaya Karmacharya

    with transpose function can we reverse the data set; like AAA, BBB, CCC transpose to CCC,BBB,AAA. Thanks

  • Ron007

    MS has already created a summary lists of all functions with links to brief descriptions.

    functions (alphabetical) 2016-2007

    https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

    Functions (by category) – 2016 – 2007

    https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

  • Abhishekkumar Jasoriya

    Thanks punnet for providing such a good platform to learn excel

  • Sudhir Kumar

    How can I create a timezone convertor??

  • Sathishkumar Manasa

    very nice useful puneet how i download this excel function book

  • Om

    Hi,
    Please provide the link for Inv. Management System

  • Ryan

    Thanks It is very useful to know which formula where to use. Thanks Puneet Bro