From data to report in one minute or less. Ajelix BI is launched! 🚀 Get started for free.

# Excel Formula Cheat Sheet

This Microsoft Excel Formula Cheat Sheet provides a quick reference guide to the most commonly used formulas and functions in Excel. From basic math to advanced statistical functions, this cheat sheet has all the formulas and shortcuts you need to work with data and make calculations in Excel.

## Math & Trigonometry Formulas

Excel Formula Cheat Sheet

SUM
Calculates the sum of a range of cells.

=SUM(A1:A7) to calculate the sum of numbers in cells A1 through A7.

AVERAGE
Calculates the average of a range of cells.

=AVERAGE(A1:A7) to calculate the average of numbers in cells A1 through A7.

MEDIAN
Calculates the median of a range of cells.

=MEDIAN(A2:A10)to calculate the median of numbers in cells A2 through A10

MOD
Calculates the remainder of a division operation.

=MOD(A1,5) calculates the remainder of the value in cell A1 divided by 5.

POWER
Calculates the result of a number raised to a power.

=POWER(A1,2) calculates the value in cell A1 raised to the power of 2

SQRT
Calculates the square root of a number.

=SQRT(A1) calculates the square root of the value in cell A1.

SIN
Calculates the sine of a number.

=SIN(A1) calculates the sine of the value in cell A1.

COS
Calculates the cosine of a number.

=COS(A1) calculates the cosine of the value in cell A1.

TAN
Calculates the tangent of a number.

=TAN(A1) calculates the tangent of the value in cell A1.

DEGREES
Converts an angle from radians to degrees.

=DEGREES(A1) converts the angle in cell A1 from radians to degrees.

Converts an angle from degrees to radians.

=RADIANS(A1) converts the angle in cell A1 from degrees to radians.

PI
Returns the value of pi.

=PI() returns the value of pi.

ROUND
Rounds a number to a specified number of digits.

=ROUND(A1,2) rounds the number in cell A1 to two decimal places.

Struggling with Excel formula creation? Try AI Formula Generator for free.

## Statistical Formulas

MAX
Returns the largest value in a range of cells.

=MAX(A2:A10) to find the maximum value in cells A2 through A10.

MIN
Returns the smallest value in a range of cells.

=MIN(A2:A10) to find the minimum value in cells A2 through A10.

COUNTIF
Counts the number of cells in a range that meet specified criteria.

=COUNTIF(A2:A10,”apple”) to count the number of cells in range A2 through A10 that contain the value “apple”.

SUMIF
Returns the sum of a range of cells that meet specified criteria.

=SUMIF(A2:A10,”apple”) to sum the values in cells A2 through A10 that contain the value “apple”, use the formula.

AVERAGEIF
Returns the average of a range of cells that meet specified criteria.

=AVERAGEIF(A2:A10,”apple”) to calculate the average of cells A2 through A10 that contain the value “apple”

STDEV
Returns the population standard deviation of a range of numbers.

=STDEV(A2:A10) to calculate the population standard deviation of numbers in cells A2 through A10.

VAR
Returns the population variance of a range of numbers.

=VAR(A2:A10) to calculate the population variance of numbers in cells A2 through A10.

VARP
Returns the sample variance of a range of numbers.

=VARP(A2:A10) to calculate the sample variance of numbers in cells A2 through A10.

PERCENTILE
Returns the nth percentile of a range of numbers.

=PERCENTILE(A2:A10,0.90) to calculate the 90th percentile of numbers in cells A2 through A10

QUARTILE
Returns the nth quartile of a range of numbers.

=QUARTILE(A2:A10,3) to calculate the third quartile of numbers in cells A2 through A10.

SKEW
Returns the skewness of a range of numbers.

=SKEW(A2:A10) to calculate the skewness of numbers in cells A2 through A10.

KURT
Returns the kurtosis of a range of numbers.

=KURT(A2:A10) to calculate the kurtosis of numbers in cells A2 through A10.

CORREL
Returns the correlation coefficient of two ranges of numbers.

=CORREL(A2:A10,B2:B10) correlation coefficient of two ranges of numbers in cells A2 through A10 and B2 through B10

FORECAST
Returns a value along a linear trend.

=FORECAST(X,A2:A10,B2:B10) value Y for a given value X based on a linear trend in cells A2 through A10 and B2 through B10.

SLOPE
Returns the slope of a linear regression line.

=SLOPE(A2:A10,B2:B10) to calculate the slope of a linear regression line fitted to the data in cells A2 through A10 and B2 through B10.

Register for free and let AI Excel Assistant generate and explain Excel and Google Sheets formulas for you.

## Logical Formulas

IF/THEN
If a certain condition is true, then a certain value is returned.

=IF(A2=2,”Yes“,”No“) would returnYes if cell A2 equals 2, andNo if it does not.

AND
This function returns TRUE if all of the conditions are met.

=AND(A2=2,B2=3) would return TRUE if A2 equals 2 and B2 equals 3.

OR
This function returns TRUE if any of the conditions are met.

