There are four ways to reference spreadsheet cells: using relative references, absolute refer- ences, hybrid references, and named references.
The first three of these methods are related to the physical cell coordinates of column letter and row number. The distinction between relative, absolute, and hybrid references has to do with how cell references get adjusted when a cell formula is copied and pasted to other cells. For example, within the spreadsheet for a dashboard, you may have some quantity (say, in column A) that is increased by a surcharge factor. The final amount could be computed in column C (see Figure 4.2).
Figure 4.2 Cell referencing affects how formulas get cloned.
74 Chapter 4 Embedded Spreadsheets: The Secret Sauce of Xcelsius 2008
4
For your convenience, Figure 4.2 shows the formulas used in Column C. As you can see, this is the formula in cell C2:
=A2*(100%+B2)
If you copy and paste this formula into cell C3, this is the resulting formula:
=A3*(100%+B3)
If you were to paste this formula into, say, cell C47, this would be the resulting formula:
=A47*(100%+B47)
This formula is logical enough. It requires that you supply a value for the amount and sur- charge factor in every row where you are computing the total amount. But what if you have a single surcharge you would like to apply across the board? You could replicate that sur- charge in column B for every row. That would be inefficient and potentially prone to error. Every time you needed to update the surcharge, you would have to replicate it into each and every instance where it occurs. It would be simpler to lock the cell reference of the sur- charge to a single cell. In this manner, when you replicate the spreadsheet formula for total cost, there would be a single master surcharge rate. This buys you simplicity of design and peace of mind. You won’t have to worry about your formulas going awry.
Locking in a cell reference does come at a slight cost, however: You have to identify what is being locked when you replicate your formulas. This is done by prepending a dollar ($) sym-
bol to either or both the column letter and row number of the cell reference. If you want to lock both the column letter and the row number, so that the cell reference is absolute, you would therefore have to use the dollar symbol twice—once in front of the column letter and once in front of the row number. In the current example, you might designate the “master” surcharge rate to be located in cell B2. This could be the formula for the total result in cell C4:
=A4*(100%+$B$2)
Notice that there is no need to populate a surcharge factor in cell B4 because the reference is locked to cell B2. If you replicate the formula in cell C4 to C5, the formula becomes the following:
=A5*(100%+$B$2)
What was A4 is now A5 because it is a relative reference. The surcharge factor is locked in column B and row 2 because the absolute reference, $B$2, is used in the formula.
All this may seem like an undue amount of complication just to replicate a handful of formu- las. But when you’re working with a spreadsheet that has dozens or hundreds of formulas, you’ll appreciate that you can lock cell reference with a high degree of control.
So far, you’ve seen relative references and absolute references. What if you want to lock in the column but not the row, or lock the row but not the column? These are known as hybrid
cell references. Sometimes a formula can involve both kinds of hybrid cell references—where
one of the terms locks the row and not the column and the other locks the column and not the row. You will typically encounter this when you try building a table in a spreadsheet (see Figure 4.3).
75 Building Spreadsheet Models in Xcelsius 2008
4 The contents of the spreadsheet table are computed by using the amount that is in column
A and the surcharge rate, which is in row 2. You could have a formula like the following in cell B3:
=A3*(100%+B2)
If you try copying and pasting this formula across the table, the formula will not replicate correctly. You need to lock the column so the amount is always associated with column A. In this case, you would change A3 to $A3. You also need to lock the surcharge rate to row 2, so you would change B2 to B$2. This would be the resulting formula for cell B3:
=$A3*(100%+B$2)
Figure 4.3 A spreadsheet table driven by two cells (A3 and B2).
T I P
There is a very easy way to cycle through the various permutations of relative, absolute, and hybrid cell references in your spreadsheet formulas. While you are entering or edit- ing a formula, you highlight the portion of the formula you want to change and then repeatedly press the F4 key. Each time you do so, the locking $symbols will be inserted and/or deleted, thus allowing you to cycle through the various reference modes.
To make the Spreadsheet Table component on the canvas a little more useful, I have placed two sliders, one vertical and one horizontal (refer to Figure 4.3). The vertical slider is bound or linked to cell A3.
In cell A4 I have the following formula:
=A3+25
76 Chapter 4 Embedded Spreadsheets: The Secret Sauce of Xcelsius 2008
4
This is the surcharge factor in cell C2:
=B2+1%
This formula can be replicated to the right so that cell D2 is as follows:
=C2+1%
There are several things you should notice here. Because of the way the cells are arranged, it is not necessary to insert $ symbols in the expressions. If you chose to do so, however, your formulas would have looked like this:
=$A3+25
and this:
=B$2+1%
Both ways in this particular example are equally valid, so you should choose the set of formulas that’s easier to read.
The idea of building a table is based on having to supply only two numbers—in this exam- ple, the amount in cell A3 and the factor in cell B2. Formula chaining drives the whole table, so that as A3 and B2 are adjusted, so is the rest of the table. Conveniently, the vertical and horizontal sliders on the dashboard canvas can be used to affect the values of the two input cells A3 and B2.
Notice also that Excel allows you to embed percentage symbols in a formula, as in the following example:
=B2+1%
Alternatively, you could use a decimal representation, such as the following:
=B2+0.01
While either way is valid, I recommend that you generally opt for the decimal representa- tion. Suppose you need to add the value 1234.073to cell B2 and you used percentages. You
would have a formula like this:
=B2+123407.3%
Very large and very small percentages tend to be difficult to read. I suggest that you use per- centages only when you are formatting a cell (that is, for presentation purposes) and avoid the use of percentage representation in actual spreadsheet formulas.
In addition, generally speaking, you should avoid using hardwired values in your spreadsheet formulas. As an example, you could place the 0.01(or 1%) value in cell B1 and then use a
formula like this:
=B2+$B$1
There are several very good reasons to isolate hardwired values:
■ If a hardwired value is embedded inside a formula, it is impossible to change it without hand editing your formula.
77 Building Spreadsheet Models in Xcelsius 2008
4
■ If you need to update a hardwired value in a formula, you might have a difficult time finding all your needles in the haystack. For example, say that you have several formulas in your spreadsheet that incorporate the sales tax rate for New York City. Say that the rate changes, and you change it in 12 places, but you miss an unlucky 13th place. Such potential spreadsheet errors are avoidable.
■ When you have an isolated hardwired value, it can be used as an input cell in your Xcelsius dashboard.