Mixed References for Tables

Absolute references lock both the column and row. But sometimes you need to lock only one of them. That is a mixed reference.

Mixed references are the key to filling a whole table from a single formula, because they let a reference slide in one direction while staying fixed in the other.

There are exactly two mixed forms:

• $A1 the column is locked, the row is free
• A$1 the row is locked, the column is free

Remember the rule: the dollar sign locks whatever comes immediately after it.

$A1 always stays in column A, but its row can change. Copy it down and you get $A2, $A3. Copy it right and it stays $A1 because the column is locked.

Use this when every column of your table needs to read the same input column, such as labels or base values down the left side.

A$1 always stays in row 1, but its column can change. Copy it right and you get B$1, C$1. Copy it down and it stays A$1 because the row is locked.

Use this when every row of your table needs to read the same header row across the top.

A grid pulls from two directions at once: a row of headers across the top and a column of labels down the side. Each inner cell combines one from each.

To fill the whole grid from one formula, the row source must lock its row, and the column source must lock its column. That is exactly what mixed references provide.

Say sales amounts run down column A (in A2, A3, ...) and commission rates run across row 1 (in B1, C1, ...). To fill the grid you write in B2:

=$A2*B$1

The $A2 always reads the sales column; the B$1 always reads the rate row.

Copy =$A2*B$1 from B2 down to B3:

• $A2 becomes $A3 (row free, moves down)
• B$1 stays B$1 (row locked)

So B3 reads =$A3*B$1: the next sales amount times the same first rate. Exactly right.

Now copy =$A2*B$1 from B2 across to C2:

• $A2 stays $A2 (column locked)
• B$1 becomes C$1 (column free, moves right)

So C2 reads =$A2*C$1: the same sales amount times the next rate. The single formula adapts both ways.

To decide where the dollar goes, ask which direction the reference must stay as you fill the grid:

• It must stay in one column? Lock the column: $A2.
• It must stay in one row? Lock the row: B$1.

Lock the part that should not move, leave the part that should.

Mixed references are not only for math. Build a distance table where cities run down column A and the same cities run across row 1. Each inner cell looks up the distance between its row city and column city.

The lookup formula uses $A2 for the row city and B$1 for the column city, so one formula fills the entire grid just like a multiplication table.

Pressing F4 repeatedly cycles through all four styles, so you can land on either mixed form:

• 1st press: $A$1
• 2nd press: A$1 (row locked)
• 3rd press: $A1 (column locked)
• 4th press: A1

Stop on the press that locks the part you need.

Check your understanding of mixed references.

Mixed references lock one part and free the other:

• $A1 locks the column, row moves
• A$1 locks the row, column moves

They let a single formula fill an entire table that pulls from a header row and a label column. Next you'll put this to work building a full multiplication table from one formula.