Shares

# 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.

And, for this, I have compiled a list of 101 Excel functions which you can use everyday work to speed up your all the calculations.

This is a comprehensive list in which you will find easy to understand examples and for each example, you can also download sample files to try it yourself.

I have split this list into categories so that you can pick them according to your need.

## Text Functions

1. Excel LEFT Function: Get text from the left side of a cell.

2. Excel RIGHT Function: Get text from the right side of a cell.

3. Excel MID Function: ​Get text from a specific position from a text string.

4. Excel LOWER Function: ​Convert a text into lower case.

5. Excel UPPER Function: ​Convert a text into upper case.

6. Excel PROPER Function: ​Convert a text into proper case.

7. Excel REPT Function: ​Repeat a text a number of times.

8. Excel LEN Function: ​Get the numbers of characters in a cell.

9. Excel FIND Function: ​Find a text string from another text string (Case Sensitive).

10. Excel SEARCH Function: ​Search a text string from another text string (Non-Case Sensitive).

J. ​Star Rating

## Date Functions

11. Excel DATE Function: Create a valid date.

12. Excel DATEDIF Function: Calculate interval between two dates.

13. Excel DATEVALUE Function: Create a valid date from a text.

14. Excel EDATE Function: Return the same date in future or past.

15. Excel DAY Function: Return day number from a date.

16. Excel DAYS Function: Return number of days between two dates.

17. Excel TODAY Function: Return current date.

18. Excel MONTH Function: Return month’s number from a date.

19. Excel EOMONTH Function: Return the last date of the month in future or past.

20. Excel YEAR Function: Return year’s number from a date.

21. Excel WEEKDAY Function: Return weekday of a given date.

22. Excel WEEKNUM Function: Return week number of a given date.

23. Excel NETWORKDAYS Function: Return the number of working days between two dates.

24. Excel NETWORKDAYS.INTL Function: Return the work days between two dates.

## Time Functions

25. Excel TIME Function: Create a valid time.

26. Excel TIMEVALUE Function: Create a valid time from atext.

27. Excel HOUR Function: Return hours from a time value.

28. Excel MINUTE Function: Return minute from a time value.

29. Excel SECOND Function: Return seconds from a time value.

30. Excel NOW Function: Return current date & time.

## Logical Functions

31. Excel IF Function: Perform a specific task if a condition is true or false.

32. Excel IFERROR Function: Evaluate a value for error & return a specific value if an error occurs.

33. Excel IFNA Function: Evaluate value for #N/A error & return a specific value if an error occurs.

34. Excel OR Function: Test two or more conditions differently.

35. Excel AND Function: Test two or more conditions jointly.

36. Excel NOT Function: Convert TRUE into FALSE & FALSE into TRUE.

37. Excel FALSE Function: Return logical FALSE.

38. Excel TRUE Function: Return logical TRUE.

A. Rank IF

B. IF OR

C. IF AND

D. ​MAXIF

## Maths Functions

39. Excel SUM Function: Return the sum of numeric values.

40. Excel SUMIF Function: Return the sum of the numeric values of which meet a condition.

41. Excel SUMIFS Function: Return the sum of the numeric values which meet multiple conditions.

42. Excel SUMPRODUCT Function: Multiply andSUM arrays.

43. Excel ABS Function: Convert a number into an absolute number.

44. Excel EVEN Function: Round a number to the nearest even number.

45. Excel INT Function: Round down a number to the nearest integer.

46. Excel MOD Function: Return remainder after dividing two numbers.

47. Excel MROUND Function: Round to the nearest multiple of a number.

48. Excel TRUNC Function: Remove fractional part of a number.

49. Excel RAND Function: Generate random numbers.

### Related Tips

F. ​SUMPRODUCT IF

## Statistical Functions

50. Excel COUNT Function: Return the count cells which have numbers.

51. Excel COUNTA Function: Return the count of cells which are non-blank.

52. Excel COUNTBLANK Function: Return the count of cells which are blank.

53. Excel COUNTIF Function: Return the count of cells which meet criteria.

54. Excel COUNTIFS Function: Return the count of cells which meet multiple criteria.

55. Excel AVERAGE Function: Return average of numbers.

56. Excel AVERAGEA Function: Return the average of a group of numbers and text.

57. Excel AVERAGEIF Function: Return the average of numbers which meet criteria.

58. Excel AVERAGEIFS Function: Return the average of numbers which meet multiple criteria.

59. Excel MAX Function: Return largest values from a given set of values.

60. Excel MIN Function: Return smallest values from a given set of values.

## Lookup Functions

61. Excel ADDRESS Function: Return a number of address of a reference.

62. Excel AREAS Function: Return a number of areas in a reference.

63. Excel CHOOSE Function: Return a value from a list based on position.

64. Excel COLUMN Function: Return the column number of the reference.

65. Excel COLUMNS Function: Return the number of columns in a range.

66. Excel FORMULATEXT Function: Return formula from a cell as a text.

67. Excel HLOOKUP Function: Lookup for values from a row.

69. Excel INDEX Function: Return a value from a list or table based on location.

70. Excel INDIRECT Function: Create a cell reference from a text.

71. Excel LOOKUP Function: Lookup for a value in a column range.

72. Excel MATCH Function: Return a number representing the position of a cell in an array.

73. Excel OFFSET Function: Create a reference offset from given starting cell.

74. Excel ROW Function: Return the row number of the reference.

75. Excel ROWS Function: Return the number of rows in a range.

76. Excel TRANSPOSE Function: Return the average of numbers.

77. Excel VLOOKUP Function: Lookup for values from a column.

## Financial Functions

78. Excel FV Function: Calculate the future value of an investment.

79. Excel PMT Function: Calculate the periodic payment for a loan.

80. Excel PV Function: Calculate the present value of an investment.

## Information Function

81. Excel CELL Function: Get cell information

82. Excel ERROR.TYPE Function: Return a number for every error.

83. Excel INFO Function: Return information about the current operating environment.

84. Excel ISBLANK Function: Check if a cell is blank.

85. Excel ISERR Function: Check if there is any error in the cell other than #N/A.

86. Excel ISERROR Function: Check if a cell has an error.

87. Excel ISEVEN Function: Check if a cell has an even number.

88. Excel ISFORMULA Function: Check if a cell has a has a formula.

89. Excel ISLOGICAL Function: Check if a cell has a has a logical value.

90. Excel ISNA Function: Check if a cell has a has a #N/A value.

91. Excel ISNONTEXT Function: Check if a cell has a has a non-text value.

92. Excel ISNUMBER Function: Check if a cell has a has a number.

93. Excel ISODD Function: Check if a cell has a has an odd number.

94. Excel ISREF Function: Check if a cell has a has a reference.

95. Excel ISTEXT Function: Check if a cell has a has a text value.

96. Excel N Function: Convert a logical value into a number.

97. Excel NA Function: Generate a #N/A error value.

98. Excel SHEET Function: Get the sheet number of a worksheet.

99. Excel SHEETS Function: Get the numbers of sheets in a reference.

100. Excel TYPE Function: Get check the type of value from a cell.

• 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.

• 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,

• 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

• 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