Excel Functions

Advance Your Career

Hone your Excel skills and advance your career with our paid courses. Knowledge is power and being able to leverage Excel to your advantage will ensure you stand out in the office.
 

Specialized Courses

If you need to learn how to run a regression analysis or create an LBO model, our advanced courses are for you. We provide detailed examples of how to use Excel in your career.
 

Professional or Pleasure

Learning can be a leisure hobby as much as it is a tool to advance at work. For those looking to learn Excel simply to stay sharp or for personal reasons, our content is for you as well.

A Guide to Excel Functions

The following Excel functions are broken out by category. The optional arguments are in brackets.

Date & Time Functions - Beginner



Function Description Arguments
DATE Creates a date with day, month, and year. Year, Month, Day
DATEDIF Returns days, months, years between two dates. Start_Date, End_Date, Unit
DATEVALUE Converts a date in text format to an Excel date format. Date_Text
DAY Returns the day as a number (1 - 31) Date
DAYS Returns the days between two dates. End_Date, Start_Date
DAYS360 Returns days between 2 dates in a 360-day year. Start_Date, End_Date, [Method]
HOUR Returns the hour as a number (0 - 23) Serial_Number
MINUTE Returns minutes as a number (0 - 59) Serial_Number
MONTH Returns the month as a number (1 - 12) Date
NOW Returns the current date and time. No arguments required.
SECOND Returns the second as a number (0 - 59) Serial_Number
TODAY Returns the current date. No arguments required.
WEEKDAY Returns the day of the week as a number. Serial_Number, [Return_Type]
WORKDAY Returns a date n working days in the past or future. Start_Date, Days, [Holidays]
YEAR Returns the year from a user provided date. Date


Date & Time Functions - Advanced



Function Description Arguments
EDATE Shifts date n months in the future or past Start_Date, Months
EOMONTH Returns last day of the month n months in the future or past. Start_Date, Months
NETWORKDAYS Returns the number of working days between two dates. Start_Date, End_Date, [Holidays]
ISOWEEKNUM Returns the ISO week number for a given date. Date
NETWORKDAYS.INTL Returns the number of working days between two dates. Start_Date, End_Date, [Weekend], [Holidays]
TIME Create a time with user provided hours, minutes, and seconds. Hour, Minute, Second
TIMEVALUE Returns the time from a text string. Time_Text
WEEKNUM Returns the week number for a given date. Serial_Num, [Return_Type]
WORKDAY.INTL Returns the date n working days in the future or past. Start_Date, Days, [Weekend], [Holidays]
YEARFRAC Returns the fraction of a year between two dates. Start_Date, End_Date, [Basis]


Logical Functions - Beginner



Function Description Arguments
AND Test multiple conditions with an AND statement Logical1, [Logical2], [...]
FALSE Returns the logical value False. No arguments required.
IF Tests for specific conditions. Logical_Test, [Value_if_True], [Value_if_False]
IFNA Identifies #N/A errors Value, Value_if_NA
NOT Reverses arguments and results Logical
OR Test multiple conditions with an OR statement >Logical1, [Logical2], [...]
TRUE Returns the logical value True. No arguments required.


Logical Functions - Advanced



Function Description Arguments
IFERROR Identifies and handles errors Value, Value_if_Error
IFS Tests multiple conditions, returning the first True Test1, Value1, [Test2], [Value2], [...]
SWITCH Matches values and returns the first match Expression, Value1, Result1, [Value2], [Result2], [...]
XOR Performs an exclusive OR Logical1, [Logical2], [...]


Math Functions - Beginner



