Absolute cell references in Excel: lock the cells that must not move

Accounting work is full of one formula repeated down a column: commission on each sale, each expense as a share of the total, each month's cost split across departments. Write it once, fill it down, and the spreadsheet adjusts every reference for you. That is helpful until it adjusts a reference that should have stayed put. This page shows how relative, absolute and mixed references work, walks through two examples with real numbers, and covers the mistakes that quietly break a schedule.

Why accountants need to control references

Most accounting schedules have two kinds of input. Some values change on every row: the sales for each salesperson, the amount of each expense, the balance of each account. Other values are shared by every row: one commission rate, one monthly total, one allocation percentage. A good formula treats these two kinds differently. The row values should move as you fill the formula down. The shared value should stay fixed.

If you get this wrong, Excel does not warn you. The first row looks right, because the first row is where you wrote the formula. The damage appears further down, where nobody checks closely. A commission schedule might pay one person correctly and everyone else nothing. A cost allocation might charge every month at January's rate.

This is also the foundation for everything else in the course. Lookups, running balances, depreciation schedules and loan tables all depend on knowing which part of a formula moves and which part stays. That is why this lesson comes first in our MO-220 practice path.

How references move when you copy a formula

A cell reference such as B5 is stored as a position relative to the cell that holds the formula. If you type =B5*B2 in C5, Excel effectively remembers "the cell one column to the left, same row" and "the cell one column to the left, three rows up". When you fill the formula down to C6, both references shift one row: the formula becomes =B6*B3. This is a relative reference.

A dollar sign stops the shift. It locks whichever part it sits in front of:

Using F4 to add the dollar signs

You can type the dollar signs by hand, but there is a faster way. While you are editing a formula, put the cursor in or just after a reference and press F4. Each press cycles through the four forms in order:

1. $B$2: column and row locked (absolute).
2. B$2: only the row locked.
3. $B2: only the column locked.
4. B2: nothing locked (back to relative).

On some laptops you need to hold the Fn key with F4.

Worked example 1: commission at one example rate

Northside Print Shop pays its salespeople a commission at one example rate of 6.00%, stored in B2. Salespeople are listed in A5:A14 and their sales in B5:B14. The task is to fill C5:C14 with each person's commission, using one formula written in C5 and filled down.

Think about each part of the formula before you type it. The sales figure changes on every row, so it should be relative: B5. The rate is the same for everyone and lives in one cell, so it must be absolute: $B$2. The formula in C5 is:

=B5*$B$2

Fill it down and Excel writes =B6*$B$2 in C6, =B7*$B$2 in C7, and so on. The sales reference walks down the rows, and the rate reference stays anchored. Here are the first four rows:

A quick way to check your work: each commission should be a small, sensible fraction of the sales next to it. At 6%, $34,828.18 of sales gives a little over $2,000. If any row is zero, an error, or larger than the sales figure, a reference has moved.

Notice what you gain by keeping the rate in its own cell. If the example rate changes, you edit B2 once and every commission updates.

Worked example 2: a mixed-reference grid

Some schedules need to copy one formula both down and across. Maple Lane Catering splits its shared office costs across departments. Each month's cost sits in row 4 (C4:F4, April to July), and each department's share sits in column B (B6:B10). Every cell in C6:F10 should be that month's cost multiplied by that department's share.

Here neither fully relative nor fully absolute references work. Look at what each part must do:

• The cost must always come from row 4, but the month changes as you copy across. Lock the row, leave the column free: C$4.
• The share must always come from column B, but the department changes as you copy down. Lock the column, leave the row free: $B6.

The formula in C6 is =C$4*$B6. Copied to F10, it becomes =F$4*$B10: July's cost times the Operations share. One formula fills all twenty cells correctly.

A useful check for any allocation: each month's column should add back to that month's cost, as long as the shares add to 100%. Here they do (20%, 5%, 15%, 35% and 25%), so a total row under the grid should match row 4 exactly.

Common mistakes and how to spot them

These are the errors our exercises look for. Each one passes on the first row and fails further down, which is why you should always check a few rows below the one you wrote.

1. The rate reference moves when you fill down

Writing =B5*B2 works in C5. Filled down, it becomes =B6*B3, then =B7*B4, and so on. B3 is empty, so the second commission is zero. B4 holds the heading "Sales", so the third row shows #VALUE!. Lower rows multiply one person's sales by another's and produce absurd figures in the hundreds of millions. The fix is to lock the rate: $B$2.

2. Locking the sales reference too

Some learners discover dollar signs and lock everything: =$B$5*$B$2. Now nothing moves, so every row repeats Casey's $2,089.69. Lock only the shared value. The row value stays relative.

3. Dividing by a total that is not locked

When you show each expense as a share of the month's total, the formula is the expense divided by the total, for example =B5/$B$15. If the total is left as B15, the next row divides by B16, which is empty. Dividing by an empty cell gives #DIV/0! in every row below the first. When you see that error running down a column, look for an unlocked denominator.

4. Dividing the total by the amount

The share of total is the part divided by the whole: =B5/$B$15. Writing =$B$15/B5 turns it upside down and gives numbers above 1, which would display as shares well over 100%. A quick test: every share should be between 0% and 100%, and the shares together should add to 100%.

5. Fully locking the cost in the grid

In the allocation grid, writing =$C$4*$B6 locks the cost to April. Copied across, May, June and July all use April's $12,795.07, so Finance shows $2,559.01 in every month. Lock only the row: C$4.

6. Leaving the cost row free

The opposite error is =C4*$B6. The first department is right, but copied down, the cost reference slides to C5 (a month name), then C6 (the Finance allocation itself), and so on. The result is a mix of #VALUE! errors and small numbers that are shares of shares. Locking the row with C$4 keeps every department pointed at the cost line.

A routine for any formula you fill

Before you type the formula, ask one question for each reference: when I copy this, should this part move? Then apply the lock that matches the answer.

• Moves down and across: leave it relative, like B5.
• Never moves: make it absolute, like $B$2.
• Moves across but not down: lock the row, like C$4.
• Moves down but not across: lock the column, like $B6.

After filling, click a cell near the bottom of the range and read its formula. If the shared reference still points at the right cell, the range is almost certainly right.

How this shows up in MO-220 practice

MO-220, the Excel for Accounting exam, is a 50-minute, performance-based exam [Official]. Its tasks describe the purpose rather than naming the function, so you will not be told "use an absolute reference" [Official]. A task might simply ask you to calculate a value for every row using a rate shown at the top of the sheet. Knowing that this means a locked reference, and getting it right on the first try, saves time you will need for harder tasks.

Accounting Exercises is not affiliated with Microsoft or Certiport. Our original practice for Microsoft® Excel® skills checks each answer by running your formulas and comparing the results, so any correct formula passes.