Chapter 4 — Spreadsheets done safely

Chapter 4 — Spreadsheets done safely

Lesson map

Lesson 04 published. Map, example, EX013.

flowchart TD
  N0["Learning outcomes"]
  N1["4.1 A messy spreadsheet"]
  N2["4.2 Organising"]
  N3["4.3 Checks"]
  N4["4.4 Versions"]
  N0 --> N1 --> N2 --> N3 --> N4
  N4 --> EX["Worked example"]
  EX --> Q["Exercise EX013"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["4.1 A messy spreadsheet"]
  N2["4.2 Organising"]
  N3["4.3 Checks"]
  N4["4.4 Versions"]
  N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX013"]

[Learning outcomes]
    |
    v
[4.1 A messy spreadsheet]
    |
    v
[4.2 Organising]
    |
    v
[4.3 Checks]
    |
    v
[4.4 Versions]
    |
    v
[Worked example] --> [Exercise EX013]

Learning outcomes

You will (1) organise spreadsheets clearly; (2) protect formulas and add checks; (3) keep versions under control.

4.1 A messy spreadsheet

Linkway's monthly passenger report is built in a spreadsheet. Marta opens it and finds data pasted from the count system, formulas mixed with typed numbers, colours with no explanation and three hidden tabs. She cannot tell which figures are calculated and which were typed. Mr. Osei explains that spreadsheets like this are common and risky, because nobody can check them easily and one mistake can change every total. The person who built it has left the company, and the current preparer copies the steps without knowing why some of them exist.

4.2 Organising

Marta reorganises a copy: one tab for raw data pasted unchanged, one for calculations, one for the report and one explaining sources and steps. Calculated cells are shaded consistently, with a key. Typed adjustments go in a separate column with a reason. She removes hidden tabs after checking with the report owner that they are not needed. The new layout lets anyone see where each figure comes from. The report owner reviews the copy and agrees to use the new layout from the next month onwards.

4.3 Checks

Marta adds check cells: total passengers in the report must equal the total in the raw data, and the number of trips must match the timetable for the month. If a check fails, the cell turns red. She also protects formula cells so they cannot be overwritten by accident. Mr. Osei notes that simple checks catch most spreadsheet errors, and that protection prevents the most common mistake of typing over a formula. The first month's checks pass, which gives the owner confidence in the new layout.

4.4 Versions

The old report was saved as passenger report final, final2 and final latest. Marta sets up a naming convention with the year and month, saves each month's report in a dated folder and keeps the template separate. She writes the convention on the explanation tab. She notes that clear versions mean the authority can be sent exactly the version that was approved, and earlier versions can be found if questions arise. The report owner adopts the convention and asks other teams to use it too.

Worked example

Marta reorganises a messy passenger report into raw, calculation, report and explanation tabs, adds red check cells and formula protection and replaces final-final names with dated versions.

Synthetic case. Spreadsheets are synthetic.

Errors and limits

Mixing typed numbers with formulas hides manual changes.

Exercises

• EX013 — Hidden tabs. How many hidden tabs did Marta find? Rubric: 3 = three;; 2 = partly correct;; 1 = wrong;; 0 = none.
• EX014 — Adjustment column. Where do typed adjustments go? Rubric: 3 = a separate column with a reason;; 2 = partly correct;; 1 = vague;; 0 = none.
• EX015 — Trip check. What must the trip count match? Rubric: 3 = the timetable for the month;; 2 = partly correct;; 1 = vague;; 0 = none.
• EX016 — Version benefit. What do clear versions allow? Rubric: 3 = sending exactly the approved version;; 2 = partly correct;; 1 = vague;; 0 = none.

Continue studying — lesson 04 (chapter 04 of 18)

← Chapter 03 · Course chapter index · Chapter 05 →

Continue to the next authored lesson.