Excel Functions & Formulas Examples and Videos

Date & Time

EXCEL FUNCTION – DATE

Microsoft Excel “DATEVALUE” is the very useful where dates are available as TEXT and need to convert them to proper DATE format. DATEVALUE function helps to convert the dates to proper DATE format to make the database useful.

EXCEL FUNCTION – WORKDAY

“Workday” as name suggest that it brings the day when you will be working or you are expecting something to be done on a particular date. You can derive all these dates with the help of Excel Function Workday.

EXCEL FUNCTION – WORKDAY.INTL

WORKDAY.INTL function is an advanced version of WORKDAY function with additional advantage of “Custom weekend options”

EXCEL FUNCTION – NETWORKDAYS

“NETWORKDAYS” function is very helpful feature in the Microsoft excel to calculate the working days from a particular period excluding “Saturday and Sundays”. NETWORKDAYS function subtract the Start Day from the End Date provided.

EXCEL FUNCTION – DATEVALUE

EXCEL FUNCTION – DAY, MONTH, YEAR

Microsoft Excel “DAY, MONTH, YEAR Functions” are date related functions helps to extract the Day, Month or Year from a Date. These functions are very helpful and while transforming the existing database and each of them has their unique characteristics.

EXCEL FUNCTION – HOUR, MINUTE, SECOND

Microsoft Excel “HOUR, MINUTE, SECOND Functions” are time related functions helps to extract the Hour, Minute or Second from a complete Time. These functions are very useful while transforming the existing database and each of them has their unique characteristics.

EXCEL FUNCTION – NOW

Microsoft Excel “NOW” function is used to get the current Date and Time. It is very useful function and can be used in many ways. “NOW Function” does not have any argument that makes this easy to apply and implement.

EXCEL FUNCTION – TODAY

Microsoft Excel “TODAY” function is used to get the current Date. It is very useful function and can be used in many ways. “TODAY Function” does not have any argument that makes this easy to apply and implement.

EXCEL FUNCTION – WEEKDAY

WEEKDAY as the word suggest it is “Day count in a week”. WEEKDAY function applies to a Date and returns the output for Day of the week. The output of the function varies from 0 to 7 and by default WEEKDAY function takes “Sunday to Saturday” as a week and counts Sunday as 1st Day till Saturday as 7th Day

Mathematical

EXCEL FUNCTION – SUM

Excel is a mathematical spreadsheet where you can perform multiple calculations with the help of Excel Formulas. These are automated formulas which refreshes automatically once you refresh your data in a given range. Here you should know about “Excel Ranges” before starting to use Excel Formulas.

EXCEL FUNCTION – SUBTOTAL

Below are the Subtotal Formula syntax commonly used in Excel:
=SUBTOTAL(Function_num, Ref1,..)

EXCEL FUNCTION – AVERAGE

AVERAGE function is used to get the average of numbers. Function applies formula i.e. average = Sum of all values / (Divided by) number of items

EXCEL FUNCTION – INT

INT function is used to round down the numeric value to nearest integer. INT Function has one required argument i.e. number.

EXCEL FUNCTION – MOD

MOD function is used to get the remainder of number that is divided by divisor.
MOD Function has two required arguments i.e. number and divisor.

EXCEL FUNCTION – ROUND

ROUND function rounds the number value to nearest digit mentioned in argument.

EXCEL FUNCTION – SUMPRODUCT

SUMPRODUCT function performs multiplication of numbers within arrays and then sum the values

Statistical

EXCEL FUNCTION - RANDBETWEEN

RANDBETWEEN function is used where user needs integer values (not decimal) and between a predefined range.

EXCEL FUNCTION – RAND

Rand Function generates values between 0 to less than 1. We always have output of the Rand function in decimals. The formula of RAND function is very simple because it has no arguments and provides the random output.

EXCEL FUNCTION – MAX

MAX function is used to get the largest number in range or list of values.

EXCEL FUNCTION – MIN

MIN function is used to get the smallest number in range or list of values.

EXCEL FUNCTION – RANK

RANK function performs the Ranking in a range or list of numbers. Function returns the rank position and can assigned as Highest or Lowest value as 1st rank as per order argument.

EXCEL FUNCTION – COUNT

COUNT function is used to get the total count of Number values in range or list.

EXCEL FUNCTION – COUNTA

COUNTA function is used to get the total count of Any-value or Non-Blanks in range.

EXCEL FUNCTION – COUNTBLANK

COUNTBLANK function is used to get the total count of Blank or Empty cell in range.
COUNTBLANK Function has one required argument i.e. range.

EXCEL FUNCTION – LARGE

LARGE function is used to get the Largest k-th value from the range.
LARGE Function has two required arguments i.e. array, and k

EXCEL FUNCTION – SMALL

SMALL function is used to get the Smallest k-th value from the range.
SMALL Function has two required arguments i.e. array, and k

Text

EXCEL FUNCTION REPLACE

REPLACE Function has argument four arguments i.e. old_text, start_num, num_chars and new_text. We need to give the cell references and appropriate values in those arguments

EXCEL FUNCTION – CONCATENATE

CONCATENATE function is used for combining two or more Microsoft Excel strings into one. The output of the function returns as a combined string in new cell.

EXCEL FUNCTION – LEFT

