Skip to content
FORGENO ADS GAMESUnlock all $8

Data Lab · Unit 2 of 8

Unit 2: Spreadsheet basics

Cells, formulas and functions, relative and absolute references, and sorting and filtering, practiced in a working spreadsheet.

Opens the first lesson of this unit in Data Lab. Every lesson is open from the start.

Lesson 1: Cells and formulas

Goal: Write formulas that use cell references and arithmetic.

A spreadsheet is a grid. Columns are lettered (A, B, C...) and rows are numbered (1, 2, 3...). Each cell has an address made of its column and row: B3 is column B, row 3. The same basics work in Excel, Google Sheets and most other spreadsheets.

A formula starts with an equals sign and calculates a value. Instead of typing numbers into formulas, you point at cells. Then, when a number changes, every answer that depends on it updates automatically.

FormulaMeans
=B2*C2B2 times C2
=B2+B3+B4add three cells
=(B2-C2)/C2subtract, then divide. Brackets go first, then the usual math order
=B2*1.08B2 increased by 8%

Worked example: revenue for one day

Cups sold are in B2 (40) and the price per cup is in C2 (1.50).

  1. Click D2.
  2. Type =B2*C2 and press Enter.
  3. D2 shows 60. Change B2 to 50, and D2 changes to 75 by itself.

That automatic update is the whole point. Never type an answer you could calculate.

Hands-on lab in the room: Spreadsheet lab. This is a working spreadsheet. Click a cell, type in the bar above the grid and press Enter. Finish the tasks.

Cell reference
The address of a cell, such as B3: column letter then row number.
Formula
A calculation that starts with = and can use numbers, cell references and functions.

Remember: Start with =, point at cells instead of typing numbers, and the sheet keeps itself up to date.

Lesson 2: Functions: SUM, AVERAGE, MIN, MAX, COUNT

Goal: Summarize a column with the five most-used functions.

A function is a ready-made calculation. You give it a range of cells, written with a colon: B2:B8 means every cell from B2 down to B8.

FunctionWhat it givesExample
SUMthe total=SUM(B2:B8)
AVERAGEthe mean: total divided by how many numbers=AVERAGE(B2:B8)
MIN / MAXthe smallest / largest number=MAX(B2:B8)
COUNThow many cells contain numbers=COUNT(B2:B8)
COUNTAhow many cells are not empty (any type)=COUNTA(A2:A8)

Worked example: a week of lemonade sales

Cups sold Monday to Sunday are in B2:B8: 22, 18, 25, 20, 31, 52, 47.

  1. =SUM(B2:B8) → 215 cups in the week.
  2. =AVERAGE(B2:B8) → 215 ÷ 7 ≈ 30.7 cups a day.
  3. =MAX(B2:B8) → 52, on Saturday.
  4. =MIN(B2:B8) → 18, on Tuesday.
  5. =COUNT(B2:B8) → 7 days with numbers.

Two details that trip people up: AVERAGE and COUNT skip empty cells and text, so an empty cell is not treated as zero. If a day had zero sales, type 0, or the average will be too high.

Hands-on lab in the room: Spreadsheet lab. Use functions to summarize the week.

Function
A built-in calculation, such as SUM or AVERAGE, that takes inputs in brackets.
Range
A block of cells written with a colon, such as B2:B8.

Remember: SUM totals, AVERAGE means, MIN and MAX find extremes, COUNT counts numbers. Empty is not zero.

Lesson 3: Copying formulas: relative and absolute references

Goal: Copy formulas correctly using $ to lock a reference.

Here is the spreadsheet superpower: write a formula once, then copy it down a whole column. When you copy =B2*C2 from D2 down to D3, it becomes =B3*C3 by itself. References that shift like this are called relative references.

But sometimes one cell must stay fixed. Say the sales tax rate is in F1 and every row needs it. If you write =B2*F1 and copy it down, the next row becomes =B3*F2, and F2 is empty. Wrong answers everywhere.

The fix is a dollar sign. $F$1 is an absolute reference: the $ before the F locks the column, and the $ before the 1 locks the row. Copy =B2*$F$1 down, and you get =B3*$F$1, =B4*$F$1... always pointing at the tax rate.

WrittenWhen copied down one rowWhen copied right one column
B2B3C2
$B$2$B$2$B$2
$B2 (column locked)$B3$B2
B$2 (row locked)B$2C$2

Hands-on lab in the room: Spreadsheet lab. Write the tax formula once with an absolute reference, then use Fill down. Try it without the $ first to see what goes wrong.

Relative reference
A cell reference like B2 that shifts when the formula is copied.
Absolute reference
A cell reference like $B$2 that stays fixed when the formula is copied.

Remember: Put a $ in front of anything that must not move when you copy: $F$1 for one fixed cell.

Lesson 4: Sorting and filtering

Goal: Sort and filter data safely to answer quick questions.

Sorting reorders rows by a column: smallest to largest (ascending) or largest to smallest (descending), or A to Z. Sort sales from largest to smallest and your best days are at the top.

Filtering hides the rows you do not want to see, without deleting them: only orders from Riverton, only scores below 60, only dates in March.

Watch out: The classic disaster: selecting just one column and sorting it. The names get reordered but the numbers next to them do not, so every row is now scrambled and wrong. Always select the whole table, or click one cell inside it and use the sort or filter tool, which takes the whole table along.

Most spreadsheets also let you freeze the header row so it stays visible while you scroll, and turn the data into a table or filter view with dropdown arrows on each column. Both make big sheets much easier to work with.

Worked example: which product sold best in March?

  1. Turn on filters for the table.
  2. Filter the date column to March.
  3. Sort the quantity column from largest to smallest.
  4. The top row is the answer. Clear the filter afterwards so nothing stays hidden by accident.
Sort ascending
Smallest to largest, A to Z, or oldest to newest.
Sort descending
Largest to smallest, Z to A, or newest to oldest.
Filter
Temporarily hide rows that do not match a condition, without deleting them.

Remember: Sort the whole table, never one column on its own. Filters hide; they do not delete.

Practise it in Data Lab

Each lesson ends with two or three questions that check you can use the idea, not just recognise it. Wrong answers explain why and come back in Review. The unit ends with a 6-question quiz; pass it with 80% or more to master the unit. Hands-on lab: Spreadsheet lab.

Every lesson is free: no ads, no lives and no paywall.