July 21, 2011, 11:11 a.m.
posted by antir
So far, you've learned how to build a formula by entering it manually. That's a good way to start out because it forces you to understand the basics of formula writing. But writing formulas by hand is 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.
Note: In previous versions of Excel, the Insert Function dialog box was almost exactly the same, except it was known as the Function wizard.
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:
Move to the cell where you want to enter the formula.
This cell's 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.
Press the equal sign (=) key.
The equal sign tells Excel you're going to enter a formula.
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.
Press the + key.
Excel adds the + sign to your formula so that it now reads =A1+.
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. Even experienced Excel fans get frustrated with this step. If you click another cell before you press Enter, then you won't move to the cellinstead, Excel inserts 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, then 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.
Point-and-Click Formula Editing
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's 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.
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 (or cells) 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 then click any corner of the border that surrounds the range you want to change. Next, drag the border to change the size of the range. If you want to move the range, then click any part of the range border and drag the outline in the same way as you would with a cell reference.
Press Enter or click the formula bar checkmark to accept your changes.
The Formulas Tab
The most important part of the Formulas tab is the Function Library section at the left. It includes the indispensable Insert Function button, which you'll take for a spin in the next section. It also includes many more buttons that arrange Excel's vast catalog of functions into related categories for easier access. Figure show how it works.
The Function Library divides its functions into the following categories:
Recently Used has exactly what you'd expectfunctions that you've recently chosen from the Function Library. If you're just starting out with functions, you see that Excel fills the Recently Used list with a small set of commonly used functions, like SUM( ).
Logical functions let you create conditional logic for even smarter spreadsheets that make calculation decisions.
Text functions manipulate words, sentences, and other non-numeric information.
Date & Time functions perform calendar math and can help you sort out ages, due dates, and more.
Lookup & Reference functions perform the slightly mind-bending feat of searching for information in other cells.
Math & Trig functions are the mathematic basics, including sums, rounding, and all the other high-school trigonometry you're trying to forget.
More Functions groups together some heavy-duty Excel functions that are intended for specialized purposes. This category includes high-powered statistical and engineering functions.
Using the Insert Function Button to Quickly Find and Insert Functions
Excel provides more than 300 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.
To use the Insert Function feature, choose Formulas Function Library Insert Function. However, formula pros skip straight to the action by clicking the fx button that appears just to the left of the formula bar. (Or, they press the Shift+F3 shortcut key.)
No matter which approach you use, Excel displays the Insert Function dialog box (shown in Figure), which offers three ways to search for and insert any of Excel's functions.
If you're looking for a function, the easiest way to find one is to choose a category from the "Or select a category" drop-down list. For example, when you select the Math & Trig category, you see a list of functions with names like SIN( ) and COS( ), which perform basic trigonometric calculations.
If you choose the Most Recently Used category, you'll see a list of functions you've recently picked from the ribbon or the Insert Function dialog box.
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 bottomleft corner of the window. To build a formula using this function, click OK.
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).
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, then 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.
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.
Click the text box for the first argument.
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.
Repeat step 2 for each argument in the function.
As you enter the arguments, Excel updates the formula automatically.
Once you've specified a value for every required argument, click OK.
Excel closes the window and returns you to your worksheet.