Cut/copy/paste and fill, and the need to make them convenient, add another layer of wrinkles onto the concept of reference. That complexity is represented by a dollar sign. You may have seen formulas such as:

For examples of absolute and relative references, see the ReferenceExamples. This additional complexity has nothing at all to do with the basic idea that references in formulas retrieve the values of the indicated cell or cells. Indeed, the two formulas (1) and (2) above return the same value at all times on the same worksheet. Always. So what's the difference between them?

The additional complexity relates to how the references behave when you copy a cell's formula onto another cell, or when you fill that formula across a range of cells. The dollar signs control the behavior of the reference when you undertake the operations of cut/copy/paste and fill. You can think of the dollar signs as instructions to Excel's paster and filler. They have nothing at all to do with how a cell with that formula computes its value.

Well, then, what do the dollar signs do? Simply put, they tell Excel how to modify the formula when you paste or fill. Here's the story:.

A reference, like A1, has two parts. The alphabetic part, A, is the column component. The numeric part, the 1, is the row component. Each part can have a dollar sign prefix. The four possibilities are given in the table below, which specifies the behavior of the reference component when it's pasted or filled.

Here's the difference between an absolute reference component and a relative reference component. When a relative reference component is pasted or filled, it points to the same row or column relative to its new location as it did relative to its original location. When an absolute reference component is pasted or filled, it points to the same absolute row or column it pointed to in its original location.

For example, suppose the formula in cell E5 is =B3+$B3+B$3+$B$3. I chose this formula because it has one of each kind of reference. Now suppose I copy this cell and paste it into the cells directly above, to the right, below, and to the left of E5.These cells are, respectively, E4, F5, E6, and D4. The formulas that result are shown in the following table:

Notice that the effect of the dollar sign is to "nail down" the component it modifies — when you paste an absolute component, it continues to point to where it pointed in its original cell. Relative components behave as if they were updated relative to their new "home base." For example, when you copy B$3 from E5 to E4, it continues to refer to B3, because the row is absolute and the column hasn't changed. On the other hand, when you copy it to F5, it becomes C$3, because the row hasn't changed (and the row reference is absolute, anyway!), but the column is relative, so it increases by one.

This may seem difficult to follow at first. But soon, you'll develop an intuition about it, and it will become second nature. As with any skill, a good way to learn this technique is to use it at every opportunity.

Excel provides a convenient tool for cycling through the four possible states of a reference. As you enter a reference into the formula bar or a dialog box, you can use Command+T (Mac) or F4 (Windows) to cycle the reference through its four states.

All references can be expressed in either A1 style or R1C1 style. These reference styles are merely different notations for the same thing. Excel provides an option that controls which style it uses to display references. In A1 style, letters represent columns, and numbers represent rows. In R1C1 style, numbers represent both. For example, the reference $D$3 in A1 style is represented as R3C4 in R1C1 style. In R1C1 style, brackets represent relative references. To represent as a relative reference a cell one row above and one column to the right, you would write R[-1]C[1].

In R1C1 style, the table above would look like:

If you examine these formulas carefully, you'll see that they're all identical! This is mysterious at first, but it happens because in this notation, relative references and absolute references are invariant in their appearance, no matter where you paste or fill them.

To change the reference style, choose the menu command Excel>Preferences… (Excel 2004) or Tools>Options… (Excel 2003), and then either check or clear the R1C1 check box on the General page of the Preferences or Options dialog. In Excel 2007, choose Microsoft Office>Excel Options>Formulas and then either check or clear the R1C1 check box.