## Tapping into Power of Excel Functions

Excel has many built-in formulas that can be very basic or extremely complex called as ** worksheet functions**.

A formula is an expression that includes specific values and cell addresses which returns a particular result in the cell or a position specified in the syntax. These functions are useful because they are swift and straightforward for performing a spreadsheet task.

Below you can find the important functions of **seven** main categories:

## Text Functions

# UPPER

The Excel UPPER function converts all letters in the text to uppercase. E.g., “=UPPER(“Hello World”)” returns ” HELLO WORLD”.

# CONCAT

The CONCAT function display text by combining multiple ranges, array and/or strings. I.e., =CONCAT(“Mike “, “Tyson”) returns “Mike Tyson”, note space after “Mike ”.

# CONCATENATE

The CONCATENATE function display text by merging multiple text strings. I.e., combine separate texts, =CONCATENATE(“Indian “, “Ocean”) returns “Indian Ocean”.

# LEN

The Excel LEN function counts a number of characters of a given text string excluding number formatting. For example, LEN(“Excel”) returns “5”.

# DOLLAR

The DOLLAR function display a number with a dollar currency. I.e. =DOLLAR(500) returns $500 as a currency but in a text format.

# REPT

The Excel REPT function repeat a text for a specified number of times. For example, =REPT(“M”,3) returns “MMM”.

# CODE

The CODE function displays a unique number of each valid character based on the underlying set standard of ANSI. I.e, =CODE(B8) returns ! (exclamation mark).

# SEARCH

The SEARCH function finds the position of a specified character or sub-string within a text string. I.e., =SEARCH(“ring”,”String”,1) return “3”, “ring” begins at the third character of “String”.

# EXACT

The Excel EXACT function display (True or False) after matching two text strings if they are exactly same. Also identifies case-sensitive differences between two text.

# REPLACE

The REPLACE function substitute part of a text string with a new text string. I.e., =REPLACE(“Michael Jackson”,9,6,”Jorda”) returns “Michael Jordan”.

# VALUE

The VALUE function converts valid numbers with various number formatting (number, date, $ etc.) into a code number. E.g. “=VALUE(“31-12-2017”)” returns “43100”.

# CLEAN

The CLEAN function delete the non-printable characters from 1 to 31 of the ANSI character set, the “garbage” characters that often appear after importing texts.

# LEFT

The LEFT function display number of characters specified in the argument of text starting from the left. I.e., LEFT(“Excel LEFT function”,5) returns “Excel”.

# FIXED

The FIXED function displays a number in a text format after rounding the specified number of decimal places with or without commas.

# PROPER

The PROPER function capitalizes the first letter of each word in a given alphabetical text. I.e., =PROPER(“michael jackson”) returns “Michael Jackson”.

# TEXT

The TEXT function allows converting a value to a specific format based on the given format codes. E.g. =TEXT(31-12-2018,”MM/DD/YYYY”) return ” 12/31/2018″.

# LOWER

The Excel LOWER function converts all letters in the text to lowercase. For example, “LOWer” returns “lower”.

# SUBSTITUTE

The SUBSTITUTE function replace the specific text in a text string that occurs in a specific location. i.e, =SUBSTITUTE(“Butter”,”t”,”l”,2) return “Butler”.

# TRIM

The TRIM function removes excess spaces from a text string except for one space between each word. E.g. =TRIM(“Hello World”) [3 spaces] return “Hello World”.

# CHAR

The CHAR function display characters based on the underlying ANSI numeric codes. I.e. in Window, = CHAR(33) in Excel returns “!” (exclamation mark).

# RIGHT

The Excel RIGHT function extract rightmost characters based on the specified number. I.e., =RIGHT(“String”,4) returns “ring”.

# BAHTTEXT

The Excel BAHTTEXT function converts a number to Thai currency with a suffix of “Baht”. I.e., to translate $5.50 in cell B7 =BAHTTEXT(B7) returns “ห้าบาทห้าสิบสตางค์”.

# FIND

The FIND function returns the position of occurrence of a given character(s) in a string. i.e. to find the 2nd occurrence of 8, =FIND(8,128128,4) returns 6.

