Course / Spreadsheet Basics

Mixed references

Lock just the row or just the column to build a grid from one formula. Intermediate · 4 min · $A1, A$1

A mixed reference locks only one part:

• $A2 locks the column, so the row can still change
• B$1 locks the row, so the column can still change

This lets one formula fill a whole table. Each copy reads its row header from column A and its column header from row 1.

Steps

1. In B2, multiply the row number in column A by the column number in row 1, locking column A and row 1: =$A2*B$1

2. Fill the whole table B2:F6 from that one formula. Drag the fill handle right to F2, then drag the selected row down to row 6.

3. In G2:G6, add up each row of the table.

Remember

• $A1: column locked, row free.
• A$1: row locked, column free.
• Mixed references let one formula fill a two-way table.

Previous: Absolute references with $ · Next: Percentages and number formats