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:
|Insert Current Date||ctrl+;|
|Insert Current Time||shift+ctrl+;|
|Edit Cell Comment||shit+F2|
|Display Active Cell||ctrl+backspace|
|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.
|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.|
Top courses in Excel
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.
|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.
|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.
|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.
|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.