Excel Cell Reference Types: Relative, Absolute, and Mixed

Excel has three main cell reference types: relative (A1), absolute ($A$1), and mixed ($A1 or A$1). They determine whether a formula’s column, row, or both adjust when you copy or fill the formula into other cells.

What a cell reference means

A cell reference identifies a cell by its column letter and row number. In A1 notation, B3 means the cell in column B, row 3. Formulas use references to read values from cells, so a formula such as =A2+B2 adds the contents of A2 and B2. The reference type controls how those addresses behave when the formula is copied.

The dollar signs in a reference are markers, not currency symbols. They lock a column, a row, or both during copying. A dollar sign before the column letter locks that column; one before the row number locks that row. This is about how a reference changes when a formula moves, not whether the referenced cell can be edited.

Relative: A1; neither the column nor row is locked.
Absolute: $A$1; both the column and row are locked.
Mixed: $A1 locks the column, while A$1 locks the row.

These are the three core reference types most people mean when asking about Excel cell references. Excel also supports ranges, references to other sheets and workbooks, named ranges, and table references; those describe what is being referenced or how it is written, rather than adding more row-and-column locking combinations.

Relative references: the default

When you enter a normal reference such as A1, it is relative. Excel tracks the position of the referenced cell in relation to the formula cell. If you copy the formula elsewhere, Excel updates the reference by the same row and column displacement. This makes relative references useful for calculations that repeat across a list or grid.

For example, with quantities in A2:A4 and unit prices in B2:B4, enter the formula in C2. If A2 contains 3 and B2 contains 12, the result is 36. Copy the formula down one row and Excel changes the references to A3 and B3, so the next result uses that row’s quantity and price.

With quantities in A2:A4, prices in B2:B4, and the first calculation in C2, use =A2*B2. If A2 is 3 and B2 is 12, the result is 36.

Relative references are often the right choice when each formula should use the data alongside it. They are also why copied formulas usually work without manually editing every row. However, if a formula must keep pointing to one particular rate, threshold, or input cell, a relative reference may drift away from it as the formula is filled.

Absolute references: lock both coordinates

An absolute reference puts a dollar sign before both the column and row, as in $A$1. When you copy a formula, that reference continues to point to A1 regardless of where the formula goes. The cell’s value can still change; only the address is fixed within the copied formula.

This is useful when many calculations share one input, such as a tax rate stored in a single cell. Suppose A2 contains a subtotal of 100 and B1 contains a tax rate of 8%. In C2, the formula below returns 108. When copied to C3 or D2, the reference to B1 remains fixed, while the relative reference to the subtotal can adjust as needed.

With subtotals in A2:A4, the tax rate in B1, and the first total in C2, use =A2*(1+$B$1). If A2 is 100 and B1 is 8%, the result is 108.

Absolute references are particularly helpful for assumptions kept in a small input area: exchange rates, commission percentages, conversion factors, or a fixed lookup value. They can also make a formula easier to audit because the fixed dependency is explicit. Still, locking a reference is not the same as protecting a cell from changes. Worksheet protection is a separate feature.

Mixed references: lock only a row or column

A mixed reference locks one coordinate and leaves the other relative. In $A1, the column A stays fixed but the row can change. In A$1, the row 1 stays fixed but the column can change. Mixed references are valuable when a formula will be copied in two directions across a table.

Consider a multiplication grid with item values down A2:A4 and adjustment factors across B1:D1. A formula in B2 should always use the item value from column A in its current row, and the factor from row 1 in its current column. The column in $A2 is locked, while its row can change; the row in B$1 is locked, while its column can change. The formula can then be filled across and down without rewriting it.

With item values in A2:A4, factors in B1:D1, and the first result in B2, use =$A2*B$1. If A2 is 10 and B1 is 1.1, B2 returns 11. Copied to C3, the formula becomes =$A3*C$1.

That behavior makes mixed references a practical choice for rate tables, pricing matrices, and schedules where each result combines a row input with a column input. The pattern can take a moment to understand, so test a formula in one cell and inspect how it changes when copied one cell right and one cell down.

How copying and filling change references

Copying a formula does not simply preserve its written address. Excel adjusts each relative part based on the formula’s new location, while keeping any absolute part fixed. The same rule applies when you use the fill handle, copy and paste, or fill a selected range with a formula.

For a formula in C2 copied to D3, the destination is one column right and one row down. A2 becomes B3 because both parts are relative. $A$2 stays $A$2. $A2 becomes $A3, and A$2 becomes B$2. Thinking in terms of the movement—right, left, up, or down—usually makes the result predictable.

Write the formula in its first intended cell.
Check which inputs should move with each result and which should stay fixed.
Add dollar signs only to the coordinates that must not move.
Copy or fill the formula, then inspect a few destination cells to confirm the references.

Excel’s fill handle is the small square at the lower-right corner of a selected cell. Drag it to extend a formula into adjacent cells. You can also copy and paste, or select a destination range and use Ctrl+D to fill down or Ctrl+R to fill right on Windows. On Mac, the corresponding shortcuts are typically Control+D and Control+R. Shortcuts can vary with keyboard settings, so the ribbon commands are another option.

