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):
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):
Here is a rule of thumb for using excelabsolute 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.
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 absolute 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 relative). 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|
|=$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 Filling 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 AutoFilling 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 Formulas tab, in the Formula Auditing 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: