Excel Formulas Cheat Sheet

Excel Formulas Cheat Sheet

If you want to become a master of Excel financial analysis and an expert on building financial models then you’ve come to the right place.  We’ve built an Excel formulas cheat sheet of the most important formulas and functions required to become a spreadsheet power user.

Below is a written overview of the main formulas for your own self-study.  However, if you want a video explanation of the formulas, check out our Free Excel Crash Course.

If you’re already a power user, check out our Advanced Excel Course and learn the most powerful combinations of formulas and functions.

Shortcuts

• List of Excel shortcuts – a list of the most important Excel shortcuts to speed up financial modeling.

Dates and Time Excel Formulas Cheat Sheet

• =EDATE – add a specified number of months to a date in Excel
• =EOMONTH – convert a date to the last day of the month (e.g., 7/18/2018 to 7/31/2018)
• =DATE – Returns a number that represents the date (yyyy/mm/dd) in Excel. This formula is useful when working with Excel functions that have a date as an argument.
• =TODAY – insert and display today’s date in a cell
• =NETWORKDAYS – Returns the number of whole workdays between two specified dates.
• =YEAR – extracts and displays the year from a date (e.g., 7/18/2018 to 2018) in Excel
• =YEARFRAC – expresses the fraction of a year between two dates (e.g., 1/1/2018 – 3/31/2018 = 0.25)
• Convert Time to Seconds – converts an amount of time to seconds (e.g., 5 minutes to 300 seconds)

Navigation Excel Formulas Cheat Sheet

• Go To Special – press F5 and find all cells that are hard-coded, formulas, and more. Great for auditing.
• Find and Replace – press Ctrl + F and you can change parts of many formulas at once.

Lookup Formulas

• INDEX MATCH – a combination of lookup functions that are more powerful than VLOOKUP
• =VLOOKUP – a lookup function that searches vertically in a table
• =HLOOKUP – a lookup function that searches horizontally in a table
• =INDEX – a lookup function that searches vertically and horizontally in a table
• =MATCH – returns the position of a value in a series
• =OFFSET – moves the reference of a cell by the number of rows and/or columns specified

Math Functions Excel Formulas Cheat Sheet

• =SUM – add the total of a series of numbers
• =AVERAGE – calculates the average of a series of numbers
• =MEDIAN – returns the median average number of a series
• =SUMPRODUCT – calculates the weighted average, very useful for financial analysis
• =PRODUCT – multiplies all of a series of numbers
• =ROUNDDOWN – rounds a number to the specified number of digits
• =ROUNDUP – the formula rounds a number to the specific number of digits
• AutoSum – a shortcut to quickly sum a series of numbers
• =ABS – returns the absolute value of a number
• =PI – Returns the value of pi, accurate to 15 digits
• =SUMIF – sum values in a range that are specified by a condition
• =SUMQ – Returns the sum of the squares of the arguments

Financial Formulas

• =NPV – calculates the net present value of cash flows based on a discount rate
• =XNPV – calculates the NPV of cash flows based on a discount rate and specific dates
• =IRR – this formula calculates the internal rate of return (discount rate that sets the NPV to zero)
• =XIRR – calculates the internal rate of return (discount rate that sets the NPV to zero) with specified dates
• =YIELD – returns the yield of a security based on maturity, face value, and interest rate
• =FV – calculates the future value of an investment with constant periodic payments and a constant interest rate
• =PV – calculates the present value of an investment
• =INTRATE – the interest rate on a fully invested security
• =IPMT – this formula returns the interest payments on a debt security
• =PMT – this function returns the total payment (debt and interest) on a debt security
• =PRICE – calculates the price per $100 face value of a periodic coupon bond
• =DB – calculates depreciation based on the fixed-declining balance method
• =DDB – calculates depreciation based on the double-declining balance method
• =SLN – calculates depreciation based on the straight-line method

Conditional Functions

• =IF – checks if a condition is met and returns a value if yes and if no
• =OR – checks if any conditions are met and returns only “TRUE” or “FALSE”
• =XOR – the “exclusive or” statement returns true if the number of TRUE statements is odd
• =AND – checks if all conditions are met and returns only “TRUE” or “FALSE”
• =NOT – changes “TRUE” to “FALSE”, and “FALSE” to “TRUE”
• IF AND – combine IF with AND to have multiple conditions
• =IFERROR – if a cell contains an error, you can tell Excel to display an alternative result

Other Functions and Formulas

• Sheet Name Code – a formula using MID, CELL, and FIND functions to display the worksheet name
• Consolidate – how to consolidate information between multiple Excel workbooks

Additional Resources

Thank you for reading CFI’s Excel Formulas Cheat Sheet. To keep practicing, check out these CFI resources below:

• Excel Shortcuts for PC and Mac
• List of Excel Functions
• Google Sheets
• Sheet Name Code Template
• See all Excel resources