Do you ever struggle with remembering all the Microsoft Excel formulas? Well, fret not! We’ve got you covered with this handy cheat sheet that you can use to your heart’s content.
Excel Formulas Cheat Sheet
This comprehensive cheat sheet has all the formulas that you will ever need to use in Excel. Whether you’re a beginner or an advanced user, this cheat sheet will save you time and make your life a whole lot easier.
How to use the cheat sheet
The cheat sheet is organized by formula categories, making it easy for you to find the formula you need quickly. Each category is color-coded, making it easy to differentiate between them. All you need to do is find the formula you need, and you’re good to go!
Formula categories
- Math & Trig
- Statistical
- Date & Time
- Lookup & Reference
- Logical
- Text
- Information
- Financial
- Engineering
Math & Trig Formulas
Math & Trig formulas are used to perform basic mathematical operations, such as addition, subtraction, multiplication, and division. They also include more advanced formulas, such as exponents, logarithms, and trigonometric functions.
SUM Formula
The SUM formula is used to add up a range of numbers. For example, if you have a range of cells that contain numbers, you can use the SUM formula to add them up quickly.
For example, if you want to add up the numbers in cells A1 through A5, you would use the following formula:
=SUM(A1:A5)
AVERAGE Formula
The AVERAGE formula is used to calculate the average of a range of numbers. For example, if you have a range of cells that contain test scores, you can use the AVERAGE formula to calculate the average score.
For example, if you want to calculate the average of the numbers in cells A1 through A5, you would use the following formula:
=AVERAGE(A1:A5)
MAX Formula
The MAX formula is used to find the maximum value in a range of numbers. For example, if you have a range of cells that contain sales figures, you can use the MAX formula to find the highest sales figure.
For example, if you want to find the highest sales figure in cells A1 through A5, you would use the following formula:
=MAX(A1:A5)
MIN Formula
The MIN formula is used to find the minimum value in a range of numbers. For example, if you have a range of cells that contain expenses, you can use the MIN formula to find the lowest expense.
For example, if you want to find the lowest expense in cells A1 through A5, you would use the following formula:
=MIN(A1:A5)
Statistical Formulas
Statistical formulas are used to perform statistical analysis on data. They include formulas to calculate averages, variances, standard deviations, and other statistical measures.
AVERAGEIF Formula
The AVERAGEIF formula is used to calculate the average of a range of numbers that meet a certain criteria. For example, if you have a range of cells that contain test scores for a class, you can use the AVERAGEIF formula to calculate the average score for only the students who passed the test.
For example, if you want to calculate the average test score for only the students who passed the test (which has a passing score of 70), and the test scores are in cells A1 through A10, and the pass/fail status is in cells B1 through B10, you would use the following formula:
=AVERAGEIF(B1:B10, “Pass”, A1:A10)
COUNT Formula
The COUNT formula is used to count the number of cells in a range that contain a number or a date. For example, if you have a range of cells that contains test scores, you can use the COUNT formula to count the number of students who took the test.
For example, if you want to count the number of students who took the test, and the test scores are in cells A1 through A10, you would use the following formula:
=COUNT(A1:A10)
COUNTIF Formula
The COUNTIF formula is used to count the number of cells in a range that meet a certain criteria. For example, if you have a range of cells that contain test scores for a class, you can use the COUNTIF formula to count the number of students who passed the test.
For example, if you want to count the number of students who passed the test (which has a passing score of 70), and the test scores are in cells A1 through A10, and the pass/fail status is in cells B1 through B10, you would use the following formula:
=COUNTIF(B1:B10, “Pass”)
Date & Time Formulas
Date & Time formulas are used to perform operations on dates and times. They include formulas to calculate the difference between two dates, add or subtract days from a date, and much more.
TODAY Formula
The TODAY formula is used to insert the current date into a cell. For example, if you want to have the current date in a cell, you would use the TODAY formula.
Simply type “=TODAY()” into the cell and press Enter. The current date will be displayed.
DATEDIF Formula
The DATEDIF formula is used to calculate the difference between two dates. For example, if you want to know how many days there are between two dates, you can use the DATEDIF formula.
For example, if you want to find the difference in days between 1/1/2022 and 1/10/2022, you would use the following formula:
=DATEDIF(“1/1/2022”, “1/10/2022”, “d”)
EDATE Formula
The EDATE formula is used to add or subtract a specified number of months to a date. For example, if you want to know what date it will be six months from today, you can use the EDATE function.
For example, if today’s date is in cell A1 and you want to know what the date will be six months from today, you would use the following formula:
=EDATE(A1, 6)
Lookup & Reference Formulas
Lookup & Reference formulas are used to search for specific data in a range of cells. They include formulas to find data based on a specific criteria, search for the highest or lowest value in a range, and much more.
VLOOKUP Formula
The VLOOKUP formula is used to find data in a range based on a specific criteria. For example, if you have a table that contains sales data and you want to find the sales figures for a specific product, you can use the VLOOKUP formula.
For example, if you have a table of sales data in cells A1 through C10, and you want to find the sales figures for Product A (which is in cell A15), you would use the following formula:
=VLOOKUP(A15, A1:C10, 3, FALSE)
HLOOKUP Formula
The HLOOKUP formula is similar to the VLOOKUP formula, but it searches for data in a row instead of a column. For example, if you have a table of sales data with the products listed horizontally instead of vertically, you can use the HLOOKUP formula to find the sales figures for a specific product.
For example, if you have a table of sales data in cells A1 through C5, and you want to find the sales figures for Product A, which is listed in cell G1, you would use the following formula:
=HLOOKUP(G1, A1:C5, 2, FALSE)
Logical Formulas
Logical formulas are used to test a condition and return a certain value depending on whether the condition is true or false. They include formulas to test for equality, inequality, and much more.
IF Formula
The IF formula is used to test a condition and return a certain value depending on whether the condition is true or false. For example, if you have a range of cells that contain test scores, and you want to see if a student passed or failed the test, you can use the IF formula.
For example, if you have a student’s test score in cell B1, and the passing score is 70, you would use the following formula:
=IF(B1>=70, “Pass”, “Fail”)
AND Formula
The AND formula is used to test whether multiple conditions are true. For example, if you have a range of cells that contain test scores, and you want to see if a student passed both tests, you can use the AND formula.
For example, if you have a student’s scores for two tests in cells B1 and C1, and the passing score is 70 for both tests, you would use the following formula:
=AND(B1>=70, C1>=70)
OR Formula
The OR formula is used to test whether at least one of multiple conditions is true. For example, if you have a range of cells that contain test scores, and you want to see if a student passed at least one test, you can use the OR formula.
For example, if you have a student’s scores for two tests in cells B1 and C1, and the passing score is 70 for at least one test, you would use the following formula:
=OR(B1>=70, C1>=70)
Text Formulas
Text formulas are used to manipulate text in Excel. They include formulas to concatenate text, extract specific characters from a string of text, and much more.
CONCATENATE Formula
The CONCATENATE formula is used to join two or more cells together into one cell. For example, if you have a first name in cell A1 and a last name in cell B1, you can use the CONCATENATE formula to join them together.
For example, if you want to join the first name and last name into one cell, you would use the following formula:
=CONCATENATE(A1, " “, B1)
LEFT Formula
The LEFT formula is used to extract a specified number of characters from the beginning of a string of text. For example, if you have a list of email addresses and you want to extract the first part of the email address (the username), you can use the LEFT formula.
For example, if you have an email address in cell A1 and you want to extract the username, which is the part of the email address before the “@” symbol, you would use the following formula:
=LEFT(A1, FIND(”@", A1)-1)
RIGHT Formula
The RIGHT formula is similar to the LEFT formula, but it extracts a specified number of characters from the end of a string of text. For example, if you have a list of phone numbers and you want to extract the last four digits, you can use the RIGHT formula.
For example, if you have a phone number in cell A1 and you want to extract the last four digits, you would use the following formula:
=RIGHT(A1, 4)
Information Formulas
Information formulas are used to return information about your Excel workbook or a specific cell. They include formulas to show the current date and time, the path of the current workbook, and much more.
NOW Formula
The NOW formula is used to insert the current date and time into a cell. For example, if you want to have the current date and time in a cell, you would use the NOW formula.
Simply type “=NOW()” into the cell and press Enter. The current date and time will be displayed.
CELL Formula
The CELL formula is used to return information about a specific cell. For example, if you want to know the row number or column letter of a specific cell, you can use the CELL formula.
For example, if you want to know the row number of cell A1, you would use the following formula:
=CELL(“row”, A1)
Financial Formulas
Financial formulas are used to perform financial calculations, such as calculating the present value of an investment, or the payments on a loan.
PV Formula
The PV formula is used to calculate the present value of an investment. For example, if you are considering an investment that will pay $1,000 in one year, and you want to know what that investment is worth today, you can use the PV formula.
For example, if the interest rate is 5%, and the investment will pay $1,000 in one year, you would use the following formula:
=PV(5%, 1, 0, 1000)
PMT Formula
The PMT formula is used to calculate the payments on a loan. For example, if you are considering a loan for $10,000 at an interest rate of 5%, with a repayment period of five years, you can use the PMT formula to calculate the monthly payments.
For example, if the loan amount is $10,000, the interest rate is 5%, and the repayment period is five years, you would use the following formula:
=PMT(5%/12, 5*12, 10000)
Engineering Formulas
Engineering formulas are used to perform calculations related to engineering, such as calculating the force required to lift a weight, or the torque required to turn a wheel.
FORCE Formula
The FORCE formula is used to calculate the force required to lift a weight. For example, if you need to lift a weight of 100 pounds, you can use the FORCE formula to calculate how much force is required.
For example, if the weight is 100 pounds, and the acceleration due to gravity is 32.2 feet per second squared, you would use the following formula:
=FORCE(100, 32.2)
TORQUE Formula
The TORQUE formula is used to calculate the torque required to turn a wheel. For
