Absolute references ($) practice exercises

When you copy a formula down, every cell reference moves with it. That's usually what you want, except for a setting like a commission rate that every row must use. A $ locks the reference: $K$1 stays put when the formula is copied.

Syntax

A1 (moves)  ·  $A$1 (locked)  ·  A$1 (row locked)  ·  $A1 (column locked)
A1
Relative: changes to A2, A3... as you copy down.
$A$1
Absolute: always A1. Use it for settings, rates and totals.
A$1 / $A1
Mixed: only the row or only the column is locked.

The practice data

The orders table again, plus three settings in column K that formulas must keep pointing at. Every exercise below uses this sheet, and your answer goes in H2.

ABCDEFGHIJK
1RepRegionProductUnitsPriceSalesCommission0.05
2AnaEastWidget12560Unit target8
3BenWestGadget520100Markup0.2
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

Worked example

=F2*$K$1

Commission for each row: the Sales in that row times the one rate in K1. Copied down to row 9, the F reference moves but $K$1 stays. Result:

3
5
8
5
7.5
1.75
5
6

Your turn: 3 exercises

Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.

1 Return each row's share of total Sales (Sales divided by the sum of all Sales). Copied down to H9.

H2

2 Return how far each row's Units are above or below the target in K2. Copied down to H9.

H2

3 Return each row's Price including the markup in K3 (Price × (1 + markup)). Copied down to H9.

H2

Practice Absolute references ($) in the full lesson, free

Lesson 3 has more exercises in a real spreadsheet grid, with hints and progress tracking. No card needed.

Open lesson 3

Common Absolute references ($) mistakes

FAQ

Is there a shortcut for adding $?

Yes. Put the cursor on a reference and press F4 (Windows) or Fn+F4 / ⌘+T (Mac) to cycle through $A$1, A$1, $A1 and A1.

When do I need a mixed reference?

When a formula is copied both down and across, such as a multiplication table: =$A2*B$1.

Keep practicing