Excel Functions Cheat Sheet (100)

One key feature that makes Excel so powerful is its extensive library of functions. If you’re a beginner looking to understand the basics, these functions can enhance your Excel Skills.

This cheat sheet is not just a guide; it’s a practical tool to help you understand the top 100 Excel functions quickly. So, let’s dive in and unlock Excel’s full potential with our ultimate functions cheat sheet.

TEXT Functions

1. CONCATENATE

  • Usage: Joins several text strings into one.
  • Example: =CONCATENATE(A1, “” “”, B1)

2. LEFT

  • Usage: Returns the first character(s) in a text string.
  • Example: =LEFT(A1, 5)

3. RIGHT

  • Usage: Returns the last character(s) in a text string.
  • Example: =RIGHT(A1, 3)

4. MID

  • Usage: Returns a specific number of characters from a text string, starting at the position you specify.
  • Example: =MID(A1, 2, 5)

5. LEN

  • Usage: Returns the number of characters in a text string.
  • Example: =LEN(A1)

6. TRIM

  • Usage: Removes all spaces from a text string except for single spaces between words.
  • Example: =TRIM(A1)

7. LOWER

  • Usage: Converts all letters in a text string to lowercase.
  • Example: =LOWER(A1)

8. UPPER

  • Usage: Converts all letters in a text string to uppercase.
  • Example: =UPPER(A1)

9. PROPER

  • Usage: Converts the first letter of each word in a text string to uppercase.
  • Example: =PROPER(A1)

10. FIND

  • Usage: Finds one text value within another (case-sensitive).
  • Example: =FIND(“”find_text””, A1)

11. SEARCH

  • Usage: Finds one text value within another (not case-sensitive).
  • Example: =SEARCH(“”search_text””, A1)

12. REPLACE

  • Usage: Replaces part of a text string with a different text string.
  • Example: =REPLACE(A1, 1, 3, “”new_text””)

13. SUBSTITUTE

  • Usage: Substitutes new text for old text in a text string.
  • Example: =SUBSTITUTE(A1, “”old_text””, “”new_text””)

14. TEXT

  • Usage: Formats a number and converts it to text.
  • Example: =TEXT(A1, “”0.00″”)

15. CHAR

  • Usage: Returns the character specified by a number.
  • Example: =CHAR(65)

16. CODE

  • Usage: Returns a numeric code for the first character in a text string.
  • Example: =CODE(“”A””)

17. CLEAN

  • Usage: Removes all non-printable characters from text.
  • Example: =CLEAN(A1)

18. DOLLAR

  • Usage: Converts a number to text using currency format.
  • Example: =DOLLAR(1234.56, 2)

19. EXACT

  • Usage: Checks whether two text strings are exactly the same.
  • Example: =EXACT(A1, B1)

20. FIXED

  • Usage: Rounds a number to a specified number of decimals and returns the result as text.
  • Example: =FIXED(1234.567, 2)

21. REPT

  • Usage: Repeats text a given number of times.
  • Example: =REPT(“”x””, 5)

22. T

  • Usage: Converts its arguments to text.
  • Example: =T(123)

23. TEXTJOIN

  • Usage: Combines text from multiple ranges and/or strings and includes a delimiter.
  • Example: =TEXTJOIN(“”, “”, TRUE, A1:A5)

24. UNICHAR

  • Usage: Returns the Unicode character that is referenced by the given numeric value.
  • Example: =UNICHAR(65)

25. UNICODE

  • Usage: Returns the number (code point) corresponding to the first character of the text.
  • Example: =UNICODE(“”A””)

26. VALUE

  • Usage: Converts a text argument to a number.
  • Example: =VALUE(“”123.45″”)

AVERAGE

  • Returns the average (arithmetic mean) of the arguments.
  • =AVERAGE(A1:A10)

MEDIAN

  • Returns the median of the given numbers.
  • =MEDIAN(A1:A10)

MODE

  • Returns the most frequently occurring value in a range of numbers.
  • =MODE(A1:A10)

STDEV

  • Estimates the standard deviation based on a sample.
  • =STDEV(A1:A10)

VAR

Estimates variance based on a sample.

=VAR(A1:A10)

VAR.P

Calculates variance based on the entire population.

=VAR.P(A1:A10)

VAR.S

Estimates variance based on a sample.

=VAR.S(A1:A10)

STDEVP

Calculates standard deviation based on the entire population.

=STDEVP(A1:A10)

STDEV.S

Estimates standard deviation based on a sample.

=STDEV.S(A1:A10)

PERCENTILE

Returns the k-th percentile of values in a range.

=PERCENTILE(A1:A10, 0.5)

QUARTILE

Returns the quartile of a data set.

=QUARTILE(A1:A10, 1)

LARGE

Returns the k-th largest value in a data set.

=LARGE(A1:A10, 2)

SMALL

Returns the k-th smallest value in a data set.

=SMALL(A1:A10, 3)

RANK

