Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell. If, for example, you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A)—in the same row (2). When you copy a formula that contains a relative cell reference, that reference in the formula will change.

As an example, if you copy the formula =B4*C4 from cell D4 to D5, the formula in D5 adjusts to the right by one column and becomes =B5*C5. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (B and C) and row (2) with a dollar sign ($). Then, when you copy the formula =$B$4*$C$4 from D4 to D5, the formula stays exactly the same.

Relative Cell reference

Less often, you may want to mixed absolute and relative cell references by preceding either the column or the row value with a dollar sign—which fixes either the column or the row (for example, $B4 or C$4).

To change the type of cell reference:

  1. Select the cell that contains the formula.

  2. In the formula bar Button image, select the reference that you want to change.

  3. Press F4 to switch between the reference types.

    The table below summarizes how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.

For a formula being copied:

If the reference is:

It changes to:

Formula being copied from A1, to two cells down and to the right

$A$1 (absolute column and absolute row)

$A$1 (the reference is absolute)

A$1 (relative column and absolute row)

C$1 (the reference is mixed)

$A1 (absolute column and relative row)

$A3 (the reference is mixed)

A1 (relative column and relative row)

C3 (the reference is relative)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×