# TEXTJOIN

The TEXTJOIN function allows concatenating (join) the text from multiple ranges with a delimiter between each text value. E.g., =TEXTJOIN(” ,”,TRUE,”Blue”,”Green”,”White”,”Black”) return ” Blue ,Green ,White ,Black”.

# MID

The MID function extracts a specified number of characters from the middle of a given text string. For example, =MID(“number”,1,3) returns “num”.

## Lookup Functions

# ROWS

The ROWS function returns the number of rows of a cell range or array formula, =ROWS(E10:F100) returns 91 (91 rows in the cell range i.e. 10 to 100).

# ADDRESS

The ADDRESS function returns cell address from given row and column numbers of a worksheet. For example, if you pass ADDRESS(5,6) the function returns $F$5.

# HLOOKUP

The HLOOKUP function extract data in a horizontal pattern which means heading in 1st row and value begins from 2nd row, and retrieve data from left to right.

# COLUMN

The COLUMN function returns the column number of a first reference cell. For example, =COLUMN(E10:F10) returns 5 (5th column, a sequence of E in alphabets).

# CHOOSE

The CHOOSE function returns specific information from a list of data points, =CHOOSE(4,”Railways”,”Roads”,”Airways”,”Waterways”) returns Waterways.

# AREAS

The AREAS function returns a number of areas or region. I.e., =AREAS((B2:C4,D8:F9)) returns 2 (number of areas), include extra sets of parentheses.

# LOOKUP

The LOOKUP function search in single row or column for a given value and return value from the same position or, next row or column.

# ROW

The ROW function returns the row number of a reference cell. For example, =ROW(E9:E10) returns 9 (9th from top to bottom, only the first cell).

# COLUMNS

The COLUMNS function returns a count of all columns of a cell range or array formula, =COLUMNS(E10:F100) returns 2 (two columns E & F).

# VLOOKUP

The VLOOKUP function extract data in a vertical pattern which means heading in 1st column and value begins from the 2nd column and retrieve data from left to right.

# HYPERLINK

The HYPERLINK function creates a shortcut or clickable link of a given location with a “friendly name”. User Click that initiates a call for action.

# INDIRECT

The INDIRECT function creates a cell reference from a given text string same as the name of a worksheet and extracts value from a specific cell.

# TRANSPOSE

The TRANSPOSE function “switch data direction” or the range of data from vertical format (row) to horizontal format (column), or vice versa.

# INDEX

The INDEX function returns a cell value from one or multiple data range at a given row‐and‐column intersection and gets a specific value from a data set.

# FORMULATEXT

The FORMULATEXT function displays the syntax of a reference cell as a string, cell B5 contains =5+2 return 7, = FORMULATEXT(B5) returns a text value “=5+2”.

# OFFSET

The OFFSET function returns one or multiple data points of a data set by giving a reference point with a specified number of rows and columns to offset.

# MATCH

The MATCH function finds row or column of a given value containing in a data set, cell B3 in a list contains ‘Feb’ =MATCH(B3,B1:B10,0) returns 2 (3rd row).

## Information Functions

# NA

The NA function returns with #N/A error. A #N/A error means “no value is available”, used to highlight empty cells or any missing information of data set.

# TYPE

The Excel TYPE function returns data type of reference cell or value of an argument. Example, =TYPE(“ABC”) return data type 2 (Text data format).

# ISERR

The ISERR function checks if a specified cell has error value ‘except #N/A’ and returns “TRUE” only if a cell has an error. For example: “=ISERR(#DIV/0!)” return TRUE.

# CELL

The CELL function returns with information of a cell about the formatting, location, or contents. The type of information is based on the info_type parameter.

# ISLOGICAL

The ISLOGICAL function check if a specified cell has Boolean value and returns “TRUE” when a cell has a Boolean data type, and “FALSE” if it does not.

# ISFORMULA

The ISFORMULA function check if a specified cell is containing a formula and returns “TRUE” if it contains, I.e., cell C6 =2+2, “=ISFORMULA(C6)” returns TRUE.

# ISREF

The ISREF function checks if a cell has a correct reference, returns “TRUE” when a cell has a valid reference, and “FALSE” for reference error or not linked.

