Spreadsheets and Data

⏱ 8 min✏️ Quiz at the end

The Most-Used Program in the World?

Ask what software runs businesses, science labs, sports clubs, and family budgets, and the honest answer is often not something glamorous — it is the humble spreadsheet. A spreadsheet is a grid that computes: part table, part calculator, part chart-maker. It is also secretly a gateway to programming: formulas are expressions, filling-down is a loop, and a well-built sheet is an algorithm you can see all at once.

Cells and Addresses

The grid: columns lettered A, B, C…, rows numbered 1, 2, 3… Each cell — one box — has an address: B3 is column B, row 3, exactly like a map reference (and close cousin to a list index, just in two dimensions).

A cell holds one of two things:

  • A value — a number, some text, a date
  • A formula — a calculation, announced by =

Formulas: Cells That Think

Type 12 into A1 and 30 into A2. In A3, type:

=A1+A2

A3 displays 42 — not the formula, its result. And here is the moment that makes spreadsheets special: change A1 to 20, and A3 becomes 50, by itself. Every formula watches the cells it references and recalculates automatically — and formulas can reference other formulas, so one change ripples correctly through an entire model. A spreadsheet is a web of live expressions, always up to date.

That ripple is the spreadsheet's answer to "what if?": what if we sell 200 tickets instead of 150? what if costs rise 10%? Change one input cell and read off the new future. Modelling that would take a program takes a keystroke.

Functions and Ranges

Adding thirty cells as =B2+B3+B4+… would be misery. Spreadsheets provide functions — prebuilt calculations, the same idea as functions in programming — applied to ranges of cells:

=SUM(B2:B31)        total of the thirty cells B2 to B31
=AVERAGE(B2:B31)    their mean
=MAX(B2:B31)        the largest
=COUNTIF(B2:B31, ">50")    how many exceed 50
=IF(C2>=40, "Pass", "Fail")    selection, in a cell!

B2:B31 (the colon means "through") is the range. And yes — that last one is a genuine IF condition living in a grid square. Sequence, selection, arithmetic: the ingredients of programming, wearing a friendlier face.

One more trick completes the toolkit: fill down. Write =A2*B2 (price × quantity) in C2, drag it down the column, and the spreadsheet adjusts each copy — =A3*B3, =A4*B4… Each row processed identically by one formula written once: a loop over a table, made visible.

From Numbers to Pictures

Columns of numbers hide their story; charts tell it. Select your data, insert a chart, and patterns leap out — the trend across months (line chart), the comparison between categories (bar chart), the share of a whole (pie chart). In science class, plotting experiment results this way is data analysis: spot the pattern, question the outlier.

About that outlier — one warning deserves capital letters: GIGO — garbage in, garbage out. A formula computes perfectly on whatever it is given. One mistyped 1000 for 10 and your class-average formula reports nonsense with total confidence. The remedies are habits, not features: sanity-check results ("can the average really be 14.2 if most marks look like 60?"), eyeball extremes with MAX and MIN, and treat surprising answers as bugs to investigate, not truths to report.

Spreadsheet or Database?

Spreadsheets and databases both hold tables, so which is which for? Rough guide: a spreadsheet is a brilliant personal calculator for hundreds-to-thousands of rows, one or few users, and lots of what-if. A database is infrastructure — many simultaneous users, millions of records, strict consistency. The club treasurer wants a spreadsheet; the ticketing website needs a database.

Try It Yourself

Build a mini tuck-shop model: column A items, B price, C quantity sold, D =B2*C2 filled down, and a SUM for total income. Add =IF(D2>10, "star seller", "") in E. Now the what-if: raise all prices 10% and watch the total. Which cells did you change — and which changed themselves?

Worked Example — Relative vs Absolute References

Fill-down usually works exactly as you want — but occasionally it silently breaks a formula, and understanding why is a genuinely useful spreadsheet skill. Suppose column B holds prices, and a single cell E1 holds a tax rate of 20%. In C2, you write =B2*E1 to calculate tax, intending to fill this formula down the whole column:

C2:  =B2*E1     →   works correctly
C3:  =B3*E2     ←   fill-down shifted E1 into E2, which is empty!

The reference to B2 correctly became B3 (each row needs its own price) — but the reference to E1 also shifted, to E2, even though the tax rate lives in exactly one place and should never move. This is the difference between a relative reference (shifts when copied, like B2) and an absolute reference (stays fixed, written $E$1 in most spreadsheet programs). The corrected formula, =B2*$E$1, fills down perfectly: =B3*$E$1, =B4*$E$1, always pointing at the one fixed tax cell. This single dollar-sign convention is one of the most common places beginners get tripped up, and recognising the symptom — "my filled-down formula gives sensible results in some rows and nonsense or errors in others" — quickly points to a reference that should have been absolute but wasn't.

Key Words

  • Cell / address — one grid box, named by column letter + row number (B3)
  • Formula — an = calculation whose result the cell displays
  • Automatic recalculation — dependent formulas update the instant inputs change
  • Range / function — a block of cells (B2:B31) / a prebuilt calculation (SUM, AVERAGE, IF)
  • GIGO — garbage in, garbage out: perfect formulas on bad data give confident nonsense
Related Topics