Lesson 2 of 16 · Level 1: Beginner
Basic Excel Formulas: SUM, AVERAGE, MAX, MIN and Percentages
- ⏱ 10 min read
- ❓ 4 review questions
- ✍️ Exercise with solution
- 📥 Downloadable template
In this lesson
- Write formulas that use cell references instead of typed numbers.
- Use SUM, AVERAGE, MAX, MIN, COUNT and ROUND.
- Calculate percentages, increases, discounts and sales tax correctly.
- Recognize and fix the most common errors (#DIV/0!, #NAME?, ####).
- Summarize six months of sales with five formulas.
On the Sales sheet, column I holds the amount of 300 orders. Check your formulas: =SUM(I2:I301) should return $122,759.50 and =AVERAGE(I2:I301) $409.20.
- practice-excel.xlsx · Sales sheet
A formula is an instruction that tells Excel to calculate something. Its big advantage over a calculator is that when you change an input, the result updates on its own. In this lesson you'll learn to write formulas, use the most common functions and read the error messages that appear when something goes wrong.
- Write formulas with references
- Use the basic functions
- Build an invoice
- Work with percentages
- Fix the errors
How to write a formula
Every formula starts with =. After that you can combine numbers, cell references and operators:
| Operator | Meaning | Example | Result if A1=10 and B1=4 |
|---|---|---|---|
+ | Addition | =A1+B1 | 14 |
- | Subtraction | =A1-B1 | 6 |
* | Multiplication | =A1*B1 | 40 |
/ | Division | =A1/B1 | 2.5 |
^ | Exponent | =A1^2 | 100 |
& | Join text | =A1&" units" | 10 units |
Always point to cells instead of typing numbers. =A1*B1 updates when A1 changes; =10*4 never will. A number "hidden" inside a formula is the number one cause of errors in work spreadsheets, because nobody remembers it's there when the price or rate changes.
Order of operations
Excel follows the order you learned in school (PEMDAS): parentheses, exponents, multiplication and division, then addition and subtraction. =2+3*4 returns 14, not 20. If you want a different order, use parentheses: =(2+3)*4 returns 20. When in doubt, add parentheses; they cost nothing and make formulas easier to read.
1. Why is =B2*C2 better than =5*12.5?
Cell references keep the formula correct when the data changes. Typed numbers force you to rewrite the formula every time.
Functions: formulas that come ready-made
A function is a predefined formula with a name. Its structure is always the same:
=FUNCTION_NAME(argument1, argument2, ...)
U.S. Excel separates arguments with commas, and that's what this course uses. If your computer's regional settings use a comma as the decimal separator (common in Europe and Latin America), Excel expects semicolons instead: =ROUND(A1;2). If Excel rejects a formula from this course, try swapping the commas.
The six functions you'll use most
| Function | What it does | Example |
|---|---|---|
SUM | Adds a range | =SUM(D2:D50) |
AVERAGE | Arithmetic mean | =AVERAGE(D2:D50) |
MAX / MIN | Highest / lowest value | =MAX(D2:D50) |
COUNT | Counts cells that contain numbers | =COUNT(D2:D50) |
COUNTA | Counts non-empty cells (text or numbers) | =COUNTA(B2:B50) |
ROUND | Rounds to a number of decimals | =ROUND(D2*0.07, 2) |
The difference between COUNT and COUNTA doubles as an error detector: if COUNTA returns 50 for your amount column and COUNT returns 48, two amounts are stored as text.
Full example: a simple invoice with sales tax
An office-supply company in Indianapolis bills a customer for three items. Indiana charges a 7% state sales tax, so the rate goes in its own cell:
| A | B | C | D | Result | |
|---|---|---|---|---|---|
| 1 | Product | Quantity | Unit price | Amount | |
| 2 | Keyboard | 3 | 25 | =B2*C2 | 75.00 |
| 3 | Mouse | 5 | 12 | =B3*C3 | 60.00 |
| 4 | Monitor | 2 | 180 | =B4*C4 | 360.00 |
| 5 | Subtotal | =SUM(D2:D4) | 495.00 | ||
| 6 | Sales tax | 7% | =D5*C6 | 34.65 | |
| 7 | Total | =D5+D6 | 529.65 |
- Monitors (68.0%) $360.00
=B4*C4: 2 × $180. - Keyboards (14.2%) $75.00
=B2*C2: 3 × $25. - Mice (11.3%) $60.00
=B3*C3: 5 × $12. - Sales tax 7% (6.5%) $34.65
=D5*C6: 7% of the $495 subtotal.
Notice three professional habits:
- You only typed the formula in
D2; for D3 and D4 you dragged the fill handle. - The tax rate has its own cell (
C6). If you ship to a state with a different rate, you change one cell. - The cell shows the result, while the formula bar shows the formula. That's how anyone can audit your math.
AutoSum: the fastest button in Excel
Click the cell below a column of numbers and press Home → AutoSum (the Σ symbol) or the shortcut Alt + = (Mac: Cmd + Shift + T). Excel suggests a range; if it's right, press Enter. The arrow next to Σ also offers Average, Count Numbers, Max and Min.
Percentages without confusion
Percentages trip up a lot of people. These are the three operations you'll need most:
X% of a value
=B2*15% (or =B2*0.15)
Apply a discount
=B2*(1-15%)
Percent change
=(C2-B2)/B2 with Percentage format.
And a fourth one that causes plenty of mistakes: backing out sales tax that's already included. Some prices are tax-inclusive (many event tickets, food trucks and concession stands, for example). If a $107 total includes 7% tax, the pre-tax price is =A2/(1+7%) = $100. Subtracting 7% (=A2*(1-7%)) gives $99.51, which is wrong, because the 7% was calculated on $100, not on $107.
2. A product costs $80 (in A2) and its price goes up 15% (in B2). Which formula gives the new price?
A 15% increase means multiplying by 1.15: $80 × 1.15 = $92. A2+B2 would only add 0.15.
Worked example: six months at a café in Austin
A café in Austin, Texas, records its monthly sales in B2:B7, January through June. The owner is meeting with her bank about a small-business loan and wants five numbers:
| Question | Formula | Result |
|---|---|---|
| Total for the six months? (in B8) | =SUM(B2:B7) | $120,500 |
| A typical month? | =AVERAGE(B2:B7) | $20,083.33 |
| Best and worst month? | =MAX(B2:B7) and =MIN(B2:B7) | $23,700 (June) and $16,900 (February) |
| How much did June grow over May? | =(B7-B6)/B6 | 15.6% |
| What share of the half-year was June? | =B7/B8 | 19.7% |
With these five formulas she has a summary that updates itself: if her bookkeeper corrects March in July, everything recalculates without touching anything else. Notice that percent growth divides by the earlier value (May), not by the new one.
3. A ticket price of $214 includes 7% sales tax. What's the price before tax?
Divide by 1.07: $214 / 1.07 = $200. Subtracting 7% ($214 × 0.93 = $199.02) is the classic mistake: the tax was calculated on $200, not on $214.
The most common errors and how to fix them
| Error | What it means | Typical fix |
|---|---|---|
#DIV/0! | You're dividing by zero or by an empty cell. | Check the divisor, or use =IFERROR(A1/B1, 0). |
#NAME? | Excel doesn't recognize a name in the formula. | Check the spelling (SUM, not SUMM), put text in quotes, or confirm your version has the function. |
#VALUE! | Wrong data type, such as text × number. | Look for numbers stored as text. |
#REF! | The formula points to a cell you deleted. | Undo the deletion or rewrite the reference. |
#N/A | A lookup didn't find the value. | Covered in the VLOOKUP lesson. |
###### | Not an error: the column is too narrow. | Double-click the right edge of the column header. |
Press Ctrl + ` (the backtick key, above Tab on U.S. keyboards) to show every formula on the sheet instead of its result. Press it again to switch back. It's the fastest way to spot a typed number where a formula should be.
4. A cell shows #DIV/0!. What does it mean?
A narrow column shows ####, and a misspelled function returns #NAME?. Reading the error tells you what to fix.
Calculation mistakes that show no warning
Errors with a # at least catch your eye. These ones return a number that looks right, which makes them more dangerous:
Adding a percentage as if it were a number: =A2+15%.
Multiply: =A2*(1+15%). With $80 that's $92, not $80.15.
Removing included tax by subtracting the percentage.
Divide by (1 + rate): =A2/(1+7%).
Typing the tax rate inside every formula (=D5*7%).
Put it in a cell and point to it: if it changes, you update one cell.
Assuming that hiding decimals with formatting changes the value.
Formatting only changes what you see. When the value itself must change (an invoice), use ROUND.
Practice exercise
Open the expense list you built in the previous lesson and, below the data, calculate:
- Total spending for the week.
- The average purchase.
- The largest and smallest expense.
- How many expenses you recorded.
- What percentage of the total the largest expense represents.
See solution
If your amounts are in D2:D11:
Total: =SUM(D2:D11)
Average: =AVERAGE(D2:D11)
Largest: =MAX(D2:D11)
Smallest: =MIN(D2:D11)
Records: =COUNT(D2:D11)
% of total: =MAX(D2:D11)/SUM(D2:D11) → Percentage format
Check: if COUNT returns fewer records than you typed, one amount is stored as text.
Summary
- Formulas start with
=and should point to cells, not contain typed numbers. SUM,AVERAGE,MAX,MINandCOUNTcover most everyday totals and summaries.- Put inputs like tax rates and discounts in their own cells.
- Every error code means something specific: read it before you delete the formula.
- To back out included sales tax, divide by (1 + rate); never subtract the percentage.
Next lesson: Relative, Absolute and Mixed Cell References in Excel (the $ Sign). You'll see why a copied formula sometimes "moves" a reference you wanted to keep still, and how the $ sign fixes it.
Create your account in 30 seconds, mark your lessons and download your certificate at the end.