# INFO

The INFO function get specifications about the current operating environment, include the operating system, Excel version, num. of active worksheets.. so on.

# ISTEXT

The Excel ISTEXT function check if a specified cell has a value in text format and returns “TRUE” when a cell contains a text value, and “FALSE” if not.

# SHEET

The SHEET function returns a sequence number of an Excel worksheet in a workbook of a given reference, worksheet, range name or table name.

# SHEETS

The SHEETS function finds the total number of sheets in a workbook. In the case of no argument the function returns total count of active workbook.

# ISERROR

The Excel ISERROR function check if a specified cell has error value and returns “TRUE” when a cell has an error, and “FALSE” it does not.

# ISBLANK

The ISBLANK function checks if a cell is blank and returns “TRUE” when it is, I.e., “=ISBLANK(“Text”)” return FALSE as the cell contains some value “Text”.

# ERROR.TYPE

The Excel ERROR.TYPE function returns an integer code of the corresponding error type or returns the #N/A if no error exists.

# ISEVEN

The ISEVEN function checks if a cell has even numeric value and returns “TRUE” for even numeric value, & “FALSE” for an odd number.

# N

The N function returns a code number by converting specific reference based on a set of rule. I.e., =N(B6) will return 43093, B6 refers to ’24-12-2017′.

# ISODD

The ISODD function checks if a specified cell has an odd numeric value and returns “TRUE” when a cell has an odd numeric value and “FALSE” for an even number.

# ISNUMBER

The ISNUMBER function checks if a specified cell has a numeric value and returns “TRUE” if a cell has a numeric value, and “FALSE” when a cell does not.

# ISNONTEXT

The ISNONTEXT function checks if a specified cell has non-text value and returns “TRUE” when a cell has a non-text, and “FALSE” it contains a text value.

# ISNA

The Excel ISNA function checks if a specified cell has #N/A error and returns “TRUE” when a cell has a #N/A error, and “FALSE” it does not.

## Logical Functions

# NOT

The NOT function reverse a boolean result (TRUE or FALSE) of logical criteria in the argument. For example, =NOT(5=5) return FALSE (opposite the result).

# SWITCH

The Excel SWITCH function lookup value in a list of multiple labels and return the corresponding first match otherwise default clause or #N/A return.

# IFNA

The IFNA function evaluate given criterion (value) in case the return value is a #N/A error then it substitutes with a different text string (value_if_na).

# AND

The AND function is a logical operator that join two or more criteria i.e., if A2 is 75, =AND(A2>1,A2<50), return “FALSE” as none of the condition matches.

# IFS

Rational Check multiple criteria and return the first match. Description The Excel IFS function evaluate multiple criteria at once and return the first TRUE criteria

# OR

The OR function is a logical operator that returns TRUE if the condition matches i.e. =OR(A1=10, A1=100) check cell A1 contains either 10 or 100.

# TRUE

The TRUE function returns the logical value ‘TRUE’. The formula is useful to enter the TRUE directly into the cell or added as part of criteria in a formula.

# IFERROR

How to change the Excel formula result like #DIV, #VALUE!, #REF!, #NAME?, #NUM! by another value like string, blank, and zero with the IFERROR function.

# FALSE

The FALSE function returns the logical value FALSE. The formula is useful to enter the FALSE directly into the cell or added as part of criteria.

# IF

The IF function is a handy logical operator that return value based on a valid test of criteria, if it matches (TRUE) and another value if it does not.

# XOR

The XOR function returns TRUE when only one of the logic or condition is true. It returns TRUE if only one is right, not ‘at least one’ like OR function.

## Date & Time Functions

# DATEVALUE

The DATEVALUE function translates a date into a valid date serial number. I.e; if date is in text format, use =DATEVALUE(“10-6-17”) that returns 42896.

# DAY

The DAY function returns an integer value that represents the day element of a date. I.e.; to extract day component, use =DAY(“08-08-2017”) that returns ‘8’.

# MINUTE

The MINUTE function returns the minute component of a time value. For example, with a time of 6:48 PM or text, =MINUTE(time) will return ’48’.

# WEEKNUM

The WEEKNUM function finds the week number of date and week start day, i.e., week starting from Monday (05-06-2018-C7), use =WEEKNUM(B7,2) that returns ‘23’.

# EDATE

The EDATE function returns a future or past month from a given date. I.e., =EDATE(“31-03-2016”,-1) returns a leap date 29-02-2016 as 2016 is a leap year.

# DAYS360

The Excel DAYS360 function result assumes that a Year is of 360 days, 30 days per month.

use =DAYS360(“01/05/2017”, “01/08/2018”) that returns ‘450’.

# WEEKDAY

The WEEKDAY function returns an integer between 1 and 7 the corresponds specific day of a week. Example; (07-04-2018: Cell-B6), use =WEEKDAY(B6,2) returns ‘6’.

# WORKDAYS.INTL

WORKDAYS.INTL returns a working day before and after a date, excl. custom Weekend days i.e. to find a working day 2 days after a date (01-11-2018-B7) excluding Friday and Saturday, =WORKDAY.INTL(B7,2,7), returns ‘05-11-2018’.

# EOMONTH

The Excel EOMONTH function gives the last date on the basis of the past or future month number. For Example; if start_date is 12-10-2019 and month is 8, use formula =EOMONTH (B3,C3) that returns (30-06-2020).

# WORKDAY

WORKDAY function returns a date based on a given number of working days before and after starting date, to find two days after a date excl. weekends,=WORKDAY(date,2) returns 2.

# DAYS

The DAYS function returns the difference between two dates. To find days duration between two dates, use DAYS(“01/08/2018″,”01/05/2017”) that returns ‘457’.

# ISOWEEKNUM

The ISOWEEKNUM function return ISO week number considering a week begins on Monday. Example; date (09-03-2017), use = ISOWEEKNUM(date) that returns 10.

# NETWORKDAYS

The NETWORKDAYS function returns a number of working days between two dates, 01-11-2018 (B6) and 31-12-2018 (C6), use =NETWORKDAYS(B6,C6) that returns ‘43’.

# NETWORKDAYS.INTL

The NETWORKDAYS.INTL counts days between two dates, excl. custom Weekend days. I.e.; workdays between 01-11-2018 (B6) and 31-12-2018 (C6) excl. Friday and Saturday, use =NETWORKDAYS.INTL(B6,C6,7) that returns 43.

# DATEDIF

The DATEDIF is an unknown compatibility function that can be used to calculate the difference between two dates. To find years diff.,=DATEDIF(start_date, end_date,”y”).

# YEARFRAC

The YEARFRAC function finds the fraction of years between two dates, like 01-01-2017 and 30-09-2017, use =YEARFRAC(startdate,enddate,2), that returns ‘0.755’.

# SECOND

The SECOND function returns the second component (a number from 0 to 59). Example, 6:48:15 PM, the SECOND function will return ‘15’.

# TIMEVALUE

The TIMEVALUE function returns the time serial number (in decimal). I.e., =TIMEVALUE(“12:45:30 PM”) as 0.53125 (proportion of total time value of a day).

# TIME

The TIME function returns the time value by merging the three-time components. Example, use =TIME(9,15,35), that may returns “09:15:35”.

# YEAR

The YEAR function extracts the Year component of a given date. Example; to get or find Year from a date (11/08/2017- cell B5), use =YEAR(B5), returns ‘2017’.

# DATE

The DATE function returns a date value using year, month and date, sequentially. I.e.; to merge all 3 date component, =DATE(2012,12,4) returns “04-12-2012”.

# MONTH

The MONTH function extracts an integer between 1 and 12. I.e., to get month component of a date (8/15/2017) in cell B5, use =MONTH(B5) that returns ‘8’.

# HOUR

The Excel HOUR function returns the hour component of a timestamp between 0 (12:00 A.M.) and 23 (11:59 P.M.), use =HOUR(0.3) that returns ‘7’ (0.3*24).

# NOW

The NOW function returns the serial number of the current date and time. Example; to show the current date and time, use NOW(), empty parenthesis.

# TODAY

