Master Excel Formulas: A Comprehensive Cheat Sheet with Examples
Excel is a powerful tool that simplifies data management and analysis. At its core are formulas that perform calculations, manipulate data, and extract insights. This cheat sheet provides a quick reference to essential Excel formulas, along with examples to help you understand and apply them.
Basic Arithmetic Formulas
Excel supports basic arithmetic operations: addition (+), subtraction (-), multiplication (*), and division (/). You can use these operators in cells to perform calculations.
- Addition: =SUM(A1:A10) adds values from A1 to A10
- Subtraction: =A1-A2 subtracts the value in A2 from A1
- Multiplication: =A1*A2 multiplies the values in A1 and A2
- Division: =A1/A2 divides the value in A1 by A2
Financial Formulas
Excel offers numerous financial functions to analyze and forecast financial data.
- PMT: Calculates the payment on a loan. Example: =PMT(10%,24,100000) calculates the monthly payment for a $100,000 loan with a 10% interest rate over 24 months.
- NPV: Calculates the net present value of a series of cash flows. Example: =NPV(0.1,[-100000,50000,60000,70000]) calculates the NPV of a series of cash flows with a discount rate of 10%.
Logical and Lookup Formulas
Logical and lookup formulas help you make decisions based on conditions and find specific data within a range.
- IF: Performs different actions based on a condition. Example: =IF(A1>50,"Pass","Fail") returns "Pass" if the value in A1 is greater than 50, and "Fail" otherwise.
- VLOOKUP: Searches for a value in the first column of a table and returns a corresponding value from the same row in a specified column. Example: =VLOOKUP(A1,Table1,2,FALSE) looks up the value in A1 in the first column of Table1 and returns the corresponding value from the second column.
Text Formulas
Text formulas manipulate and analyze text data in Excel.
- LEN: Returns the number of characters in a text string. Example: =LEN(A1) returns the number of characters in the text in A1.
- CONCATENATE: Joins two or more text strings into one. Example: =CONCATENATE(A1," ",B1) combines the text in A1 and B1 with a space in between.
Date and Time Formulas
Date and time formulas help you work with dates and times in Excel.
- TODAY: Returns the current date. Example: =TODAY() returns the current date.
- DAYS: Calculates the number of days between two dates. Example: =DAYS(B1,A1) calculates the number of days between the dates in A1 and B1.
Error Handling Formulas
Error handling formulas help you manage and display errors in your formulas.
- IFERROR: Returns a specified value if a formula results in an error. Example: =IFERROR(A1/B1,"Cannot divide by zero") returns the result of A1/B1 if B1 is not zero, and "Cannot divide by zero" otherwise.
- ISERROR: Checks if a formula results in an error. Example: =IF(ISERROR(A1/B1),"Error in calculation",A1/B1) checks if A1/B1 results in an error, and displays an error message or the result accordingly.
This cheat sheet covers essential Excel formulas to help you perform calculations, manipulate data, and extract insights from your spreadsheets. Practice using these formulas in your work and explore Excel's extensive library of functions to become a power user. Happy calculating!
