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.
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 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 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-codes, formulas, and more. Great for auditing.
Find and Replace – press Ctrl + F and you can change parts of many formulas at once.
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 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
=NPV – calculates the net present value of cash flows based on a discount rate
=XNPV – calculatec 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
=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 are 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
Thanks for reading CFI’s Excel formulas cheat sheet guide. By now you’re ready to perform even more advanced financial analysis. To keep practicing, check out these resources below: