Section 7.2. Formula Shortcuts

7.2. Formula Shortcuts

So far, you've learned how to build a formula by entering it manually. That's a solid way to start out because it forces you to understand the basics of formula writing. But writing formulas by hand can be a drag; plus, it's easy to type in the wrong cell address. For example, if you type A2 instead of A3 you can end up with incorrect data, and you won't necessarily notice your mistake.

As you become more comfortable with formulas, you'll find that Excel gives you a few toolslike point-and-click formula creation and the Insert Function buttonto speed up your formula writing and reduce your mistakes. You'll learn about these features in the following sections.

7.2.1. Point-and-Click Formula Creation

Instead of entering a formula by typing it out letter-by-letter, Excel lets you create formulas by clicking the cells you want to use. For example, consider this simple formula that totals the numbers in two cells:


To build this formula by clicking, just follow these steps:

  1. Move to the cell where you want to enter the formula.

    This cell is where the result of your formula's calculation will appear. While you can pick any cell on the worksheet, A3 works nicely because it's directly below the two cells you're adding.

  2. Press the equal sign (=) key.

    The equal sign tells Excel you're going to enter a formula.

  3. Move to the first cell you want to use in your formula (in this case, A1).

    You can move to this first cell by pressing the up arrow key twice, or by clicking it with the mouse. You'll notice that moving to another cell doesn't cancel your edit, as it would normally, because Excel recognizes that you're building a formula. When you move to the new cell, the cell reference appears automatically in the formula (which Excel displays in cell A3, as well as in the Formula Bar just above your worksheet). If you move to another cell, Excel changes the cell reference accordingly .

  4. Add the + sign to your formula by pressing the + key.

    Excel adds the + sign to your formula so that it now reads =A1+ .

  5. Finish the formula by moving to cell A2 and pressing Enter.

    Again, you can move to A2 either by pressing the up arrow key or by clicking the cell directly. Remember, you can't just finish the formula by moving somewhere else; you need to press Enter to tell Excel you're finished writing the formula. Another way to complete your edit is to click the checkmark that appears on the Formula Bar, to the left of the current formula. This is a common source of frustration, even for experienced Excel fans. If you click another cell before you press Enter, you won't move to the cellinstead, Excel will insert the cell into your formula.

    Tip: You can use this technique with any formula. Just type in the operators, function names , and so on, and use the mouse to select the cell references. If you need to select a range of cells, just drag your mouse until the whole group of cells is highlighted. You can practice this technique with the SUM( ) function. Start by typing =SUM( into the cell, and then selecting the range of cells you want to add. Finish by adding a final closing parenthesis and pressing Enter.

7.2.2. Point-and-Click Formula Editing

You can use a similar approach to edit formulas, although it's slightly trickier.

  1. Move to the cell that contains the formula you want to edit, and put it in edit mode by double-clicking it or pressing F2.

    Excel highlights all the cells that this formula uses with a colored outline. Excel is even clever enough to use a helpful color-coding system. Each cell reference uses the same color as the outline surrounding the cell it's referring to. This can help you pick out where each reference is.

  2. Click the outline of the cell you want to change. (Your pointer changes from a fat plus sign to a four-headed arrow when you're over the outline.) With the mouse button still held down, drag this outline over to the new cell(s) you want to use.

    Excel updates the formula automatically. You can also expand and shrink cell range references. To do so, put the formula-holding cell into edit mode, and click the bottom-left corner of the border surrounding the range you want to change. Next , drag the border to change the size of the range. If you want to move the range, click any part of the range border and drag the outline in the same way as you would with a cell reference.

  3. Press Enter or click the Formula bar checkmark to accept your changes.

    That's it.

Showing and Printing Formulas

How in the world do I print out formulas that appear in my cells?

When you print a worksheet, Excel prints the calculated value in each cell rather than any formula that happens to be inside a cell. Usually, that's what you want to have happen. But in some cases, rather than a printout of the formula's results, you want a record of the calculations used to generate the results.

Excel provides a view setting that makes this possible. Just choose Tools Options from the Excel menu, and select the View tab. Then, turn on the Formulas checkbox, and click OK. Now, Excel will display the formula's contents instead of its results. Uncheck the Formulas checkbox to return to normal mode.

7.2.3. Using the Insert Function Button to Quickly Find and Use Functions

Excel provides more than 500 useful built-in functions. In order to use a function, however, you need to type its name in exactly . That means that every time you want to employ a function, you'll need to refer to this book, call on your own incredible powers of recollection, or click over to the convenient Insert Function button.

When you click this button (labeled fx , just to the right of the Formula bar), Excel displays the Insert Function dialog box (shown in Figure 7-5), which offers three ways to search for and insert any of Excel's functions:

  • Any functions you've previously used appear in the text box below "Select a function."

  • If you're looking for a function, the easiest way to find one is to choose a category from the "Select a category" pull-down menu. For example, if you select the Math & Trig category, you'll see a list of functions with names like SIN( ) and COS( ), which perform basic trigonometric calculations.

  • If you're really ambitious, you can type a couple of keywords into the "Search for a function" text box. Next, click Go to perform the search. Excel gives you a list of functions that match your keywords.

When you spot a function that looks promising , click it once to highlight its name. Excel then displays a brief description of the function at the bottom of the window. For more information, you can click the "Help on this function" link in the bottom-left corner of the window. To build a formula using this function, click OK.

Figure 7-5. Top : The Insert Function dialog box lets you quickly find the function you need. You can choose a category that seems likely to have the functions you're interested in.
Bottom : You can also try to search by entering keywords in the "Search for a function" box. Either way, when you click one of the functions in the list, Excel presents you with a description of the function at the bottom of the window.

Excel then inserts the function into the currently active cell, followed by a set of parentheses. Next, it closes the Insert Function dialog box and opens the Function Arguments dialog box (Figure 7-6).

Note: Depending on the function you're using, Excel may make a (somewhat wild) guess about which arguments you want to supply. For example, if you use the Insert Function window to add a SUM( ) function, you'll see that Excel picks a nearby range of cells. If this isn't what you want, just replace the range with the correct values.

Figure 7-6. Top : Here, the COMBIN( ) function has just been inserted via the Insert Function dialog box. Because the COMBIN( ) function requires two arguments (Number and Number_chosen), the Function Arguments dialog box shows two text boxes. The first argument uses a literal value (52), while the second argument uses a cell reference (A1). (You can use literal values or a cell reference for either argumentit's up to you.) As you enter the arguments, Excel updates the formula in the worksheet's active cell, and displays the result of the calculation at the bottom of the Function Arguments dialog box.
Bottom : If you need more room to see the worksheet and select cells, you can click the Collapse Dialog Box icon to reduce the window to a single text box. Clicking the Expand Dialog Box icon restores the window to its normal size.

Now you can finish creating your formula by using the Function Arguments dialog box, which includes a text box for every argument in the function. It also includes a help link for detailed information about the function, as shown in Figure 7-7.

To complete your formula, follow these steps:

  1. Click the text box for the first argument.

    A brief sentence describing the argument appears in the Function Arguments dialog box.

    Some functions don't require any arguments. If this is the case, you won't see any text boxes, although you'll still see some basic information about the function. Skip directly to step 4.

  2. Enter the value for the argument.

    If you want to enter a literal value (like the number 52), type it in now. To enter a cell reference, you can type it in manually, or click the appropriate cell on the worksheet. To enter a range, drag the cursor to select a group of cells.

    You may need to move the Function Arguments dialog box to the side to expose the part of the worksheet you want to click. The Collapse Dialog Box icon (located to the immediate right of each text box) is helpful since clicking it shrinks the window's size. This way, you'll have an easier time selecting cells from your worksheet. To return the window to normal, click the Expand Dialog Box icon, which is to the right of the text box.

    Figure 7-7. Both the Insert Function and Function Arguments dialog boxes make it easy to get detailed reference information about any function by clicking the "Help on this function" link at the bottom left of the window. The help page shown here shows the reference for the trigonometric SIN( ) function, which calculates the sine of an angle. Excel's help page includes a brief description, important notes, and a couple of sample formulas that use the function, complete with results.

  3. Repeat step 2 for each argument in the function.

    As you enter the arguments, Excel updates the formula automatically.

  4. Once you've specified a value for every argument, click OK.

    Excel closes the window and returns you to your worksheet.

7.2.4. The Analysis ToolPak

Not all of Excel's functions are available to you right off the bat. Some of the most sophisticated and complex ones are part of a separate toolkit, called the Analysis ToolPak. The Analysis ToolPak comes installed as an add-in with the standard Excel setup, but it isn't available until you specifically switch it on.

To turn on the Analysis ToolPak, select Tools Add-Ins from the menu. Place a checkmark next to the Analysis ToolPak entry, as shown in Figure 7-8, and click OK.

Figure 7-8. Once you've switched on the Analysis ToolPak add-in, you'll have access to a slew of new functions, many of which are described in the following chapters. All the Analysis ToolPak functions will appear in the Insert Function dialog box, but only if the add-in is currently switched on.

Tip: You may need to insert the CD you received when you purchased Excel (or Microsoft Office) in order to activate the Analysis ToolPak. If so, Excel will prompt you with a dialog box asking you to insert the CD.

The Analysis ToolPak includes many additional functions for statistical, financial, engineering, and date calculations. Many of these formulas are described in the next few chapters, so you should turn on the Analysis ToolPak before continuing any further. Once you've switched on the Analysis ToolPak, it will load automatically every time you launch Excel. Unfortunately, the Analysis ToolPak functions won't show a descriptive tooltip when you type them into a function. If you need some help using or learning more about these functions, use the Insert Function dialog box.

Note: If you try to use a formula that contains an Analysis ToolPak function and you receive the #NAME? error message, the problem just might be that you haven't enabled the Analysis ToolPak. Consequently, Excel can't find the function you're trying to use.
Functions that Return Arrays

A few exotic functions actually give you multiple results, so rather than simply generating a single value, as a function like SUM( ) would, these functions generate more than one value. To use these functions properly, you need to create a special type of formula called an array formula . (An array is a group of numbers.) You can create an array formula quite easily. Simply select all the cells that Excel will use to display the results, type in the formula, and end by pressing Ctrl+Shift+Enter. It's this final keystroke that actually creates the array formula and links the cells together.

You'll know that you have an array formula because the formula appears in the Formula Bar with curly braces { } around it. You also won't be able to edit the individual cells in the array formula. (If you try, Excel warns you that you can't change part of an array.) To make a change to an array formula, you need to select all the cells in the array, edit the formula, and then press Ctrl+Shift+Enter to recreate the array formula.

Some functions that require array formulas include FREQUENCY( ) and TREND( ), which are demonstrated on pages Section 8.2.8 and Section 8.5, respectively; and TRANSPOSE( ), discussed on Section 11.2.9.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: