The Ultimate Excel Formulas Cheat Sheet: The 50 Most Important Formulas

Microsoft Excel is easy for anyone to begin. But a lot of advanced features are also hiding under the hood. 

Most people can do basic functions in Excel right away. But being able to do more sophisticated work in Excel first requires that you know what is available.

By reading through this cheat sheet, you’ll learn more about what Excel can do for you. And, after that, you can brush up on your Excel skills with our Advanced Excel article.

Excel shortcuts and commands

In addition to needing to know the core functions of Excel, you might want to learn a little more about shortcuts. Shortcuts and commands make it easier to do basic things in Excel because you don’t need to search through the menus. It’s easier to just press CTRL + D than it is to manually copy and paste.

Here are some of the most popular shortcuts:

Function	Shortcut
Insert Current Date	ctrl+;
Insert Current Time	shift+ctrl+;
Edit Cell Comment	shit+F2
Display Active Cell	ctrl+backspace
Insert Column	alt+lC
Insert Row	alt+lR
Fill Down	ctrl+D
Fill Right	ctrl+R
AutoSum	Alt+=
Insert Chart	Alt+F1
Save Workbook	shift+alt+F2
Move to Last	ctrl+END

For more shortcuts, check out our complete article of Excel Keyboard Shortcuts.

Excel cell reference cheat sheet

Most formulas in Excel require a cell reference. How you define the cell reference will affect how the formula is applied and copied from one to another. Following is the list of the most common ways to reference a cell in Excel.

Relative Cell Reference	=A2+B2
Absolute Cell Reference	+$A$1

Relative cell reference

A relative cell reference refers to the address of a particular cell and that cell reference is automatically adjusted when you copy a formula from one cell to the next. A relative cell reference consists of the address of the cell. 

If you use a relative reference, when you copy the formula +A1+B1, to another cell Excel automatically changes the formula to reflect the new column or row numbers like +A2+B2.

Absolute cell references

To stop Excel from changing the row or column reference when you copy a formula, you can use absolute cell references in your formulas instead of relative references. To create an absolute cell reference you need to insert the “$” symbol into the cell reference to indicate that that value should not be adjusted.

Excel date and time functions

What day is it? You have to look at the calendar. But your computer already knows. Excel has a number of embedded date and time functions that can make your life easier.

Note: Internally, Excel uses a “serial number” to represent dates. So, Excel’s functions usually return a serial number date — and there are other functions used to format that into a readable version.

Function	Syntax	Description
DATE	DATE(year, month, day)	The Excel date function returns a date given the parameters of year, month, date.
DATEDIF	DATEDIF(start_date,end_date,unit)	This function calculates the time between two given dates.
DAY	DAY(serial number)	This function returns the actual day of a date, as an integer between 1 through 31.
EDATE	EDATE(start_date, months)	This function adds a period of months onto a start date.
EOMONTH	EOMONTH(start_date, months)	This function does the same thing as the EDATE function, but it returns the last period in the month.
NOW	NOW()	The now function returns the serial number representing the date at the immediate time (including hour).
TODAY	TODAY()	The today function returns the serial number representing the date (just the date).
YEAR	YEAR()	The year function turns the serial number representing the date into a year.

Excel logic functions

Logic functions are where MS Excel gets really advanced. Most programming languages have similar logic functions; in fact, any program can be created through the use of “IF” commands. Understanding logic will help you build workbooks that are more dynamic.

If you’re having trouble understanding Excel logic functions, the easiest way is to usually map them out on an index card.

Function	Syntax	Description
IF	IF(statement, true, false)	The “IF” function starts out with a given parameter. If that parameter is true, it does one thing. If it’s false, it does another.
OR	OR(condition 1, condition 2)	The “OR” function is like the “IF” function but it can test for multiple logical conditions at the same time. It will return either true or false, so it can be used nested within an IF function.
AND	AND(condition 1, condition 2)	The “AND” function is similar to the “OR” function. But while the “OR” function returns true if any of the conditions are met, the AND function only returns true if all the functions are met.
NOT	NOT(condition 1)	The “NOT” function will run a statement and return true or false. It can also be used as a nested function within an IF function.
IFERROR	IFERROR(argument, error_note)	The “IFERROR” function is a way to debug your logic. You send an argument and what should be displayed if that argument produces an error.

Excel lookup functions

Need to look up data? Excel has you covered with a wide array of (admittedly complex) LOOKUP functions.

