Lessons Lesson 9

Mixed References: $B4 and B$4

About this lesson

A mixed reference locks the column or the row, but not both. $B4 always reads column B while its row moves. B$4 always reads row 4 while its column moves.

By the end you can

  • lock only the column with $A4, or only the row with B$2.
  • write one formula that fills a whole block in both directions.

By Ali, Founder, Excel Lessons

The idea

This is the one most people never learn properly. It only makes sense in front of a block. Put the rates across the top and the quantities down the side. Now you need one formula that reads its own row's quantity and its own column's rate. The quantity is always in column A, so lock the column: $A4. The rate is always in row 2, so lock the row: B$2. One formula, filled across and down, fills the whole table.

To read a mixed reference, cover the dollar sign with a finger. Whatever is under it cannot move. Everything else can. If you are not sure you got it right, do not check the cell you typed it in. Check the opposite corner of the block. A formula that is correct where you typed it and correct in the opposite corner is correct everywhere between.

The mistake to watch for

Locking both halves of both references feels safe, and it makes the formula useless. Every cell in the block shows the same number. Locking the wrong half is worse. The formula is right in the cell you typed it in and wrong everywhere else. Check the opposite corner of the block, not the first cell. A formula that is right in both corners is right in between.

Read more about this

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

$A4 locks the column. B$2 locks the row.

In B4, type:

=$A4*B$2

That costs 250 A5 copies.

B4
Row A B C D E F G
1 Print run A5 A4 A3
2 Unit price 0.18 0.26 0.44
3 Copies Cost Cost Cost
4 250
5 500
6 1000
7 2000
8

Every step

  1. 1

    $A4 locks the column. B$2 locks the row. In B4, type: =$A4*B$2. That costs 250 A5 copies.

    Hint. Two references. One dollar sign each, in different places.

  2. 2

    Read =$A6*C$2 and say what it returns in C6.

    Hint. A dollar sign locks the thing immediately after it.

  3. 3

    C5 was filled from B4, but its row now points at Cost, not the price. Fix C5.

    Hint. Which row do the prices sit in?

  4. 4

    In D7, cost 2000 copies at the A3 price, using the same mixed pattern.

    Hint. Same shape as the first one you wrote.

  5. 5

    Two dollar signs lock a reference completely. Read =$A$4*$B$2 and say what it returns in D7.

    Hint. A locked reference stays on the same cell everywhere it goes.