What is a cell reference in Excel, and what does $A$1 mean?
Quick answer
A cell reference is the address of a cell in a formula, such as B2. A relative reference (B2) shifts when you copy the formula to another cell; an absolute reference ($B$2) stays fixed; a mixed reference ($B2 or B$2) fixes only the column or only the row.
Last updated
Updated · By Robert Breen
Why it matters
Copying one formula down a column is how a spreadsheet does a hundred calculations in a second. That only works if each copy points at the right cells. Relative references move with the copy, which is usually what you want; absolute references stay put, which is what you want for a single rate or total that every row uses.
Getting this wrong is one of the most common spreadsheet errors: the first row is right, every row below is quietly wrong, and nobody notices until a total looks odd.
A worked example
A staffing agency calculates each contractor’s pay with tax set aside. Hours are in column B, the hourly rate in column C, and the 22% withholding rate sits once in cell F1. The first row’s formula is =B2*C2*$F$1. Copied down, it becomes =B3*C3*$F$1, =B4*C4*$F$1 and so on: hours and rate follow the row, the withholding rate does not.
Written as =B2*C2*F1 instead, the second row would read F2, which is empty, and show zero withholding. The fix is two dollar signs.
Often confused with
Relative vs absolute
Relative (A1) adjusts when copied. Absolute ($A$1) never changes. The dollar sign locks whatever comes right after it.
Mixed references
$A1 locks the column but lets the row change; A$1 locks the row. They are useful in grids, such as a price table where rows are products and columns are quantities.
Cell reference vs structured reference
Inside an Excel table, Excel can write references by column name, such as [@Hours], instead of B2. They are easier to read and do not need dollar signs.
Tips
- In desktop Excel for Windows, select a reference in the formula bar and press F4 to cycle through the reference types. In Excel for the web, type the dollar signs yourself.
- Put rates and settings in their own clearly labeled cells and refer to them absolutely.
- After copying a formula, click two or three cells below and read their formulas.
Related terms
Learn it in a free lesson
- Excel for the Web Tutorial: Your First Budget Spreadsheet (about 15 minutes)
Sources
Written from these official help pages. Menus change, so if a button has moved, the help page has the current wording.
- Microsoft Support: Switch between relative, absolute and mixed references
- Microsoft Support: Overview of formulas in Excel
Frequently asked questions
- What does the $ sign mean in an Excel formula?
- It locks the column, the row, or both, so that part of the reference does not change when the formula is copied.
- What is the shortcut for an absolute reference?
- F4 in Excel for Windows (Cmd+T or F4 on a Mac). Microsoft notes the shortcut does not apply in Excel for the web.
- Does Google Sheets use the same references?
- Yes. A1, $A$1, $A1 and A$1 work the same way in Google Sheets.
All microsoft 365 terms, A to Z · Free microsoft 365 lessons