Function	Syntax	Description
LOOKUP	LOOKUP(lookup_value, lookup_vector, [result_vector])	The LOOKUP function is one of the most important functions in Excel to understand, though most will use the VLOOKUP or HLOOKUP functions (as they are easier). You start with a LOOKUP_VALUE (what you’re trying to look up) then a LOOKUP_VECTOR (where you’re looking).
VLOOKUP	VLOOKUP(lookup_value, lookup_location, column_return, approx)	The VLOOKUP function is generally easier to use than LOOKUP. For VLOOKUP, you start with the value you’re looking up and the location you’re looking. You then add a column_return; the match that you’re looking for. Finally, you select whether you want approximate values or exact matches.
HLOOKUP	HLOOKUP(lookup_value, lookup_location, column_return, approx)	The HLOOKUP operates the same as VLOOKUP, but it’s less commonly used because it’s used on horizontally configured charts.
INDEX	INDEX(array, row, column)	The INDEX function will return an element within a table or an array, given its position.
MATCH	MATCH(value, array, match_type)	The MATCH function can be used if VLOOKUP or HLOOKUP isn’t suitable. It will look up a value within an array. Importantly, when you use MATCH, INDEX, and logical commands, you can iterate throughout an entire sheet to find a given value. (Learn more about the MATCH function here.)

Excel math functions

Mathematics functions are some of the most important functions in Excel and some of the functions used most frequently. If you find yourself starting to type out a long algorithm, consider that Excel might already have the function to do it.

You can perform basic mathematical operations like addition (+), subtraction (-), multiplication (*), and division (/) without using an actual function. You can also produce more complicated things, such as exponents (2^2) in this way. 

Function	Syntax	Description
ABS	ABS(value)	ABS gives you the absolute value of a number. Both 1 and -1 will return 1.
AVERAGE	AVERAGE(range)	AVERAGE gives you the average of a given range of numbers.
MEDIAN	MEDIAN(range)	MEDIAN will give you the median value of a range of numbers.
PI	PI()	You don’t need to memorize PI. The PI function will do it all for you.
SUM	SUM(range)	One of the most commonly used functions, the SUM function will combine multiple cells in a range into a single sum.
SUMIF	SUMIF(range, standard)	A variant on SUM, the SUMIF function returns the values in the range for a given set of standards. There’s also the SUMIFS function.

Excel financial functions

What industry uses Microsoft Excel more than the financial industry? Excel is primarily used for “doing the books,” so it makes sense that Excel actually has a lot of built-in financial functions.

It should be noted that a lot of these financial functions are quite complex. But that’s not an issue with Excel. Usually, these functions are being used by people who already know what the financial functions do. If you’re not aware of a complicated financial function, the results might not come out accurately, even if you have the Microsoft Excel syntax correct.

Function	Syntax	Description
DB	DB(cost_basis, salvage_cost, life, period, [month])	DB calculates the depreciation of an asset with a fixed, declining balance.
DDB	DDB(cost_basis, salvage_cost, life, period, [factor])	The DDB function calculates the depreciation of an asset with a double-declining balance.
FV	FV(rate_of_return, number_of_payments, payments_made, [future_payments], [eob])	The FV function is used to return the future value of a given investment. It’s an incredibly intricate calculation, but it can be used easily in Excel.
IPMT	IPMT(interest_rate, payment_period, number_of_payments, present_value, [desired_cash_balance], [eob])	This is used to calculate the interest portion of a loan payment, in case you want to know how much you’re being charged for interest.
IRR	IRR(values, [estimate])	IRR is a function that returns the Internal Rate of Return for a series of cash flows.
INTRATE	INTRATE(settlement_date, maturity_date, invested, redemption, [basis])	INTRATE returns the interest rate for a security that’s been invested in; in other words, the rate of return.
NPV	NPV(rate, range_of_cashflows)	NPV is one of the most commonly used financial functions. It calculates the net present value of a loan or an investment.
PMT	PMT(rate, number_of_payments, present_value, [future_value], [eob])	This function will return the amount of periodic repayment for a loan; how much needs to be paid back regularly for a given loan.
PV	PV(rate, number_of_payments, payment, [future_value], [eob])	This function returns the current value of an investment, given parameters about that investment.
SLN	SLN(cost, salvage_value, life)	The SLN function will return depreciation on an asset for a single period, calculated on a straight line.
XNPV	XNPV(rate, value_range, dates)	This function is similar to the NPV function, but it calculates it based on irregular values rather than regular values.
XIRR	XIRR(values, dates, [guess])	This function is similar to the IRR function, but it calculates it based on irregular values rather than regular values.
YIELD	YIELD(settlement_date, maturity_date, rate, price, redemption, frequency, basis)	The YIELD function returns the amount that has been yielded on a security with periodic interest.

Learning more about Excel

The above Excel formulas and functions should give you a start. There are a lot of options if you want to learn Excel. In addition to the above, there are charting functions, pivot tables, and SQL in Excel. Excel can be quite the advanced solution — if you’re willing to take the time to learn it.