Changing reference types with F4

You do not have to type every dollar sign manually. While editing a formula, place the insertion point in a cell reference or select that reference, then press F4 in Excel for Windows to cycle through its relative, absolute, and mixed forms. Starting from A1, successive presses cycle through $A$1, A$1, $A1, and back to A1.

On a Mac, Command+T cycles reference styles while editing a formula. Keyboard behavior can depend on the Mac’s function-key settings and Excel release; if the shortcut does not act as expected, enter the dollar signs directly. F4 may also require the Fn key on some laptop keyboards. The shortcut changes the selected reference in the formula, not the cell’s contents or formatting.

F4 is especially convenient when building a formula that uses one fixed input alongside changing row data. It is still worth reading the final formula before filling it. A reference can be syntactically valid but locked in the wrong direction, leading to plausible-looking yet incorrect results.

Ranges, sheets, and other reference styles

A range reference identifies multiple adjacent cells, usually with a colon between the first and last cell, such as A2:A10. The same locking rules apply to the endpoints. For example, $A$2:$A$10 locks both endpoints; A$2:A$10 keeps the row numbers fixed but leaves the column relative. The correct choice depends on how the range should shift when the formula is copied.

A reference can also name a cell on another worksheet. Excel writes a sheet name, an exclamation mark, and the cell address, as in Rates!B2. You can combine a sheet reference with dollar signs, for example Rates!$B$2, to keep both coordinates fixed when copying. If a sheet name contains spaces, Excel encloses it in single quotation marks, as in ‘Sales Data’!B2.

References to another workbook can include the workbook name and worksheet name. These links may depend on the source workbook’s location and whether it is open, so they require more care than references within one sheet. A workbook link is not a fourth cell-reference type: its row and column can still be relative, absolute, or mixed.

Excel’s R1C1 reference style is another way to display addresses. Instead of a column letter and row number, it uses row and column numbers. In Windows Excel, you can change the setting through File > Options > Formulas and select R1C1 reference style. This changes how addresses are displayed, not the underlying idea of relative and fixed references.

Named ranges and Excel table references offer more readable alternatives in some workbooks. A name such as TaxRate can stand for a cell or range, while a table formula can refer to a column by its heading. They have their own rules and can be easier to maintain in structured workbooks, but they do not replace the basic understanding of relative, absolute, and mixed cell references.

Choosing the right type and avoiding mistakes

Choose a reference by asking what should happen when the formula is copied. If both row and column should follow the formula, use a relative reference. If neither should change, use an absolute reference. If only one coordinate should follow, use a mixed reference. A single formula can combine all three types when its different inputs have different roles.

Use relative references for row-by-row calculations using each row’s own inputs.
Use absolute references for one shared cell, such as a fixed rate or constant.
Use a column-locked mixed reference when copying down should change rows but not the source column.
Use a row-locked mixed reference when copying across should change columns but not the source row.

A frequent mistake is locking too much. For instance, making both coordinates absolute in a formula intended to use a different row’s subtotal causes every copied formula to keep using the original subtotal. The opposite mistake—leaving a shared rate relative—can make a copied formula point to a blank or unrelated cell. Check the copied formula itself, not only whether the displayed answer looks reasonable.

Another misconception is that a dollar sign makes a reference permanently fixed in the workbook. It only controls how that reference adjusts when the formula is copied or filled. Inserting or deleting rows and columns can affect references as Excel updates the workbook structure, and a formula can still be edited to point elsewhere. Absolute references also do not stop someone from changing the referenced value.

For troubleshooting, select a formula cell and inspect its address components. If a copied result is wrong, compare its formula with the formula in the original cell: identify which row or column changed, then decide whether that coordinate should have been locked. When formulas are difficult to read, use Formula Bar to inspect them or select a reference while editing so Excel highlights the referenced cell or range.

Frequently Asked Questions

What are the four cell reference styles in Excel?
People often describe four written forms: A1, $A$1, $A1, and A$1. They represent three types—relative, absolute, and mixed—with mixed references having two orientations depending on whether the row or column is locked.

What does the dollar sign mean in an Excel formula?
A dollar sign locks the coordinate immediately after it when a formula is copied. In $C5, column C is locked and the row can change; in C$5, row 5 is locked and the column can change.

How do I make a cell reference stay the same when copying a formula?
Put a dollar sign before both the column and row, as in $B$2. While editing the reference, you can also use F4 in Windows Excel or Command+T in Mac Excel to cycle through reference styles.

What is the difference between $A1 and A$1?
$A1 fixes column A but lets the row number adjust. A$1 fixes row 1 but lets the column letter adjust. Choose between them based on the direction in which you plan to copy the formula.

Do absolute references prevent a cell from changing?
No. An absolute reference keeps its address when a formula is copied; it does not freeze the value or protect the cell. To restrict editing, use worksheet protection separately.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.