Spreadsheets:
Getting Started, Part VI
Back Forward "Getting Started" Home Excel Review Home


VI. Formulas


Format
Select the cell where you want to enter a formula and type an equal sign to start the formula (and activate the formula bar). Type the formula into the cell and hit the enter key when you're finished. Use parentheses where necessary to make operations clear. In the illustration above, the current cell displays $225.00, or the result of the calculation. The calculation formula displays in the "formula bar" that's located right under Excel's menus.
You can assign any worksheet cell or range a name and then use that name in a formula. This can make your worksheet more readable. An easy way to assign a name is to select a cell or range and enter the name you want to assign it in Excel's "name box" on the formula bar. The formula bar is located right under Excel's menus and the "name box" is the leftmost part of the bar. In the illustration below, Cell B1 is assigned the name "Subtotal" by typing that word into the name box. If you name cells or ranges after you've already used their values in formulas, you need to explicitly tell Excel to use the names in the formulas. You can do this with the command Insert, Name, Apply. Select the names you want to use from the Apply Names dialog box that displays.
Tips for Avoiding Errors Naming key cells and using those names in formulas instead of cell references can help you avoid errors. For example, the formula =Q55*M92 is probably less immediately comprehensible than the formula =Subtotal*Taxrate. When constructing a formula you can use the mouse to click a cell you want to include in the formula instead of typing in the cell's name or reference. Excel includes the cell reference (or name, if available) in the formula automatically. Clicking the cell you want to include can help you avoid typing in the wrong reference by mistake. Keep formulas short and straightforward. If you need to express a complex relationship with a formula it's better to build several short formulas and use them as building blocks for intermediate values rather than to build one long, baroque, errorprone, and hardtodebug formula. Excel displays an error value in a cell when the formula for that cell can't be calculated. If a formula includes a reference to a cell that contains an error value, that formula also produces an error value. Examples of Excel error values: #DIV/0! The formula is trying
to divide by zero.
Excel has a large library of builtin functions you can use to perform standard worksheet calculations. In many cases you can use these functions instead of writing your own formulas. Use the builtin functions by themselves or embed them within your own formulas. Functions can be nested within other functions. All Excel functions have the same basic syntax: =function name(parameters) If you don't already know the syntax of the function you need, use the function button on the Standard toolbar to call up the Function Wizard dialog box. The Function Wizard dialog box walks you through selecting and completing a function. Copying Formulas (Relative Referencing) There will be many occasions when you build a worksheet model with several like formulas. For example, perhaps your budget model totals sales in six departments for each of the months of January to December. The formula to sum departmental sales for January is the same as the formula to sum departmental sales for December, except they refer to different columns of data. If your January to December columns are structured alike, there's no reason to separately enter twelve summing formulas. Instead, enter the formula for January and then copy it across the worksheet to the other eleven columns. Excel adjusts cell references in each copied formula across the row so that each formula refers to its own column's data. That is, if the January sales data is in Column A, the formula for January refers only to cells in Column A. February's data is in Column B. Even though the sum formula for February was copied from January's formula in Column A, the February formula correctly refers only to cells in Column B. This formula copying and adjusting arrangement is the default in Excel. It's termed relative referencing. Keep in mind that it's an issue only when you're copying a formula. Absolute Addressing There may be times when you copy a formula that you don't want Excel to adjust a cell reference in the copy from the master formula. That is, when you copy the formula, you don't want the default of relative addressing for some or all of the copied formula's cell references. For any reference in the source formula that you want to "fix" (e.g., disallow any changes during the copy) use a dollar sign before the row and column indicator. The dollar sign is an arbitrary symbol that just instructs Excel not to change the reference. For example, a source formula that allows the D5 reference to change but fixes the reference to A1 would look like this: =D5*$A$1 If we copy the source formula above across the worksheet to columns E, F, and G, the copied formulas in those columns would look like this: =E5*$A$1 =F5*$A$1 =G5*$A$1 In this example, the formulas in E2, F2, and G2 were copied from the original formula in D2. In the master formula in Cell D2, the reference to cell A1 was absolute (fixed) but the reference to the value in Row 1 (D1) was relative. As a result, when this formula was copied to E2, F2, and G2, each copied formula referred to Cell A1 (the absolute reference value) and also to the value in Row 1 of its own column (the relative reference value).
For More Information Click Help on the toplevel menu. Select Contents and Index. Click the Find tab and in the text box that's displayed enter absolute addressing. 