Excel formulas: list with examples
Anything you type into a cell that starts with = is a formula. This page shows how formulas are built and entered, and lists the Excel functions people actually use at work, grouped by job, each with an example and a lesson to practice it.
In short: every Excel formula starts with =, followed by cell references such as B2, operators such as + or *, and functions such as =SUM(B2:B7). The basic Excel formulas most people need are SUM, AVERAGE, IF, COUNTIF, SUMIF and VLOOKUP or XLOOKUP.
Try it now: your first formula
Quantity times price for every line of an order. Write the formula once and Excel fills it down.
Task In D2, calculate the revenue for printer paper: quantity times price. The formula is then filled down to D7 and has to be right in every row.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price $ | Revenue $ |
| 2 | Printer paper | 40 | 4.99 | |
| 3 | Toner | 6 | 59.9 | |
| 4 | Binder | 25 | 2.49 | |
| 5 | Ballpoint pen | 120 | 0.79 | |
| 6 | Sticky notes | 30 | 1.89 | |
| 7 | USB stick | 10 | 8.5 |
How an Excel formula is built
| Part | What it does | Example |
|---|---|---|
| = | Starts every formula. Without it, Excel treats the entry as plain text. | =B2*C2 |
| Cell reference | Points to a cell. When that cell changes, the formula recalculates. | B2 |
| Range | A block of cells from one corner to the other, joined by a colon | B2:B7 |
| Operator | Add, subtract, multiply, divide, exponent | + - * / ^ |
| Function | A named, ready-made calculation that takes arguments in brackets | SUM(B2:B7) |
| Arguments | The inputs inside the brackets, separated by commas | ROUND(B2,2) |
People use "formula" and "function" interchangeably, but there is a difference. The formula is everything in the cell; a function is one building block inside it. =SUM(B2:B7)*1.08 is a formula that uses the SUM function.
How to enter a formula in Excel
- Click the cell where the result should appear.
- Type =.
- Click the first cell you need, or type its address. Excel outlines it in color.
- Type an operator or start typing a function name. Excel suggests matching functions after the first letters; press Tab to accept one.
- Press Enter. The cell shows the result, and the formula bar shows the formula.
To edit, click in the formula bar or press F2. To reuse a formula down a column, drag the small square at the bottom right of the cell, or double-click it. References shift as they go, so B2 becomes B3, B4 and so on. Add dollar signs to keep one fixed, as explained in absolute references. Alt+= inserts an AutoSum over the numbers above. To see every formula on a sheet at once, go to the Formulas tab and click Show Formulas.
Excel operators
| Operator | Meaning | Example |
|---|---|---|
| + - | add, subtract | =B2+C2, =C2-B2 |
| * / | multiply, divide | =B2*C2, =C2/B2 |
| ^ | exponent | =B2^2 |
| % | percent, divides by 100 | =B2*8% |
| & | join text | =A2&" "&B2 |
| = <> > < >= <= | compare, returns TRUE or FALSE | =C2>=1000 |
| : | range | B2:B7 |
Microsoft's order of operations: references first, then negation, percent, exponents, multiplication and division, addition and subtraction, then &, and comparisons last. Operators of equal rank go from left to right. Brackets override all of it: =(B2+C2)*D2.
Basic Excel formulas list, grouped by purpose
These are the formulas that come up most at work, in training and in Excel assessment tests. Every name links to a lesson with an explanation and exercises.
Math and totals
| Formula | Purpose | Example |
|---|---|---|
| + - * / | basic arithmetic | =B2*C2 |
| SUM | add values | =SUM(B2:B7) |
| Percentages | share, markup, change | =C2/B2-1 |
| ROUND | round to decimal places | =ROUND(B2,2) |
| POWER, SQRT, ABS, MOD | exponent, square root, absolute value, remainder | =ABS(C2-B2) |
| SUMPRODUCT | quantity times price in one cell, weighted average | =SUMPRODUCT(B2:B7,C2:C7) |
| SUBTOTAL | totals of visible rows only | =SUBTOTAL(109,C2:C50) |
| FV, PMT | compound interest, loan payment | =PMT(B2/12,B3*12,-B1) |
Counting, averages and rank
| Formula | Purpose | Example |
|---|---|---|
| AVERAGE | mean | =AVERAGE(B2:B7) |
| COUNT, COUNTA | count numbers or filled cells | =COUNTA(A2:A50) |
| LARGE, RANK.EQ | top values and ranking | =LARGE(C2:C9,2) |
Logic and conditions
| Formula | Purpose | Example |
|---|---|---|
| IF | if this, then that, else something else | =IF(C2>=1000,"Bonus","") |
| AND, OR | combine several conditions | =AND(B2>0,C2="North") |
| IFS | several tiers without nesting | =IFS(B2>=90,"A",B2>=75,"B",TRUE,"C") |
| IFERROR | replace an error with a fallback | =IFERROR(C2/B2,0) |
| COUNTIF, COUNTIFS | count with one or more conditions | =COUNTIF(A2:A50,"Berlin") |
| SUMIF, SUMIFS | add with conditions | =SUMIF(A2:A50,"Berlin",C2:C50) |
| AVERAGEIF | average with a condition | =AVERAGEIF(B2:B50,"IT",C2:C50) |
| MAXIFS, MINIFS | largest or smallest value with a condition | =MAXIFS(C2:C50,A2:A50,"North") |
Lookup and reference
| Formula | Purpose | Example |
|---|---|---|
| VLOOKUP | fetch a value for a key from a list | =VLOOKUP(E2,A2:C50,3,FALSE) |
| XLOOKUP | the modern successor, looks in any direction | =XLOOKUP(E2,A2:A50,C2:C50) |
| INDEX and MATCH | flexible lookup in every version | =INDEX(C2:C50,MATCH(E2,A2:A50,0)) |
| TRANSPOSE | switch rows and columns | =TRANSPOSE(A1:E4) |
Text, dates and lists
| Formula | Purpose | Example |
|---|---|---|
| &, TEXTJOIN | combine text | =A2&" "&B2 |
| LEFT, RIGHT, LEN | parts of a text, count characters | =LEFT(A2,3) |
| MID, FIND | cut text out at a position | =MID(A2,FIND("@",A2)+1,50) |
| SUBSTITUTE, TRIM | clean up text | =TRIM(A2) |
| DATE, MONTH, YEAR | work with dates | =MONTH(A2) |
| DATEDIF, NETWORKDAYS | gap in years, months or working days | =NETWORKDAYS(A2,B2) |
| FILTER, SORT, UNIQUE | filter and sort lists with a formula | =UNIQUE(A2:A50) |
Practice: the SUM formula
The formula you will write more than any other. Add up six months of expenses.
Task In B9, use SUM to calculate the office supply expenses for the first half of the year.
| A | B | |
|---|---|---|
| 1 | Month | Expenses $ |
| 2 | January | 1240 |
| 3 | February | 980 |
| 4 | March | 1105 |
| 5 | April | 1320 |
| 6 | May | 1015 |
| 7 | June | 1190 |
| 8 | ||
| 9 | Total: |
Common formula problems
The formula shows as text
If the = is missing, or the cell was formatted as Text before you typed, Excel shows the formula instead of the result. Set the format to General, then press F2 and Enter to confirm the formula again.
#NAME? instead of a result
Excel does not recognize a name, usually a typo in the function name. All error values are explained in Excel error values.
The formula does not recalculate
Check Formulas, Calculation Options. If it says Manual, Excel only recalculates when you press F9. Switch it back to Automatic.
Excel formulas in German
German Excel translates most function names, so SUM becomes SUMME, IF becomes WENN and VLOOKUP becomes SVERWEIS, and arguments are separated by semicolons. The full list is in Excel functions in English and German.
FAQ
What is the difference between a formula and a function in Excel?
The formula is the whole entry starting with =. A function such as SUM or IF is a ready-made building block used inside a formula. =B2*C2 is a formula with no function at all.
What are the 10 most important Excel formulas?
To start with: arithmetic with cell references, SUM, AVERAGE, COUNT, ROUND, IF, COUNTIF, SUMIF, VLOOKUP or XLOOKUP, and IFERROR. Those cover most everyday office tasks. After that, SUMIFS, INDEX and MATCH and the text functions are worth learning.
Are Excel commands the same as formulas?
No. Commands are the buttons and menus, such as Sort, Filter or Paste Special, and they change the sheet once when you click. A formula lives in a cell and recalculates every time the data changes. Many jobs can be done either way, for example Transpose as a paste command or TRANSPOSE as a formula.
Is there an Excel formulas cheat sheet?
This page works as one: print it with Ctrl+P or save it as a PDF. Practicing the formulas still beats reading them, because writing a formula yourself is what makes it stick.
Where can I see all Excel functions?
Click Insert Function on the Formulas tab, or the fx button next to the formula bar. You can browse by category, and Excel explains each argument as you fill it in.
Do these formulas work in Google Sheets?
Most of them do, with the same names and commas as separators. SUM, IF, COUNTIF, SUMIF, VLOOKUP and SUMPRODUCT all exist in Google Sheets. A few newer Excel functions differ, so check before you rely on one.