Skip to content

MODULE 2 · LESSON 7 · Working with tables

Spreadsheet fundamentals

Read a tabular worksheet and separate raw observations from calculations and presentation.

By Laurence Svekis · Self-paced · All worked data is synthetic unless you supply your own source.

Try the practice ↓

PredictInspectCalculateCheckExplainTransfer

Your starting point

Prerequisites: Use the ideas and vocabulary from lesson 6; revisit its worked examples if needed.

Definition: A spreadsheet organizes cells at row-column intersections. A cell can hold a value or a formula. A cell reference identifies a position; a range identifies a collection of cells. A worksheet is one tab within a workbook.

Give the table a stable structure

Use one header row with meaningful field names, then one consistent record per row. Keep units in the header or dictionary instead of typing them into numeric cells. Avoid merged cells inside a data table: they make row and column meaning ambiguous. Keep explanatory notes outside the record area. A visually attractive worksheet is not necessarily a reliable analytic table.

Separate source and derived work

Keep a raw copy, a cleaned working table and an explanation of calculations. This separation lets you revisit a decision without reconstructing the original from memory. Formatting changes how a value looks; formulas derive a new value. Neither should be confused with correcting the source. A rounded display can conceal decimals that still affect a sum.

Check a small case first

Before copying a formula through many records, calculate one row by hand. Check units and a boundary case, such as zero quantity or a missing price. Then compare the derived column with any source total. A spreadsheet can repeat an incorrect formula perfectly. Your job is to verify that the operation matches the question, not simply that the cells contain results.

Two worked examples

A simple sales row

Suppose cell B2 contains 4 units and C2 contains a price of 15. A spreadsheet formula =B2*C2 produces 60 currency units. The workbench expresses the same operation by selecting units, Multiply and unit_price. A label such as “15 dollars” would need explicit conversion before numerical multiplication.

Display versus value

Two values are 1.24 and 1.24. Displaying each with one decimal place shows 1.2 and 1.2, but their underlying sum is 2.48, which displays as 2.5 at one decimal. Adding the displayed rounded numbers gives 2.4 instead. Decide whether you are calculating from underlying measurements or rounded reports.

Where this becomes useful

A volunteer tracks event expenses. Separate the original receipts, entered transaction rows and a category summary. This makes a later correction traceable without silently replacing the evidence used in an earlier report.

Make an attempt

Load Retail sales in the Formula Builder. Identify the header, record grain and numeric fields. Recalculate one revenue row manually, then create a new verification column. Compare several records rather than trusting the first matching result.

Open the accompanying interactive lab →

Misconception check

Claim: A formula result must be correct because the spreadsheet calculated it.

Inspect the correction

The software follows the operation supplied. It cannot know whether you chose the right units, fields or denominator.

Independent application

Create a six-row fictional expense worksheet with a stable header. Document the units, calculate one derived field by hand and with a tool, and show how a source correction flows into the summary without overwriting the original record.

Completion records practice and self-review. It is not certification of statistical competence.