📎 This article includes 1 downloadable practice file ↓
In this article
You write a perfect commission formula in the first row, copy it down, and every other row shows 0 or an error. It’s the most common Excel frustration, and the fix is a single character: the dollar sign.
Relative references move
In Lesson 4, =B2*C2 copied down became =B3*C3, =B4*C4. That’s a relative reference: Excel remembers “the cell one to the left and two to the left” and shifts it as you copy. Usually that’s what you want.
When moving is wrong
Commission = sales × a single rate in B8.
C2: =B2*B8 ' correct in row 2
C3: =B3*B9 ' copied: B9 is empty → 0
C4: =B4*B10 ' copied: B10 is text → #VALUE!The sales reference should move; the rate reference should not.
Absolute references stay: $
C2: =B2*$B$8The $ before B locks the column; the $ before 8 locks the row. Copy it down and every row uses B8: =B3*$B$8, =B4*$B$8…
Mixed references: lock just one part
| Reference | Column | Row | Copied down | Copied across |
|---|---|---|---|---|
| B8 | moves | moves | B9 | C8 |
| $B$8 | fixed | fixed | $B$8 | $B$8 |
| $B8 | fixed | moves | $B9 | $B8 |
| B$8 | moves | fixed | B$8 | C$8 |
The exercise that makes it click: a multiplication table
Numbers 1–10 down A2:A11, and 1–10 across B1:K1. In B2 write one formula that, copied across and down, fills the whole table:
B2: =$A2*B$1$A2: always column A (the row numbers), but the row changes. B$1: always row 1 (the column numbers), but the column changes. Fill right and down; every cell is correct. If you understand why, you understand references.
Share of total: a classic use
D2: =B2/SUM($B$2:$B$6)Each rep’s sales divided by the team total. Without the $ the total range slides down and the shares stop adding to 100%.
Names: an easier way to fix a cell
Click B8, type CommRate in the Name Box (left of the formula bar) and press Enter. Now write =B2*CommRate. Names are always absolute and far easier to read six months later. Manage them in Formulas › Name Manager.
Tables don’t need $
Inside an Excel Table, =[@Sales]*CommRate fills down correctly by itself, another reason to use Tables (Lesson 8).
Where beginners go wrong
| Symptom | Cause |
|---|---|
| First row right, others 0 | Rate cell not locked: use $B$8 |
| Every row shows the first row’s answer | Locked the wrong part: $B$2 instead of B2 for the sales |
| Shares don’t add up to 100% | Total range not locked |
| Formula wrong after copying sideways | Row locked but column not, or the other way round; think about which direction you copy |
Practice
Download this lesson’s workbook below. The Comm sheet has five reps, a commission rate and a USD rate; one formula must work in every row. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 11 practice workbookA commission sheet with a rate cell and a USD rate: copy one formula down correctly, plus team shares.⬇ XLSX · 13 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.