Function Description Arguments
ABS Returns the absolute value of a number. Number
CEILING Returns number rounded up to nearest multiple. Number, Multiple
CEILING.MATH Returns number rounded up to nearest multiple. Number, [Significance], [Mode]
CEILING.PRECISE Returns number rounded up to nearest multiple. Number, [Significance]
DECIMAL Converts alpha numeric numbers to decimals. Number, Radix
EVEN Returns a number rounded up to the next even integer. Number
GCD Returns the greatest common divisor of two or more numbers. Number1, [Number2], [...]
INT Returns the integer portion of a decimal by rounding down. Number
LCM Returns the least common multiple of two or more numbers. Number1, [Number2], [...]
FLOOR Returns a number rounded down to the nearest specified multiple. Number, Multiple
FLOOR.MATH Returns a number rounded down to the nearest multiple. Number, [Significance], [Mode]
FLOOR.PRECISE Returns a number rounded down to the nearest multiple. Number, [Significance]
PI Returns the value of pi No arguments required.
ODD Returns a number rounded up to the next odd integer. Number
LN Returns the natural logarithm of a number. Number
LOG Returns the logarithm of a number. Number, [Base]
MOD Returns the remainder from division. Number, Divisor
POWER Raises a number to a power. Number, Power
PRODUCT Returns the product of supplied numbers. Number1, [Number2], [...]
QUOTIENT Returns the quotient without the remainder. Numerator, Denominator
ROUND Returns a number rounded to a given number of digits. Number, Num_Digits
ROUNDDOWN Returns a number rounded down to a given number of digits. Number, Num_Digits
ROUNDUP Returns a number rounded up to a given number of digits. Number, Num_Digits
SQRT Returns the positive square root of a number. Number
SUBTOTAL Returns the subtotal in a list or database. Function_Num, Ref1, [Ref2], [...]
SUM Returns the sum of numbers. Number1, [Number2], [...]
SUMIF Returns the sum of numbers in a range that meet specific criteria. Range, Criteria, [Sum_Range]
SUMIFS Returns the sum of numbers in a range that meet multiple criteria. Sum_Range, Range1, Criteria1, [Range2], [Criteria2], [...]
SUMPRODUCT Performs an exclusive OR Logical1, [Logical2], [...]
TRUNC Truncates a number to a given precision. Number, [Num_Digits]


Math Functions - Advanced



Function Description Arguments
AGGREGATE Returns the aggregate calculation. Function_Num, Options, Ref1, Ref2
ARABIC Convert Roman numerals to Arabic numerals. Roman_Text
ASIN Returns the angle of a triangle. Number
COS Returns the cosine of an angle. Number
ROMAN Convert numbers to Roman numerals. Number, [Form]
DEGREES Converts radians to degrees. Angle
EXP Returns the value of e raised to a number. Number
FACT Returns the factorial of a number. Number
FACTDOUBLE Returns the double factorial of a number. Number
LOG10 Returns the base-10 logarithm of a number. Number, [Base]
MROUND Returns a number rounded to the nearest specified multiple. Number, Multiple
MDETERM Returns the matrix determinant of an array. Array
MINVERSE Returns the inverse matrix of an array. Array
MUNIT Returns the unit matrix for a given dimension. Dimension
RADIANS Converts degrees to radians. Angle
RAND Returns a random number between 0 (zero) and 1. No argument required.
RANDBETWEEN Returns a random number between two specified values. Bottom, Top
SIGN Returns the sign of a number. Number
SIN Returns the sine of an angle. Number
TAN Returns the tangent of an angle. Number


Lookup & Reference - Beginner



Function Description Arguments
CHOOSE Returns a value from a list based on position. Index_Num, Value1, [Value2], [...]
COLUMN Returns the column number of a reference. [Reference]
COLUMNS Returns the number of columns in an array or reference. Array
FORMULATEXT Returns the formula in a cell. Reference
HYPERLINK Creates a clickable link. Link_Location, [Friendly_Name]
ROW Returns the row number of a reference. [Reference]
ROWS Returns the number of rows in an array or reference. Array
LOOKUP Looks up a value in a one column range. Lookup_Value, Lokoup_Vector, [Result_Vector]
TRANSPOSE Flips the orientation of a range of cells. Array
VLOOKUP Lookup a value in a table by matching on the first column. Value, Table, Col_Index, [Range_Lookup]
HLOOKUP Lookup a value in a table by matching on the first row. Value, Table, Row_Index, [Range_Lookup]


Lookup & Reference - Advanced



