- Home
- Data Visualization
- Tools
- Excel Formula Generator
- Excel Formula Explainer
- Google Apps Script Generator
- Excel VBA Script Explainer
- Excel VBA Script Generator
- Excel VBA Code Optimizer
- Excel VBA Code Debugger
- Google Sheets Formula Generator
- Google Apps Script Explainer
- Google Sheets Formula Explainer
- Google Apps Script Optimizer
- Google Apps Script Debugger
- Excel File Translator
- Excel Template Generator
- Excel Add-in
- Your Virtual AI Assistant For Excel Spreadsheets
- AI Answer Generator
- AI Math Solver
- AI Graph Generator
- AI SQL Generator
- Pricing
- Resources
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
| Formula | Description | Example | More information |
|---|---|---|---|
SUM | Calculates the sum of a range of cells. | =SUM(A1:A7) to calculate the sum of numbers in cells A1 through A7. | Details |
AVERAGE | Calculates the average of a range of cells. | =AVERAGE(A1:A7) to calculate the average of numbers in cells A1 through A7. | Details |
MEDIAN | Calculates the median of a range of cells. | =MEDIAN(A2:A10)to calculate the median of numbers in cells A2 through A10 | Details |
MOD | Calculates the remainder of a division operation. | =MOD(A1,5) calculates the remainder of the value in cell A1 divided by 5. | Details |
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 | Details |
SQRT | Calculates the square root of a number. | =SQRT(A1) calculates the square root of the value in cell A1. | Details |
SIN | Calculates the sine of a number. | =SIN(A1) calculates the sine of the value in cell A1. | Details |
COS | Calculates the cosine of a number. | =COS(A1) calculates the cosine of the value in cell A1. | Details |
TAN | Calculates the tangent of a number. | =TAN(A1) calculates the tangent of the value in cell A1. | Details |
DEGREES | Converts an angle from radians to degrees. | =DEGREES(A1) converts the angle in cell A1 from radians to degrees. | Details |
RADIANS | Converts an angle from degrees to radians. | =RADIANS(A1) converts the angle in cell A1 from degrees to radians. | Details |
PI | Returns the value of pi. | =PI() returns the value of pi. | Details |
ROUND | Rounds a number to a specified number of digits. | =ROUND(A1,2) rounds the number in cell A1 to two decimal places. | Details |
Struggling with Excel formula creation? Try AI Formula Generator for free.
Statistical Formulas
| Formula | Description | Example | More information |
|---|---|---|---|
MAX | Returns the largest value in a range of cells. | =MAX(A2:A10) to find the maximum value in cells A2 through A10. | Details |
MIN | Returns the smallest value in a range of cells. | =MIN(A2:A10) to find the minimum value in cells A2 through A10. | Details |
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”. | Details |
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. | Details |
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” | Details |
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. | Details |
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. | Details |
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. | Details |
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 | Details |
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. | Details |
SKEW | Returns the skewness of a range of numbers. | =SKEW(A2:A10) to calculate the skewness of numbers in cells A2 through A10. | Details |
KURT | Returns the kurtosis of a range of numbers. | =KURT(A2:A10) to calculate the kurtosis of numbers in cells A2 through A10. | Details |
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 | Details |
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. | Details |
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. | Details |
Register for free and let AI Excel Assistant generate and explain Excel and Google Sheets formulas for you.
Logical Formulas
| Formula | Description | Example | More information |
|---|---|---|---|
IF/THEN | If a certain condition is true, then a certain value is returned. | =IF(A2=2,”Yes“,”No“) would return “Yes“ if cell A2 equals 2, and “No“ if it does not. | Details |
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. | Details |
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. | Details |
NOT | This function returns the opposite of a condition. | =NOT(A2=2) would return TRUE if A2 does not equal 2. | Details |
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. | Details |
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 value “Yes“. | Details |
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 value “Yes“. | Details |
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 value “Yes“. | Details |
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 value “Yes“. | |
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. | Details |
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. | Details |
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. | Details |
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. | Details |
ABS | This function returns the absolute value of a number. | =ABS(A2) would return the absolute value of the value in A2. | Details |
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. | Details |
LEN | This function returns the length of a string. | =LEN(A2) would return the length of the string in cell A2. | Details |
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. | Details |
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. | Details |
FIND | This function returns the starting position of a substring in a string. | =FIND(“text“,A2,1) would return the starting position of the substring “text“ 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
| Formula | Description | Example | More information |
|---|---|---|---|
DATE | Returns the current date. | =DATE() | Details |
TIME | Returns the current time. | =TIME() | Details |
CONCATENATE | Combines two or more text strings into one string. | =CONCATENATE(A1,B1) combines the text in cells A1 and B1 into one string. | Details |
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“) returns “Yes“ if the value in cell A1 is greater than the value in cell B1 and “No“ if the condition is not met. | Details |
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. | Details |
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. | Details |
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. | Details |
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. | Details |
LEN | Returns the number of characters in a text string. | =LEN(A1) returns the number of characters in the text in cell A1. | Details |
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 string “Hello“ 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 string “Hello“ in the text in cell A1 with the text string “Goodbye“. | |
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. | Details |
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
| Formula | Description | Example |
|---|---|---|
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!
