Lesson 2: Absolute references: lock the tax rate with $

Practice exercises for accountants, checked instantly in your browser.

When you fill a formula down, Excel moves every reference with it: =B2*G1 in row 2 becomes =B3*G2 in row 3. That suits the invoice amount but breaks the tax rate, which lives in one cell.

A dollar sign locks a reference. $G$1 stays on G1 wherever you copy it. G$1 locks only the row, and $G1 locks only the column. Press F4 while the cursor is on a reference to cycle through the options. Forgetting the lock is an easy way to lose points on an Excel test, because row 2 looks right and the error only shows further down.

Worked example: =B2*$G$1
Net amount in this row times the rate fixed in G1. Filled down, B2 becomes B3, B4… while $G$1 stays put.

Exercises

Exercise 1

Brightwater Plumbing's sales tax rate is in G1. Enter a formula in C2 for the tax on INV-201. It will be filled down to C6.

Exercise 2

Tax is now in column C. In D2, calculate the gross amount (net plus tax). It will be filled down to D6.

Exercise 3

Sales staff earn the commission rate in G2 on the net amount. In E2, calculate the commission and make it fill down to E6.