Returns the rank of a number in a list of numbers.

=RANK(A1, B1:B10)

PERCENTRANK

Returns the percentage rank of a value in a data set.

=PERCENTRANK(A1:A10, B1)

GEOMEAN

Returns the geometric mean of an array or range of positive data.

=GEOMEAN(A1:A10)

HARMEAN

Returns the harmonic mean of a data set.

=HARMEAN(A1:A10)

DEVSQ

Returns the sum of squares of deviations.

=DEVSQ(A1:A10)

AVERAGEA

Returns the average of its arguments, including numbers, text, and logical values.

=AVERAGEA(A1:A10)

STDEV.P

Calculates standard deviation based on the entire population.

=STDEV.P(A1:A10)

PERCENTILE.EXC

Returns the k-th percentile of values in a range, exclusive.

=PERCENTILE.EXC(A1:A10, 0.5)

PERCENTILE.INC

Returns the k-th percentile of values in a range, inclusive.

=PERCENTILE.INC(A1:A10, 0.5)

QUARTILE.EXC

Returns the quartile of a data set, exclusive.

=QUARTILE.EXC(A1:A10, 1)

QUARTILE.INC

Returns the quartile of a data set, inclusive.

=QUARTILE.INC(A1:A10, 1)

NEGBINOMDIST

Returns the negative binomial distribution.

=NEGBINOMDIST(10, 5, 0.25)

NORMDIST

Returns the normal cumulative distribution.

=NORMDIST(42, 40, 1.5, TRUE)

NORMINV

Returns the inverse of the normal cumulative distribution.

=NORMINV(0.975, 40, 1.5)

NORMSDIST

Returns the standard normal cumulative distribution.

=NORMSDIST(1.333333)

NORMSINV

Returns the inverse of the standard normal cumulative distribution.

=NORMSINV(0.95)

PEARSON

Returns the Pearson product-moment correlation coefficient.

=PEARSON(A1:A10, B1:B10)

POISSON

Returns the Poisson distribution.

=POISSON(2, 5, FALSE)

RSQ

Returns the square of the Pearson product-moment correlation coefficient.

=RSQ(A1:A10, B1:B10)

SKEW

Returns the skewness of a distribution.

=SKEW(A1:A10)

SLOPE

Returns the slope of the linear regression line.

=SLOPE(A1:A10, B1:B10)

STEYX

Returns the standard error of the predicted y-value for each x in the regression.

=STEYX(A1:A10, B1:B10)

T.DIST

Returns the Student’s t-distribution.

=T.DIST(1.959964, 10, TRUE)

T.DIST.2T

Returns the two-tailed Student’s t-distribution.

=T.DIST.2T(1.959964, 10)

T.DIST.RT

Returns the right-tailed Student’s t-distribution.

=T.DIST.RT(1.959964, 10)

T.INV

Returns the inverse of the Student’s t-distribution.

=T.INV(0.05, 10)

T.INV.2T

Returns the inverse of the two-tailed Student’s t-distribution.

=T.INV.2T(0.05, 10)

T.TEST

Returns the probability associated with a Student’s t-test.

=T.TEST(A1:A10, B1:B10, 2, 3)

TREND

Returns values along a linear trend.

=TREND(A1:A10, B1:B10, C1:C10)

TRIMMEAN

Returns the mean of the interior of a data set.

=TRIMMEAN(A1:A10, 0.2)

WEIBULL

Returns the Weibull distribution.

=WEIBULL(5, 1, 2, TRUE)

Z.TEST

Returns the one-tailed probability value of a z-test.

=Z.TEST(A1:A10, 2)

FORECAST.ETS

Returns a future value based on existing values, using the AAA version of the Exponential Smoothing (ETS) algorithm.

=FORECAST.ETS(A1, B1:B10, C1:C10)

FORECAST.ETS.CONFINT

Returns a confidence interval for the forecast value at the specified target date.

=FORECAST.ETS.CONFINT(A1, B1:B10, C1:C10)

FORECAST.ETS.SEASONALITY

Returns the length of the repetitive pattern Excel detects for the specified time series.

N/A

FORECAST.ETS.STAT

Returns a statistical value as a result of time series forecasting.

=FORECAST.ETS.STAT(B1:B10, C1:C10, 1)

FORECAST.LINEAR

Returns a future value based on existing values.

=FORECAST.LINEAR(A1, B1:B10, C1:C10)

GAMMA

Returns the Gamma function value.

=GAMMA(A1)

GAMMA.DIST

Returns the gamma distribution.

=GAMMA.DIST(A1, 2, 3, TRUE)

GAMMA.INV

Returns the inverse of the gamma cumulative distribution.

=GAMMA.INV(0.5, 2, 3)

GAUSS

Returns 0.5 less than the standard normal cumulative distribution.

=GAUSS(A1)

EXPON.DIST

Returns the exponential distribution.

=EXPON.DIST(A1, 0.5, TRUE)

F.DIST