Function Description Arguments
ADDRESS Creates a cell address from a given row and cell. Rom_Num, Col_Num, [Abs_Num], [A1], [Sheet]
AREAS Returns the number of areas in a reference. Reference
INDEX Return a value in a list or table based on location. Array, Row_Num, [Col_Num], [Area_Num]
INDIRECT Creates a reference from text. Ref_Text, [A1]
MATCH Returns the position of an item in an array. Lookup_Value, Lookup_Array, [Match_Type]
MMULT Performs matrix multiplication. Array1, Array2
OFFSET Creates a reference offset from a given starting point. Reference, Rows, Cols, [Height], [Width]
GETPIVOTDATA Returns data retrieved from a pivot table in a formula. Data_Field, Pivot_Table, [Field1], [Item1], [...]


Text



Function Description Arguments
CHAR Returns a character from a number. Number
CLEAN Removes non-printable characters from text. Text
CODE Returns the code for a character. Text
CONCAT Joins text values without a delimiter. Text1, [Text2], [...]
CONCATENATE Joins text together. Text1, [Text2], [Text3], [...]
DOLLAR Coverts a number to text using currency format. Number, Decimals
EXACT Compares two text strings. Text1, Text2
FIND Returns the location of text within a string. Find_Text, Within_Text, [Start_Num]
FIXED Formats a number as text with fixed decimals. Number, [Decimals], [No_Commas]
LEFT Extract text from the left of a string. Text, [Num_Chars]
RIGHT Extract text from the right of a string. Text, [Num_Chars]
LEN Returns the length of text. Text
LOWER Coverts text to lower case. Text
MID Extracts text from within a string. Text, Start_Num, Num_Chars
PROPER Capitalizes the first letter in each word. Text
REPLACE Replaces text based on location. Old_Text, Start_Num, Num_Chars, New_Text
REPT Repeats text as specified Text, Number_Times
SEARCH Returns the location of text in a string. Find_Text, Within_Text, [Start_Num]
UPPER Converts text to upper case. Text
SUBSTITUTE Replaces text based on content. Text, Old_Text, New_Test, [Instance]
TEXT Converts a number to text in number formatting. Value, Format_Text
TRIM Removes extra spaces from text. Text
TEXTJOIN Joins text values with a delimiter. Delimiter, Ignore_Empty, Text1, [Text2], [...]
VALUE Converts text to a number. Text


Information



Function Description Arguments
TYPE Returns the type of value in a cell. Value
T Filters for text values only. Value
INFO Returns information about the current environment. Type_Text
ERROR.TYPE Tests for a specific error value. Error_Val
ISBLANK Tests if a cell is empty. Value
ISERR Tests for any error except the #N/A error value. Value
ISERROR Tests for any error. Value
ISEVEN Tests if a value is even. Value
ISFORMULA Tests if a cell contains a formula. Reference
ISLOGICAL Tests if a value is a logical value. Value
ISNA Tests for the #N/A error. Value
ISNONTEXT Tests for a non-text value. Value
ISNUMBER Tests for numerical values. Value
ISODD Tests if a value is odd. Value
ISREF Tests for a reference. Value
ISTEXT Tests for a text value. Value
N Converts a value to a number. Value
NA Creates the #N/A error value. No arguments required.
SHEET Returns the sheet index number. [Value]
SHEETS Returns the number of sheets in a reference. [Reference]
CELL Returns information about a cell. Info_Type, [Reference]


Financial - Beginner



Function Description Arguments
FV Returns the future value of an investment. Rate, Nper, Pmt, [PV], [Type]
IPMT Returns the interest in a given period. Rate, Per, Nper, PV, [FV], [Type]
PMT Returns the periodic payment for a loan. Rate, Nper, PV, [FV], [Type]
PPMT Returns the principal payment for a given period. Rate, Per, Nper, PV, [FV], [Type]
NPER Returns the number of periods for a loan or investment. Rate, Pmt, PV, [FV], [Type]
PV Returns the present value of an investment. Rate, Nper, Pmt, [FV], [Type]
RATE Returns the interest rate per period of an annuity. Nper, Pmt, PV, [FV], [Type], [Guess]
IRR Returns the internal rate of return. Values, [Guess]
NPV Calculates the net present value. Rate, Value1, [Value2], [...]
ISPMT Returns the interest paid for a specific period. Rate, Per, Nper, PV
NOMINAL Returns the annual nominal interest rate. Effect_Rate, Npery


