Your First Formulas: SUM, AVERAGE, and Basic Math
Formulas are the single feature that separates a spreadsheet from a table in a Word document — they let numbers update themselves. This lesson teaches you the equals sign, basic arithmetic operators, and your first two aggregate functions, SUM and AVERAGE, which together cover a huge share of everyday spreadsheet work.
Why this skill matters professionally
Almost every Excel-based job task — budgets, invoices, timesheets, sales reports — boils down to adding numbers up and finding averages. Employers expect this to be automatic and error-free; manually adding a column with a calculator (and mistyping the total) is a fast way to lose credibility with a manager who trusts the numbers you hand them. Formulas also make your work auditable: a manager can click a total and instantly see how it was built, instead of wondering if you typed it correctly by hand.
Learning objectives
- Write formulas using +, -, *, /.
- Use SUM to add a range.
- Use AVERAGE to find the mean of a range.
Background concepts
Everything starts with =
Excel treats anything starting with an equals sign as an instruction to calculate, not a plain value. Typing 2+2 into a cell just shows the text '2+2'; typing =2+2 shows 4.
Cell references beat hard numbers
A formula like =50+75 is fragile — if the numbers change, you must retype the formula. A formula like =A1+A2 references the cells directly, so it recalculates automatically the instant either cell changes. This is the whole point of using a spreadsheet instead of a calculator.
Ranges and the colon
A colon between two cell addresses means 'through.' A1:A10 means every cell from A1 down to A10, inclusive. Functions like SUM and AVERAGE are built to accept ranges as their argument.
Order of operations
Excel respects standard math order of operations (PEMDAS): parentheses first, then exponents, then multiplication/division, then addition/subtraction. Use parentheses liberally to make your intent explicit, e.g. =(A1+A2)*0.08 for tax on a subtotal.
Step-by-step
1. 1. Write a basic arithmetic formula
Click an empty cell, type =4*5, press Enter. Excel shows 20.
2. 2. Use cell references instead
Put 4 in A1 and 5 in A2. In A3, type =A1*A2. Now changing A1 or A2 automatically updates A3.
3. 3. Learn the four basic operators
Four operators cover almost all everyday spreadsheet math. Each one works the same way whether you type a number or a cell reference.
+ addition =A1+A2 - subtraction =A1-A2 * multiplication =A1*A2 / division =A1/A2
4. 4. Sum a range with SUM
In a blank cell type =SUM(A1:A10). This adds every value in that column range, ignoring blank cells, and is far more reliable than typing =A1+A2+A3+A4... by hand.
5. 5. Average a range with AVERAGE
Type =AVERAGE(B2:B20) to get the arithmetic mean. Excel automatically skips blank cells when computing the average, but treats a zero as a real value that lowers the average.
6. 6. Select a range visually instead of typing it
Type =SUM( and then click-and-drag across the cells you want instead of typing addresses manually — Excel fills in the reference for you, and this is faster and less error-prone.
7. 7. Use AutoSum for speed
Click a cell just below or beside a column/row of numbers, then press Alt+= (Windows) or the AutoSum button on the Home tab. Excel guesses the range and inserts =SUM(...) automatically — check the highlighted range before pressing Enter.
8. 8. Copy a formula down a column
Select the cell with your formula, grab the small square at its bottom-right corner (the fill handle), and drag down. Excel adjusts the row references automatically for each new row — this is called a relative reference.
9. 9. Combine SUM with simple math
You can layer formulas: =SUM(A1:A10)*0.1 calculates a 10% total of the sum, useful for quick tax or tip calculations.
Real-world workplace examples
Weekly sales total
A retail shift supervisor enters daily sales figures Monday through Sunday, then uses =SUM(B2:B8) to get the weekly total instantly instead of adding by hand on a calculator.
Class grade average
A teacher enters five quiz scores per student and uses =AVERAGE(C2:G2) to calculate each student's average automatically, copying the formula down for the whole roster.
Expense report totals
An employee tracks a business trip's expenses by category and uses =SUM(D2:D15) at the bottom of the column to get the reimbursement total that finance will review.
Quick markup calculation
A small retailer buys product at cost in column A and uses =A2*1.4 in column B to instantly calculate the 40% markup retail price for every item.
Practical scenarios
The frozen total
James built a monthly budget where the 'Total' cell was just a typed number, 1,250, instead of a formula. Every time he changed an expense line, he had to remember to manually retype the total — and twice he forgot, handing his manager a report where the total didn't match the line items. Switching that cell to =SUM(B2:B12) fixed the problem permanently: the total now always reflects reality, no matter what changes above it.
Average versus total confusion
A call center supervisor wanted to know the average number of calls handled per agent per day, but accidentally used SUM instead of AVERAGE, reporting a number nine times too high to upper management. Understanding the distinct purpose of each function — SUM adds everything together, AVERAGE divides by the count of entries — prevented the same mistake from happening again after the correction.
Common mistakes beginners make
Forgetting the equals sign
Typing SUM(A1:A10) without the leading = just enters it as text. Excel formulas always begin with =.
Hard-coding numbers into formulas
Typing =50+75 instead of referencing the cells means the formula never updates when source data changes. Always reference cells.
Including the header row in a SUM range
=SUM(A1:A10) when A1 contains a text label like 'Sales' just gets ignored by SUM (it ignores text), but if A1 accidentally contains a number, it will be wrongly included. Start ranges from the first true data row.
Leaving a stray blank row inside a range
If a formula's range is set before new rows are inserted, newly added rows outside the original range won't be included in the total — always double check the range after adding rows.
Mixing up SUM and AVERAGE in reporting
These functions answer very different business questions; using the wrong one produces numbers that are technically calculated correctly but answer the wrong question entirely.
Best practices
- Always start a formula with =.
- Reference cells rather than typing raw numbers whenever the source data could change.
- Use SUM for totals and AVERAGE for typical/mean values — know which question you're answering.
- Use the fill handle to copy formulas down a column instead of retyping them.
- Verify AutoSum's guessed range before confirming — it can guess wrong near blank rows.
- Use parentheses to make multi-step math formulas unambiguous.
- Add a visible label next to every total cell so readers know what it represents.
Professional tips
- Press Alt+= (Windows) or Cmd+Shift+T isn't standard on Mac — use the ribbon AutoSum button on Mac instead for instant SUM insertion.
- Select a range and check the Status Bar at the bottom of the screen — it instantly shows Sum, Average, and Count without writing any formula.
- Use Ctrl+` to reveal every formula on the sheet at once when checking a colleague's work.
- Wrap a formula in ROUND, e.g. =ROUND(AVERAGE(A1:A10),2), to control decimal places cleanly rather than just formatting the display.
- Name a range (Formulas > Define Name) so formulas read like =SUM(SalesQ1) instead of cryptic cell addresses.
Practice exercises
Basic operators drill
In five separate cells, write formulas using +, -, *, /, and one combining parentheses, referencing at least two cells each time.
Build a mini expense sheet
List 8 expenses with amounts in column B, then use =SUM(B2:B9) in B10 for the total.
Add an average row
In the same sheet, add =AVERAGE(B2:B9) in a new cell and interpret what it tells you about typical spending.
Fill-handle practice
Create a 3-column table of quantity and price, write one formula for line total, then use the fill handle to copy it down all rows.
Review questions
Why is =A1+A2 generally better than =50+75?
Referencing cells means the formula recalculates automatically if the underlying values change, keeping the spreadsheet accurate without manual retyping.
What does the colon mean inside a range like A1:A10?
It means 'through' — every cell from A1 down to A10 inclusive.
When would you use AVERAGE instead of SUM?
When you need a typical/mean value across a set of numbers (like an average test score) rather than the combined total.
What's the fastest way to sum a column you just finished typing?
Click the cell below the numbers and press the AutoSum shortcut or button; verify the guessed range before pressing Enter.
What happens to blank cells inside an AVERAGE range?
They're ignored entirely — not counted as zero — which affects the resulting mean compared to how a zero would.
Key takeaways
- Every formula starts with an equals sign.
- Reference cells, don't hard-code numbers, so formulas stay accurate.
- SUM adds a range; AVERAGE finds the mean of a range.
- A colon between two cell addresses defines an inclusive range.
- The fill handle copies formulas down or across efficiently.
- AutoSum guesses ranges — always double-check before confirming.
- The Status Bar shows quick stats on any selected range without a formula.
Frequently asked questions
Does the order I select cells in matter for SUM?
No — SUM(A1:A10) and a range selected in any direction produce the same total; order doesn't matter for addition.
Can I sum non-adjacent cells?
Yes, separate references with commas: =SUM(A1:A5,C1:C5) sums both ranges together.
Why does AVERAGE give an error with all-text cells?
AVERAGE requires at least one numeric value in the range; an all-text or all-blank range returns a #DIV/0! error.
Is =A1+A2+A3 wrong compared to SUM?
It's not wrong for three cells, but it doesn't scale — SUM is faster to write and safer to extend for larger ranges.
Can formulas reference cells on another sheet?
Yes, using SheetName!CellAddress, e.g. =SUM(Sheet2!A1:A10).
What's a quick way to check a formula's result without adding a new cell?
Select the range and read the Sum/Average/Count shown automatically in the Status Bar.
Related lessons
12 min • Beginner
20 min • Intermediate
20 min • Advanced
15 min • Beginner