totn Excel

MS Excel 2003: Relative vs Absolute referencing

In Microsoft Excel 2003/XP/2000/97 when you create a formula, chances are you've referenced another cell within your formula. This cell reference can either be a relative or an absolute reference.

What is a relative reference?

A relative reference occurs when a cell is entered in a formula without using the $ symbol.

For example,

=A1

When you copy this formula to another cell, Excel automatically adjusts the cell reference to refer to different cells relative to the position of the formula.

In our example below, we've created a formula in cell D1. Then we've copied this formula to cells D2, E1, and E2. As you can see, the original reference to cell A1 has been readjusted.

Microsoft Excel

What is an absolute reference?

An absolute reference occurs when a cell is entered in a formula using the $ symbol.

For example:

=$A$1
=A$1
=$A1

Each component of the cell reference can either be defined as absolute or relative.

Let's look at an example. Again, we've created a formula in cell D1. Then we've copied this formula to cells D2, E1, and E2. As you can see, the original formula has stayed intact. This is because column the column (A) and row (1) cell references have been absolutely referenced.

Microsoft Excel

Absolute column and relative row

Now, let's look at some hybrid references where we use both absolute and relative referencing.

In this example, we'll look at a reference involving an absolute column with a relative row reference.

Again, we've creating a formula in cell D1. Then we've copied this formula to cells D2, E1, and E2. This time the reference to column A remains static while the reference to the row is readjusted.

Microsoft Excel

Relative column and absolute row

Our last combination involves referencing a cell with a relative column and an absolute row reference.

Again, we've creating a formula in cell D1. Then we've copied this formula to cells D2, E1, and E2. This time the reference to the column is readjusted with the row remains static.

Microsoft Excel

Frequently Asked Questions

Question: I'm using the "drag and Ctrl key" to repeat a formula, however the formula adds 1 to both the beginning and the end of my range, how do I correct this?

For example, if I have the following formula:

=COUNTIF(A2:A45,B6)

When I copy the formula to the next cell down, the formula changes to

=COUNTIF(A3:A46,B7)

How can I keep the formula the same when copying?

Answer: To create a formula that does not readjust when copied to other cells, you need to create an absolute reference (ie: $ symbol). In this case, you'd use the following formula:

=COUNTIF($A$2:$A$45,$B$6)

If you wanted the A2:A45 range to remain absolute, but the B6 range to readjust when copied to the next cell, you'd use the following formula:

=COUNTIF($A$2:$A$45,B6)
Share on: