Relative And Absolute Formulas
October 08, 2002 - by Bill Jelen
Merwyn from England sent in this problem.
Is there a single formula in cell B2 which can be copied across and down to create a multiplication table?
Merwyn's goal is to enter a single formula in B2 which can easily be copied to all 144 cells to create a multiplication reference table.
Let's attack this as an Excel novice and see what pitfalls we run in to. One's initial reaction might be to have the formula in B2 =A2*B1. This formula produces the correct result, but it is not suitable for Merwyn's assignment.
When you copy this formula from cell B2 to cell C2, the cells referenced in the formula also move over one cell. The formula which was =A2*B1 now becomes =C1*B2. This is a feature designed into Microsoft Excel and is called a relative formula reference. As you copy a formula, the cell references in the formula also move a corresponding number of cells across and down.
There are times when you do not want Excel to exhibit this behavior and the current case is one of those. To prevent Excel from changing the reference while copying the cells, insert a "$" before the portion of the reference which you want to freeze. Examples:
- $A1 tells Excel you always want to refer to column A.
- B$1 tells Excel you always want to refer to row 1.
- $B$1 tells Excel you always want to refer to cell B1.
Learning this code will dramatically decrease the time required to build worksheets. Rather than having to enter 144 formulas, Merwyn can use this shorthand to enter one single formula and copy it to all cells.
Looking at Merwyn's formula, =B1*A2, we realize that the "B1" part of the expression should always point to a number in Row 1. This should be rewritten as "B$1". The "A2" section of the formula should always point to a number in column A. This should be rewritten as "$A2". So, the new formula in cell B2 is =B$1*$A2.
After entering the formula in B2, I can copy and paste it to the appropriate range. Excel follows my instructions and after doing the copy, I find the following formulas:
- Cell M2 contains
=M$1*$A2 - Cell B13 contains
=B$1*$A13 - Cell M13 contains
=M$1*$A13
Each of these types of formulas has a name. Formulas without any dollar signs are called relative formulas. Formulas where both the row and column are locked with a dollar sign are called absolute formulas. Formulas where either the row or column are locked with a dollar sign are called mixed formulas.
There is a shorthand method for entering the dollar signs. As you are typing a formula and finish a cell reference, you can hit the key to toggle between the 4 reference types. Let's say you started typing a formula and you typed =100*G87.
- Hit F4 and your formula changes to
=100*$G$87 - Hit F4 again and your formula changes to
=100*G$87 - Hit F4 again and your formula changes to
=100*$G87 - Hit F4 again and your formula returns to the original
=100*G87
You can pause during formula entry at each cell reference to hit F4 until you get the right reference type. The keystrokes to enter Merwyn's formula above is =B1*A1.
One of my favorite uses of mixed formula references comes up when I have a long list of entries in column A. When I want a quick and dirty method to find duplicates, I sometimes enter this formula in cell B4 and then copy it down:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Note the 2nd term of the VLOOKUP formula uses both an absolute ($A$2) and a mixed ($A3) reference to describe the lookup range. In English, I am telling Excel to search always from cell $A$2 to the cell in column A just above the current cell. As soon as Excel encounters a duplicate value, the result of this formula changes from #N/A! to a value.
With creative use of the $ in cell references, you can make sure that one single formula can be copied to many cells with correct results.
