Home / Excel Functions

Excel Functions Cheat Sheet (100)

- Written by Puneet

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?

  6. Thank you sir I must so help ful.

  7. =search(“9″,”99 9000”) = result is 1
    How about another “9” position? What is formula?

  8. Sir, 2nd Sept.2019.
    Really you have shown very clearly and deeply all points.
    I must thank you.
    Hoping to receive more articles in future too.

    Kanhaiyalal Newaskar.

  12. Thank you for working hard to help others. Your generous effort will be put into good use.

  13. Thank you for your hard work for helping out us…

  15. This is amazing and very generous of you…many thanks…

  16. 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?

  17. Lavoro ottimo e ++ utile. Grazie e complimenti

  19. we are here because you and your work is awesome. 😉

  20. Keep on sharing. This help us a lot. Its a time save for us and no need to search each functions from net cause its already here.

  21. Hi, I find your Excel tips very useful. It helps me a lot. Tips likes this give me a lesser time to search for the uses and functions of each formulas. Thanks a lot.

  22. Very useful information..thank you sir

  23. Sir, very useful information,notes and formula you have given.User like me would appreciate the work.
    Once again thanks.

  24. Thank you very much Mr. Punit Sir. I am computer faculty and recently I have asked to teach Excel functions and I find the complete list here. Could you possibly provide us the PDF format of all the functions with examples to use. Thank you.

  25. Thanks a lot for sharing your knowledge with us with clear explanation. Your blog is my guru. All your excel tips are being very useful for my work. Thanks again.

  26. GDay Punit

    Needed a refresher, thanks for the info.

  27. First of All.. Thank you for sharing the above info.. I fully appreciate this gesture..

  28. A honest effort with intention of educating…. Really Super …………..

  29. Hello Punit,

    I am very thankful to you as you have start this learning portal for all. And thanks for giving ur stuff from this web portal i will be able to use excel smartly and efficiently. I learn so far with this really Excellent work ur doing
    Thanks a lot again.

  31. Dear Puneet thanks, i want learn Macro in detail, can you help me
    Thanks
    Nitin

  32. Please check each category. some files are not there or linked wrong

  33. Dear Puneet,
    Excellent work. Many files are not working or the link is wrong.

    Thanks Rashid

  34. Dear Puneet,
    Excellent work. Many files are working or the link is wrong.

    Thanks Rashid

  35. Hi Puneet,

    I want to learn macro VBA step by step please help me.. you are doing really great job

  37. Thank you so much puneet. It is very nice tutoral.

  40. Thank you so much Puneet Gogia and nice to see the whole functions list in online,it is helpful to me.I’m really appreciate to you… i want to learn VBA pls need you guideline (confusion in coding)

  41. Hi, Dear Punit,

    I am very thankful to you as you have start this learning portal for all. In my office people are jealous they don’t give me chance to learn excel and no one guide me how to use excel smartly even though many of them are working smartly in excel. But from your this web portal i will be able to use excel smartly and efficiently. Thanks a lot again.

  42. (Maraming salamat po sayo Puneet..) Thank you so much Puneet. It’s a great help for me to learn more from you

  44. It will be better if you put more information about Excel macros. Thank you for your contributions in order to make people’s working lives easier. Keep up the good work.

  45. my self deepak,
    thanks for this, good job
    kindly provide advance vba/excle programes

  46. Great Puneet.. You made wonderful work to make everyone expert in Excel.

  47. I want to have an example as well after explaining

  48. after long time i find such amazing summary about excel function …thanxs alot sir

  49. Gogia sir, plz suggest solution, if any

    • Sir it was done manually just to let you explain my actual need, please suggest function, if you can help in this regard.

    • You have already sorted in above snapshot?

  51. please give formulas and function in the pdf formet

  53. Very useful tips like camera option, Picture graph thanks a lot

  54. Thanks a lot for providing a helpful resources. You did a excellent job. Thanks again…

  55. Are these available in a PDF or Word File? This is a good reference, but it’s hard to remember to come back here for the information. Having them as something you can download would be really helpful.

  56. How to get data of various sheets on Summarry sheet of the inventory register.

  57. Thank you for the valuable information…I would like to know one specific thing.
    Once we use the transpose, only values are copied. If i want to retain the values with formula in transpose function, is it possible. This is very important to keep scientific way to prove that the transpose operation done is correct. In data analytics big excel sheets are handled and a small mistake can lead to big problems .. Please advise me ..

  58. How Can I creat a daly sale report in excel
    please help me

  59. Puneet, you are simply amazing.

  60. Thanks It is very useful to know which formula where to use. Thanks Puneet Bro

  61. Hi,
    Please provide the link for Inv. Management System

  62. very nice useful puneet how i download this excel function book

  63. How can I create a timezone convertor??

  64. Thanks punnet for providing such a good platform to learn excel

  66. with transpose function can we reverse the data set; like AAA, BBB, CCC transpose to CCC,BBB,AAA. Thanks

  67. Hi Puneet I think it is great if you combine these formulas in one store in PDF it is really awesome

  68. I’d love it.but it will be nice if you combine it in a one PDF file 🙂

  69. HI, is there any way to work out how long there is between 2 times? ie. 09:42 and 10:01 (that is, I want it to show 19 minutes)

    • Try,
      =TEXT(10:01-09:42,”HH:MM:SS”)

  70. SUBTOTAL – which I find is a powerful function, but often overlooked – particularly with the different function numbers that formula uses.

  71. GETPIVOTDATA, I haven’t nailed that one yet…dan.kordelski@gmaildotcom

  72. OFFSET and exact use of Array with detailed example.

  73. Punnet thanks, May I suggest this, an average subscriber fairly uses excel and functions but in isolation. Every one knows match, offset etc what most need is the combination of these functions to generate more potent practical problem solving forces is the next step I will like you to do after these functions in isolation.

    • Yeah Iyek. I am trying to give two different examples. one is solo & other is combined with some other function.

      • Great! Puneet. I am looking forward to this.

  76. I would love others to learn these functions “Indirect, Offset, Match, Index, Vlookup, Mid, Find, Replace, Substitute, Sumif, Sumifs, SumProduct mainly and of course array functions as well.

  77. I would really like to have a better understanding of the Indirect with Row and/or Columns function. Could we include these please.

  78. Offset plays a vital role

  79. Hi Puneet, I saw someone at work using both the INDEX and MATCH functions. I see that someone has already requested MATCH so if possible, I would like to see INDEX added to the list. Thank you!

    • Alright Diana. Thanks for your reply.

  80. Hello again, Puneet (and congratulations on your 1st 1,000 subscribers)! I would like to see the IFERROR function covered. I am just learning about it, so all lessons are greatly appreciated. Thank you for asking!

    • Thanks Brenda. I will for sure.

      You’ll get your E-book on 30 Apr

  81. i would like to Get a good description of the match function and/or the choose function. Thanks Torstein (sejohnse@yahoo.no)

    • Hey Torstein,

      You will get the both.

      Thanks for words.

      You’ll get your E-book on 30 Apr

  82. Hi, Can you add the sumproduct function. I want to know how you use it to bring back sums based on 3 or conditions. Thanks eannablack@gmail.com

    • Hello Eanna,

      Already Added.

      Thanks for your comment. You’ll get your E-book on 30 Apr

  83. very helpful, i really appreciate. keep it up Bro

  84. Nice to see the whole functions list..would appreciate if you let us know the complete descriptions of all functions.

  85. Great to have such a summary available

Comments are closed.