If you use Excel to model businesses, business processes, or business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.

To Order On Line

 Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
 Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.

To Order by Mail

 Make your check payable to Chaco Canyon Consulting, for the amount indicated: For the download: USD 199.00 For access online for three months: USD 199.00 For access online for one month: USD 69.95 And send it to: Chaco Canyon Consulting 700 Huron Avenue, Suite 19C Cambridge, MA 02138

To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.

# 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?

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.

 Reference Column Row A1 Relative Relative \$A1 Absolute Relative A\$1 Relative Absolute \$A\$1 Absolute Absolute

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:

 Cell Row Col Formula E4 -1 0 =B2+\$B2+B\$3+\$B\$3 F5 0 +1 =C3+\$B3+C\$3+\$B\$3 E6 +1 0 =B4+\$B4+B\$3+\$B\$3 D4 0 -1 =A3+\$B3+A\$3+\$B\$3

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.

In addition to Copy, Paste, and Fill, there is one more way to transport formulas from one place to another within a worksheet: Dragging. Using the mouse you can drag any selection consisting of a single area from wherever it is to someplace else on the worksheet. When you do this, the formulas in the cells of that selection are unchanged when they arrive at their new location even if they contain mixed or relative references. This property can be very useful when you’ve constructed a set of cells using relative references, and you decide you want to change their position, or open up some space between them and the rest of the worksheet.

## 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:

 Cell Row Col Formula E4 -1 0 =R[-2]C[-3]+R[-2]C2+R3C[-3]+R3C2 F5 0 +1 =R[-2]C[-3]+R[-2]C2+R3C[-3]+R3C2 E6 +1 0 =R[-2]C[-3]+R[-2]C2+R3C[-3]+R3C2 D4 0 -1 =R[-2]C[-3]+R[-2]C2+R3C[-3]+R3C2

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.