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.
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.|
|Order "Spreadsheet Models for Managers, downloadable hyperbook edition" 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:
||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.
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.
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.
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.
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 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.
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.
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.
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
The first homework assignment has a fair amount of reading attached to it. Some students feel that the best approach is to read it all, and then try to do the homework. For most of us, such an approach doesn’t work very well.
Later, as you begin the homework, let the homework drive your reading choices. For instance, the first homework assignment does require that you master certain techniques. Read “Names” and “The Ripple Principle.” Then, if something confuses you, read up on it: examples are “The Basics of Recalculation” and “References.” Learning something when you need it, and only when you need it, is usually the best way to go.
Parentheses sometimes make a real difference. For instance A1*B1+2 is very different from A1*(B1+2). But A1*(B1*2) is exactly the same as A1*B1*2. When the parentheses don’t make any difference in the value of the result, it’s not usually a good idea to include them. They tend to make the formulas harder to read, and there’s always the chance that you’ll put them in the wrong place. More