=OR(A2=2,B2=3) would return TRUE if A2 equals 2 or B2 equals 3.

NOT
This function returns the opposite of a condition.

=NOT(A2=2) would return TRUE if A2 does not equal 2.

IFERROR
This function returns a specified value if an error occurs.

=IFERROR(A2/B2,0) would return 0 if there is an error in the division of A2 by B2.

COUNTIF
This function counts the number of cells that meet a certain condition.

=COUNTIF(A2:A10,”Yes“) would count the number of cells in the range A2 to A10 that contain the valueYes“.

SUMIF
This function adds up the values of cells that meet a certain condition.

SUMIF(A2:A10,”Yes“,B2:B10) would add up the values in the range B2 to B10 if the corresponding cell in the range A2 to A10 contains the valueYes“.

MINIF
This function returns the minimum value of cells that meet a certain condition.

=MINIF(A2:A10,”Yes“,B2:B10) would return the minimum value in the range B2 to B10 if the corresponding cell in the range A2 to A10 contains the valueYes“.

MAXIF
This function returns the maximum value of cells that meet a certain condition.

=MAXIF(A2:A10,”Yes“,B2:B10) would return the maximum value in the range B2 to B10 if the corresponding cell in the range A2 to A10 contains the valueYes“.

MATCH
This function returns the position of a value in a range.

=MATCH(A2,B2:B10,0) would return the position of the value in A2 in the range B2 to B10.

ROUND
This function rounds a number to a specified number of decimal places.

=ROUND(A2,2) would round the value in A2 to two decimal places.

CEILING
This function rounds a number up to the nearest integer or multiple of the specified number.

=CEILING(A2,2) would round the value in A2 up to the nearest multiple of 2.

FLOOR
This function rounds a number down to the nearest integer or multiple of the specified number.

=FLOOR(A2,2) would round the value in A2 down to the nearest multiple of 2.

ABS
This function returns the absolute value of a number.

=ABS(A2) would return the absolute value of the value in A2.

CONCATENATE
This function combines multiple strings into a single string.

=CONCATENATE(A2,B2,C2) would combine the values in cells A2, B2, and C2 into a single string.

LEN
This function returns the length of a string.

=LEN(A2) would return the length of the string in cell A2.

LEFT
This function returns a specified number of characters from the left side of a string.

=LEFT(A2,3) would return the first three characters from the string in cell A2.

RIGHT
This function returns a specified number of characters from the right side of a string.

=RIGHT(A2,3) would return the last three characters from the string in cell A2.

FIND
This function returns the starting position of a substring in a string.

=FIND(“text“,A2,1) would return the starting position of the substringtext in the string in cell A2, starting from the first character.

Register for free and let AI Excel Assistant generate and explain Excel and Google Sheets formulas for you.

## Text Formulas

DATE
Returns the current date.

=DATE()

TIME
Returns the current time.

=TIME()

CONCATENATE
Combines two or more text strings into one string.

=CONCATENATE(A1,B1) combines the text in cells A1 and B1 into one string.

IF
Tests a condition and returns one value if the condition is met and another value if the condition is not met.

=IF(A1>B1,Yes“,No“) returnsYes if the value in cell A1 is greater than the value in cell B1 andNo if the condition is not met.

AND
Returns TRUE if all of the conditions are met and FALSE if any of the conditions are not met.

=AND(A1>B1, B1>C1) returns TRUE if the value in cell A1 is greater than the value in cell B1 and the value in cell B1 is greater than the value in cell C1 and FALSE if any of the conditions are not met.

OR
Returns TRUE if any of the conditions are met and FALSE if all of the conditions are not met.

=OR(A1>B1, B1>C1) returns TRUE if the value in cell A1 is greater than the value in cell B1 or the value in cell B1 is greater than the value in cell C1 and FALSE if both of the conditions are not met.

LEFT
Returns a specified number of characters from the left side of a text string.

=LEFT(A1,3) returns the first three characters from the left side of the text in cell A1.

MID
Returns a specified number of characters from the middle of a text string.

=MID(A1,2,4) returns the four characters from the middle of the text in cell A1 starting at the second character.

RIGHT
Returns a specified number of characters from the right side of a text string.

=RIGHT(A1,3) returns the last three characters from the right side of the text in cell A1.

LEN
Returns the number of characters in a text string.

=LEN(A1) returns the number of characters in the text in cell A1.

FIND
Locates a character or text string within another text string and returns the position of the character or text string.

=FIND(“Hello“,A1) returns the position of the text stringHello in the text in cell A1.

SUBSTITUTE
Replaces a character or text string with another character or text string in a text string.

=SUBSTITUTE(A1,”Hello“,”Goodbye“) replaces the text stringHello in the text in cell A1 with the text stringGoodbye“.

VLOOKUP
Looks for a value in the leftmost column, and returns a value in the same row from another column.

=VLOOKUP(A1,B1:C6,2,FALSE) looks for the value in cell A1 in the leftmost column of the table in range B1 through C6, and then returns the value in the same row from the second column of the table.

