Top 89 Excel Formulas and Functions You Should Know
Microsoft Excel is the go-to tool for working with data. There are probably a handful of people who haven’t used Excel, given its immense popularity. Excel is a widely used software application in industries today, built to generate reports and business insights. Excel supports several in-built applications that make it easier to use.
1. LEN Function
Syntex :- “=LEN()”
Use :- returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function.
2. PROPER Function
Syntex :- “= PROPER ()”
Use :- Excel PROPER function can be used when you want to capitalize the first character of every word. Numbers, special characters, and punctuations are not changed by the PROPER function.
3. LOWER Function
Syntex :- “=LOWER ()”
Use :- This function converts any text string to lowercase.
4. UPPER Function
Syntex :- “= UPPER ()”
Use :- Excel UPPER function can be used when you want to convert all lowercase letters in a text string to uppercase. Numbers, special characters, and punctuations are not changed by the UPPER function.
5. AVERAGE Function
Syntex :- “=AVERAGE()”
Use :- function focuses on calculating the average of the selected range of cell values. As seen from the below example, to find the avg of the total sales, you have to simply type in “AVERAGE(C2, C3, C4)”. It automatically calculates the average, and you can store the result in your desired location.
6. COUNT Function
Syntex :- “=COUNT()”
Use :- counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric.
7. SUBTOTAL Function
Syntex :- “=SUBTOTAL()”
Use :- The SUBTOTAL function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, min, max, min, and others. Let’s have a look at two such examples. Moving ahead, let’s now understand how the subtotal function works.
8. MODULUS Function
Syntex :- “=MODULUS()”
Use :- function works on returning the remainder when a particular number is divided by a divisor. Let’s now have a look at the examples below for better understanding.
9. POWER Function
Syntex :- “=POWER()”
Use :- The function “Power” returns the result of a number raised to a certain power. Let’s have a look at the examples shown below:
10. CEILING Function
Syntex :- “=CEILING()”
Use :- Next, we have the ceiling function. The CEILING function rounds a number up to its nearest multiple of significance.
11. FLOOR Function
Syntex :- “=FLOOR()”
Use :- Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.
12. CONCATENATE Function
Syntex :- “=CONCATENATE()”
Use :- This function merges or joins several text strings into one text string. Given below are the different ways to perform this function.
13. SUBSTITUTE Function
Syntex :- “=SUBSTITUTE()”
Use :- The SUBSTITUTE function replaces the existing text with a new text in a text string. The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”. Next, we are substituting the second 2010 that occurs in the original text in cell A21 with 2016 by typing “=SUBSTITUTE(A21,2010, 2016,2)”. substitute-function
14. TIME Function
Syntex :- “=TIME()”
Use :- The TIME function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.
15. VLOOKUP Function
Syntex :- “=VLOOKUP()”
Use :- Next up in this article is the VLOOKUP function. This stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table. It then returns a value in the same row from a column you specify.
16. HLOOKUP Function
Syntex :- “=HLOOKUP()”
Use :- Similar to VLOOKUP, we have another function called HLOOKUP or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify.
17. IF Formula Function
Syntex :- “=IF Formula()”
Use :- The IF function checks a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.
18. INDEX-MATCH Function
Syntex :- “=INDEX-MATCH()”
Use :- The INDEX-MATCH function is used to return a value in a column to the left. With VLOOKUP, you’re stuck returning an appraisal from a column to the right. Another reason to use index-match instead of VLOOKUP is that VLOOKUP needs more processing power from Excel. This is because it needs to evaluate the entire table array which you’ve selected. With INDEX-MATCH, Excel only has to consider the lookup column and the return column.
19. DATE Function
Syntex :- “=DATE ()”
Use :- Excel DATE function can be used when you want to get the date value using the year, month and, day values as the input arguments. It returns a serial number that represents a specific date in Excel.
20. DATEVALUE Function
Syntex :- “= DATEVALUE ()”
Use :- Excel DATEVALUE function is best suited for situations when a date is stored as text. This function converts the date from text format to a serial number that Excel recognizes as a date.
21. DAY Function
Syntex :- “= DAY ()”
Use :- function can be used when you want to get the day value (ranging between 1 to 31) from a specified date. It returns a value between 0 and 31 depending on the date used as the input.
22. WORKDAY.INTL Function
Syntex :- “= WORKDAY.INTL ()”
Use :- Excel WORKDAY.INTL function can be used when you want to get the date after a given number of working days. In this function, you can specify the weekend to be days other than Saturday and Sunday.
23. WORKDAY Function
Syntex :- “= WORKDAY ()”
Use :- Excel WORKDAY function can be used when you want to get the date after a given number of working days. By default, it takes Saturday and Sunday as the weekend
24. WEEKDAY Function
Syntex :- “= WEEKDAY ()”
Use :- Excel WEEKDAY function can be used to get the day of the week as a number for the specified date. It returns a number between 1 and 7 that represents the corresponding day of the week.
25. TODAY Function
Syntex :- “= TODAY ()”
Use :- Excel TODAY function can be used to get the current date. It returns a serial number that represents the current date.
26. SECOND Function
Syntex :- “= SECOND ()”
Use :- Excel SECOND function can be used want to get the integer value of the seconds from a specified time value. It returns a value between 0 and 59 depending on the time value used as the input.
27. NOW Function
Syntex :- “= NOW ()”
Use :- Excel NOW function can be used to get the current date and time value.
28. HOUR Function
Syntex :- “= HOUR ()”
Use :- Excel HOUR function can be used when you want to get the HOUR integer value from a specified time value. It returns a value between 0 (12:00 A.M.) and 23 (11:00 P.M.) depending on the time value used as the input
29. MINUTE Function
Syntex :- “= MINUTE ()”
Use :- Excel MINUTE function can be used when you want to get the MINUTE integer value from a specified time value. It returns a value between 0 and 59 depending on the time value used as the input.
30. NETWORKDAYS Function
Syntex :- “= NETWORKDAYS ()”
Use :-
31. NETWORKDAYS.INTL Function
Syntex :- “= NETWORKDAYS.INTL ()”
Use :- Excel NETWORKDAYS.INTL function can be used when you want to get the number of working days between two given dates. It does not count the weekends and holidays, both of which can be specified by the user. It also enables you to specify the weekend (for example, you can specify Friday and Saturday as the weekend, or only Sunday as the weekend).
32. DATEDIF Function
Syntex :- “= DATEDIF ()”
Use :- Excel DATEDIF function can be used when you want to calculate the number of years, months, or days between the two specified dates. A good example would be calculating the age.
33. AND Function
Syntex :- “= AND ()”
Use :- Excel AND function can be used when you want to check multiple conditions. It returns TRUE only when all the given conditions are true.
34. FALSE Function
Syntex :- “= FALSE ()”
Use :- Excel FALSE function returns the logical value FALSE. It does not take any input arguments.
35. IF Function
Syntex :- “= IF ()”
Use :- Excel IF Function is best suited for situations where you want to evaluate a condition, and the return a value if it is TRUE and another value if it is FALSE.
36. IFS Function
Syntex :- “= IFS ()”
Use :- Excel IFS Function is best suited for situations where you want to test multiple conditions at once and then return the result based on it. This is helpful as you don’t have to create long nested IF formulas that can get confusing.
37. IFERROR Function
Syntex :- “= IFERROR ()”
Use :- Excel IFERROR function is best-suited to handle formula that evaluates to an error. You can specify a value to show if the formula returns an error.
38. NOT Function
Syntex :- “= NOT ()”
Use :- Excel NOT function can be used when you want to reverse the value of a logical argument (TRUE/FALSE).
39. OR Function
Syntex :- “= OR ()”
Use :- Excel OR function can be used when you want to check multiple conditions. It returns TRUE if any of the given condition is true.
40. TRUE Function
Syntex :- “= TRUE ()”
Use :- Excel TRUE function returns the logical value TRUE. It does not take any input arguments.
41. COLUMN Function
Syntex :- “= COLUMN ()”
Use :- Excel COLUMN function can be used when you want to get the column number of a specified cell.
42. COLUMNS Function
Syntex :- “= COLUMNS ()”
Use :- Excel COLUMNS function can be used when you want to get the number of columns in a specified range or array. It returns a number that represents the total number of columns in the specified range or array.
43. INDEX Function
Syntex :- “= INDEX ()”
Use :- Excel INDEX function can be used when you have the position (row number and column number) of a value in a table, and you want to fetch that value. This is often use with the MATCH function and is a powerful alternative to the VLOOKUP function.
44. INDIRECT Function
Syntex :- “= INDIRECT ()”
Use :- Excel INDIRECT function can be used when you have the references as text and you want to get the values from those references. It returns the reference specified by the text string.
45. MATCH Function
Syntex :- “= MATCH ()”
Use :- Excel MATCH function can be used when you want to get the relative position of a lookup value in a list or an array. It returns a number that represents the position of the lookup value in the array.
46. OFFSET Function
Syntex :- “= OFFSET ()”
Use :- Excel OFFSET function can be used when you want to get a reference which offsets specified number of rows and columns from the starting point. It returns the reference that OFFSET function points to.
47. ROW Function
Syntex :- “= ROW ()”
Use :- Excel ROW Function function can be used when you want to get the row number of a cell reference. For example, =ROW(B4) would return 4, as it is in the fourth row.
48. ROWS Function
Syntex :- “= ROWS ()”
Use :- Excel ROWS Function can be used when you want to get the number of rows in a specified range or array. It returns a number that represents the total number of rows in the specified range or array.
49. VLOOKUP Function
Syntex :- “= VLOOKUP ()”
Use :- Excel VLOOKUP function is best suited for situations when you are looking for a matching data point in a column, and when the matching data point is found, you go to the right in that row and fetch a value from a cell which is a specified number of columns to the right.
50. XLOOKUP Function
Syntex :- “= XLOOKUP ()”
Use :- Excel XLOOKUP function is a new function for Office 365 users and is an enhanced version of the VLOOKUP/HLOOKUP functions. It can be used to lookup and fetch the value in a dataset, and can replace most of what we do with older lookup formulas.
51. FILTER Function
Syntex :- “= FILTER ()”
Use :- Excel FILTER function is a new function for Office 365 users that allows you to quickly filter and extract data based on the given condition (or multiple conditions).
52. INT Function
Syntex :- “= INT ()”
Use :- Excel INT Function can be used when you want to get the integer portion of a number.
53. MOD Function
Syntex :- “= MOD ()”
Use :- Excel MOD function can be used when you want to get the remainder when one number is divided by another. It returns a numerical value that represents the remainder when one number is divided by another.
54. RAND Function
Syntex :- “= RAND ()”
Use :- Excel RAND function can be used when you want to generate evenly distributed random numbers between 0 and 1. It returns a number between 0 and 1
55. RANDBETWEEN Function
Syntex :- “= RANDBETWEEN ()”
Use :- Excel RANDBETWEEN function can be used when you want to generate evenly distributed random numbers between a top and bottom range specified by the user. It returns a number between the top and bottom range specified by the user.
56. ROUND Function
Syntex :- “= ROUND ()”
Use :- Excel ROUND function can be used when you want to return a number rounded to a specified number of digits.
57. SUM Function
Syntex :- “= SUM ()”
Use :- Excel SUM function can be used to add all numbers in a range of cells.
58. SUMIF Function
Syntex :- “= SUMIF ()”
Use :- Excel SUMIF function can be used when you want to add the values in a range if the specified condition is met.
59. SUMIFS Function
Syntex :- “= SUMIFS ()”
Use :- Excel SUMIFS function can be used when you want to add the values in a range if multiple specified criteria are met.
60. SUMPRODUCT Function
Syntex :- “= SUMPRODUCT ()”
Use :- Excel SUMPRODUCT function can be used when you want to first multiply two or more sets to arrays and then get its sum
61. RANK Function
Syntex :- “= RANK ()”
Use :- Excel RANK function can be used when you want to rank a number against a list of numbers. It returns a number that represents the relative rank of the number against the list of numbers.
62. AVERAGEIFS Function
Syntex :- “= AVERAGEIFS ()”
Use :- Excel AVERAGEIFS function can be used when you want to get the average (arithmetic mean) of all the cells in a range that meets multiple criteria.
63. COUNTA Function
Syntex :- “= COUNTA ()”
Use :- Excel COUNTA function can be used when you want to count all the cells in a range that are not empty.
64. COUNTBLANK Function
Syntex :- “= COUNTBLANK ()”
Use :- Excel COUNTBALNK function can be used when you have to count all the empty cells in a range.
65. COUNTIF Function
Syntex :- “= COUNTIF ()”
Use :- Excel COUNTIF function can be used when you want to count the number of cells that meet a specified criterion.
66. COUNTIFS Function
Syntex :- “= COUNTIFS ()”
Use :- Excel COUNTIFS function can be used when you want to count the number of cells that meet a single or multiple criteria.
67. LARGE Function
Syntex :- “= LARGE ()”
Use :- Excel LARGE function can be used to get the Kth largest value from a range of cells or array. For example, you can get the third largest value from a range of cells.
68. MAX Function
Syntex :- “= MAX ()”
Use :- Excel MAX function can be used when you want to get the largest value from a set of values.
69. MIN Function
Syntex :- “= MIN ()”
Use :- Excel MIN function can be used when you want to get the smallest value from a set of values.
70. SMALL Function
Syntex :- “= SMALL ()”
Use :- Excel SMALL function can be used to get the Kth smallest value from a range of cells or array. For example, you can get the third smallestvalue from a range of cells.
71. FIND Function
Syntex :- “= FIND ()”
Use :- Excel FIND function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is case-sensitive.
72. LEFT Function
Syntex :- “= LEFT ()”
Use :- Excel LEFT function can be used to extract text from left of the string. It returns the specified number of characters from the left of the string
73. MID Function
Syntex :- “= MID ()”
Use :- Excel MID function can be used to extract a specified number of characters from a string. It returns the sub-string from a string.
74. REPLACE Function
Syntex :- “= REPLACE ()”
Use :- Excel REPLACE function can be used when you want to replace a part of the text string with another string. It returns a text string where a part of the text has been replaced by the specified string.
75. REPT Function
Syntex :- “= REPT ()”
Use :- Excel REPT function can be used when you want to repeat a specified text a certain number of times.
76. RIGHT Function
Syntex :- “= RIGHT ()”
Use :- The RIGHT function can be used to extract text from the right of the string. It returns the specified number of characters from the right of the string
77. SEARCH Function
Syntex :- “= SEARCH ()”
Use :- Excel SEARCH function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is NOT case-sensitive.
78. TEXT Function
Syntex :- “= TEXT ()”
Use :- Excel TEXT function can be used when you want to convert a number to text format and display it in a specified format.
79. TRIM Function
Syntex :- “= TRIM ()”
Use :-
80. PMT Function
Syntex :- “= PMT ()”
Use :- Excel PMT function helps you calculate the payment you need to make for a loan when you know the total loan amount, interest rate, and the number of constant payments.
81. NPV Function
Syntex :- “= NPV ()”
Use :- Excel NPV function allows you to calculate the Net Present Value of all the cashflows when you know the discount rate
82. VBA TRIM Function
Syntex :- “=VBA TRIM ()”
Use :- VBA TRIM function allows you to remove the leading and trailing spaces from a text string in Excel. It can be a useful VBA function if you want to quickly clean the data.
83. IRR Function
Syntex :- “= IRR ()”
Use :- Excel IRR function allows you to calculate the Internal Rate of Return when you have the cashflows data
84. VBA SPLIT Function
Syntex :- “=VBA SPLIT ()”
Use :- VBA SPLIT function alllows you to split a text string based on the delimiter. For example, if you want to split text based on a comma or tab or colon, you can do that with the SPLIT function.
85. VBA MsgBox Function
Syntex :- “=VBA MsgBox ()”
Use :- VBA MsgBox is a function that displays a dialog box that you can use to inform your users by showing a custom message or get some basic inputs (such as Yes/No or OK/Cancel).
86. VBA INSTR Function
Syntex :- “=VBA INSTR ()”
Use :- VBA InStr function finds the position of a specified substring within the string and returns the first position of its occurrence.
87. VBA UCase Function
Syntex :- “=VBA UCase ()”
Use :- Excel VBA UCASE function takes a string as the input and converts all the lower case characters into upper case.
88. VBA LCase Function
Syntex :- “=VBA LCase ()”
Use :- Excel VBA LCASE function takes a string as the input and converts all the upper case characters into lower case.
89. VBA DIR Function
Syntex :- “=VBA DIR ()”
Use :- Use VBA DIR function when you want to get the name of the file or a folder, using their path name