Part II: Working with Formulas and Functions
Excel created the following eight names:North =Sheet1!$B$2:$E$2 Quarter1 =Sheet1!$B$2:$B$5
South =Sheet1!$B$3:$E$3 Quarter2 =Sheet1!$C$2:$C$5
West =Sheet1!$B$4:$E$4 Quarter3 =Sheet1!$D$2:$D$5
East =Sheet1!$B$5:$E$5 Quarter4 =Sheet1!$E$2:$E$5With these names defined, you can create formulas that are easy to read and use. For exam-
ple, to calculate the total for Quarter 4, just use this formula:
=SUM(Quarter4)To refer to a single cell, use the intersection operator. Move to any blank cell and enter the
following formula:=Quarter1 WestThis formula returns the value for the first quarter for the West region. In other words, it
returns the value that exists where the Quarter1 range intersects with the West range.
Naming ranges in this manner can help you create very readable formulas.Applying names to existing references
When you create a name for a cell or a range, Excel doesn’t automatically use the name in
place of existing references in your formulas. For example, suppose you have the following
formula in cell F10:
=A1–A2If you later define a name Income for A1 and Expenses for A2, Excel won’t automatically
change your formula to =Income–Expenses. Replacing cell or range references with their
corresponding names is fairly easy, however.To apply names to cell references in formulas after the fact, start by selecting the range
that you want to modify. Then choose Formulas ➪ Defined Names ➪ Define Name ➪ Apply
Names. The Apply Names dialog box appears. Select the names you want to apply by click-
ing them and then click OK. Excel replaces the range references with the names in the
selected cells.Working with Formulas
In this section, we offer a few additional tips and pointers relevant to formulas.Not hard-coding values
When you create a formula, think twice before you use any specific value in the formula.
For example, if your formula calculates sales tax (which is 6.5%), you may be tempted to
enter a formula, such as the following:=A1*.065