Formulas and cell references: relative, absolute and mixed

The real power of a spreadsheet is that one formula can be copied down hundreds of rows. To do that correctly you must understand cell references, the most important concept in Excel and Google Sheets.

Formulas recap

Every formula starts with =:

Order of operations: brackets, then ^ (power), then * and /, then + and -.

Relative references (the default)

In this price list, D2 has =B2*C2:

When you copy D2 down (drag the fill handle, the small square at the cell's corner, or double-click it), Excel adjusts the references: row 3 gets =B3*C3. That's a relative reference: it means "the cell two to the left, on my row".

Absolute references: $ locks a cell

Now add VAT using a rate stored in one cell, F1 = 16%. In E2 you type =D2*F1 and copy down... row 3 becomes =D3*F2, which is empty. Wrong!

Lock the rate with dollar signs: =D2*$F$1. Now every row uses F1.

Shortcut: after typing a reference, press F4 to cycle through F1 → $F$1 → F$1 → $F1.

Mixed references

Classic example: a multiplication table or a price grid. In B2, =$A2*B$1 copied across and down multiplies each row header by each column header.

Referencing other sheets

=Prices!B2                (cell B2 on the sheet called Prices)
=SUM('Term 1'!C2:C40)     (quote sheet names that contain spaces)

Named ranges: easier to read

Select F1, type a name like VAT in the Name Box (left of the formula bar) and press Enter. Now write =D2*VAT: clear, and automatically absolute.

Common errors and what they mean

Show all formulas at once with Ctrl + ` (the key left of 1), great for checking a sheet.

Practice

Build a fee sheet: columns Name, Fee, Paid, Balance (=B2-C2), and a % paid column =C2/B2 formatted as a percentage. Add a cell with a late-payment penalty rate and a Penalty column that uses it with an absolute reference.

Why cell references matter

The power of a spreadsheet is that you write a formula once and copy it to hundreds of rows. Cell references decide what happens when you copy: whether the formula follows along (relative) or keeps pointing at one fixed cell (absolute), such as a VAT rate, commission percentage or exchange rate. Almost every spreadsheet error in offices, SACCOs, schools and shops comes from a wrong reference, and fixing it is a skill employers test in interviews.

Worked example: a price list with a markup

Type the formula in C3 once and drag the fill handle down. B3 changes to B4, B5 (relative), but $B$1 stays fixed. Change the markup in B1 to 30% and every selling price updates instantly. This is the habit that separates a spreadsheet from a calculator: put changeable values in their own cells and reference them.

Building a multiplication or pricing grid with mixed references

A grid where rows are quantities and columns are prices uses one formula for the whole table:

Enter =$A2*B$1 in B2 and fill right and down. $A keeps reading the quantity column; $1 keeps reading the price row. One formula fills all 9 cells correctly.

Settings sheets: a professional pattern

Keep rates and constants on a separate sheet called Settings:

Settings!B1   VAT rate          16%
Settings!B2   Commission rate   5%
Settings!B3   USD to KES rate   (update when it changes)

Then formulas read them:

=C2*Settings!$B$1          VAT for the row
=D2*Settings!$B$2          commission
='Sales Data'!B5           a sheet name with spaces needs single quotes

Better still, name the cells (Formulas → Define Name, or type in the Name Box left of the formula bar): =C2*VAT_Rate reads like English and never needs $ signs, because names are always absolute.

References to whole columns and ranges

=SUM(B:B)                  total of a whole column (don't put the total inside column B!)
=SUM(Sales[Amount])        Table column: grows automatically as rows are added
=AVERAGE(B2:B31)           average of 30 days

Putting =SUM(B:B) inside column B creates a circular reference (the formula includes itself), and Excel warns you.

3D references: the same cell across sheets

If you have one sheet per month (Jan, Feb, Mar) with the same layout:

=SUM(Jan:Mar!B10)          adds B10 from every sheet between Jan and Mar

This is how a quarterly summary is built from monthly sheets without retyping.

Tracing and checking formulas

Before sending a spreadsheet to a manager or client, press Ctrl + ` and scan for formulas that point to the wrong place or hard-typed numbers where a formula should be.

Avoid hard-coded numbers

Hard-coded numbers hide inside formulas. When the rate changes, someone has to find and fix every formula, and usually misses some.

Common reference errors in real work

Practice

1. Build a price list with a markup cell and selling prices that update when the markup changes.
2. Make a 10 × 10 multiplication table using a single formula with mixed references.
3. Create a Settings sheet with VAT and commission rates, name them, and use the names in a sales sheet.
4. Make three monthly sheets with the same layout and a summary using a 3D reference.