Microsoft Office Tutorials and References

In Depth Information

**USE NATURAL LANGUAGE FORMULAS WITHOUT USING NATURAL LANGUAGE FORMULAS**

Solution:
You can use
INDIRECT("B10")
to ensure that the formula always

points to cell B10. Even if you delete or insert rows, and even if you cut B10

and paste to B99, your formula will always point to B10 (Figure 39).

Figure 39.
INDIRECT("B10")
tells Excel to

always look in cell B10.

Gotcha:
If you are a fan of formula auditing, note that the Trace Dependents

and Trace Precedents commands do not recognize the relationship between

cell B10 and the formula in Figure 39. If you use Trace Dependents from cell

B10, Excel will report that there are no dependents.

Summary:
To force a formula to always point to cell B10, you can use

INDIRECT("B10")
.

USE NATURAL LANGUAGE FORMULAS WITHOUT

USING NATURAL LANGUAGE FORMULAS

Challenge:
Excel 2003 offers relatively obscure natural language formulas, but

they were removed from Excel 2007. The table nomenclature in Excel 2007 isn’t

as easy to use.

Solution:
To solve this problem, you can use the intersection character in your

SUM
function. Everyone knows that
=SUM(A2:A10)
sums the nine-cell range

from A2 through A10. Most people realize that
=SUM(A1,A3,A5,A7,A9)
adds

up the ﬁ ve cells speciﬁ ed. However, very few people understand that the space

character is actually an intersection operator when used in a
SUM
function!

Say that you have the worksheet shown in Figure 40. As discussed in “Quickly

Create Many Range Names” on page 76, you can add range names by using

these steps:

Select the range A1:F13.

Use Insert, Name, Create (in Excel 2003) or Formulas, Create from Selection

(in Excel 2007).

In the Create Names or Create Names from Selection dialog, select Top

Row and Left Column. Click OK. Excel creates names for the 12 cities in

column A. For example, the name Louisville applies to cells B13:F13. Excel

1.

2.

3.