Financial - Advanced



Function Description Arguments
CUMIPMT Calculates the cumulative interest paid on a loan. Rate, Nper, PV, Start_Period, End_Period, Type
CUMPRINC Calculates the cumulative principal paid on a loan. Rate, Nper, PV, Start_Period, End_Period, Type
MIRR Calculates the modified internal rate of return. Values, Finance_Rate, Reinvest_Rate
XIRR Calculates the internal rate of return for irregular cash flows. Values, Dates, [Guess]
XNPV Calculates the net present value for irregular cash flows. Rate, Values, Dates
DB Depreciation with a fixed declining balance. Cost, Salvage, Life, Period, [Month]
DDB Depreciation with a double declining balance. Cost, Salvage, Life, Period, [Factor]
DOLLARDE Converts dollar price as fraction to decimal. Fractional_Dollar, Fraction
DOLLARFR Converts a price to a fractional notation. Decimal_Dollar, Fraction
EFFECT Returns an effective annual interest rate. Nominal_Rate, Npery
RRI Returns the equivalent interest rate for growth. Nper, PV, FV
SLN Straight line depreciation Cost, Salvage, Life
SYD Sum-of-years depreciation. Cost, Salvage, Life, Period
VDB Double declining variable depreciation Cost, Salvage, Life, Start, End, [Factor], [No_Switch]


Database



Function Description Arguments
DAVERAGE Returns the average from matching records. Database, Field, Criteria
DCOUNT Counts matching records in a database. Database, [Field], Criteria
DCOUNTA Counts matching records in a database. Database, [Field], Criteria
DGET Returns a value from a matching record. Database, Field, Criteria
DMAX Returns the max value from a matching record. Database, Field, Criteria
DMIN Returns the min value from a matching record. Database, Field, Criteria
DPRODUCT Returns product from a matching record. Database, Field, Criteria
DSUM Returns the sum from matching records. Database, Field, Criteria
DSTDEV Returns the standard deviation of a sample from matching records. Database, Field, Criteria
DSTDEVP Returns the standard deviation of a population from matching records. Database, Field, Criteria
DVAR Returns the variance of a sample from matching records. Database, Field, Criteria
DVARP Returns the variance of a population from matching records. Database, Field, Criteria


Engineering - Beginner



Function Description Arguments
BIN2DEC Converts a binary value to a decimal. Number
BIN2HEX Converts a binary value to a hexadecimal. Number, [Places]
BIN2OCT Calculates a binary number to octal. Number, [Places]
BITAND Returns a bitwise AND of two numbers Number1, Number2
BITOR Returns a bitwise OR of two numbers. Number1, Number2
CONVERT Converts measurement units. Number, From_Unit, To_Unit
DEC2BIN Converts a decimal number to binary. Number, [Places]
DEC2HEX Converts a decimal number to hexadecimal. Number, [Places]
DEC2OCT Converts a decimal to octal number. Number, [Places]
HEX2BIN Converts a hexadecimal to binary number. Number, [Places]
HEX2DEC Converts a hexadecimal to decimal number. Number
HEX2OCT Converts a hexadecimal to octal number. Number, [Places]


Engineering - Advanced



Function Description Arguments
BITLSHIFT Returns a number shifted left by a specified number of bits. Number, Shift_Amount
BITRSHIFT Returns a number shifted right by a specified number of bits. Number, Shift_Amount
BITXOR Returns a bitwise XOR of two numbers. Number1, Number2
COMPLEX Converts coefficients to complex numbers. Real_Num, I_Num, [Suffix]
DELTA Test if two value are equal. Number1, [Number2]
IMABS Returns the absolute value of complex numbers. Inumber
IMAGINARY Returns the imaginary coefficient of complex number. Inumber
IMPOWER Raises complex numbers to a given power. Inumber, Number
IMPRODUCT Returns the product of complex numbers. Inumber1, [Inumber2], [...]
IMREAL Returns the real coefficient of complex a number. Inumber
IMSUB Returns the difference between two complex numbers. Number1, Number2
IMSUM Returns the sum of complex numbers. Inumber1, [Inumber2], [...]


Statistical - Beginner



