Part II: Working with Formulas and Functions
202
values, literal text strings, expressions, and even other functions. Here are some examples of func-
tions that use various types of arguments:l (^) Cell reference: =SUM(A1:A24)
l Literal value: =SQRT(121)
l (^) Literal text string: =PROPER(“john smith”)
l Expression: =SQRT(183+12)
l (^) Other functions: =SQRT(SUM(A1:A24))
Note
A comma is the list-separator character for the U.S. version of Excel. Some other versions may use a semicolon.
The list separator is a Windows setting, which can be adjusted in the Windows Control Panel (the Regional and
Language Options dialog box). n
More about functions ....................................................................................
All told, Excel includes more than 400 functions. And if that’s not enough, you can purchase addi-
tional specialized functions from third-party suppliers — and even create your own custom func-
tions (by using VBA) if you’re so inclined.Some users feel a bit overwhelmed by the sheer number of functions, but you’ll probably find that
you use only a dozen or so on a regular basis. And as you’ll see, the Excel Insert Function dialog
box (described later in this chapter) makes it easy to locate and insert a function, even if it’s not
one that you use frequently.Cross-Reference
You’ll find many examples of Excel’s built-in functions in Chapters 11 through 17. Appendix A contains a com-
plete listing of Excel’s worksheet functions, with a brief description of each. Chapter 40 covers the basics of
creating custom functions with VBA. n
Entering Formulas into Your Worksheets .........................................................................
As I mention earlier, a formula must begin with an equal sign to inform Excel that the cell contains
a formula rather than text. Excel provides two ways to enter a formula into a cell: manually or by
pointing to cell references. The following sections discuss each way in detail.Excel provides additional assistance when you create formulas by displaying a drop-down list that
contains function names and range names. The items displayed in the list are determined by what
you’ve already typed. For example, if you’re entering a formula and then type the letter L, you’ll see
the drop-down list shown in Figure 10.2. If you type an additional letter, the list is shortened to
show only the matching functions. To have Excel autocomplete an entry in that list, use the navi-
gation keys to highlight the entry, and then press Tab. Notice that highlighting a function in the
list also displays a brief description of the function. See the sidebar “Using Formula Autocomplete”
for an example of how this feature works.