Excel formulas cheat sheet
If you want to become a master of Excel financial analysis and building financial models then you’ve come to the right place. We’ve built a cheat sheet of the most important Excel 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
=EDATE – add a specified number of months to a date in Excel
=EOMONTH – convert a date to the last day of month (i.e. 7/18/2018 to 7/31/2018)
=TODAY – insert and display today’s date in a cell
=YEAR – extract and display the year from a date (i.e. 7/18/2018 to 2018) in Excel
=YEARFRAC – expresses the fraction of a year between two dates (i.e. 1/1/2018 – 3/31/2018 = 0.25)
Convert time to seconds – convert an amount of time to seconds (i.e. 5 minutes to 300 seconds)
Navigation
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 changes 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
=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
=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
Financial formulas
=NPV – calculates the net present value of cash flows based on a discount rate
=XNPV – calculate the NPV f 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 dates
=YIELD – returns the yield of a security based on maturity, face value, and interest rate
=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
=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”
=AND – checks if all conditions are met and returns only “TRUE” or “FALSE”
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
Thanks for sharing this Excel formulas. It is really helpful.
ReplyDeleteAdvanced Excel Training in Chennai | Excel Advanced course | Excel classes in Chennai