Function Description Arguments
AVEDEV Returns the sum of squared deviations. Number1, [Number2], [...]
AVERAGE Returns the average of a group of numbers. Number1, [Number2], [...]
AVERAGEA Returns the average of a group of numbers and text. Value1, [Value2], [...]
AVERAGEIFS Returns the average of cells that match multiple criteria. Avg_Rng, Range1, Criteria1, [Range2], [Criteria2], [...]
COMBIN Returns the total number of combinations. Number, Number_Chosen
COUNT Counts numbers. Value1, [Value2], [...]
COUNTA Counts the number of non-blank cells. Value1, [Value2], [...]
COUNTIFS Counts cells that match multiple criteria. Range1, Criteria1, [Range2], [Criteria2], [...]
COUNTIF Counts cells that match criteria. Range, Criteria
COUNTBLANK Counts cells that are blank. Range
FREQUENCY Returns the frequency of values in a data set. Data_Array, Bins_Array
GEOMEAN Returns the geometric mean. Number1, [Number2], [...]
HARMEAN Returns the harmonic mean. Number1, [Number2], [...]
LARGE Returns the nth largest number. Array, N
SMALL Returns the nth smallest number. Array, N
MAX Returns the largest value. Number1, [Number2], [...]
MAXA Returns the largest value. Value1, [Value2], [...]
MEDIAN Returns the median of a group of numbers. Number1, [Number2], [...]
MIN Returns the smallest value. Number1, [Number2], [...]
MINA Returns the smallest value. Value1, [Value2], [...]
MODE Returns the most frequently occurring number. Number1, [Number2], [...]
MODE.MULT Returns the most frequently occurring number. Number1, [Number2], [...]
MODE.SNGL Returns the most frequently occurring number. Number1, [Number2], [...]
PERCENTILE Returns the kth percentile. Array, K
PERCENTILE.EXC Returns the kth percentile, exclusive. Array, K
PERCENTILE.INC Returns the kth percentile, inclusive. Array, K
PERMUT Returns the total number of permutations. Number, Number_Chosen
QUARTILE Returns the quartile in a data set. Array, Quart
QUARTILE.EXC Returns the quartile in a data set, exclusive. Array, Quart
QUARTILE.INC Returns the quartile in a data set, inclusive. Array, Quart
RANK Ranks a number against a range of number. Number, Array, [Order]
RANK.AVG Ranks a number against a range of number. Number, Ref, [Order]
RANK.EQ Ranks a number against a range of number. Number, Ref, [Order]
STANDARDIZE Calculates a normalized value (z-score). X, Mean, Standard_Dev
STDEV Returns the standard deviation of a sample. Number1, [Number2], [...]
STDEV.S Returns the standard deviation of a sample. Number1, [Number2], [...]
STDEV.P Returns the standard deviation of the population. Number1, [Number2], [...]
STDEVP Returns the standard deviation of the population. Number1, [Number2], [...]
STDEVA Returns the standard deviation of a sample. Number1, [Number2], [...]
STDEVPA Returns the standard deviation of the population. Number1, [Number2], [...]
TRIMMEAN Calculates the mean while excluding outliers. Array, Percent
VAR Returns the variation of a sample. Number1, [Number2], [...]
VAR.P Returns the variation of a population. Number1, [Number2], [...]
VAR.S Returns the variation of a sample. Number1, [Number2], [...]
VARP Returns the variation of a population. Number1, [Number2], [...]
VARA Returns the variation of a sample. Number1, [Number2], [...]
VARPA Returns the variation of a population. Number1, [Number2], [...]


Statistical - Advanced



Function Description Arguments
DEVSQ Returns the sum of squared deviations. Number1, [Number2], [...]
MAXIFS Returns the maximum value with criteria. Max_Range, Range1, Criteria1, [Range2], [Criteria2], [...]
MINIFS Returns the minimum value with criteria. Min_Range, Range1, Criteria1, [Range2], [Criteria2], [...]
PERCENTRANK Returns the percentile rank, inclusive. Array, X, [Significance]
PERCENTRANK.EXC Returns the percentile rank, exclusive. Array, X, [Significance]
PERCENTRANK.INC Returns the percentile rank, inclusive. Array, X, [Significance]