Mixed References in Excel: $A1 vs A$1 Explained
Mixed references lock only the row or only the column. Learn when to use $A1 and A$1 with a multiplication grid, price tables and SUMIFS summary tables.
By Ayoub Farid · · 2 min read
A mixed reference locks half of an address: the column ($A1) or the row (A$1). You need it when one formula is copied down and across, like a grid or a summary table.
The two kinds
| Reference | Locked | Copying down | Copying right |
|---|---|---|---|
$A1 | Column A | $A2 (row moves) | $A1 (stays in A) |
A$1 | Row 1 | A$1 (stays in row 1) | B$1 (column moves) |
Rule of thumb: the $ sits in front of the part that should stay put.
Example: multiplication grid
Numbers 1–10 down A2:A11 and across B1:K1. In B2:
=$A2*B$1$A2: always read column A (the row number), but move down rows.B$1: always read row 1 (the column number), but move across columns.
Fill right and down: the whole grid fills correctly from one formula.
Example: prices with different rates
Prices down A3:A20, three discount rates across B1:D1. In B3:
=$A3*(1-B$1)Example: SUMIFS summary table
Categories down F2:F6, months across G1:R1:
=SUMIFS($C$2:$C$500,$B$2:$B$500,$F2,$A$2:$A$500,G$1)The data ranges are fully locked, $F2 locks the category column, and G$1 locks the month row. See SUMIFS.
How to type them quickly
Press F4 while the cursor is on a reference. The third press gives B$1, the fourth $B1. See F4 shortcut.
How to check
Fill the formula, then click the bottom-right cell of the grid. Read each reference: does it still point at the header row and header column? If not, a $ is in the wrong place.
Frequently asked questions
What is a mixed cell reference?
A reference with only the column or only the row locked with $, like $A1 or A$1.
When do you use mixed references?
When one formula is copied both down and across, such as grids, rate tables and two-way summaries.
What is the difference between $A1 and A$1?
$A1 keeps the column fixed; A$1 keeps the row fixed.
How do I toggle mixed references?
Press F4 (Windows) or Cmd+T (Mac) repeatedly while editing the reference.