Returns the F probability distribution.

=F.DIST(A1, 4, 5, TRUE)

F.DIST.RT

Returns the F probability distribution.

=F.DIST.RT(A1, 4, 5)

F.INV

Returns the inverse of the F probability distribution.

=F.INV(0.5, 4, 5)

F.INV.RT

Returns the inverse of the F probability distribution.

=F.INV.RT(0.5, 4, 5)

NORM.S.DIST

Returns the standard normal cumulative distribution.

=NORM.S.DIST(A1, TRUE)

NORM.S.INV

Returns the inverse of the standard normal cumulative distribution.

=NORM.S.INV(0.95)=NORM.S.INV(0.95)

PERCENTRANK.EXC

Returns the rank of a value in a data set as a percentage (0..1, exclusive).

=PERCENTRANK.EXC(A1:A10, 4)

PERCENTRANK.INC

Returns the rank of a value in a data set as a percentage (0..1, inclusive).

=PERCENTRANK.INC(A1:A10, 4)

PHI

Returns the value of the density function for a standard normal distribution.

=PHI(1)

PROB

Returns the probability that values in a range are between two limits.

=PROB(A1:A10, B1:B10, 1, 3)

RANK.AVG

Returns the rank of a number in a list of numbers, averaging duplicate values.

=RANK.AVG(A1, A1:A10)

RANK.EQ

Returns the rank of a number in a list of numbers.

=RANK.EQ(A1, A1:A10)

SKEW.P

Returns the skewness of a distribution based on a population.

=SKEW.P(A1:A10)

SKEW.S

Returns the skewness of a distribution based on a sample.

=SKEW.S(A1:A10)

SMLN

Returns the smallest k-th value in a data set.

=SMLN(A1:A10, 1)

WEIBULL.DIST

Returns the Weibull distribution.

=WEIBULL.DIST(5, 1, 2, TRUE)

TODAY

  • Usage: Returns the current date.
  • Example: =TODAY()

NOW

  • Usage: Returns the current date and time.
  • Example: =NOW()

TODAY

  • Returns the current date.
  • =TODAY()

NOW

  • Returns the current date and time.
  • =NOW()

YEAR

  • Returns the year of a date.
  • =YEAR(A1)

MONTH

  • Returns the month of a date.
  • =MONTH(A1)

DAY

  • Returns the day of a date.
  • =DAY(A1)

HOUR

  • Returns the hour of a time.
  • =HOUR(A1)

MINUTE

  • Returns the minute of a time.
  • =MINUTE(A1)

SECOND

  • Returns the second of a time.
  • =SECOND(A1)

WEEKDAY

  • Returns the day of the week corresponding to a date.
  • =WEEKDAY(A1)

WEEKNUM

  • Returns the week number in a year.
  • =WEEKNUM(A1)

NETWORKDAYS

  • Returns the number of whole working days between two dates.
  • =NETWORKDAYS(A1, B1)

EDATE

  • Returns the date that is the indicated number of months before or after a specified date.
  • =EDATE(A1, 3)

EOMONTH

  • Returns the last day of the month, n months before or after a date.
  • =EOMONTH(A1, 1)

WORKDAY

  • Returns the date before or after a specified number of workdays.
  • =WORKDAY(A1, 5)

DAYS

  • Returns the number of days between two dates.
  • =DAYS(A1, B1)

ISOWEEKNUM

  • Returns the number of the ISO week number of the year for a given date.
  • =ISOWEEKNUM(A1)

DATEVALUE

  • Converts a date in the form of text to a serial number.
  • =DATEVALUE(1/1/2021)

TIMEVALUE

  • Converts a time in the form of text to a serial number.
  • =TIMEVALUE(12:00 PM)

WORKDAY.INTL

  • Returns the date before or after a specified number of workdays with custom weekend parameters.
  • =WORKDAY.INTL(TODAY(), 10, 1)

NETWORKDAYS.INTL

  • Returns the number of whole workdays between two dates with custom weekend parameters.
  • =NETWORKDAYS.INTL(TODAY(), A1, 1)

ISNUMBER

  • Checks whether a value is a number.
  • =ISNUMBER(A1)

ISTEXT

  • Checks whether a value is text.
  • =ISTEXT(A1)

ISERROR

  • Checks whether a value is an error.
  • =ISERROR(A1)

ISBLANK

  • Checks whether a reference is to an empty cell.
  • =ISBLANK(A1)

ISLOGICAL

  • Checks whether a value is a logical value (TRUE or FALSE).
  • =ISLOGICAL(A1)

ISERR

  • Checks whether a value is an error except #N/A.
  • =ISERR(A1)

ISEVEN

  • Checks whether a number is even.
  • =ISEVEN(A1)

ISODD

  • Checks whether a number is odd.
  • =ISODD(A1)

ISNA

  • Checks whether a value is the error #N/A.
  • =ISNA(A1)

ISNONTEXT

  • Checks whether a value is not text.
  • =ISNONTEXT(A1)

