Spreadsheets: Getting Started, Part VI

 Back     Forward    "Getting Started" Home    Excel Review Home

 

VI. Formulas
    Format
    Naming Cells & Ranges
    Tips for Avoiding Errors
    Using Built-in Functions
    Copying Formulas (Relative Referencing)
    Absolute Addressing
    For More Information

 

 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.

Return to Top

 

Naming Cells & Ranges

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 left-most 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.

 

Return to Top

 

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, error-prone, and hard-to-debug 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.
#N/A   No value is available.
#NAME?   Excel doesn't recognize a name used in the formula.
#NUM!   There's a problem with a number in the formula.
#REF!   The formula refers to an invalid cell.
#VALUE!   An argument or operand is incorrect.

Return to Top

 

Using Built-in Functions

Excel has a large library of built-in 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 built-in 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.

Return to Top

 

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.

Return to Top

 

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).

Return to Top

 

For More Information

Click Help on the top-level menu. Select Contents and Index. Click the Find tab and in the text box that's displayed enter absolute addressing.

Return to Top