Discord
Me

Spreadsheets: formulae and cell references Cambridge IGCSE Information & Communication Technology revision

Not started

Learn it

In plain words

A spreadsheet is a grid of cells that can calculate. Type a number into one cell, and every cell that depends on it changes. That is what makes it a model: change an input, and see what happens.

7 things to know

  1. A cell is named by its column letter and row number, such as B3.
  2. A formula starts with an equals sign. It is a calculation written by the user, using cell references, numbers and the operators + − * / and ^ for powers.
  3. A function is a ready-made calculation with a name, such as SUM or AVERAGE. A formula can contain functions.
  4. Order of operations: brackets first, then powers, then multiplication and division, then addition and subtraction. Use brackets to force the order you want: =(A1+B1)/2.
  5. A relative cell reference, such as B2, changes when the formula is copied to another cell. An absolute cell reference, such as $B$2, stays the same wherever the formula is copied.
  6. A named cell or named range gives a meaningful name, such as TaxRate, to a cell or a group of cells. It makes formulae easier to read, and it behaves like an absolute reference.
  7. A sheet can display the values or the formulae. Column widths must be set so that everything is fully visible.

Worked example

Cell C2 contains =A2*B2. Cell D2 contains =C2*$F$1, where F1 holds a tax rate. Both are copied down to row 3. What do they become?

  1. Relative references change with the row: C3 contains =A3*B3.
  2. In D3, the relative part changes and the absolute part does not: =C3*$F$1.
  3. Every row uses its own figures but the same tax rate.

Tips and tricks

  • The dollar signs lock a reference. Use an absolute reference for a single value that many formulae share, such as a rate.
  • =A1+B1/2 divides only B1 by 2. For the average of the two, brackets are essential: =(A1+B1)/2.
6 questions, about 2 minutes.

It lands in your notebook with its questions as flashcards.

Spreadsheets: formulae and cell references: 6 questions and answers

These are the quiz’s questions. Do the quiz first, then come back here for the ones that got you.

  1. What must every formula begin with?
    • a bracket
    • an equals sign (the answer)
    • a cell reference
    • a dollar sign

    Without it, the cell just shows text.

  2. Which is an absolute cell reference?
    • B2
    • B:2
    • $B$2 (the answer)
    • #B2

    The dollar signs fix the column and the row.

  3. D4 contains =B4+C4. It is copied to D5. What does D5 contain?
    • =B4+C4
    • =B5+C5 (the answer)
    • =D4+D5
    • =B5+C4

    Relative references move with the formula.

  4. A1 contains 10 and B1 contains 2. What is the value of =A1+B1^2?
    • 24
    • 14 (the answer)
    • 144
    • 22

    The power is worked out first: 2 squared is 4.

  5. Why are named ranges used?
    • to make the file larger
    • to make formulae easier to read and understand (the answer)
    • to hide data
    • to change the font

    =Price*TaxRate is clearer than =B2*$F$1.

  6. What is the difference between a function and a formula?
    • There is none.
    • A function is built in and has a name. A formula is written by the user. (the answer)
    • A function has no brackets.
    • A formula cannot use cell references.

    A formula may contain a function.

Still stuck on this one?Ask in the Papermunch Discord, or help someone else who is. Discord is for ages 13 and up.Join the server

Things you can type

Or go straight to

Or browse a shelf