Relative, Absolute, and Mixed References

Why reference types matter on MO-210

Domain 4 of Exam MO-210 (Perform Operations by Using Formulas and Functions) includes the skill Insert references. At Associate level that means you can build formulas that use relative, absolute, and mixed cell references correctly, then copy or fill them so every destination cell calculates the right value. Graders do not only look at one finished number—they inspect formula text after Auto Fill, so a missing $ is a frequent score loss even when the first row looks perfect.

Task wording often sounds like "In cell C2, multiply quantity by the unit price in B2, then fill down", "Calculate sales tax using the rate in cell H1 for every row", or "Create a multiplication table that locks the row headers and column headers appropriately." Those map to choosing B2 vs $H$1 vs $B2 / B$2, and using F4 to toggle the dollar signs quickly.

Relative references (the default)

A relative reference is written without dollar signs: A1, B2, C10:C20. Excel stores the offset from the formula cell, not a fixed address. When you copy or fill the formula, that offset moves with it.

Worked example: line totals

Enter =B2*C2 in D2. Drag the fill handle down to D4. Excel adjusts to =B3*C3 and =B4*C4. That is correct relative behavior: each row multiplies its own quantity and price.

If you had written =$B$2*$C$2 by mistake and filled down, every row would repeat the Pens calculation. Relative references are the right default whenever the formula should track the same shape of cells as it moves.

Absolute references ($A$1)

An absolute reference locks both column and row with dollar signs: $H$1, $Tax!$B$2. No matter where you copy the formula, Excel keeps pointing at that same cell.

Worked example: shared tax rate

Cell H1 holds 0.08 (8% sales tax). Column D should compute tax for each line total in column C:

C2 = 100
C3 = 250
C4 = 75
H1 = 0.08

In D2 enter =C2*$H$1. Fill down. Results:

• D2: =C2*$H$1 → 8
• D3: =C3*$H$1 → 20
• D4: =C4*$H$1 → 6

C2 stays relative so each row uses its own total; $H$1 stays absolute so every row reads the single rate cell. On MO-210, if you leave H1 relative (=C2*H1) and fill down, D3 becomes =C3*H2—often blank or wrong—and you lose the task.

Exam habit: Any constant the project stores once (tax rate, discount, commission, hourly rate, conversion factor) usually needs $ on both column and row before you fill.

Mixed references ($A1 and A$1)

A mixed reference locks only one axis:

Mixed references shine when you build grids: prices across the top, quantities down the side, or lookup-style multiplication tables.

Worked example: price × quantity grid

Row 1 has unit prices in B1:D1. Column A has quantities in A2:A5. You need each cell in B2:D5 to multiply the quantity from column A by the price from row 1.

In B2 enter:

=$A2*B$1

• $A2 keeps column A locked when you fill right (always use quantity from column A), but the row can change when you fill down.
• B$1 keeps row 1 locked when you fill down (always use the header price), but the column can change when you fill right.

Fill B2 across to D2, then fill that row down to row 5. Sample formulas after fill:

If you used fully relative =A2*B1 and filled right, B2's neighbor becomes =B2*C1—wrong. If you used fully absolute =$A$2*$B$1 everywhere, every cell repeats one product. Mixed references are the Associate-level tool for this pattern.

Toggling with F4

While editing a formula, place the cursor on (or select) a reference and press F4. Excel cycles:

1. A1 (relative)
2. $A$1 (absolute)
3. A$1 (mixed — row locked)
4. $A1 (mixed — column locked)
5. back to A1

On many Mac keyboards the cycle is Fn+F4 or Command+T, depending on Excel settings and the keyboard. Learn the cycle on your lab machine before exam day. F4 is faster and safer than typing dollar signs by hand under time pressure.

You can also insert references by clicking cells while building a formula; Excel inserts relative references by default. Select the reference in the formula bar and press F4 until the locks match the task.

Fill, copy, and paste behavior

• Fill handle / Auto Fill: relative parts shift; absolute parts stay; mixed parts shift only on the unlocked axis.
• Copy and Paste: same adjustment rules as fill when pasting formulas.
• Cut and Paste: Excel usually keeps the same reference addresses (it does not re-offset the way copy does). Know the difference if a task says cut versus copy.
• Paste Values: replaces formulas with numbers—avoid unless the task asks for values only.

Display formulas with Formulas → Show Formulas (or Ctrl+) to audit $` signs across a filled block before you move to the next project step.

Common MO-210 traps

• Filling a rate formula without locking the rate cell (H1 instead of $H$1).
• Locking everything ($B$2*$C$2) when rows should stay relative.
• Using $A$2 in a grid when $A2 or A$2 was required—one wrong lock breaks the entire fill.
• Typing dollar signs on the wrong character ($A1$ is invalid).
• Checking only the first calculated cell and never verifying formulas two or three rows down.
• Confusing named ranges (Domain 2) with absolute references—both can "lock" a value, but the task wording tells you which tool to use.

Practice sequence for the exam

1. Build =B2*C2, fill down, confirm relative adjustment.
2. Add a rate in G1, rewrite as =B2*$G$1, fill down, open Show Formulas and verify $G$1 on every row.
3. Build a small 3×3 product grid with =$A2*B$1, fill across and down, spot-check corner cells.
4. Start with a relative reference and press F4 through a full cycle so muscle memory is automatic.

Master relative, absolute, and mixed locks plus F4, and you cover the classic cell-reference half of Domain 4's Insert references objective.