The $ sign, the F4 key, sheet prefixes, and named ranges โ the small syntax that separates a fragile spreadsheet from a maintainable one. Cycle references live, refactor disaster formulas, and scan the full syntax matrix.
A reference syntax is how a spreadsheet formula points to another cell. A1 is relative โ it shifts when copied; $A$1 is absolute โ it stays fixed; $A1 and A$1 lock only the column or the row. The F4 key cycles these four states, with $ acting as a padlock on whatever follows it. A named range is a readable alias such as Sales_Amount standing in for a raw address like Sales!$A$2:$A$500, so formulas become self-documenting and survive row insertions instead of silently breaking.
Excel extends this in three directions. Cross-sheet references add a sheet prefix โ Sheet2!A:A, or 'Sheet Name'!A1 when the name contains a space โ and cross-workbook references add [Book.xlsx]. Names have scope: workbook-scoped names work everywhere, while sheet-scoped ones are prefixed like Sheet1!TaxRate. Structured references such as Sales[Amount] address an Excel Table by column name and expand automatically when rows are added. Choosing the right form is the difference between a model that survives editing and one that fails quietly.
A $ before the column locks the column; a $ before the row locks the row. Click the button (or press F4 / Fn+F4) to cycle. Watch the highlighted cell and the copy target: blue = fixed, amber = locked row, red ring = where the formula lands when copied down-and-right.
A lookup table like $E$2:$G$500 is absolute because the table must not drift when you fill the formula down a thousand rows. A criteria cell like $A1 locks the column so a horizontal fill keeps reading the same key column. Get the $ in the wrong place and every copied cell quietly points at the wrong data โ the kind of bug that survives review for months.
Same result, radically different readability. Each case below is a real pattern โ raw addresses on the left, named ranges or structured references on the right.
A2 should stay relative (it changes per row) but the table must be pinned. Locking only the table is the canonical fix.'Price List'! quoting every time. A name removes the quoting burden and hides the physical layout.Click the sheet tabs to inspect a multi-sheet workbook. The syntax on the right updates to show exactly how you address each sheet from another sheet.
Sales!A:A
A single-part sheet name needs no quotes. Sales!A:A references the whole column A on the Sales sheet โ whole-column refs grow with your data.
! is the sheet; everything after is the range.'Price List'!A1 โ the single quotes protect the space in the name.PriceList!$A$2:$B$50 combines a sheet prefix with absolute addressing.
15 structured rows spanning relative/absolute/mixed references, named-range scope, cross-sheet and cross-workbook references, and structured table references. Built to be quoted and reused.
| Category | Syntax | Name | Behavior / when to use |
|---|---|---|---|
| RELATIVE | A1 | Fully relative | Both column and row shift when copied. Default for per-row keys and fill-down sequences. |
| ABSOLUTE | $A$1 | Fully absolute | Neither part moves. Use for fixed constants, tax rates, and lookup tables copied across rows. |
| MIXED | $A1 | Lock column | Column fixed, row moves. Ideal for a criteria column when filling formulas to the right. |
| MIXED | A$1 | Lock row | Row fixed, column moves. Use for a header row or a single assumption row in a horizontal model. |
| KEY | F4 / Fn+F4 | Cycle toggle | Rotates A1 โ $A1 โ A$1 โ $A$1 โ A1 while the cursor is inside a reference. |
| KEY | F5 | Name box / Go To | Opens Go To; also where you can jump to or define a named range by name. |
| NAMED | Sales_Amount | Workbook-scoped name | Valid in every sheet. Default scope. Self-documenting and survives row insertions. |
| NAMED | Sheet1!TaxRate | Sheet-scoped name | Valid only inside Sheet1. Reuse the same name with different values on different sheets. |
| NAMED | =year โ 2026 | Dynamic named formula | A name can hold a formula, not just a range; recalculates wherever the name is used. |
| CROSS-SHEET | Sheet2!A1 | Simple sheet ref | One cell on Sheet2. No quotes needed for a single-word name. |
| CROSS-SHEET | Sheet2!A:A | Whole-column sheet ref | Entire column A on Sheet2; expands as data grows. Avoid on whole arrays in old Excel for speed. |
| CROSS-SHEET | 'Sheet Name'!A1 | Quoted sheet ref | Required when the sheet name has spaces or special characters. |
| CROSS-WORKBOOK | [Book.xlsx]Sheet1!A1 | External ref | Points to another workbook. Shows #REF! if the source file is closed or moved. |
| STRUCTURED | Sales[Amount] | Table column ref | References one column of an Excel Table by name; auto-expands with new rows. |
| STRUCTURED | Sales[[#Totals],[Amount]] | Table totals row | Targets the Totals row of a specific table column in structured-reference notation. |