How to Use Absolute References to Keep Formula Copies Working

When you copy a formula to another cell in Excel, it does not copy the actual cell references. Instead, it keeps the spatial relationship of the cells in the formula.

Say that a cell sums the two cells to its left (e.g., Cell C2 contains the formula =A2+B2).  Copies of that formula will always sum the two cells to their left (e.g., =A7+B7 in C7 or =BA2+BB2 in BC2).  This makes it easy to turn a single formula into a column or row of identical formulas.

We call these, “Relative References”.

But sometimes you want a formula to keep using the same cell, or just the same row or column, when you copy it.  What then?

You need “Absolute References”.

Excel uses the dollar sign to make a row or column reference absolute.  This stops it from changing when copied.  Thus, if reference $A1 is copied, the column reference will not change.  With A$1 the row reference will stay.  The entire reference will be kept if both dollars are used: $A$1.

Shortcut: While editing or entering a formula, put the cursor on a cell reference.  Press the F4 key to cycle through the absolute references: A1 à $A$1 à A$1 à $A1 à A1.

1.           Show Formulas

In the next section, we will want to see the formulas in all cells.

To do this, press Ctrl+` (back apostrophe – on the ~ (tilde) button, top left). Or use the Formulas ribbon > Formula Auditing group > Show Formulas:

2.           Demonstration of Absolute References

Below, we have one block of data and four blocks of formulas.  The latter illustrate the different options for Absolute References:

In cells A1:B2 we have numbers.  We then enter the formula =A1 in cell D1.

As you can see, when we copy this formula to cells below and/or to the right, the cell reference adjusts.  It will always refer to the cell three columns left of itself.

In cell G1, we again use =A1 but now insert the $ before the column letter A.  When we copy the formula to the right, it stays $A.

In cell D4, we insert the $ before the row number 1.  When we copy the formula down, it stays $1.

Finally, in cell G4, we insert both $ signs.  When we copy the formula anywhere, it stays $A$1.

Swapping back to showing answers, not formulas (use Ctrl+` again), we see:

3.           Absolute References Exercise: A Multiplication Table

At school, you might have encountered a table that looks like this.  Kids used to memorise them before we had calculators and computers:

1	2	3	4	5	6	7	8	9	10
2	4	6	8	10	12	14	16	18	20
3	6	9	12	15	18	21	24	27	30
4	8	12	16	20	24	28	32	36	40
5	10	15	20	25	30	35	40	45	50
6	12	18	24	30	36	42	48	54	60
7	14	21	28	35	42	49	56	63	70
8	16	24	32	40	48	56	64	72	80
9	18	27	36	45	54	63	72	81	90
10	20	30	40	50	60	70	80	90	100

The number in any row and column is the leftmost number in that row times the top number in that column. Thus, the 100 at the bottom right = 10 x 10.

Set this up now in a blank worksheet:

1. Fill in the numbers 1 to 10 in cells A1 to A10.  Tip: 1 in A1, 2 in A2.  Select both.  With the mouse, grab the small black + at the bottom right corner of the selection and drag down to A10.
2. Fill in the numbers 2 to 10 in cells B1 to J1.  Tip: Same as above, or use Copy > Paste Transpose.
3. Formula in cell B2:  =A2*B1 will work in B2, but not when you copy it down or across.  Where must the “$” signs go?

Tip: On cell B2, with the mouse, double-click the small black + at the bottom right corner of the selection.  Your formula will be copied into B3:B10.

Answer:

Look at the formula =A2*B1.  We want to fix the leftmost column (A) and the top row (1).  Each of these must get a dollar before it.  A dollar anywhere else will break the copies.  Hence the formula must become =$A2*B$1.  No matter where you copy it to, the formula will always use column A and row 1.  Does this make sense?