The AutoSum button on the Standard toolbar is one of the most useful tools in Excel. AutoSum 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
AutoSum can total
Figure 5.8. Using AutoSum to total cells in a row.
Review the information in Hour 4, "Managing Your Files and Workbooks," if you need help naming a range.
You must name a range before you can use it in a formula. If you remember the range name, you can type it into the formula in place of a cell reference. Remember, though, that you must type the range
When you come to the place in the formula where you want to insert a range name, click the Insert menu and choose Name and then Paste. The Paste Name dialog box, as displayed in Figure 5.9, appears. Select the range name you want to include in the formula and click OK. The box
You can select the
Using Relative and Absolute Addressing
When you enter a formula that contains cell references into a cell, Excel keeps track of it in two ways. The first is to record the value of that
Here's how relative addressing works if you enter the formula =a1+a2 in cell A3.
If the formula could talk, it would say, "Take the cell two rows above me and add the value of that cell to the cell one row above me and display the results in my cell." (Talking formulas would be great, wouldn't they!) If you
Relative cell referencing is great if you're adding a number of like
What if your calculation isn't so straightforward? Suppose you want to calculate the
Figure 5.10. The relative cell reference formula is =B18*B6 .
After you copy the formula in cell B9 to C9, the February payroll projection in cell C9 would be =C18*C6 . Although the cell reference C6 (February revenue) is correct, the cell reference C18 references an empty cell. The formula for cell C9 should be =B18*C6 rather than =C18*C6 . In this case, you need to keep a cell reference in a formula the same when you copy the formula.
To keep a cell reference constant when you copy a formula or function, Excel uses
. The concept of absolute cell referencing is somewhat difficult to understand. To make it easier to understand, keep in mind that the paste function is the only one affected by an absolute cell reference. An absolute cell reference
When you're entering a cell reference into a cell, you can type the dollar signs to make the reference absolute. Or, if you're using the mouse to point to the cells you want to use in the formula, click the cell and press F4. The dollar sign character appears before both the column and row indicators, meaning the cell reference is absolute.
For example, $B$18 is an absolute reference, whereas B18 is a relative reference. Both reference the same cell. The difference is when they are pasted into other cells. A formula using the absolute reference $B$18 tells Excel to keep the cell reference B18 constant (absolute) as you paste the formula into a new location. A formula using the relative cell reference B18 tells Excel to adjust the cell reference as it pastes.
Cell B9 contains the payroll formula =$B$18*B6 , as shown in Figure 5.11. To enter this formula, click cell B9, type = (equal sign), and then click cell B18. Press F4 to change B18 to an absolute cell reference in the formula. Type * (asterisk) and then click cell B6.
Figure 5.11. The absolute formula displays in cell B9 and in the Formula bar.
Working with Simple Functions
Excel contains a range of
designed to help you enter formulas easily. Excel functions run the
Functions ” Built-in calculations available in Excel.
In Hour 15, "Using Functions," you explore functions in greater detail. This section examines a few of Excel's simpler functions. Table 5.2 shows some simple, commonly used functions.
Table 5.2. Common Excel Functions
Excel functions are handled like formulas. Each function begins with an =. Next enter the function name, which is usually a one-word description of what the function does. Following the function
Arguments ” The information provided to a function so that an answer can be computed.
returns the sum of the cells from A1 through A5. (The
A few functions don't use arguments. For example, =NOW() enters the serial number for the current date in the cell.