Referencing Cells

In addition to typing values into your worksheets, you can enter references to cells. Using cell references is often more effective than typing actual values when you want to build formulas. If a value in a referenced cell changes, the formula that points to the reference is updated automatically. Best of all, one cell can be referenced in an unlimited number of formulas. A cell reference does not need to contain an operator unless you want to use it to perform a calculation.

For example, if you were calculating the amount of sales tax due on several car purchases, you could reference the cell that held the current sales tax percentage in each formula. If the sales tax percentage changes or you enter it incorrectly, updating the percentage amount also updates each car purchase formula.

As you build a formula, you can point to a cell to include the cell's value in the calculation. For example, if you're building a formula that subtracts cell D16 from D18, begin the formula with an equal sign, click cell D18, type a minus sign, and point to cell D16. Click the Enter button (the green checkmark) when the formula is complete.

Each time you reference a cell while you're building a formula, you need to type an operator. Otherwise, Excel won't store the cell reference, and your formula will be incomplete.

You can reference cells that are not currently visible in the worksheet by scrolling to the cell with the scrollbars. You can even reference a cell in another sheet by clicking the sheet tab and then clicking the cell you want to include.

Don't press the Enter key while you're still constructing a formula by pointing to cells. Pressing Enter is equal to clicking the green checkmark and tells Excel that the formula is complete.

If you need to edit a formula, press F2 and make your changes. You can type the values and cell references, or you can point to them with the mouse. Click the Enter button when the formula is complete.

Excel uses color coding to assist you when you're editing a cell. Each cell reference and the cell it refers to in the worksheet are displayed in the same color. You can use the color coding to identify which references in the formula match which cells in the worksheet.

Using AutoSum

The AutoSum button on the Standard toolbar is one of the most useful tools in Excel. The AutoSum feature automatically totals a range of values. Click into the cell where you want the total to appear and click the AutoSum button. The SUM formula appears in the cell and a marquee surrounds the range of values in the column. Click the AutoSum button again. A total of the range in the column directly appears in the last cell.

AutoSum can total cells in a row, as well as a column. Click the first empty cell in a row that contains values and click AutoSum. The SUM formula appears in the cell and a marquee surrounds the range of values in the row. Click the AutoSum button again. The total of the preceding cells appears in the last cell.

AutoSum adds ranges that contain values. If an empty cell appears in the column or row, AutoSum does not add cells that appear before the blank one.