TYPE

  • Returns a number indicating the data type of a value.
  • =TYPE(A1)

N

  • Returns a value converted to a number.
  • =N(A1)

ERROR.TYPE

  • Returns a number corresponding to an error type.
  • =ERROR.TYPE(A1)

INFO

  • Returns information about the current operating environment.
  • =INFO(directory)

CELL

  • Returns information about the formatting, location, or contents of a cell.
  • =CELL(address, A1)

ISFORMULA

  • Checks whether a reference is to a formula.
  • =ISFORMULA(A1)

SHEET

  • Returns the sheet number of the referenced sheet.
  • =SHEET(Sheet1)

SHEETS

  • Returns the number of sheets in a reference.
  • =SHEETS()

GETPIVOTDATA

  • Extracts data stored in a PivotTable.
  • =GETPIVOTDATA(Sales, A1)

ISREF

  • Checks whether a value is a reference.
  • =ISREF(A1)

NA

  • Returns the error value #N/A.
  • =NA()

IF

  • Returns one value if a condition is true and another value if it’s false.
  • =IF(A1>10, High, Low)

AND

  • Returns TRUE if all arguments are true.
  • =AND(A1>5, B1<10)

OR

  • Returns TRUE if any argument is true.
  • =OR(A1>10, B1<5)

NOT

  • Reverses the logical value of its argument.
  • =NOT(A1>10)

IFERROR

  • Returns a value if an error is found in a formula.
  • =IFERROR(A1/B1, Error)

IFNA

  • Returns a value if a #N/A error is found in a formula.
  • =IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), Not Found)

XOR

  • Returns a logical exclusive OR of all arguments.
  • =XOR(A1>5, B1<10)

SWITCH

  • Evaluates an expression against a list of values and returns the corresponding result.
  • =SWITCH(A1, Red, 1, Blue, 2, Green, 3)

TRUE

  • Returns the logical value TRUE.
  • =TRUE()

FALSE

  • Returns the logical value FALSE.
  • =FALSE()

XLOOKUP

  • Looks for a value from a specified column or row, based on a matching value in another column or row.
  • =XLOOKUP(“Banana”, A2:A4, B2:B4)

VLOOKUP

  • Looks for a value in the first column of a table and returns a value in the same row from a specified column.
  • =VLOOKUP(A1, B1:D10, 3, FALSE)

HLOOKUP

  • Looks for a value in the top row of a table and returns a value in the same column from a specified row
  • =HLOOKUP(A1, B1:D10, 3, FALSE)

MATCH

  • Searches for a specified value in a range and returns the relative position of that item.
  • =MATCH(A1, B1:B10, 0)

INDEX

  • Returns the value of an element in a table or an array, selected by the row and column number indexes.
  • =INDEX(A1:C10, 2, 3)

OFFSET

  • Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
  • =OFFSET(A1, 2, 3)

UNIQUE

  • Returns a list of unique values in a list or range.
  • =UNIQUE(A1:A10)

FILTER

  • Filters a range of data based on criteria you define.
  • =FILTER(A1:A10, A1:A10>5)

SORT

  • Sorts the contents of a range or array.
  • =SORT(A1:A10)

SORTBY

  • Sorts the contents of a range or array based on the values in a corresponding range or array.
  • =SORTBY(A1:A10, B1:B10)

SEQUENCE

  • Generates a list of sequential numbers in an array.
  • =SEQUENCE(10)

RANDARRAY

  • Returns an array of random numbers between 0 and 1.
  • =RANDARRAY(10)

SINGLE

  • Returns a single value using implicit intersection.
  • =@A1:A10

CONCAT

  • Combines the text from multiple ranges and/or strings.
  • =CONCAT(A1:A5)

IFS

  • Checks whether one or more conditions are met and returns a value corresponding to the first TRUE condition.
  • =IFS(A1=1, One, A1=2, Two, TRUE, Other)

MAXIFS

  • Returns the maximum value among cells specified by a given set of conditions or criteria.
  • =MAXIFS(A1:A10, B1:B10, >5)

MINIFS

  • Returns the minimum value among cells specified by a given set of conditions or criteria.
  • =MINIFS(A1:A10, B1:B10, >5)

FV

  • Calculates the future value of an investment.
  • =FV(0.05/12, 60, -100, -1000)

PV

  • Calculates the present value of an investment.
  • =PV(0.05/12, 60, -100, 5000)

PMT

  • Calculates the payment for a loan based on constant payments and a constant interest rate.
  • =PMT(0.05/12, 60, 5000)

NPV

  • Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.
  • =NPV(0.05, A1:A10)

IRR

  • Calculates the internal rate of return for a series of cash flows.
  • =IRR(A1:A10)

RATE

  • Returns the interest rate per period of an annuity.
  • =RATE(10, -200, 4000)

NPER

  • Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
  • =NPER(0.05/12, -100, 1000)

