Excel Formulas Cheat Sheet: 30 Formulas Everyone Should Know
Essential Excel formulas with examples for finance, analysis, and productivity.
Introduction: Why Mastering Excel Formulas Is a Superpower
In the world of data, spreadsheets are the lingua franca. Whether you're a financial analyst forecasting quarterly revenue, a marketing manager tracking campaign ROI, or a small business owner balancing the books, Excel formulas are the engine that turns raw numbers into actionable insights. Yet, many users only scratch the surface, relying on manual calculations or basic SUM functions. This Excel formulas cheat sheet is designed to bridge that gap.
Over the next few sections, we'll cover 30 essential formulas that every professional should know. From foundational arithmetic to advanced lookup functions, these formulas will cut your spreadsheet time in half and eliminate costly errors. We'll use real-world examples with actual numbers—like calculating a 15% commission on $50,000 in sales, or finding the average delivery time from a list of 200 orders. By the end, you'll have a toolkit that handles everything from simple budgets to complex data analysis.
And if you ever need a quick calculation without opening Excel, remember our Percentage Calculator and Average Calculator are just a click away. Let's dive in.
1. The Foundation: Basic Arithmetic & SUM Formulas
SUM: The Workhorse of Excel
The SUM function adds up a range of cells. It's the most used formula in Excel, and for good reason.
Syntax: =SUM(number1, [number2], ...)
Real Example: You have monthly sales for Q1: January ($12,000), February ($15,500), March ($13,800). To find total Q1 sales, use =SUM(B2:B4). The result is $41,300.
AVERAGE: Finding the Middle Ground
The AVERAGE function calculates the mean of a set of numbers.
Syntax: =AVERAGE(number1, [number2], ...)
Real Example: A call center tracks handling times (in seconds): 45, 52, 38, 61, 47. Using =AVERAGE(A2:A6) gives 48.6 seconds. For a quick sanity check, you can use our Average Calculator.
COUNT & COUNTA: Counting Cells with Purpose
COUNT counts numeric entries, while COUNTA counts non-blank cells (text, numbers, errors).
Real Example: In a list of 100 customer IDs, 5 are missing. =COUNTA(A2:A101) returns 95, while =COUNT(A2:A101) also returns 95 if all IDs are numeric.
MAX & MIN: The Extremes
These instantly identify the highest and lowest values.
Real Example: A teacher has test scores: 88, 92, 76, 95, 81. =MAX(B2:B6) returns 95, =MIN(B2:B6) returns 76.
2. Logical Functions: IF, AND, OR for Decision Making
IF: The Conditional Powerhouse
The IF function returns one value if a condition is true, and another if false.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Real Example: A salesperson achieved $50,000 in sales. The target was $45,000. Formula: =IF(B2>=45000, "Bonus Earned", "No Bonus"). Result: "Bonus Earned".
Nested IFs: Multiple Conditions
For more complex logic, nest IF statements. Example: Grade assignment based on score.
Real Example: Score 85: =IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F"))). Result: "B".
AND & OR: Compound Logic
Combine conditions. AND requires all conditions true; OR requires any one true.
Real Example: A loan is approved if income > $40,000 AND credit score > 700. =IF(AND(B2>40000, C2>700), "Approved", "Denied"). If income is $55,000 and score is 720, result: "Approved".
3. Lookup & Reference: VLOOKUP, HLOOKUP, XLOOKUP
VLOOKUP: Vertical Lookup
Search for a value in the first column of a table and return a value in the same row from another column.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Real Example: You have an employee table: ID in column A, Name in B, Salary in C. To find the salary of employee ID 102: =VLOOKUP(102, A2:C10, 3, FALSE). Result: $65,000 (assuming that's the value).
HLOOKUP: Horizontal Lookup
Same as VLOOKUP but searches rows instead of columns.
Real Example: A product price table with months in row 1 and products in column A. To find the price of "Widget" in March: =HLOOKUP("Mar", A1:G5, 3, FALSE).
XLOOKUP: The Modern Replacement
XLOOKUP is more flexible, allowing leftward lookups and default values.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Real Example: Find the department for employee ID 105: =XLOOKUP(105, A2:A10, D2:D10, "Not Found"). Result: "Marketing".
4. Text Functions: Clean and Manipulate Strings
LEFT, RIGHT, MID: Extract Substrings
LEFT returns the first N characters; RIGHT returns the last N; MID returns a substring from the middle.
Real Example: A product code "ABC-123-XYZ". To extract the middle segment: =MID(A2, 5, 3) returns "123".
CONCATENATE (or CONCAT): Joining Text
Combine multiple strings into one.
Real Example: First name "John" in B2, last name "Doe" in C2. =CONCATENATE(B2, " ", C2) or =B2 & " " & C2 produces "John Doe".
TRIM: Remove Extra Spaces
Cleans up data imported from other systems.
Real Example: Cell contains " Hello World ". =TRIM(A2) returns "Hello World".
5. Date & Time Functions: Work with Calendars
TODAY & NOW: Dynamic Dates
TODAY() returns the current date; NOW() returns date and time. These update automatically.
Real Example: To calculate days until a deadline (Dec 31, 2025): =DATE(2025,12,31)-TODAY(). If today is Jan 1, 2025, result: 364 days.
DATEDIF: Difference Between Dates
Calculates years, months, or days between two dates.
Real Example: Employee start date: June 15, 2020; today: Jan 10, 2025. =DATEDIF(A2, B2, "y") returns 4 (full years).
WORKDAY: Skip Weekends
Returns a date N workdays in the future, excluding weekends.
Real Example: Project starts Jan 6, 2025 (Monday). 10 workdays later: =WORKDAY(A2, 10) returns Jan 20, 2025 (skipping weekends).
6. Financial & Statistical Formulas for Analysis
PMT: Loan Payment Calculation
Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax: =PMT(rate, nper, pv, [fv], [type])
Real Example: Loan of $250,000 at 4.5% annual interest, 30-year term. Monthly payment: =PMT(4.5%/12, 30*12, 250000). Result: -$1,266.71 (negative because it's an outflow).
RANK: Determine Position
Ranks a number against a list of numbers.
Real Example: Sales team values: $45k, $60k, $38k. To rank $60k: =RANK(60000, B2:B4, 0) returns 1 (highest).
SUBTOTAL: Filtered Calculations
Performs SUM, AVERAGE, etc., but ignores hidden rows.
Real Example: Filter a list to show only "East" region sales. =SUBTOTAL(109, C2:C100) sums only visible cells.
Conclusion: Your Actionable Takeaway
This Excel formulas cheat sheet covers 30 essential formulas that will transform your spreadsheet workflow. Start by mastering the basics: SUM, AVERAGE, and IF. Then, move to VLOOKUP and XLOOKUP for data retrieval. Finally, explore text, date, and financial functions to handle specialized tasks.
Your action plan:
- Create a practice workbook with sample data (e.g., 50 rows of sales records).
- Write each formula from this guide at least once.
- Use the Percentage Calculator to double-check your percentage calculations.
- Use the Average Calculator to verify your AVERAGE results.
With consistent practice, you'll go from spreadsheet novice to data wizard. Bookmark this page—you'll come back to it often.