Here, you will find a detailed tutorial on 100+ Excel Functions & VBA Functions. Each Excel function is covered in detail with Examples and Videos.
This Tutorial Covers:
ToggleExcel Functions – Date and Time
Excel Function | Description |
---|---|
Excel DATE Function |
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. |
Excel DATEVALUE Function |
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. |
Excel DAY Function |
Excel DAY 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. |
Excel HOUR Function |
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 |
Excel MINUTE Function |
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. |
Excel NETWORKDAYS Function | Excel NETWORKDAYS function can be used when you want to get the number of working days between two given dates. It does not count the weekends between the specified dates (by default the weekend is Saturday and Sunday). It can also exclude any specified holidays. |
Excel NETWORKDAYS.INTL Function |
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). |
Excel NOW Function | Excel NOW function can be used to get the current date and time value. |
Excel SECOND Function |
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. |
Excel TODAY Function | Excel TODAY function can be used to get the current date. It returns a serial number that represents the current date. |
Excel WEEKDAY Function |
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. |
Excel WORKDAY Function |
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 |
Excel WORKDAY.INTL Function |
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. |
Excel DATEDIF Function |
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. |
Excel Functions – Logical
Excel Function | Description |
---|---|
Excel AND Function |
Excel AND function can be used when you want to check multiple conditions. It returns TRUE only when all the given conditions are true. |
Excel FALSE Function |
Excel FALSE function returns the logical value FALSE. It does not take any input arguments. |
Excel IF Function |
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. |
Excel IFS Function |
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. |
Excel IFERROR Function | 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. |
Excel NOT Function |
Excel NOT function can be used when you want to reverse the value of a logical argument (TRUE/FALSE). |
Excel OR Function | Excel OR function can be used when you want to check multiple conditions. It returns TRUE if any of the given condition is true. |
Excel TRUE Function |
Excel TRUE function returns the logical value TRUE. It does not take any input arguments. |
Excel LAMBDA Function | Excel LAMBDA function allows you to create and use your own functions right within the worksheet. |
Excel SWITCH Function | Excel SWITCH function evaluates an expression (which returns a value) and matches this value with a list of values to return the corresponding result from the first matching value. |
Excel Functions – Lookup & Reference
Excel Function | Description |
---|---|
Excel COLUMN Function |
Excel COLUMN function can be used when you want to get the column number of a specified cell. |
Excel COLUMNS Function |
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. |
Excel HLOOKUP Function |
Excel HLOOKUP function is best suited for situations when you are looking for a matching data point in a row, and when the matching data point is found, you go down that column and fetch a value from a cell which is specified a number of rows below the top row. |
Excel INDEX Function |
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. |
Excel INDIRECT Function |
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. |
Excel MATCH Function |
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. |
Excel OFFSET Function |
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. |
Excel ROW Function |
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. |
Excel ROWS Function |
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. |
Excel VLOOKUP Function |
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. |
Excel XLOOKUP Function |
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. |
Excel FILTER Function |
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). |
Excel TAKE Function | TAKE function is a new Excel function that allows you to extract the given number of contiguous rows or columns from a dataset. It’s mostly used in other functions such as FILTER and SORT. |
Excel DROP Function | DROP function allows you to extract the given number of contiguous rows or columns from a dataset after dropping the specified number of rows or columns (or both). |
Excel Functions – Math
Excel Function | Description |
---|---|
Excel INT Function |
Excel INT Function can be used when you want to get the integer portion of a number. |
Excel MOD Function |
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. |
Excel RAND Function |
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 |
Excel RANDBETWEEN Function |
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. |
Excel ROUND Function |
Excel ROUND function can be used when you want to return a number rounded to a specified number of digits. |
Excel SUM Function | Excel SUM function can be used to add all numbers in a range of cells. |
Excel SUMIF Function |
Excel SUMIF function can be used when you want to add the values in a range if the specified condition is met. |
Excel SUMIFS Function |
Excel SUMIFS function can be used when you want to add the values in a range if multiple specified criteria are met. |
Excel SUMPRODUCT Function |
Excel SUMPRODUCT function can be used when you want to first multiply two or more sets to arrays and then get its sum |
Excel LN Function | LN Function in Excel Is used to calculate the natural log of a number |
Also read: VLOOKUP vs XLOOKUP Function – What’s the Difference?
Excel Functions – Statistics
Excel Function | Description |
---|---|
Excel RANK Function |
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. |
Excel AVERAGE Function |
Excel AVERAGE function can be used when you want to get the average (arithmetic mean) of the specified arguments. |
Excel AVERAGEIF Function |
Excel AVERAGEIF function can be used when you want to get the average (arithmetic mean) of all the values in a range of cells that meet a given criteria. |
Excel AVERAGEIFS Function |
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. |
Excel COUNT Function | Excel COUNT function can be used to count the number of cells that contain numbers. |
Excel COUNTA Function |
Excel COUNTA function can be used when you want to count all the cells in a range that are not empty. |
Excel COUNTBLANK Function |
Excel COUNTBALNK function can be used when you have to count all the empty cells in a range. |
Excel COUNTIF Function |
Excel COUNTIF function can be used when you want to count the number of cells that meet a specified criterion. |
Excel COUNTIFS Function |
Excel COUNTIFS function can be used when you want to count the number of cells that meet a single or multiple criteria. |
Excel LARGE Function |
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. |
Excel MAX Function |
Excel MAX function can be used when you want to get the largest value from a set of values. |
Excel MIN Function |
Excel MIN function can be used when you want to get the smallest value from a set of values. |
Excel SMALL Function | Excel SMALL function can be used to get the Kth smallest value from a range of cells or arrays. For example, you can get the third smallest value from a range of cells. |
Excel Functions – Text Functions
Excel Function | Description |
---|---|
Excel CONCATENATE Function |
Excel CONCATENATE function can be used when you want to join 2 or more characters or strings. It can be used to join text, numbers, cell references, or a combination of these. |
Excel FIND Function |
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. |
Excel LEFT Function | 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 |
Excel LEN Function |
Excel LEN function can be used when you want to get the total number of characters in a specified string. This is useful when you want to know the length of a string in a cell. |
Excel LOWER Function |
Excel LOWER function can be used when you want to convert all uppercase letter in a text string to lowercase. Numbers, special characters, and punctuations are not changed by the LOWER function. |
Excel MID Function |
Excel MID function can be used to extract a specified number of characters from a string. It returns the sub-string from a string. |
Excel PROPER Function |
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. |
Excel REPLACE Function |
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. |
Excel REPT Function | Excel REPT function can be used when you want to repeat a specified text a certain number of times. |
Excel RIGHT Function | 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 |
Excel SEARCH Function |
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. |
Excel SUBSTITUTE Function | Excel SUBSTITUTE function can be used when you want to substitute text with new specified text in a string. It returns a text string where an old text has been substituted by the new one. |
Excel TEXT Function | Excel TEXT function can be used when you want to convert a number to text format and display it in a specified format. |
Excel TRIM Function | Excel TRIM function can be used when you want to remove leading, trailing, and double spaces in Excel. |
Excel UPPER Function | 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. |
Excel REGEX Functions | Excel has three new regex functions (REGEXEXTRACT, REGEXREPLACE, and REGEXTEST), which can be used to identify patterns and manipulate text strings. |
Excel Functions – Info
Excel Function | Description |
---|---|
Excel ISBLANK Function
| Excel IS function returns TRUE when specified condition is TRUE. For example, ISNA would return TRUE if the cell has a #N/A! error. |
Excel Functions – Financial
Excel Function | Description |
---|---|
Excel PMT Function | 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. |
Excel NPV Function | Excel NPV function allows you to calculate the Net Present Value of all the cashflows when you know the discount rate |
Excel IRR Function | Excel IRR function allows you to calculate the Internal Rate of Return when you have the cashflows data |
VBA Functions
Excel Function | Use the VBA DIR function when you want to get the name of the file or a folder, using their path name |
---|---|
VBA TRIM Function |
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. |
VBA SPLIT Function |
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. |
VBA MsgBox Function |
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). |
VBA INSTR Function |
VBA InStr function finds the position of a specified substring within the string and returns the first position of its occurrence. |
VBA UCase Function |
Excel VBA UCASE function takes a string as the input and converts all the lower case characters into upper case. |
VBA LCase Function |
Excel VBA LCASE function takes a string as the input and converts all the upper case characters into lower case. |
VBA DIR Function | Use the VBA DIR function when you want to get the name of the file or a folder using their path name |
Useful Excel Resources:
- 100+ Excel Interview Questions
- 200+ Excel Keyboard Shortcuts
- Free Excel Templates
- How to Insert Symbols in Excel
- How to Describe Excel Skills in a Resume?
- Free Online Excel Training
- Best Excel Books
- Excel Formulas Not Working: Possible Reasons and How to FIX IT!
- 20 Advanced Excel Functions and Formulas (for Excel Pros)
- Formula vs Function in Excel – What’s the Difference?
1 thought on “100+ Excel Functions (explained with Examples and Videos)”
Thank you, the explanation is easy to understand. I also wrote a similar article on Excelenesia.com