Home › Excel basics › Formulas cheat sheet
Excel Formulas Cheat Sheet
This Excel formulas cheat sheet lists the formulas you'll actually use, grouped by task, each with a short example. Every formula starts with =. Click a function name for the full guide with worked examples.
Excel CSE · 3 min read
In this guide
- Formula basics
- Math
- Counting & statistics
- Logical
- Lookup
- Dynamic arrays
- Text
- Date & time
- Tips
- FAQ
Formula basics
| What | Example | Meaning |
|---|
| Add, subtract, multiply, divide | =A2+B2-C2*D2/E2 | Normal maths order: * and / before + and - |
| Brackets | =(A2+B2)*C2 | Calculate the brackets first |
| Power | =A2^2 | A2 squared |
| Percentage | =A2/B2 | Format as %. See percentages |
| Join text | =A2&" "&B2 | "Anna" and "Smith" become "Anna Smith" |
| Fixed reference | =B2*$E$1 | E1 stays fixed when copied. See absolute references |
| Other sheet | =Sheet2!A1 | Cell A1 on Sheet2 |
Math formulas
| Formula | Example | Does |
|---|
| SUM | =SUM(A2:A10) | Adds up a range |
| SUMIF | =SUMIF(A2:A10,"North",C2:C10) | Adds up values that meet one condition |
| SUMIFS | =SUMIFS(C2:C10,A2:A10,"North",B2:B10,"Anna") | Adds up with several conditions |
| SUMPRODUCT | =SUMPRODUCT(B2:B10,C2:C10) | Multiplies row by row, then adds up |
| SUBTOTAL | =SUBTOTAL(9,C2:C10) | Sum of visible rows only |
| ROUND | =ROUND(A2,2) | Rounds to 2 decimals |
| ROUNDUP / ROUNDDOWN | =ROUNDUP(A2,0) | Always rounds up (or down) |
| MOD | =MOD(A2,2) | Remainder after division (0 = even) |
| INT | =INT(A2) | Whole number part, rounded down |
| ABS | =ABS(A2) | Value without minus sign |
Counting and statistics
| Formula | Example | Does |
|---|
| COUNT | =COUNT(A2:A10) | Counts cells with numbers |
| COUNTA | =COUNTA(A2:A10) | Counts non-empty cells |
| COUNTBLANK | =COUNTBLANK(A2:A10) | Counts empty cells |
| COUNTIF | =COUNTIF(A2:A10,"Yes") | Counts cells that meet one condition |
| COUNTIFS | =COUNTIFS(A2:A10,"North",C2:C10,">100") | Counts with several conditions |
| AVERAGE | =AVERAGE(B2:B10) | Average |
| AVERAGEIF | =AVERAGEIF(A2:A10,"North",B2:B10) | Average with a condition |
| MEDIAN | =MEDIAN(B2:B10) | Middle value |
| MIN / MAX | =MAX(B2:B10) | Smallest or largest value |
| LARGE / SMALL | =LARGE(B2:B10,2) | Second largest value |
| RANK.EQ | =RANK.EQ(B2,$B$2:$B$10) | Position of B2 in the list (1 = highest) |
| STDEV.S | =STDEV.S(B2:B10) | Standard deviation of a sample |
Logical formulas
| Formula | Example | Does |
|---|
| IF | =IF(B2>=50,"Pass","Fail") | One result if true, another if false |
| IFS | =IFS(B2>=90,"A",B2>=70,"B",TRUE,"C") | Several conditions without nesting |
| AND / OR | =IF(AND(B2>50,C2="Yes"),"OK","") | Combine conditions |
| NOT | =NOT(A2="Done") | Reverses TRUE and FALSE |
| SWITCH | =SWITCH(A2,1,"Low",2,"High") | Pick a result based on a value |
| IFERROR | =IFERROR(A2/B2,0) | Replaces an error with your own value |
| IFNA | =IFNA(VLOOKUP(…),"Not found") | Catches only #N/A |
| ISBLANK / ISNUMBER | =ISNUMBER(A2) | Checks what's in a cell |
Lookup formulas
| Formula | Example | Does |
|---|
| VLOOKUP | =VLOOKUP(E2,A2:C10,3,FALSE) | Finds E2 in column A, returns column 3 |
| HLOOKUP | =HLOOKUP(E1,A1:F3,2,FALSE) | Same, but in a row |
| XLOOKUP | =XLOOKUP(E2,A2:A10,C2:C10,"Not found") | Modern lookup in any direction |
| INDEX + MATCH | =INDEX(C2:C10,MATCH(E2,A2:A10,0)) | Flexible lookup that works in every version |
| MATCH | =MATCH(E2,A2:A10,0) | Position of a value in a list |
| INDIRECT | =INDIRECT("'"&A2&"'!B10") | Reference built from text, e.g. a sheet name |
| CHOOSE | =CHOOSE(A2,"Q1","Q2","Q3","Q4") | Picks the nth item from a list |
Dynamic array formulas (Excel 365)
| Formula | Example | Does |
|---|
| FILTER | =FILTER(A2:C10,B2:B10="North") | Returns all matching rows |
| UNIQUE | =UNIQUE(A2:A10) | List without duplicates |
| SORT | =SORT(A2:C10,3,-1) | Sorted copy, here by column 3 descending |
| TRANSPOSE | =TRANSPOSE(A1:D2) | Turns rows into columns |
| SEQUENCE | =SEQUENCE(10) | Numbers 1 to 10 |
Text formulas
| Formula | Example | Does |
|---|
| CONCAT / TEXTJOIN | =TEXTJOIN(", ",TRUE,A2:A5) | Joins text with a separator |
| LEFT / RIGHT | =LEFT(A2,3) | First (or last) 3 characters |
| MID | =MID(A2,4,2) | 2 characters starting at position 4 |
| TEXTBEFORE / TEXTAFTER | =TEXTBEFORE(A2,"@") | Text before (or after) a character |
| LEN | =LEN(A2) | Number of characters |
| FIND / SEARCH | =SEARCH("x",A2) | Position of a character or word |
| TRIM | =TRIM(A2) | Removes extra spaces |
| SUBSTITUTE | =SUBSTITUTE(A2,"-","") | Replaces text |
| UPPER / LOWER / PROPER | =PROPER(A2) | Changes capitals: "anna smith" → "Anna Smith" |
| TEXT | =TEXT(A2,"dd-mm-yyyy") | Formats a number or date as text |
| VALUE | =VALUE(A2) | Turns text into a number |
Date and time formulas
| Formula | Example | Does |
|---|
| TODAY / NOW | =TODAY() | Today's date (NOW adds the time) |
| DATE | =DATE(2026,12,31) | Builds a date from year, month, day |
| YEAR / MONTH / DAY | =YEAR(A2) | Part of a date |
| DATEDIF | =DATEDIF(A2,TODAY(),"Y") | Years between two dates, e.g. age |
| NETWORKDAYS | =NETWORKDAYS(A2,B2) | Working days between two dates |
| WORKDAY | =WORKDAY(A2,10) | Date 10 working days later |
| EOMONTH | =EOMONTH(A2,0) | Last day of the month |
| EDATE | =EDATE(A2,3) | Same day, 3 months later |
| WEEKDAY | =WEEKDAY(A2,2) | Day of the week, 1 = Monday |
| Time difference | =(B2-A2)*24 | Hours between two times |
Tips for writing formulas
- Press F4 after a cell reference to add $ signs.
- Press Tab to accept a function name from the suggestion list.
- Press Ctrl+Shift+A after typing a function name to see its arguments.
- Double-click the fill handle (the square at the bottom right of a cell) to copy a formula down.
- Get an error? See Excel errors explained.
Frequently asked questions
What are the most important Excel formulas?
For most people: SUM, IF, COUNTIF, SUMIF, VLOOKUP or XLOOKUP, AVERAGE, ROUND and TODAY. Learn these first and you can handle most everyday tasks.
What's the difference between a formula and a function?
A formula is anything you type after the = sign. A function is a built-in formula with a name, like SUM or IF. =A1+A2 is a formula without a function; =SUM(A1:A2) is a formula with one.
How many functions does Excel have?
More than 500, but most people use fewer than 30 of them regularly.
Do these formulas work in Google Sheets?
Almost all of them do, with the same names and arguments.