LEFT function is used for extracting the “Left Most” characters from the available string. The output of the function returns the extracted characters in new cell.

EXCEL FUNCTION – MID

MID function is used for extracting the mid characters from the available string. The output of the function returns the extracted characters in new cell.

EXCEL FUNCTION – RIGHT

RIGHT function is used for extracting the “Right Most” characters from the available string. The output of the function returns the extracted characters in new cell.

EXCEL FUNCTION – LEN

LEN function is used for counting number of characters in available string. The output of the function returns the count in new cell.

EXCEL FUNCTION – UPPER

UPPER function is used for changing/converting the text/string to UPPER case. The output of the function returns value in new cell.

EXCEL FUNCTION – PROPER

PROPER function is used for changing the format of any text or string to PROPER or SENTENCE Case.

EXCEL FUNCTION – LOWER

LOWER function is used for changing the format of any text or string to LOWER Case.

EXCEL FUNCTION – FIND

FIND function is used to locate the position of text, or character in an available string.

EXCEL FUNCTION – SEARCH

SEARCH function is used to find “position of character or text” in an available cell.

EXCEL FUNCTION – SUBSTITUTE

SUBSTITUTE function is used to substitute the existing old text to new text.

EXCEL FUNCTION – REPT

REPT function is used to repeat the text or cell reference to multiple times. Function has two “Required” arguments i.e. text and number times

EXCEL FUNCTION – TEXT

TEXT function is used to change the formatting or appearances of the text. There are various types of formatting available and can be applied as per the requirement.

EXCEL FUNCTION – TRIM

TRIM function is used to remove the additional spaces (i.e. spaces before/after/between the words) except for single space between words.

Lookup

EXCEL FUNCTION - VLOOKUP

VLOOKUP is a vertical lookup which helps the user to extract the values from other columns (leftmost) basis on matching column string.

EXCEL FUNCTION – COLUMN

COLUMN function is used to get the column reference number of the excel worksheet.

EXCEL FUNCTION – COLUMNS

COLUMNS function is used to get the total count of columns in an array or in cells range for excel worksheet. COLUMNS Function has only one argument i.e. array.

EXCEL FUNCTION – INDEX

INDEX function is used to get the value from a cell range or table, function returns the value from a table where row and column intersect with each other.

EXCEL FUNCTION – INDIRECT

INDIRECT function is used to convert the text/string into cell reference. Function provides output as the value of that cell reference.

EXCEL FUNCTION – ROW

ROW function is used to get the row reference number of the excel worksheet.

EXCEL FUNCTION – ROWS

ROWS function is used to get the total count of rows in an array or in cells range in an excel worksheet.

EXCEL FUNCTION – MATCH

MATCH function performs lookup for a value in a range and returns its position sequence number as output.

Logical

EXCEL FUNCTION – COUNTIF

COUNTIF Excel Function is also one of the most used function in excel. This helps the user to calculate the number of counts based on single logic given by the user. You can use this function for multiple purposes like:

EXCEL FUNCTION – AVERAGEIF

AVERAGEIF Function has two required arguments i.e. range, criteria and optional argument i.e. `{`average_range`}`.

EXCEL FUNCTION – AVERAGEIFS

AVERAGEIFS Function has required arguments i.e. average_range, criteria_range1, criteria1 and Optional arguments i.e. `{`criteria_range2, criteria2`}`… We can place add more than one range to include multiple criteria or conditions.

EXCEL FUNCTION – SUMIF

SUMIF Function has two required arguments i.e. range, criteria and optional argument i.e. `{`sum_range`}`.

EXCEL FUNCTION – COUNTIFS

COUNTIFS Function has one required arguments i.e. criteria_range1, criteria1 and Optional arguments i.e. `{`criteria_range2, criteria2`}`…

EXCEL FUNCTION – SUMIFS

SUMIFS Function has required arguments i.e. sum_range, criteria_range1, criteria1 and Optional arguments i.e. `{`criteria_range2, criteria2`}`… We can place add more than one range to include multiple criteria or conditions.

EXCEL FUNCTION – IF

“IF Function” is categorized as Logical Function and provides output as per the logical conditions provided. Function returns the value if logical function is “True” or “False”. IF function is quite often used function in Microsoft excel and helps to determine the decisions.

EXCEL FUNCTION – IFS

“IFS Function” provides the output if applicable conditions are “TRUE”.
Function statement consist of all the conditions which are “TRUE”, and it is necessary to provide the conditions in correct sequence or order (i.e. “Descending order” (if applicable)) to avoid the function error or incorrect output.

EXCEL FUNCTION – ISBLANK

Microsoft Excel “ISBLANK Function” is a Logical Function and it is used to check if cell in question is “BLANK OR NON-BLANK”. “ISBLANK Function” is used as a test to validate if cell contains any value or not.

EXCEL FUNCTION – ISERROR

Microsoft Excel “ISERROR Function” is a Logical Function and it is used to check if cell contains any “ERROR”. “ISERROR Function” is used as a test to validate if cell contains any Error or not.

EXCEL FUNCTION – AND, OR, NOT

Microsoft Excel “AND, OR, NOT Functions” are the logical functions and helps to validate the statement if said is “TRUE” or “FALSE”.