Spreadsheet Models for Managers

This reading is especially relevant for Session 1References
 

The power of spreadsheets derives mainly from their ability to assign a formula to a cell that enables the cell to recalculate its value based on the (possibly changed) values of other cells. To do this, we need a way of expressing in a formula the concept of retrieving the value of another cell or cells. This is done with cell references.

• What is a cell reference?
• Absolute and relative references
• Reference styles
   

What is a cell reference?

A cell reference is a symbol in a formula that represents another cell or cells. For example, A1 represents the value of cell A1. The formula below causes the cell that holds it to compute the square of the sum of the values of all the cells in the range A1:D4.

=SUM(A1:D4)^2

(1)

That’s all there is to references, really, until we start to think about cut/copy/paste and fill.

Notice that a cell reference isn’t just a pointer to a cell’s value — it gives access to everything about the reference.

Absolute and relative references

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:

=SUM($A$1:$D$4)^2

(2)

For examples of absolute and relative references, see the demonstrations for Session 1. 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 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 is the column component. The numeric part is the row component. In the case of A1, the alphabetic part is A, and the numeric part is 1. Each part can have a dollar sign prefix.

If a part of a reference is prefixed with a dollar sign ($), we say that that part is “absolute”. If there is no dollar sign, we say that that part is “relative”. Since each part of a reference can either have a dollar sign or not, there are four kinds of cell references. 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. We chose this formula because it has one of each kind of reference. Now suppose we 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.

Reference styles

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, in Excel 2007, choose Microsoft Office>Excel Options>Formulas and then either check or clear the R1C1 check box. In Excel 2010 and 2013, choose File>Options>Formulas and then either check or clear the R1C1 check box. In Excel 2011, choose the menu command Excel>Preferences…, then click General, and then either check or clear the R1C1 check box.

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT