A spreadsheet can look perfect until one wrong cell quietly feeds five other formulas. Today you learn how to open the bonnet: show every formula on the sheet, spot what each formula depends on, and check that your Something Real numbers still make sense before anyone else reads them.
Think about one formula already in your Module 3 workbook (a total, a VAT line, hours worked, or an IF result). If that formula suddenly showed the wrong answer, which other cells would you check first, and why? If you are not sure, use the stock example your teacher shows on screen, or the practice tab you will build in the next activity.
Formula auditing is how you check that a spreadsheet still tells the truth after you edit it. Your teacher will demo these three ideas live first; use the table as a quick map while you work.
| Concept | Why it matters | Example |
|---|---|---|
| Show formulas view — a display mode that reveals the formula text in every cell instead of the calculated result | You can scan a whole sheet for typos, broken references, and missing equals signs without clicking cell by cell | Your enterprise sheet shows =B2*C2 and =SUM(D2:D6) instead of 24.00 and 180.50, so mistakes jump out |
| Precedent — a cell that a formula reads from (the input) | If a precedent is wrong or empty, every formula that uses it will be wrong too | In =B2*C2, cells B2 and C2 are the precedents; wrong qty in C2 breaks the line total |
| Dependent — a cell whose formula uses the cell you are looking at (the knock-on result) | Before you delete or move a cell, you need to know what else will break | Cell D5 holds =SUM(D2:D4), so D5 is a dependent of D2; change D2 and the total moves with it |
Spot-check: pick one important formula and verify it by hand (phone calculator or mental maths) — one careful check often catches a pattern error early.
Trace arrows / range highlighting: desktop Excel can draw Trace Precedents and Trace Dependents arrows; Excel Online and Google Sheets show coloured ranges when you edit a formula. Your teacher will show whichever your room supports.
Imagine a short Centra-style stock sheet your teacher shows on screen:
| Item | Net € | Qty | Line total € |
|---|---|---|---|
| Milk 2L | 1.45 | 12 | =B2*C2 → 17.40 |
| Bread | 1.20 | 8 | =B3*C3 → 9.60 |
| Eggs (box) | 2.80 | 5 | =B4*C4 → 14.00 |
| Total | =SUM(D2:D4) → 41.00 |
Precedents of D2 are B2 and C2. D5 is a dependent of D2, D3, and D4. Show-formulas view makes that chain readable in one glance.
Follow the steps for your spreadsheet app to open a Module 3 workbook, switch on show-formulas view, identify precedents and dependents, spot-check one formula, and capture a clear screenshot. Your teacher will demo the first pass on the classroom screen first.
By the end of the next activity you will drop that screenshot into {{code:sm3_formula_audit}} inside {{code:09_specialism/sm3/}}.
Use YOUR open Module 3 workbook — not the sample table. The sample is only a guide. If you have no formulas yet, copy three linked formulas from the teacher demo onto a practice tab first (two line totals and a SUM).
While you audit, jot notes on scrap paper or in a blank Notepad/Docs tab — you will paste these into {{code:sm3_formula_audit}} next. Use this fill-in shape:
| Issue | Solution |
|---|---|
| Show formulas does nothing / shortcut fails | Use the menu path instead of the shortcut: Excel Online {{menu:Formulas -> Show Formulas}}; Google Sheets {{menu:View -> Show formulas}}. On some keyboards the backtick {{code:`}} shares a key with ¬ or ~ — hold Shift only if your teacher confirms that layout needs it. On Mac Google Sheets use {{kbd:Cmd+`}}, not Control. |
| My screenshot only shows numbers, not formulas | Turn show-formulas view on again and wait until you can read {{code:=}} signs in the cells, then capture. Zoom in slightly if the text is tiny. Do not turn the view off until the shot is saved. |
| I cannot tell precedents from dependents | Precedents are named inside the formula you are reading (the cells after the equals sign). Dependents are other formulas elsewhere that mention this cell. Start from one simple {{formula:=B2*C2}} line, list B2 and C2 as precedents, then find the SUM or total that includes that line total. |
| Trace arrows are missing | Full Trace Precedents / Trace Dependents arrows are a desktop Excel feature. In Excel Online and Google Sheets, use show-formulas view and coloured highlights when you edit a formula cell instead. |
| I have no formulas in my sheet | Open a different Module 3 workbook ({{code:sm3_vat_calc}} or {{code:sm3_time_tracker}}), or quickly add two linked formulas on a practice tab (line total and a SUM) before auditing. Plain typed numbers cannot be audited as formulas. |
Before you build your own file, look at this short model of {{code:sm3_formula_audit}}. Your teacher will show a completed page on the board (or read this sample aloud). Copy the structure with your own cell references from your Something Real sheet — do not copy these demo cells if yours are different.
Title: Formula audit — [your project name]
Screenshot: [show-formulas capture pasted here, with {{code:=}} signs readable]
Formula 1: In D2: {{formula:=B2*C2}}.
Precedents: B2 (net €), C2 (qty).
Formula 2: In D5: {{formula:=SUM(D2:D4)}}.
Precedents: D2, D3, D4 (the line totals).
Dependent knock-on: If C2 (qty) changed, D2 would change, and the D5 total {{formula:=SUM(D2:D4)}} would change with it.
That three-part shape — screenshot, two formulas with precedents, one dependent sentence — is exactly what you will produce next with your own workbook.
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.