Excel Practice
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.

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.

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

The grid is a price table. Paper sizes run across the top, with a unit price under each in row 2. Print runs go down column A. One formula can fill the whole block if each reference is locked in one direction only. $A4 locks the column, so the copies always come from column A. B$2 locks the row, so the price always comes from row 2.

In B4, type =$A4*B$2 to cost 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

    The grid is a price table. Paper sizes run across the top, with a unit price under each in row 2. Print runs go down column A. One formula can fill the whole block if each reference is locked in one direction only. $A4 locks the column, so the copies always come from column A. B$2 locks the row, so the price always comes from row 2. In B4, type =$A4*B$2 to cost 250 A5 copies.

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

  2. 2

    That formula was filled across and down the block. In C6 it now reads =$A6*C$2. Read it as it sits in C6 and say what it returns.

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

  3. 3

    C5 was filled across and down from B4 and has come out wrong. The formula in it should cost 500 copies at the A4 price. Repair it.

    Hint. Which row do the prices sit in?

  4. 4

    Now the bottom right of the block. 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

    Somebody locked both parts of both references. Read =$A$4*$B$2 and say what it returns in D7.

    Hint. A fully locked reference points at the same cell everywhere.