XNPV

  • Returns the net present value for a schedule of cash flows that is not necessarily periodic.
  • =XNPV(0.09, A2:A10, B2:B10)

XIRR

  • Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
  • =XIRR(A2:A10, B2:B10)

MIRR

  • Returns the modified internal rate of return for a series of periodic cash flows.
  • =MIRR(A1:A10, 0.12, 0.1)

SLN

  • Returns the straight-line depreciation of an asset for one period.
  • =SLN(10000, 1000, 5)

SYD

  • Returns the sum-of-years’ digits depreciation of an asset for a specified period.
  • =SYD(10000, 1000, 5, 1)

DDB

  • Returns the depreciation of an asset for a specified period using the double-declining balance method.
  • =DDB(10000, 1000, 5, 1)

DB

  • Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
  • =DB(10000, 1000, 5, 1)

CUMIPMT

  • Returns the cumulative interest paid on a loan between two periods.
  • =CUMIPMT(0.05/12, 60, 5000, 1, 12, 0)

CUMPRINC

  • Returns the cumulative principal paid on a loan between two periods.
  • =CUMPRINC(0.05/12, 60, 5000, 1, 12, 0)

SUM

  • Adds all the numbers in a range of cells.
  • =SUM(A1:A10)

PRODUCT

  • Multiplies all the numbers given as arguments.
  • =PRODUCT(A1:A10)

SQRT

  • Returns the square root of a number.
  • =SQRT(A1)

POWER

  • Returns the result of a number raised to a power.
  • =POWER(A1, 2)

MOD

  • Returns the remainder from division.
  • =MOD(A1, 2)

ABS

  • Returns the absolute value of a number.
  • =ABS(A1)

ROUND

  • Rounds a number to a specified number of digits.
  • =ROUND(A1, 2)

ROUNDUP

  • Rounds a number up, away from zero.
  • =ROUNDUP(A1, 2)

ROUNDDOWN

  • Rounds a number down, toward zero.
  • =ROUNDDOWN(A1, 2)

MROUND

  • Returns a number rounded to the desired multiple.
  • =MROUND(A1, 5)

CEILING

  • Rounds a number up to the nearest multiple of significance.
  • =CEILING(A1, 1)

FLOOR

  • Rounds a number down to the nearest multiple of significance.
  • =FLOOR(A1, 1)

SUMPRODUCT

  • Returns the sum of the products of corresponding ranges or arrays.
  • =SUMPRODUCT(A1:A10, B1:B10)

SUMSQ

  • Returns the sum of the squares of the arguments.
  • =SUMSQ(A1:A10)

EXP

  • Returns e raised to the power of a given number.
  • =EXP(A1)

LN

  • Returns the natural logarithm of a number.
  • =LN(A1)

LOG

  • Returns the logarithm of a number to a specified base.
  • =LOG(A1, 10)

LOG10

  • Returns the base-10 logarithm of a number.
  • =LOG10(A1)

PI

  • Returns the value of pi.
  • =PI()

RANDBETWEEN

  • Returns a random number between the numbers you specify.
  • =RANDBETWEEN(1, 100)

RAND

  • Returns a random number between 0 and 1.
  • =RAND()

TRUNC

  • Truncates a number to an integer by removing the fractional part of the number.
  • =TRUNC(A1)

CONVERT

  • Converts a number from one measurement system to another.
  • =CONVERT(A1, cm, in)

DELTA

  • Tests whether two values are equal.
  • =DELTA(A1, B1)

BESSELI

  • Returns the modified Bessel function In(x).
  • =BESSELI(A1, 1)

BESSELJ

  • Returns the Bessel function Jn(x).
  • =BESSELJ(A1, 1)

BESSELK

  • Returns the modified Bessel function Kn(x).
  • =BESSELK(A1, 1)

BESSELY

  • Returns the Bessel function Yn(x).
  • =BESSELY(A1, 1)

BIN2DEC

  • Converts a binary number to decimal.
  • =BIN2DEC(1010)

BIN2HEX

  • Converts a binary number to hexadecimal.
  • =BIN2HEX(1010)

BIN2OCT

  • Converts a binary number to octal.
  • =BIN2OCT(1010)

DEC2BIN

  • Converts a decimal number to binary.
  • =DEC2BIN(10)

DEC2HEX

  • Converts a decimal number to hexadecimal.
  • =DEC2HEX(10)

DEC2OCT

  • Converts a decimal number to octal.
  • =DEC2OCT(10)

HEX2BIN

  • Converts a hexadecimal number to binary.
  • =HEX2BIN(A)

HEX2DEC

  • Converts a hexadecimal number to decimal.
  • =HEX2DEC(A)

HEX2OCT

  • Converts a hexadecimal number to octal.
  • =HEX2OCT(A)

OCT2BIN

  • Converts an octal number to binary.
  • =OCT2BIN(12)

OCT2DEC

  • Converts an octal number to decimal.
  • =OCT2DEC(12)

