**YOUR OBJECTIVE**

When copying a formula from one cell to another using the *Auto Fill* function, you sometimes want a reference to remain unchanged and not be automatically adjusted. With the *Auto Fill* function, however, the cell references in a formula are automatically adjusted in accord with the position of the new cell. If the automatic adjustment is not suitable for your table, it can lead to an error message or incorrect result. By using an **excel absolute reference** , however, you can prevent

*Auto Fill*from making such adjustments to cell references.

In this learning module, you will find out how to use excel *absolute references* and some things you should keep in mind when doing so.

**HOW DOES IT WORK?**

In the following example, the formula in cell C4 references the value in cell F4. In the following illustration, you can see that formula in the *formula bar*: (

If you would now copy the formula in C4 down into each successive row, the reference to cell F4 would automatically shift one cell down, producing the following, though incorrect, result:

In this example, VAT (sales tax) cannot be calculated for cells C5 to C9 in this way because any reference to an empty cell, such as F5, is incorrect. As you can see in the following illustration*, Auto Fill* incremented each* *row reference as it copied downward in the column. That is: *Auto Fill* changed F4 in the initial cell to F5, then F5 to F6, and so on…

In this example, it is desired, however, that *Auto Fill* only shifts the first cell reference (B4) down by one row at each occurrence of the formula. Only the second cell reference (F4) in the formula should not be shifted at all. It should always point to F4, where the tax rate was entered. To prevent *Auto Fill *from shifting the F4 cell reference, you can make it an *absolute reference*.

*Excel absolute references* distinguish themselves from *relative references* by the use of dollar signs ($). To change a cell reference from *relative* to *absolute*, enter a dollar sign ($) before the row and/or column designation. In this example, F4 (the relative reference) would become $F$4 (an absolute reference in both row and column):

**HINT**

You can also change a* relative reference *to an *absolute reference* by clicking on the cell reference in the *formula bar* and then pressing the** [F4]** function key.Press **[F4]** once and the reference to cell F4 will be converted to an *absolute reference*. If, in the present example, you would now use *AutoFill* to copy the formula with the excel *absolute reference* from cell C4 to the cells below it, the *absolute* cell *reference* will not change at all (see illustration):

**HINT**

Here is a rule of thumb for using excel*absolute references*: Whenever a value in your table is addressed by many (or all) formulas, it is very likely that you should use an* absolute reference*.

**CAUTION**

Not using an excel *absolute reference *when you need to do so, is a* *a very common source of errors. If you happen to receive unexpected or obviously incorrect results after copying a formula, the first thing you should do is check to see whether you should have used an excel *absolute reference*.

Besides *relative* and *abs*olute *references*, there is yet another form called* mixed references*. We speak of a *mixed reference* when only one component of a *cell reference* is *absolute* (the other being *relat*ive). For example: A$1 and $B4 are both *mixed references*. In order to create a *mixed reference*, you can enter in the dollar sign ($) manually, or while pointing at a cell reference in the *formula bar*, press **[F4]** as many times as necessary until the desired reference type appears.

If the cursor in the *formula bar* (or in the cell when in *edit mode*) is positioned within *or* directly before *or* immediately after the *reference*, you can step through the various reference types just by pressing **[F4] **multiple times.

Press **[F4]** multiple times to create the desired reference form. For example…

Cell contents | Press [F4] |
New cell contents |

=B5 | F4 |
=$B$5(absolute reference) |

=$B$5 | F4 |
=B$5 (mixed reference) |

=B$5 | F4 |
=$B4 (mixed reference) |

=$B4 | F4 |
=B5 (relative reference) |

When copying formulas with *mixed references*, the reference with the dollar sign ($) preceding always remains constant. Take **B$5** for example: when copying to the right, the column reference will be incremented to C, D, E and so on, but row 5 will *always *be addressed. In the case of **$B5**, however: when copying a formula downward, though the row designation will change to 6, 7, 8 and so on, the reference to the B column will remain fixed.

The following example illustrates the use of *mixed references*:

In a given table, each value in a column is to be calculated by adding the number at the head of its own column (in the example: **B1**, **C1**, **D1** & **E1**) to the numbers in successive rows of column **A**. That is, the expected value in **B2** should be the sum of **A2+B1**; the expected value in **B3** would be the sum of **A3+B1** … the value in **C2** would be **A2+C1**; the value of **C3** would be **A3+C1**, and so on.

In the illustration below, a seemingly reasonable formula has been entered into **B2**:

If you now use *Auto Fill* to copy this formula all other cells, the results would look like this:

However, if you examine the table more closely, you can see that cell **C2** contains a **4** (**=B2+C1**) instead of the desired** 3** (**=A2+C1**). The other cells have incorrect results, too. *Auto Fill*ing to the right incremented every column reference in the formula by one column, which resulted in **B2** (instead of **A2**) being added to **C1**. … The same problem occured when *AutoFill*ing downward: While cell **C3** should contain a **4** (**=A3+C1**), it contains instead an **8** (**=B3+C2**) because the cell references were shifted both down and across.

To make sure that the first term of every formula always addresses column A -and- that the second term in each formula always addresses the cell at the top of its own column (**B1**, **C1** … etc.), you have to use *mixed references*. After a bit of thought, we see that the *correct* entry in cell B2 would be: **=$A2+B$1**.

Now when you use *AutoFill*, the first term of the formula will *always* refer to column A (only the row number will be adjusted); and the second term of each formula will always address its own column’s first row (only the column reference can be changed). Now when you transfer the new formula from **B2** (**=$A2+B$1**) to all other cells, the results will look like this:

And if you display the formulas now (click ** Show Formulas** on the

**tab, in the**

*Formulas***group), you will see that the reference to column A (in the first term) and to row 1 (in the second term) remain unchanged in all formulas:**

*Formula Auditing*

Kein Banner zum Anzeigen