HLOOKUP
Looks for a value in the top row of a table, and returns a value in the same column from another row in the table.

=HLOOKUP(A1,B1:C6,2,FALSE) looks for the value in cell A1 in the top row of the table in range B1 through C6, and then returns the value in the same column from the second row of the table.

Register for free and let AI Excel Assistant generate and explain Excel and Google Sheets formulas for you.

## Financial Formulas

PMT
Formula calculates the payment for a loan.

=PMT(5%,10,-1000) to calculate the payment for a loan of \$1000 with an interest rate of 5% and a term of 10 years

FV
This formula calculates the future value of an investment.

=FV(5%,10,1000) to calculate the future value of an investment of \$1000 with an interest rate of 5% and a term of 10 years

PV
This formula calculates the present value of an investment.

=PV(5%,10,-1000) to calculate the present value of an investment of \$1000 with an interest rate of 5% and a term of 10 years

NPV
This formula calculates the net present value of multiple cash flows.

=NPV(5%,1000,2000,3000) to calculate the net present value of the cash flows of \$1000, \$2000 and \$3000 with an interest rate of 5%.

IRR
Internal rate of return of multiple cash flows.

=IRR(1000,2000,3000) to calculate the internal rate of return of the cash flows of \$1000, \$2000 and \$3000.

DDB
Depreciation amount for an asset using the double-declining balance method.

=DDB(1000,200,5) to calculate the depreciation amount for an asset with a cost of \$1000 and a salvage value of \$200 and a useful life of 5 years

SLN
Depreciation amount for an asset using the straight-line method.

=SLN(1000,200,5) to calculate the depreciation amount for an asset with a cost of \$1000 and a salvage value of \$200 and a useful life of 5 years.

XNPV
Net present value of multiple cash flows with dates.

=XNPV(5%,1000,DATE(2021,1,1),2000,DATE(2022,1,1),3000,DATE(2023,1,1)) to calculate the net present value of the cash flows of \$1000 on 1/1/2021, \$2000 on 1/1/2022 and \$3000 on 1/1/2023 with an interest rate of 5%.

XIRR
Internal rate of return of multiple cash flows with dates.

=XIRR(1000,DATE(2021,1,1),2000,DATE(2022,1,1),3000,DATE(2023,1,1)) To calculate the internal rate of return of the cash flows of \$1000 on 1/1/2021, \$2000 on 1/1/2022 and \$3000 on 1/1/2023

CUMIPMT
Cumulative interest payments for a loan.

=CUMIPMT(5%,10,-1000) to calculate the cumulative interest payments for a loan of \$1000 with an interest rate of 5% and a term of 10 years.

CUMPRINC
Cumulative principal payments for a loan.

=CUMPRINC(5%,10,-1000) to calculate the cumulative principal payments for a loan of \$1000 with an interest rate of 5% and a term of 10 years.

PVIF
Present value interest factor.

=PVIF(5%,10) to calculate the present value interest factor for an interest rate of 5% and a term of 10 years.

FVIFA
This formula calculates the future value interest factor.

=FVIFA(5%,10) to calculate the future value interest factor for an interest rate of 5% and a term of 10 years.

NPER
This formula calculates the number of periods for a loan.

=NPER(5%,50,-1000) to calculate the number of periods for a loan of \$1000 with an interest rate of 5% and a payment of \$50.

RATE
This formula calculates the interest rate for a loan.

=RATE(10,-50,-1000) to calculate the interest rate for a loan of \$1000 with a payment of \$50 and a term of 10 years.

DB
This formula calculates the depreciation amount for an asset.

=DB(1000,200,5) to calculate the depreciation amount for an asset with a cost of \$1000 and a salvage value of \$200 and a useful life of 5 years.

VDB
Depreciation amount for an asset using the variable declining balance method.

=VDB(1000,200,5,20%) to calculate the depreciation amount for an asset with a cost of \$1000 and a salvage value of \$200 and a useful life of 5 years and a depreciation rate of 20%.

XIRR
Internal rate of return of multiple cash flows with dates.

=XIRR(1000,DATE(2021,1,1),2000,DATE(2022,1,1),3000,DATE(2023,1,1)) to calculate the internal rate of return of the cash flows of \$1000 on 1/1/2021, \$2000 on 1/1/2022 and \$3000 on 1/1/2023.

Register for free and let AI Excel Assistant generate and explain Excel and Google Sheets formulas for you.

Struggling with your Excel formulas?
Looking for a faster and easier way to write Excel formulas? Try AI Excel Formula Generator and turn your text into formulas with just a few clicks.

### Struggling with your Excel formulas?

Check out our blog for the latest tips and tricks to help you become an expert. We have the insider scoop on the best strategies and resources to help you stay ahead of the curve. Click here to read our blog and start your journey towards Excel and Google Sheets mastery today!

#### Business Intelligence platform meets AI

Trusted by 150'000 customers around the globe since 2018.

© 2018 - 2024 AJELIX, SIA