The Excel TODAY function returns the current date in the selected cell. Example; to show the current date in Excel automatically, use TODAY(), empty parenthesis.

## Statistics Functions

# FREQUENCY

The FREQUENCY function returns the occurrences of a value in a given bin. I.e., if A1:A300 contains numbers between 1-100, it returns count within 11 to 20 bins.

# COUNTBLANK

The COUNTBLANK function returns the count of blank cells only ignoring number and text cells Like,=COUNTBLANK(A1:A10) will return 3 (ignoring value).

# RANK.EQ

The Excel RANK function returns a position of the given number on the basis of order increasing (1) or decreasing (0) in the given array.

# MIN

The MIN function returns the smallest numerical with the lowest value as per given reference of cell range.

# COUNTIF

The COUNTIF function returns a count of number based on given criteria like =COUNTIF(A1:A5,”>5″) that count numbers in the list >5 and returns 3.

# TRIMMEAN

The Excel TRIMMEAN function returns an average (arithmetic mean) after excluding a given percentage i.e. remove from the high and low numbers in a given data set.

Syntax

= TRIMMEAN(array, percent)

# COUNT

The COUNT function count of numbers within the reference list of arguments ignoring text & blank cells, =COUNT(A1:A10) will return 5 (only numeric).

# MAX

The MAX function returns the highest value as per cell range, if A1:A5 contains a list of numbers (2,8,10,5,6), the formula can be =MAX(A1:A5), returns 10.

# AVERAGE

The AVERAGE function returns an average of numbers positioned in one or multiple ranges, =AVERAGE(5,15,30) add numbers and divide by 3, and returns 16.67.

# SMALL

The Excel SMALL function returns the 2nd smallest or Nth numeric value in the given list or column by giving the position argument.

# LARGE

The Excel LARGE function returns the 2nd highest or Nth numeric value in the given list or column by giving position argument.

# COUNTA

The COUNTA function count of numbers and text within the reference list of arguments ignoring blank cells, =COUNTA(A1:A10) will return 7 (disregard blank).

# AVERAGEIF

The AVERAGEIF function returns an average figure based on given criteria like =AVERAGEIF({6;8;10;3;5},”>5″) average numbers that are more than 5 and return 8.

# COUNTIFS

The COUNTIFS function returns a count of numbers based on one or multiple given criterion based on dates, numbers, and text.

# AVERAGEIFS

The AVERAGEIFS function returns an average number based on one or multiple given criteria, sum adjacent cells that meet given conditions.

## Math Functions

# ROUND

The ROUND function returns a number after rounding a supplied number to a specific number of digits like =ROUND(19.62159344,3) returns 19.621 (3 decimals).

# AGGREGATE

The Excel Aggregate Function returns a value calculating based on the function from Sum, Average, etc (1 to 19) you applied and the options used for ignorance.

# RAND

The RAND function generates a number in the decimals format between 0 to 1, =RAND()*10+10 always returns a decimal number falling between 10 and 20.

# SUMIF

The SUMIF function returns a sum of number based on given criteria, like =SUMIF({2;8;10;3;5},”>5″) add numbers that are only more than 5 and returns 18.

# MOD

The Excel MOD function returns a value (remainder) after dividing the number with the divisor.

# CONVERT

The Excel CONVERT function gives a number after changing one measurement to others. For Example, use =CONVERT(40,”C”, “F”) returns 104. i.e. it changes Celsius to Fahrenheit.

# SUBTOTAL

The SUBTOTAL is a versatile function that summarizes a list or dataset using 11 different operations by determining the actual function based on the user input.

# SUM

The SUM function returns a number after adding multiple numeric arguments together =SUM(5,10,15) the Excel add numbers together and returns 30.

# SUMIFS

The SUMIFS function returns an addition of numerical as per multiple criteria based on the match of dates, numbers, and text against the reference.

# RANDBETWEEN

The RANDBETWEEN function generates a set of random integers between two numbers, =RANDBETWEEN(10,50) returns always a random integer between 10 and 50.

# SUMPRODUCT

The SUMPRODUCT function returns a sum of the product of numbers together distributed in multiplies ranges or arrays, an easier way to add multiple integers.