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 cell and use the value in the calculation. The second way is to keep track of the relative position of the cells in the formula to one another.
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 copied the formula in A3 to C3, the new formula would read =C1+C2. Why? Because the new formula would be looking at the cells one and two rows above it.
Relative cell referencing is great if you're adding a number of like columns. Imagine how tedious it would be to have to create a formula in the total column for each day. With relative cell referencing, you create the formula in the first total column for January and then copy it to all the other months.
What if your calculation isn't so straightforward? Suppose you want to calculate the projected payroll for the next six months. You would multiply the percentage amount of increase by the current payroll amount. For example, The formula in cell B9 would be =B18*B6, as shown in Figure 5.10. When you copy the formula to cells in other columns, Excel adjusts the cell references for each column automatically.
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 absolute referencing. 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 tells the paste function to keep the same cell reference as it copies a formula from one cell to another.
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.
To make only the column or row portion of a cell address absolute, press F4 again. Each time you press the F4 key, the $ moves to a different coordinate of the cell address. For example, $A$1 becomes A$1, $A1, and so on each time you press the F4 key.
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.
Excel contains a range of functions designed to help you enter formulas easily. Excel functions run the gamut of simple calculations to complex, multitiered equations. You can use an Excel function to total a range of numbers or calculate a car payment. Excel contains more than 100 functions for your use.
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.
What It Does
Adds a range
Determines the average of a range
Inserts the date based on the system clock; updates the date whenever the worksheet is opened or saved
Computes a monthly loan payment
Sets a hyperlink
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 name is an opening parenthesis, and arguments follow that. The function is concluded with a closing parenthesis.
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 colon character indicates through.) The function could also be written as =SUM(A1+A2+A3+A4+A5). Although writing it with all the cells is technically correct, it makes more sense (and conserves space) to use range coordinates. If a function has more than one argument, commas separate the arguments.
A few functions don't use arguments. For example, =NOW() enters the serial number for the current date in the cell.