Absolute cell references allow you to “fix” part or all of a cell reference in a formula. This can be a great time-saver when copying formulas, however, there is more to it than meets the eye…
In the example below, the formula in C3, used to calculate the bonus figure is =B3*H2
This formula is copied to C4:C6. However, Excel converts the formulas as follows:
C4: =B4 * H3
C5: =B5 * H4
C6: =B6 * H5
This is how Excel is designed to work and in many instances is the desired behaviour.
In the example below, the formula in D3 is =B3+C3.
Excel interprets this as “add together the values in the 2 cells to the left”. When D3 is copied to D4:D6, this “add together the values in the 2 cells to the left” is what is copied (rather than the actual formula). The result of the copy is:
In the example below, the formula in C3 is =B3*$H$2
The $ signs around H2 indicate to Excel that H2 is an Absolute Cell Reference. This means that when the formula is copied, the reference to H2 remains constant (or fixed):
C4: =B4 * $H$2
C5: =B5 * $H$2
C6: =B6 * $H$2
A Part Absolute (also known as a Mixed Reference) Formula is where either the row or the column is fixed. This would be displayed as $C1 (Column C is fixed) or C$1 (Row 1 is fixed). Consider the example below:
The PMT Function calculates the payback on a loan. The format of the function is: =PMT(Rate, Number of Payments, Loan Amount)
The spreadsheet calculates the monthly repayment on a 15 year loan for different loan amounts at different interest rates.
The PMT function should be entered into C6 and then copied down to C7:C10 and then across to D6:F10.
Type the following into C6: =PMT($B6,$C$3,C$5)
The first cell reference, $B6, indicates that we always want to refer to the values in column B but the row reference (Rate) should change when the cell is copied.
Similarly, the mixed reference, C$5, indicates that we always want to refer to the values in row 5 but the column reference (Loan Amount) should change when the cell is copied.
The loan is fixed at 15 years (C3) and when the cell is copied, the reference to C3 should remain fixed.
Without mixed references, you would have to edit the formulas manually in each of the cells in the range C6:F10.
The concept of Absolute Cell References is found in all spreadsheet applications (including Google Docs). In Excel (and some other spreadsheet apps) you can use function key F4 to put the $ signs into a formula/function. Simply press F4 after typing or pointing to the cell to be made absolute. Pressing F4 multiple times cycles through the absolute options. For example, if whilst constructing a formula, you type A1 and press F4, you get $A$1.
Press F4 again and you get A$1.
Press F4 again and you get $A1.
Press F4 again and you get A1.
I have produced a working example (zipped Excel file) which can be downloaded from here.