By the end of this lesson you will be able to
- Write a formula that starts with = and uses cell references and the operators + - * /.
- Use SUM, AVERAGE, MIN, MAX and COUNT on a range such as B2:B6.
- Copy a formula down a column and explain what changes and why.
- Make a reference absolute with dollar signs when it must not move.
- Read the errors #DIV/0!, #NAME?, #REF! and #VALUE! and fix their causes.
Typing totals by hand defeats the purpose of a spreadsheet. A Formula is an instruction written in a cell that tells the spreadsheet to calculate something, and it recalculates every time the numbers it depends on change. Once you can write a few formulas and copy them correctly, you can make budgets, marksheets and stock sheets that stay right by themselves. This lesson covers the handful of formulas that do most of the work, and the one idea, the cell reference, that makes them powerful.
Your first formula
A formula always starts with an equals sign. Without it, the spreadsheet treats what you type as plain text. After the equals sign you write numbers, cell references and operators. The operators are + (add), - (subtract), * (multiply), / (divide) and ^ (power). A reference such as B2 means: use whatever is in cell B2 right now.
Suppose B2 holds the planned rent, 350, and C2 holds the actual rent, 350. The formula =C2-B2 in D2 shows the difference. If you later change C2 to 360, D2 changes to 10 at once. You never retype the formula. The cell displays the result, but the formula bar shows the formula itself whenever the cell is selected.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Item | Planned | Actual | Difference |
| 2 | Rent | 350 | 350 | 0 |
| 3 | Food | 120 | 135 | 15 |
| 4 | Transport | 40 | 38 | -2 |
- Marker 1: Shows 15; formula bar shows the formula
- Marker 2: Heading for the formula column
- Marker 3: Input cells: change them and D3 updates
Read this figure as text
A simulated spreadsheet with a Difference column where cell D3 is selected and the formula bar shows =C3-B3.
A simulated spreadsheet with four columns: Item, Planned, Actual and Difference. Row 2 is Rent 350, 350, 0. Row 3 is Food 120, 135, 15. Row 4 is Transport 40, 38, -2. Cell D3 is selected and the formula bar shows =C3-B3. Marker 1 points at D3, the selected cell, where only the answer 15 is displayed while the formula bar shows =C3-B3. Marker 2 points at the heading Difference in D1. Marker 3 points at C3 and B3, the two inputs: change either and the answer changes.
Write it yourself
Step 1: Write a subtraction formula
In a new sheet, type 350 in B2 and 360 in C2. In D2 type
=C2-B2and press Enter.You should see: D2 shows 10. Click D2 and the formula bar shows =C2-B2.
Step 2: Change an input
Change B2 to 340.
You should see: D2 immediately becomes 20 without you touching it.
Step 3: Try multiplication and addition
Type
=B2+C2*2in E2 and press Enter.You should see: E2 shows 1060. Multiplication happens before addition, so C2*2 is calculated first.
Step 4: Add brackets
Type
=(B2+C2)*2in F2.You should see: F2 shows 1400. Brackets make the addition happen first.
Functions: ready-made calculations
A function is a ready-made formula with a name, followed by brackets that hold the input. Instead of =B2+B3+B4+B5+B6, you write =SUM(B2:B6). The part B2:B6 is a range: the colon means from B2 to B6, including everything between. Functions can be typed in capitals or lower case; the spreadsheet converts them. When a function has several inputs, separate them with commas (with English language settings; Calc also accepts semicolons).
| Function | What it does | Example | Result for 350, 120, 40, 15, 80 |
|---|---|---|---|
| SUM | Adds the numbers | =SUM(B2:B6) | 605 |
| AVERAGE | The mean: total divided by how many numbers | =AVERAGE(B2:B6) | 121 |
| MIN | The smallest number | =MIN(B2:B6) | 15 |
| MAX | The largest number | =MAX(B2:B6) | 350 |
| COUNT | How many cells contain numbers (text is ignored) | =COUNT(B2:B6) | 5 |
Instead of typing a range, you can click the first cell and drag across the others while you are writing the formula: the range appears in the formula for you. For a quick total, select the empty cell under a column of numbers and press Alt+= (in both Calc and Excel). It writes a SUM for you and guesses the range; check that the coloured outline covers the cells you meant before pressing Enter. Calc also has a Sum button on the toolbar; Excel's is called AutoSum on the Home tab.
| Row | A | B |
|---|---|---|
| 1 | Item | Planned |
| 2 | Rent | 350 |
| 3 | Food | 120 |
| 4 | Transport | 40 |
| 5 | Airtime | 15 |
| 6 | Savings | 80 |
| 7 | Total | 605 |
- Marker 1: The range B2:B6 is added
- Marker 2: The total, 605
- Marker 3: A text label, not summed
Read this figure as text
A simulated spreadsheet with a column of planned amounts and a total in B7 using the SUM function over B2 to B6.
A simulated spreadsheet with a monthly budget. A1 is Item and B1 is Planned. Rows 2 to 6 hold Rent 350, Food 120, Transport 40, Airtime 15 and Savings 80. A7 holds Total and B7 holds 605. Cell B7 is selected and the formula bar shows =SUM(B2:B6). The range B2 to B6 is highlighted. Marker 1 points to the highlighted range of five numbers being added. Marker 2 points to B7 holding the answer 605. Marker 3 points to the label Total in A7, which is just text and not part of the sum.
Change numbers and write SUM, AVERAGE, MIN and MAX formulas, and watch the answers recalculate. This is a simulation inside the lesson, so nothing on your computer is touched.
Loading lab…
Copying a formula: relative references
Here is the idea that makes spreadsheets fast. Suppose D2 holds =C2-B2. If you copy that formula to D3, the spreadsheet does not copy the words; it copies the relationship: take the cell one column to the left, subtract the cell two columns to the left, on the same row. In D3 it becomes =C3-B3. This is a relative reference: it moves with the formula.
Fill a formula down a column
Step 1: Set up the table
Fill a small table: Item, Planned, Actual in A1:C1, with Rent 350 350, Food 120 135, Transport 40 38 below it. Put
=C2-B2in D2.You should see: D2 shows 0.
Step 2: Fill down with the handle
Select D2. Point at the small square at its bottom-right corner (the fill handle) until the pointer becomes a cross, then drag down to D4.
You should see: D3 shows 15 and D4 shows -2. Click D4 and the formula bar shows =C4-B4.
Step 3: Fill down with Ctrl+D
As an alternative, select D2:D4 and press Ctrl+D.
You should see: The formula in D2 is filled down into the cells below.
Absolute references: keeping a cell fixed
Sometimes a formula must keep pointing at one particular cell, however far it is copied. Imagine a price list where each price must have a 5% service charge added, and the rate 5% lives in a single cell, F1. If you write =B2*F1 and copy it down, the F1 becomes F2, F3 and so on, which are empty. The answers are wrong. To stop F1 moving, write dollar signs before the column letter and the row number: =B2*$F$1. This is an absolute reference. The dollar signs mean "do not change this part when copied".
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Item | Price | Price with charge | Rate | 5% | |
| 2 | Notebook | 200 | 210 | |||
| 3 | Pen | 40 | 42 | |||
| 4 | Ruler | 60 | 63 |
- Marker 1: Rate cell: fixed with $F$1
- Marker 2: Formula with a locked reference
- Marker 3: Relative: moves down the rows
Read this figure as text
A simulated spreadsheet with prices, a rate cell F1 and a Price with charge column whose formula uses $F$1.
A simulated spreadsheet. Column A is Item and column B is Price. Rows 2 to 4 hold Notebook 200, Pen 40 and Ruler 60. Column C is headed Price with charge. In column E, E1 holds the label Rate and F1 holds 5%. C2 holds 210, C3 holds 42 and C4 holds 63. Cell C3 is selected and the formula bar shows =B3*(1+$F$1). Marker 1 points to F1, the rate cell that must stay fixed. Marker 2 points to C3, whose formula keeps $F$1 while B3 changes row by row. Marker 3 points to B3, the relative reference that moves down with the formula.
| Written | Name | When copied one row down and one column right |
|---|---|---|
B2 | Relative | Becomes C3 |
$B$2 | Absolute | Stays $B$2 |
$B2 | Mixed (column locked) | Becomes $B3 |
B$2 | Mixed (row locked) | Becomes C$2 |
When a formula shows an error
Error messages look alarming but each one tells you exactly what kind of mistake you made. They start with # and are written in capitals.
| Error | Meaning | Typical cause | Fix |
|---|---|---|---|
#DIV/0! | Division by zero | =B2/C2 where C2 is zero or empty | Check the divisor. Fill the empty cell, or use =IF(C2=0,"",B2/C2). |
#NAME? | The spreadsheet does not recognise a word | A mistyped function such as =SUMM(B2:B6), or text in a formula without quotation marks | Correct the spelling of the function name. |
#REF! | The reference points to a cell that no longer exists | You deleted a row or column that the formula used | Undo the delete with Ctrl+Z, or rewrite the formula with the correct cells. |
#VALUE! | The wrong kind of data in a calculation | Adding text to a number, as in =B2+A2 where A2 holds a word | Use cells with numbers only, or remove stray text and spaces. |
| Row | A | B |
|---|---|---|
| 1 | Formula typed | Shows |
| 2 | =10/0 | #DIV/0! |
| 3 | =SUMM(1,2) | #NAME? |
| 4 | ="Rent"+5 | #VALUE! |
| 5 | =A9 after row deleted | #REF! |
- Marker 1: Divide by zero
- Marker 2: Name not recognised
- Marker 3: Wrong kind of value
- Marker 4: Deleted reference
Read this figure as text
A simulated spreadsheet with a column of formulas and a column of the error messages they produce.
A simulated spreadsheet with two columns, Formula typed in column A and Shows in column B, in rows 2 to 5. Row 2: =10/0 shows #DIV/0!. Row 3: =SUMM(1,2) shows #NAME?. Row 4: ="Rent"+5 shows #VALUE!. Row 5: a formula pointing at a deleted row shows #REF!. Marker 1 points to the divide-by-zero error. Marker 2 points to the unrecognised-name error from the misspelt function. Marker 3 points to the wrong-value error from adding text and a number. Marker 4 points to the reference error.
Common mistakes
✖ Instead of: Typing the total by hand, such as 605.
✔ Do this: Write
=SUM(B2:B6)so the total changes when the numbers change.✖ Instead of: Leaving out the equals sign, so the cell shows
SUM(B2:B6)as plain text.✔ Do this: Every formula starts with
=.✖ Instead of: Copying a formula that points to a rate cell without locking it.
✔ Do this: Use
$F$1for any cell that must not move.✖ Instead of: Summing a range that includes the total itself, or the heading row.
✔ Do this: Select only the numbers to add. Check the coloured outline before pressing Enter.
If something goes wrong
- The total is smaller than I expect.
- Some numbers are probably stored as text, so SUM skips them. Look for left-aligned numbers and remove stray spaces or symbols. Also check that the range includes every row.
- I see a circular reference error (Err:522 in Calc, a circular reference warning in Excel).
- A formula refers to its own cell, for example =SUM(B2:B7) typed in B7. Change the range so that it stops above the cell that holds the formula.
- The formula shows as text and does not calculate.
- Set the cell format to General with Ctrl+1, then press F2 and Enter to re-enter it. Also check there is no space or apostrophe before the equals sign.
- Dragging the fill handle copies the same number instead of calculating.
- Check that the cell contains a formula, not a typed number. If automatic calculation is off, open the Data menu (Calc) or the Formulas tab (Excel) and switch automatic calculation on.
Key terms
- Formula
- An instruction in a spreadsheet cell that calculates a value from other cells.
- function (spreadsheet)
- A spreadsheet function is a ready-made formula with a name, such as SUM or AVERAGE.
- Cell reference
- The name of a spreadsheet cell, made from its column letter and row number.
- relative reference
- A relative reference is a cell address that changes when the formula is copied to another cell.
- absolute reference
- An absolute reference is a cell address with dollar signs that does not change when the formula is copied.
Practise it for real
Reading is not the same as being able to do it. Complete these on your own computer, then tick only what you have actually done. These ticks are your own honest record: nothing checks them for you.
Add formulas to the budget
Open the budget you made earlier, or make a new one with the headings Item, Planned and Actual and five rows of fictional spending. Add a Total row using SUM for both columns. Add a Difference column in D with a formula copied down the rows. Below the table add Average, Smallest and Largest rows using AVERAGE, MIN and MAX on the Actual column. Then change one Actual number and check that the totals and differences update. Save the file in your practice folder.
Challenge: a rate that must stay fixed
Make a list of five fictional items with prices. Put a rate of 10% in one cell outside the list. In a new column, calculate each price plus 10%, using an absolute reference to the rate. Copy the formula down. Then change the rate to 15% in one place and check that every row updates. Finally, deliberately cause a #DIV/0! error and fix it.
Knowledge check
7 questions. You need 80% to pass. Mistakes here are part of learning, and you can retry as often as you like.
Summary
- A formula starts with = and uses cell references so that it recalculates when numbers change.
- SUM, AVERAGE, MIN, MAX and COUNT work on ranges such as B2:B6.
- A relative reference moves when you copy the formula. An absolute reference with dollar signs stays put.
- Errors such as #DIV/0!, #NAME?, #REF! and #VALUE! each point to a specific kind of mistake.
- Always check a result against a rough estimate.
Checklist: can you do these?
Marking a lesson as read does not prove you can do it. Skills count as demonstrated when you pass the quiz (not yet) and complete the practical tasks (not yet).
Related lessons
References and further reading
Official sources for details that can change between versions.
- LibreOffice Calc Guide (The Document Foundation)
- Calc functions and formulas help (The Document Foundation)
- Excel help and learning (Microsoft)