Categories
Excel

Relative and Absolute Cell References

Knowing how to use relative and absolute cell references will boost your efficiency in Microsoft Excel. Creating a formula then copying it to multiple cells is a big time-saver when you’re building a worksheet – and you’re probably using relative cell references already.

Before I describe what a relative cell reference is, let’s be clear about what a cell reference is. Any time you use a cell address in a formula, you’re referencing that cell. In the expression =B4*C4, B4 and C4 are both cell references.

Relative Cell References

A relative cell reference will change (or update) if you copy or move the formula. Relative cell references are the default in Microsoft Excel.

Let’s look at an example.

In this screenshot, I selected cell D4 and entered the formula =B4*C4. The result is $3.75.

The formula =B4*C4 is entered in cell D4
Relative Cell References

If I copy the formula to the other cells in column D, each row will display a valid total. This works because Excel uses relative cell references by default.

The formula from cell D4 has been copied to D5:D10
Additional Relative Cell References

The formula in cell D5 would say =B5*C5. The formula in D6 would say =B6*C6. The relative cell references within the formula update relative to the current position.

If I move the row about pencils (A4:D4) down to row 12 then double-click the formula in cell D12, it would say =B12*C12.

Formula to calculate the total value of pencils
Relative Cell References in a New Location

A relative cell reference will update if you copy or move the formula.

Absolute Cell References

An absolute cell reference will not change if you copy or move the formula.

Let’s look at another example.

This time, I clicked in cell D4 and typed =(B4*C4)*$E$1. The $E$1 cell reference is absolute. It won’t change if I copy or move the formula.

The B4 and C4 cell references are still relative. They will change but $E$1 cell reference won’t.

Referencing the value in cell E1 using an absolute cell reference
Formula with an Absolute Cell Reference

What Does the Dollar Sign Mean?

The dollar sign indicates that the column letter and / or row number should be constant. $E$1 says that both column E and row 1 are absolute and should not change.

The following tables describes the various combinations of relative and absolute cell addresses.

SyntaxTypeExplanation
E1RelativeColumn and row will change
$E$1AbsoluteColumn and row won’t change
E$1MixedColumn will change, row won’t
$E1MixedColumn won’t change, row will

The F4 Key

You can type to dollar signs as you’re entering absolute cell references but you may find it easier to use the F4 key to cycle through the various options. Let’s take a closer look at entering that last formula.

To calculate the tax for pencils in row 4, I would go through following steps:

  1. I would select cell D4 and type =(B4*C4)*E1.
  2. With my cursor still beside the E1 cell reference, I would press the F4 key.
  3. Then, I would press Enter.
Referencing the value in cell E1 using an absolute cell reference
Formula with an Absolute Cell Reference

After copying the formula to the remaining cells in column D, the worksheet would look like this.

Formula with an absolute cell reference copied to the remaining cells in column D
Formula Copied to Remaining Cells

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.