Part II: Working with Formulas and Functions
values, literal text strings, expressions, and even other functions. Here are some examples of functions that use various types of arguments:
•Cell reference: =SUM(A1:A24)
•Literal value: =SQRT(121)
•Literal text string: =PROPER(“john smith”)
•Expression: =SQRT(183+12)
•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 additional specialized functions from third-party suppliers — and even create your own custom functions (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 complete 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 navigation 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.
202
Chapter 10: Introducing Formulas and Functions
FIGURE 10.2
Excel displays a drop-down list when you enter a formula.
Entering formulas manually
Entering a formula manually involves, well, entering a formula manually. In a selected cell, you simply type an equal sign (=) followed by the formula. As you type, the characters appear in the cell and in the Formula bar. You can, of course, use all the normal editing keys when entering a formula.
Entering formulas by pointing
Even though you can enter formulas by typing in the entire formula, Excel provides another method of entering formulas that is generally easier, faster, and less error-prone. This method still involves some manual typing, but you can simply point to the cell references instead of typing their values manually. For example, to enter the formula =A1+A2 into cell A3, follow these steps:
1.Move the cell pointer to cell A3.
2.Type an equal sign (=) to begin the formula. Notice that Excel displays Enter in the status bar (bottom left of your screen).
3.Press the up arrow twice. As you press this key, Excel displays a faint moving border around cell A1, and the cell reference appears in cell A3 and in the Formula bar. In addition, Excel displays Point in the status bar.
4.Type a plus sign (+). A solid-color border replaces the faint border, and Enter reappears in the status bar.
5.Press the up arrow again. The moving border encompasses cell A2, and adds that cell address to the formula.
6.Press Enter to end the formula.
Tip
You can also point to the data cells by using your mouse. n
203
Part II: Working with Formulas and Functions
Using Formula Autocomplete
The Formula Autocomplete feature (introduced in Excel 2007) makes entering formulas easier than ever. Here’s a quick walk-through that demonstrates how it works. The goal is to create a formula that uses the AGGREGATE function to calculate the average value in a range that I named TestScores. The AVERAGE function will not work in this situation because the range contains an error value.
1.Select the cell that will hold the formula, and type an equal sign (=) to signal the start of a formula.
2.Type the letter A. You get a list of functions and names that begin with A (see the figure here).
This feature is not case sensitive, so you can use either uppercase or lowercase characters.
3.Scroll through the list, or type another letter to narrow down the choices.
4.When AGGREGATE is highlighted, press Tab to select it. Excel adds the opening parenthesis and displays another list that contains options for the first argument for AGGREGATE, as shown in the figure here.
204
Chapter 10: Introducing Formulas and Functions
5.Select 1 - AVERAGE and then press Tab. Excel inserts 1, which is the code for calculating the average.
6.Type a comma to separate the next argument.
7.When Excel displays a list of items for the AGGREGATE function’s second argument, select 2 - Ignore Error Values and then press Tab.
8.Type a comma to separate the third argument (the range of test scores).
9.Type a T to get a list of functions and names that begin with T. You’re looking for TestScores, so narrow it down a bit by typing the second character (e).
10.Highlight TestScores and then press Tab.
11.Type a closing parenthesis and then press Enter.
The completed formula is
=AGGREGATE(1,2,TestScores)
Formula Autocomplete includes the following items (and each type is identified by a separate icon):
•Excel built-in functions
•User-defined functions (functions defined by the user through VBA or other methods)
•Defined names (named using the Formulas Defined Names Define Name command)
•Enumerated arguments that use a value to represent an option (only a few functions use such arguments, and AGGREGATE is one of them)
•Table structure references (used to identify portions of a table)
Pasting range names into formulas
If your formula uses named cells or ranges, you can either type the name in place of the address, or choose the name from a list and have Excel insert the name for you automatically. Two ways to insert a name into a formula are available:
•Select the name from the drop-down list. To use this method, you must know at least the first character of the name. When you’re entering the formula, type the first character and then select the name from the drop-down list.
•Press F3. This action displays the Paste Name dialog box. Select the name from the list and then click OK (or just double-click the name). Excel will enter the name into your formula. If no names are defined, pressing F3 has no effect.
Figure 10.3 shows an example. The worksheet contains two defined names: Expenses and Sales. The Paste Name dialog box is being used to insert a name (Sales) into the formula being entered in cell B10.
205
Part II: Working with Formulas and Functions
Cross-Reference
See Chapter 4 for information about defining names. n
FIGURE 10.3
Use the Paste Name dialog box to quickly enter a defined name into a formula.
Inserting functions into formulas
The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). To use this method, however, you must know at least the first character of the function’s name.
Another way to insert a function is to use the Function Library group on the Formulas tab (see Figure 10.4). This method is especially useful if you can’t remember which function you need. When entering a formula, click the function category (Financial, Logical, Text, and so on) to get a list of the functions in that category. Click the function you want, and Excel displays its Function Arguments dialog box. This is where you enter the function’s arguments. In addition, you can click the Help on This Function link to learn more about the selected function.
FIGURE 10.4
You can insert a function by selecting it from one of the function categories.
Yet another way to insert a function into a formula is to use the Insert Function dialog box (see Figure 10.5). You can access this dialog box in several ways:
206
Chapter 10: Introducing Formulas and Functions
•Choose Formulas Function Library Insert Function.
•Use the Insert Function command, which appears at the bottom of each drop-down list in the Formulas Function Library group.
•Click the Insert Function icon, which is directly to the left of the Formula bar. This button displays fx.
•Press Shift+F3.
FIGURE 10.5
The Insert Function dialog box.
The Insert Function dialog box shows a drop-down list of function categories. Select a category, and the functions in that category are displayed in the list box. To access a function that you recently used, select Most Recently Used from the drop-down list.
If you’re not sure which function you need, you can search for the appropriate function by using the Search for a Function field at the top of the dialog box.
1.Enter your search terms and click Go. You get a list of relevant functions. When you select a function from the Select a Function list, Excel displays the function (and its argument names) in the dialog box along with a brief description of what the function does.
2.When you locate the function you want to use, highlight it and click OK. Excel then displays its Function Arguments dialog box, as shown in Figure 10.6.
3.Specify the arguments for the function. The Function Arguments dialog box will vary, depending on the function you’re inserting, and it will show one text box for each of the function’s arguments. To use a cell or range reference as an argument, you can enter the address manually or click inside the argument box and then select (that is, point to) the cell or range in the sheet.
4.After you specify all the function arguments, click OK.
207
Part II: Working with Formulas and Functions
FIGURE 10.6
The Function Arguments dialog box.
Tip
Yet another way to insert a function while you’re entering a formula is to use the Function List to the left of the Formula bar. When you’re entering or editing a formula, the space typically occupied by the Name box displays a list of the functions you’ve used most recently. After you select a function from this list, Excel displays the Function Arguments dialog box. n
Function entry tips
Here are some additional tips to keep in mind when you use the Insert Function dialog box to enter functions:
•You can use the Insert Function dialog box to insert a function into an existing formula. Just edit the formula and move the insertion point to the location at which you want to insert the function. Then open the Insert Function dialog box (using any of the methods described earlier) and select the function.
•You can also use the Function Arguments dialog box to modify the arguments for a function in an existing formula. Click the function in the Formula bar and then click the Insert Function button (the fx button, to the left of the Formula bar).
•If you change your mind about entering a function, click the Cancel button.
•How many boxes you see in the Function Arguments dialog box depends on the number of arguments used in the function you selected. If a function uses no arguments, you won’t see any boxes. If the function uses a variable number of arguments (such as the AVERAGE function), Excel adds a new box every time you enter an optional argument.
•As you provide arguments in the Function Argument dialog box, the value of each argument is displayed to the right of each box.
•A few functions, such as INDEX, have more than one form. If you choose such a function, Excel displays another dialog box that lets you choose which form you want to use.
208