When you use a cell (or range) reference in a formula, you can use three types of references:

• Relative: The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column.
• Absolute: The row and column references do not change when you copy the formula because the reference is to an actual cell address.
• Mixed: Either the row or column reference is relative, and the other is absolute.

An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5). Excel also allows mixed references in which only one of the address parts is absolute (for example, $A4 or A$4).

By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell.

Figure 15-7 shows a simple worksheet. The formula in cell D2, which multiplies the quantity by the price, is:

=B2*C2

This formula uses relative cell references. Therefore, when the formula is copied to the cells below it, the references adjust in a relative manner. For example, the formula in cell D3 is:

=B3*C3

FIGURE 15-7
Copying a formula that contains relative references.

But what if the cell references in D2 contained absolute references, like this?

=$B$2*$C$2

In this case, copying the formula to the cells below would produce incorrect results. The formula in cell D3 would be exactly the same as the formula in cell D2. Now I’ll extend the example to calculate sales tax, which is stored in cell B7 (see Figure 15-8). In this situation, the formula in cell D2 is:

=B2*C2*$B$7

The quantity is multiplied by the price, and the result is multiplied by the sales-tax rate stored in cell B7. Notice that the reference to B7 is an absolute reference. When the formula in D2 is copied to the cells below it, cell D3 will contain this formula:

=B3*C3*$B$7

Here, the references to cells B2 and C2 were adjusted, but the reference to cell B7 was not—which is exactly what I want.

FIGURE 15-8
Formula references to the sales tax cell should be absolute.

Figure 15-9 demonstrates the use of mixed references. The formulas in the C3:F7 range calculate the area for various lengths and widths. The formula in cell C3 is: =$B3*C$2

FIGURE 15-9
Using mixed cell references.

Notice that both cell references are mixed. The reference to cell B3 uses an absolute reference for the column ($B), and the reference to cell C2 uses an absolute reference for the row ($2). As a result, this formula can be copied down and across, and the calculations will be correct. For example, the formula in cell F7 is:

=$B7*F$2

If C3 used either absolute or relative references, copying the formula would produce incorrect results.

When you cut and paste a formula (move it to another location), the cell references in the formula aren’t adjusted. Again, this is usually what you want to happen. When you move a formula, you generally want it to continue to refer to the original cells.

• Review: Boater101: A full-reference guide to boating basics Federal law may not require boaters to take a safety course of any kind, but that does not mean that...
• Strategies and Tools for Corporate Blogging by John Cass Most people have a pretty firm grasp on why we need to blog, but knowing how to blog effectively is...
• Excel How-to: A Macro to Hide Blank Rows on a Spreadsheet Today, a coworker asked me how to hide a row on his Excel spreadsheet.  This was a fairly large spreadsheet,...
• Chords 101 In both music as well as musical theory, chords are described as being sets of three or more unique notes...
• Refer yourself to cheap hosting for your website! Are you an aspiring webmaster, but the economic downturn is leaving you a little pressed for cash? If you refer...