The way to "turn off the magic" is to use absolute cell addressing. By putting a dollar sign before the letter and number in an address (see the examples), you can tell the machine, "Hey! When I say A4, I mean A4!".
By changing our original sheet to use the absolute address $C$3
| A | B | C | |
| 1 | Salespeople And Sales | ||
| 2 | |||
| 3 | Commrate | 8% | |
| 4 | |||
| 5 | Person | Gross Sales | Commission |
| 6 | Bill | $135,000 | =B6*$C$3 |
| 7 | Mike | $110,000 | |
| 8 | Molly | $225,000 |
We can copy the formula and see that the absolute address doesn't change, while the relative address does.
| A | B | C | |
| 1 | Salespeople And Sales | ||
| 2 | |||
| 3 | Commrate | 8% | |
| 4 | |||
| 5 | Person | Gross Sales | Commission |
| 6 | Bill | $135,000 | =B6*$C$3 |
| 7 | Mike | $110,000 | =B7*$C$3 |
| 8 | Molly | $225,000 | =B8*$C$3 |
