Basic Excel formulas everyone should know
The most-used Excel formulas with examples: SUM, AVERAGE, COUNT, IF, COUNTIF, SUMIF, VLOOKUP and CONCAT, plus how to write a formula step by step.
Quick answer
Every Excel formula starts with an equals sign (=). The most common are =SUM(A1:A10) to add, =AVERAGE() for the mean, =IF(test, value_if_true, value_if_false) for decisions and =VLOOKUP() to find data in a table. Absolute references like $B$1 keep a cell fixed when you copy a formula.
Key takeaways
- Every formula starts with =.
- SUM, AVERAGE and COUNT handle most calculations.
- IF makes decisions; COUNTIF and SUMIF count or add with a condition.
- Press F4 to make a reference absolute ($B$1).
Excel becomes simple once you know a dozen formulas. They all start with = and use cell references like A1 or ranges like A1:A10.
The most-used formulas
| Formula | What it does | Example |
|---|---|---|
| SUM | Adds a range | =SUM(B2:B10) |
| AVERAGE | Average of a range | =AVERAGE(C2:C10) |
| COUNT | Counts cells with numbers | =COUNT(D2:D50) |
| COUNTA | Counts non-empty cells | =COUNTA(A2:A50) |
| MAX / MIN | Highest or lowest value | =MAX(E2:E30) |
| IF | Returns a value based on a condition | =IF(B2>=60,“Pass”,“Fail”) |
| COUNTIF | Counts cells meeting a condition | =COUNTIF(F2:F100,“Paid”) |
| SUMIF | Adds only what meets a condition | =SUMIF(A2:A100,“Rent”,B2:B100) |
| VLOOKUP | Finds data in a table | =VLOOKUP(H2,A2:C100,3,FALSE) |
| CONCAT | Joins text | =CONCAT(A2,” “,B2) |
| TODAY | Today’s date | =TODAY() |
How to write a formula step by step
- Click the cell where you want the result.
- Type = and the function name, for example =SUM(.
- Select the range with the mouse or type it (B2:B10).
- Close the parenthesis and press Enter.
Relative and absolute references
When you copy a formula, Excel shifts the cells: =A2B2 becomes =A3B3 on the next row. To keep a cell fixed, lock it with dollar signs: $B$1. Shortcut: select the reference and press F4.
Common errors
| Error | Meaning |
|---|---|
| #NAME? | Misspelled function or wrong language |
| #N/A | VLOOKUP didn’t find the value |
| #DIV/0! | Dividing by zero or an empty cell |
| ##### | The column is too narrow: widen it |
Want to look up data across tables? Continue with the VLOOKUP guide.
Frequently asked questions
How do I add up a column in Excel?
Type =SUM(B2:B20) in the cell where you want the total, or select the cell below the numbers and press Alt + = for AutoSum.
Why does my formula show #NAME?
The function name is misspelled or in the wrong language. If your Excel is in another language, function names are translated (for example, SUMA in Spanish).
How do I copy a formula to other rows?
Select the cell with the formula and drag the small square at its bottom-right corner down, or double-click it. Excel adjusts the references for each row.
Sources
- VLOOKUP function Microsoft Support
- Merge and unmerge cells Microsoft Support
This article is general and educational; it is not personalized financial, legal or tax advice. Financial product terms change: always confirm current terms with the institution.