OCT2HEX

  • Converts an octal number to hexadecimal.
  • =OCT2HEX(12)

COMPLEX

  • Converts real and imaginary coefficients into a complex number.
  • =COMPLEX(3, 4)

IMAGINARY

  • Returns the imaginary coefficient of a complex number.
  • =IMAGINARY(3+4i)

IMREAL

  • Returns the real coefficient of a complex number.
  • =IMREAL(3+4i)

IMABS

  • Returns the absolute value (modulus) of a complex number.
  • =IMABS(3+4i)

IMARGUMENT

  • Returns the argument theta, an angle expressed in radians.
  • =IMARGUMENT(3+4i)

IMCONJUGATE

  • Returns the complex conjugate of a complex number.
  • =IMCONJUGATE(3+4i)

IMCOS

  • Returns the cosine of a complex number.
  • =IMCOS(3+4i)

IMDIV

  • Returns the quotient of two complex numbers.
  • =IMDIV(3+4i, 1+2i)

IMEXP

  • Returns the exponential of a complex number.
  • =IMEXP(3+4i)

IMLN

  • Returns the natural logarithm of a complex number.
  • =IMLN(3+4i)

IMLOG10

  • Returns the base-10 logarithm of a complex number.
  • =IMLOG10(3+4i)

IMLOG2

  • Returns the base-2 logarithm of a complex number.
  • =IMLOG2(3+4i)

IMPOWER

  • Returns a complex number raised to an integer power.
  • =IMPOWER(3+4i, 2)

IMPRODUCT

  • Returns the product of from 2 to 255 complex numbers.
  • =IMPRODUCT(3+4i, 1+2i)

IMSIN

  • Returns the sine of a complex number.
  • =IMSIN(3+4i)

IMSQRT

  • Returns the square root of a complex number.
  • =IMSQRT(3+4i)

IMSUB

  • Returns the difference between two complex numbers.
  • =IMSUB(3+4i, 1+2i)

IMSUM

  • Returns the sum of complex numbers.
  • =IMSUM(3+4i, 1+2i)

