Excel Formulas Cheat Sheet
Complete Excel and Google Sheets formula reference. VLOOKUP, INDEX MATCH, SUMIFS, pivot tables, and more. Searchable, copyable, and printable.
Math
| Formula | Description | Example |
|---|---|---|
| Sum a range of cells | =SUM(B2:B50) | |
| Calculate the average | =AVERAGE(C2:C100) | |
| Count cells with numbers | =COUNT(D:D) | |
| Count non-empty cells | =COUNTA(A:A) | |
| Count empty cells | =COUNTBLANK(B2:B50) | |
| Find the largest value | =MAX(E2:E100) | |
| Find the smallest value | =MIN(E2:E100) | |
| Find the median value | =MEDIAN(F2:F50) | |
| Find the most frequent value | =MODE(G2:G100) | |
| Round to N decimal places | =ROUND(B2, 0) | |
| Round up to N decimals | =ROUNDUP(C2, 2) | |
| Round down to N decimals | =ROUNDDOWN(C2, 2) | |
| Return absolute value | =ABS(-42) → 42 | |
| Multiply arrays then sum | =SUMPRODUCT(qty, price) | |
| Raise to a power | =POWER(5, 3) → 125 | |
| Square root | =SQRT(144) → 12 | |
| Remainder after division | =MOD(10, 3) → 1 | |
| Round down to nearest integer | =INT(7.8) → 7 | |
| Random number between 0 and 1 | =RAND()*100 | |
| Random integer in range | =RANDBETWEEN(1, 6) |
Lookup
| Formula | Description | Example |
|---|---|---|
| Vertical lookup (exact match) | =VLOOKUP(A2, Products!A:C, 3, FALSE) | |
| Horizontal lookup | =HLOOKUP("Q1", A1:D3, 2, FALSE) | |
| Modern lookup (replaces VLOOKUP) | =XLOOKUP(A2, IDs, Names) | |
| Return value at position | =INDEX(A1:C10, 3, 2) | |
| Find position of value | =MATCH("Apple", A:A, 0) | |
| Flexible lookup combo | =INDEX(B:B, MATCH(D2, A:A, 0)) | |
| Convert text to cell reference | =INDIRECT("A"&B1) | |
| Offset reference by rows/cols | =OFFSET(A1, 2, 3) | |
| Return current row number | =ROW()-1 for sequential IDs | |
| Return current column number | =COLUMN(C1) → 3 |
Text
| Formula | Description | Example |
|---|---|---|
| Join text strings | =A1&" "&B1 (shorthand) | |
| Join with delimiter | =TEXTJOIN("-", TRUE, A:A) | |
| Extract N chars from left | =LEFT("Hello", 3) → "Hel" | |
| Extract N chars from right | =RIGHT("Hello", 2) → "lo" | |
| Extract from middle | =MID("Hello", 2, 3) → "ell" | |
| Count characters in text | =LEN("Hello") → 5 | |
| Remove extra spaces | =TRIM(" Hello ") → "Hello" | |
| Remove non-printable characters | =CLEAN(A1) | |
| Convert to uppercase | =UPPER("hello") → "HELLO" | |
| Convert to lowercase | =LOWER("HELLO") → "hello" | |
| Capitalize first letters | =PROPER("john doe") → "John Doe" | |
| Replace specific text | =SUBSTITUTE(A1, "@", " at ") | |
| Replace by position | =REPLACE(A1, 1, 3, "XYZ") | |
| Find position (case-sensitive) | =FIND("@", A1) | |
| Find position (case-insensitive) | =SEARCH("hello", A1) | |
| Format number as text | =TEXT(A1, "$#,##0.00") | |
| Convert text to number | =VALUE("42") → 42 | |
| Repeat text N times | =REPT("★", 5) → "★★★★★" |
Logic
| Formula | Description | Example |
|---|---|---|
| If/then/else logic | =IF(A1>50, "Pass", "Fail") | |
| Multiple conditions | =IFS(A1>=90,"A", A1>=80,"B") | |
| True if ALL conditions met | =AND(A1>0, A1<100) | |
| True if ANY condition met | =OR(A1="Yes", A1="Y") | |
| Reverse a boolean | =NOT(A1>50) | |
| Return fallback on error | =IFERROR(A1/B1, 0) | |
| Return fallback on #N/A | =IFNA(VLOOKUP(...), "Not found") | |
| Match value to results | =SWITCH(A1,1,"Jan",2,"Feb") | |
| Return Nth value | =CHOOSE(2, "A","B","C") → "B" |
Conditional
| Formula | Description | Example |
|---|---|---|
| Count cells matching criteria | =COUNTIF(A:A, ">50") | |
| Count with multiple criteria | =COUNTIFS(A:A, "Yes", B:B, ">10") | |
| Sum cells matching criteria | =SUMIF(A:A, "Sales", B:B) | |
| Sum with multiple criteria | =SUMIFS(C:C, A:A, "NY", B:B, ">100") | |
| Average matching criteria | =AVERAGEIF(B:B, ">0", C:C) | |
| Average with multiple criteria | =AVERAGEIFS(C:C, A:A, "East") | |
| Max matching criteria | =MAXIFS(B:B, A:A, "Sales") | |
| Min matching criteria | =MINIFS(B:B, A:A, "Sales") |
Date & Time
| Formula | Description | Example |
|---|---|---|
| Current date | =TODAY() → 3/10/2026 | |
| Current date and time | =NOW() | |
| Create a date | =DATE(2026, 3, 10) | |
| Extract year from date | =YEAR(TODAY()) | |
| Extract month from date | =MONTH(A1) | |
| Extract day from date | =DAY(A1) | |
| Difference between dates | =DATEDIF(A1, B1, "D") → days | |
| Add months to date | =EDATE(A1, 3) | |
| End of month N months away | =EOMONTH(TODAY(), 0) | |
| Working days between dates | =NETWORKDAYS(A1, B1) | |
| Day of week (1=Sun) | =WEEKDAY(TODAY()) | |
| Extract hour or minute | =HOUR(NOW()) |
Financial
| Formula | Description | Example |
|---|---|---|
| Loan payment amount | =PMT(5%/12, 360, -200000) | |
| Future value of investment | =FV(7%/12, 120, -500) | |
| Present value | =PV(5%/12, 60, -300) | |
| Net present value | =NPV(10%, B2:B10) | |
| Internal rate of return | =IRR(A1:A5) | |
| Interest rate per period | =RATE(60, -300, 15000) |
Frequently asked questions
What's the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches only to the right and requires a column index number. XLOOKUP searches in any direction, defaults to exact match, handles errors natively, and uses a return array instead of a column number. XLOOKUP is simpler, more flexible, and recommended for all new work.
When should I use SUMIF vs SUMIFS?
SUMIF handles a single condition (e.g., sum sales for 'East' region). SUMIFS handles multiple conditions (e.g., sum sales for 'East' region AND 'Q1'). Note: SUMIFS uses a different argument order - the sum range comes first.
How do I lock a cell reference when copying formulas?
Use the $ sign: $A$1 locks both column and row, $A1 locks only the column, A$1 locks only the row. The fastest way is to press F4 while your cursor is on a reference in the formula bar - it cycles through all four options.
What does #N/A mean and how do I fix it?
#N/A means a lookup formula couldn't find a match. Common causes: typos, extra spaces (fix with TRIM), or the lookup value doesn't exist. Wrap your formula in IFERROR or IFNA to handle it: =IFNA(VLOOKUP(...), "Not found").
How do I calculate the difference between two dates?
For simple day difference, subtract: =B1-A1. For years/months/days, use DATEDIF: =DATEDIF(A1, B1, "Y") for years, "M" for months, "D" for days. For working days only, use =NETWORKDAYS(A1, B1).
Can I use these formulas in Google Sheets?
Yes! About 95% of these formulas work identically in Google Sheets. The main differences: Google Sheets has ARRAYFORMULA (Excel uses Ctrl+Shift+Enter or dynamic arrays), and some newer functions like XLOOKUP were added to Google Sheets later.
Go from reference to real skills
Cheat sheets are great for quick lookups. Our in-depth courses take you from the fundamentals to professional-level mastery.
Browse all courses