Page 164 - Excel Timesaving Techniques for Dummies

31_574272 ch27.qxd 10/1/04 10:49 PM Page 149
Excel responds by removing the marquee from
After nearly 20 years of using electronic Pointing Out Cell References in Formulas 149
spreadsheets, you never see me typing in cell cell B4 (although it remains surrounded by a blue
references rather than pointing them out bounding box, indicating that this cell is the first
when creating a new formula. The only time I variable in the formula).
ever type them is on the rare occasion when
I’m editing a formula and just need to change
a row number or column letter in one of
addresses.

Believe me when I say that you can save yourself a lot
of heartache (and explaining to the boss) by always
pointing out the cell references in the master formu-
las you build. And just in case your pointing tech-
nique has gotten a little rusty, check out Figures 27-1
through 27-4 for a quick refresher.

Figure 27-1 shows the lower part of a balance sheet
where I need to enter a master formula that com-
putes the ratio of the gross profit for the Northern
region by subtracting the cost of goods sold from
the gross sales (B4-B12) and then dividing that result
by gross sales. To construct that formula by pointing
to the cell references, I follow these steps:
1. I begin by typing = (equal) followed by the • Figure 27-1: Starting the new formula that computes the
open parenthesis. ratio of gross profit on sales.
I need to enclose the subtraction in parentheses
so that Excel computes this result before doing 4. Next, I click cell B12, the cell containing the
the division, an operation that has a higher cost of goods sold for the Northern region.
precedence.
Excel encloses cell B12 in a marquee and adds
Excel evaluates all operations in a formula fol- this cell reference to the formula on the Formula
lowing a strict left-to-right order of precedence bar. (See Figure 27-2.)
unless you interrupt that order with the use of 5. Next, I type a closed parenthesis to close off the
parentheses. To refresh yourself on this order, subtraction operation.
press F1 and then search for “calculation oper-
ators” and follow the About Calculation When I do this, Excel removes the marquee from
Operators link. cell B12 (while at the same time encloses this cell
in a green bounding box to indicate that this cell
2. Now I’m ready to select the first cell reference. is the second variable in the formula).
I scroll up and click cell B4 — the cell contain- 6. All that remains is to type / (slash) to indicate a
ing the Northern sales.
division operation and click cell B4 again.
After I click this cell, Excel encloses it in a mar-
Once again, the program encloses cell B4 in a
quee and enters its cell reference in the formula
marquee and adds its cell reference to the
shown on the Formula bar.
Formula bar. (See Figure 27-3.)
3. Then I type - (minus) to indicate a subtraction
operation.