ENCODEURL

  • Returns a URL-encoded string.
  • =ENCODEURL(https://www.example.com)

FILTERXML

  • Returns specific data from XML content by using a specified XPath.
  • =FILTERXML(John, //name)

WEBSERVICE

  • Retrieves data from a web service.
  • =WEBSERVICE(https://api.exchangeratesapi.io/latest)

DCOUNT

  • Counts the cells that contain numbers in a column of a list or database that match conditions you specify.
  • =DCOUNT(A1:B10, Sales, D1:D2)

DAVERAGE

  • Averages the values in a column of a list or database that match conditions you specify.
  • =DAVERAGE(A1:B10, Sales, D1:D2)

DMAX

  • Returns the maximum value from selected database entries.
  • =DMAX(A1:B10, Sales, D1:D2)

DMIN

  • Returns the minimum value from selected database entries.
  • =DMIN(A1:B10, Sales, D1:D2)

DPRODUCT

  • Multiplies the values in a column of a list or database that match conditions you specify.
  • =DPRODUCT(A1:B10, Sales, D1:D2)

DSTDEV

  • Estimates the standard deviation of a population based on a sample by using the numbers in a column of a list or database that match conditions you specify.
  • =DSTDEV(A1:B10, Sales, D1:D2)

DSTDEVP

  • Calculates the standard deviation of a population based on the entire population by using the numbers in a column of a list or database that match conditions you specify.
  • =DSTDEVP(A1:B10, Sales, D1:D2)

DSUM

  • Adds the numbers in a column of a list or database that match conditions you specify.
  • =DSUM(A1:B10, Sales, D1:D2)

DVAR

  • Estimates variance based on a sample from selected database entries.
  • =DVAR(A1:B10, Sales, D1:D2)

DVARP

  • Calculates variance based on the entire population from selected database entries.
  • =DVARP(A1:B10, Sales, D1:D2)

DGET

  • Extracts a single value from a column of a list or database that matches conditions you specify.
  • =DGET(A1:B10, Sales, D1:D2)

DCOUNTA

  • Counts the nonblank cells in a column of a list or database that match conditions you specify.
  • =DCOUNTA(A1:B10, Sales, D1:D2)

N/A

CUBEMEMBER

  • Returns a member or tuple in a cube hierarchy.
  • =CUBEMEMBER(Sales, [Product].[Category].[Bikes])

CUBEMEMBERPROPERTY

  • Returns the value of a member property in the cube.
  • =CUBEMEMBERPROPERTY(Sales, [Product].[Category].[Bikes], Member_Name)

CUBERANKEDMEMBER

  • Returns the nth, or ranked, member in a set.
  • =CUBERANKEDMEMBER(Sales, [Product].[Category].[Bikes], 1)

CUBESET

  • Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set and then returns that set to Microsoft Excel.
  • =CUBESET(Sales, [Product].[Category].[Bikes])

CUBESETCOUNT

  • Returns the number of items in a set.
  • =CUBESETCOUNT(CUBESET(Sales, [Product].[Category].[Bikes]))

CUBEVALUE

  • Returns an aggregated value from a cube.
  • =CUBEVALUE(Sales, [Product].[Category].[Bikes])

CHIDIST

  • Returns the one-tailed probability of the chi-squared distribution.
  • =CHIDIST(18.307, 10)

CHINV

  • Returns the inverse of the one-tailed probability of the chi-squared distribution.
  • =CHINV(0.05, 10)

CHITEST

  • Returns the test for independence.
  • =CHITEST(A1:A5, B1:B5)

CONFIDENCE

  • Returns the confidence interval for a population mean.
  • =CONFIDENCE(0.05, 2.5, 10)

CRITBINOM

  • Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
  • =CRITBINOM(10, 0.5, 0.8)

FDIST

  • Returns the F probability distribution.
  • =FDIST(15.2067, 6, 4)

FINV

  • Returns the inverse of the F probability distribution.
  • =FINV(0.01, 6, 4)

FTEST

  • Returns the result of an F-test.
  • =FTEST(A1:A5, B1:B5)

GAMMADIST

  • Returns the gamma distribution.
  • =GAMMADIST(2, 3, 2, TRUE)

GAMMAINV

  • Returns the inverse of the gamma cumulative distribution.
  • =GAMMAINV(0.068094, 3, 2)

HYPGEOMDIST

  • Returns the hypergeometric distribution.
  • =HYPGEOMDIST(1, 4, 8, 20)

LOGINV

  • Returns the inverse of the lognormal cumulative distribution.
  • =LOGINV(0.039084, 3.5, 1.2)

LOGNORMDIST

  • Returns the cumulative lognormal distribution.
  • =LOGNORMDIST(4, 3.5, 1.2)

ACOS

  • Returns the arccosine of a number.
  • =ACOS(0.5)

ACOSH

  • Returns the inverse hyperbolic cosine of a number.
  • =ACOSH(1.5)

ASIN

  • Returns the arcsine of a number.
  • =ASIN(0.5)

ASINH

  • Returns the inverse hyperbolic sine of a number.
  • =ASINH(0.5)

ATAN

  • Returns the arctangent of a number.
  • =ATAN(1)

ATAN2

  • Returns the arctangent of the quotient of its arguments.
  • =ATAN2(1, 1)

ATANH

  • Returns the inverse hyperbolic tangent of a number.
  • =ATANH(0.5)

COS

  • Returns the cosine of a number.
  • =COS(1)

COSH

  • Returns the hyperbolic cosine of a number.
  • =COSH(1)

SIN

  • Returns the sine of a number.
  • =SIN(1)

SINH

  • Returns the hyperbolic sine of a number.
  • =SINH(1)

TAN

  • Returns the tangent of a number.
  • =TAN(1)

TANH

  • Returns the hyperbolic tangent of a number.
  • =TANH(1)

DEGREES

  • Converts radians to degrees.
  • =DEGREES(PI())

RADIANS

  • Converts degrees to radians.
  • =RADIANS(180)

MDETERM

  • Returns the matrix determinant of an array.
  • =MDETERM(A1:B2)

MINVERSE

  • Returns the matrix inverse of an array.
  • =MINVERSE(A1:B2)

MMULT

  • Returns the matrix product of two arrays.
  • =MMULT(A1:B2, C1:D2)

GCD

  • Returns the greatest common divisor.
  • =GCD(24, 36)

LCM

  • Returns the least common multiple.
  • =LCM(24, 36)

COMBIN

  • Returns the number of combinations for a given number of items.
  • =COMBIN(8, 2)

PERMUT

  • Returns the number of permutations for a given number of objects.
  • =PERMUT(8, 2)

FACT

  • Returns the factorial of a number.
  • =FACT(5)

FACTDOUBLE

  • Returns the double factorial of a number.
  • =FACTDOUBLE(5)

SQRTPI

  • Returns the square root of a number multiplied by pi.
  • =SQRTPI(3)

MULTINOMIAL

  • Returns the multinomial of a set of numbers.
  • =MULTINOMIAL(1, 2, 3)

ACCRINT

  • Returns the accrued interest for a security that pays periodic interest.
  • =ACCRINT(01-Jan-2020, 01-Jan-2021, 01-Jul-2020, 0.1, 1000, 2)

ACCRINTM

  • Returns the accrued interest for a security that pays interest at maturity.
  • =ACCRINTM(01-Jan-2020, 01-Jan-2021, 0.1, 1000)

AMORDEGRC

  • Returns the depreciation for each accounting period using a depreciation coefficient.
  • =AMORDEGRC(1000, 01-Jan-2020, 01-Jan-2021, 10, 0.2, 3)

AMORLINC

  • Returns the depreciation for each accounting period.
  • =AMORLINC(1000, 01-Jan-2020, 01-Jan-2021, 10, 0.2, 3)

COUPDAYBS

  • Returns the number of days from the beginning of the coupon period to the settlement date.
  • =COUPDAYBS(01-Jan-2020, 01-Jul-2020, 2)

COUPDAYS

  • Returns the number of days in the coupon period that contains the settlement date.
  • =COUPDAYS(01-Jan-2020, 01-Jul-2020, 2)

COUPDAYSNC

  • Returns the number of days from the settlement date to the next coupon date.
  • =COUPDAYSNC(01-Jan-2020, 01-Jul-2020, 2)

COUPNCD

  • Returns the next coupon date after the settlement date.
  • =COUPNCD(01-Jan-2020, 01-Jul-2020, 2)

COUPNUM

  • Returns the number of coupons payable between the settlement date and maturity date.
  • =COUPNUM(01-Jan-2020, 01-Jan-2025, 2)

COUPPCD

  • Returns the previous coupon date before the settlement date.
  • =COUPPCD(01-Jan-2020, 01-Jul-2020, 2)

DISC

  • Returns the discount rate for a security.
  • =DISC(01-Jan-2020, 01-Jan-2021, 97, 100)

DURATION

  • Returns the annual duration of a security with periodic interest payments.
  • =DURATION(01-Jan-2020, 01-Jan-2025, 0.05, 0.06, 2)

EFFECT

  • Returns the effective annual interest rate.
  • =EFFECT(0.1, 12)

INTRATE

  • Returns the interest rate for a fully invested security.
  • =INTRATE(01-Jan-2020, 01-Jan-2021, 97, 100)

ISPMT

  • Calculates the interest paid during a specific period of an investment.
  • =ISPMT(0.05/12, 1, 60, 5000)

MDURATION

  • Returns the Macauley modified duration for a security with an assumed par value of $100.
  • =MDURATION(01-Jan-2020, 01-Jan-2025, 0.05, 0.06, 2)

ODDFPRICE

  • Returns the price per $100 face value of a security with an odd first period.
  • =ODDFPRICE(01-Jan-2020, 01-Jan-2025, 01-Jul-2020, 0.05, 0.06, 100)

ODDFYIELD

  • Returns the yield of a security with an odd first period.
  • =ODDFYIELD(01-Jan-2020, 01-Jan-2025, 01-Jul-2020, 0.05, 100, 100)

ODDLPRICE

  • Returns the price per $100 face value of a security with an odd last period.
  • =ODDLPRICE(01-Jan-2020, 01-Jan-2025, 01-Jul-2024, 0.05, 0.06, 100)

ODDLYIELD

  • Returns the yield of a security with an odd last period.
  • =ODDLYIELD(01-Jan-2020, 01-Jan-2025, 01-Jul-2024, 0.05, 100, 100)

PRICE

  • Returns the price per $100 face value of a security that pays periodic interest.
  • =PRICE(01-Jan-2020, 01-Jan-2025, 0.05, 0.06, 100)

PRICEDISC

  • Returns the price per $100 face value of a discounted security.
  • =PRICEDISC(01-Jan-2020, 01-Jan-2021, 0.05, 100)

PRICEMAT

  • Returns the price per $100 face value of a security that pays interest at maturity.
  • =PRICEMAT(01-Jan-2020, 01-Jan-2021, 0.05, 0.06, 100)

RECEIVED

  • Returns the amount received at maturity for a fully invested security.
  • =RECEIVED(01-Jan-2020, 01-Jan-2021, 0.05, 100)

TBILLEQ

  • Returns the bond-equivalent yield for a Treasury bill.
  • =TBILLEQ(01-Jan-2020, 01-Apr-2020, 0.05)

TBILLPRICE

  • Returns the price per $100 face value for a Treasury bill.
  • =TBILLPRICE(01-Jan-2020, 01-Apr-2020, 0.05)

TBILLYIELD

  • Returns the yield for a Treasury bill.
  • =TBILLYIELD(01-Jan-2020, 01-Apr-2020, 95)

YIELD

  • Returns the yield on a security that pays periodic interest.
  • =YIELD(01-Jan-2020, 01-Jan-2025, 0.05, 100, 100)

YIELDDISC

  • Returns the annual yield for a discounted security.
  • =YIELDDISC(01-Jan-2020, 01-Jan-2021, 95)

YIELDMAT

  • Returns the annual yield of a security that pays interest at maturity.
  • =YIELDMAT(01-Jan-2020, 01-Jan-2021, 0.05, 100, 95)

109 thoughts on “Excel Functions Cheat Sheet (100)”

  1. interested in electronic math to mechanical conversion

  2. I fancy EXCEL & where ever possible I try to programe it. PLEASE help me with all the functions & their examples. Have done it for Numerology, Family genealogy, now Family trust fund am stuck with the “AND” function of using it. Please help me.83 years age. NO Website, wish I can develop one.

  3. This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?

  4. Please help. I have a column with a title.
    If it has been checked how to I copy the title to another cell?

Comments are closed.