As mentioned at the beginning of this chapter, the use of constants in formulas defeats the purpose of a spreadsheet. When you use constants, you have to change the formula each time a value changes.
The content of a cell is handled through a cell reference. A reference tells Excel which cell contains the values used in a formula (see Figure 3-5).
When you enter references, make sure that the formula doesn’t contain spaces.
Note
Formulas are not case sensitive. Excel converts all references from lowercase to uppercase if the syntax of the formula is correct. If Excel does not convert the reference, check your entry for syntax and typing errors.
You will find it easier to enter cell references by selecting the cell or cell range you want to use rather than typing it. To enter a formula, perform the following steps:
Type the equal sign.
Enter any function name, parenthesis, or character with which a formula can begin.
Select the cell or cell range you want to include in the formula as the cell reference. The selection is marked by a dashed line, and the cell reference appears in the formula.
Enter the operator or another part of the formula.
Select other required cells or ranges.
Repeat steps 2 through 5 until the formula is complete.
Press the Enter key to complete the formula.
Cell references allow you to use all the advantages offered by a spreadsheet. You can create any number of calculation schemes and change a scheme to get different results.
This means that you have to create a calculation scheme only once and can use it for similar calculations over and over again. In the table in Figure 3-6, you can change the values in columns B and C to immediately get the result in column D.
Another advantage of using cell references in a formula is that you can copy the formula by dragging the fill handle down. In this way you create all of the required calculations within seconds, which would not be possible if you were using constants.
If you copy the formula shown earlier in Figure 3-6 by dragging the fill handle down, you get the correct result for each line. This is because Excel adjusts the references in the formula by using the current row number. However, this works only because the row number in the formula is relative.
Consider a relative reference to be the relation between two cells. The relation—not the literal syntax of the reference—is copied.
The formula in cell D3 in the table shown in Figure 3-6 would read like this: “Take the value in the cell two columns to the left in the same row (=B3) and multiply (=B3*) this value by the value in the cell one column to the left in the same row (=B3*C3).”
An absolute reference is the counterpart to a relative reference. An absolute reference is a cell reference that doesn’t change when it is copied or filled. You use absolute references to anchor a reference in a formula to a particular cell. Assume that you want to calculate the sales tax for several net prices, and the sales tax rate is entered in a particular cell. You need to be able to copy the formula you created (see Figure 3-7).
The formula entered in cell C4 calculates the sales tax for the net price in cell B4. The formula is:
=B4*C1
If you copy this formula to the cells below it, you get the wrong results because the relative cell references change. Cell C6 even displays the error message #VALUE!. Because cell C3 contains the text “sales tax amount”, the #VALUE! error is generated.
When you analyze the formula in cell C4, you will see that the reference to the sales tax should not be changed. To ensure that the sales tax doesn’t change, enter a dollar sign ($) before the column (C) and the row (1) references. Now this cell reference won’t change when copied or filled and is called an absolute reference in Excel.
The formula in cell C4 in the table shown in Figure 3-7 should read:
=B4*$C$1
If you fill the range with this formula starting at cell C4 and copy the formula in the cells below it, the entire table shows the correct results (see Figure 3-8).
Actually, you didn’t need an absolute reference to copy the formula in the example shown in earlier Figure 3-8. Because you copied the formula in the same column, the column reference didn’t have to be absolute. This knowledge leads us to a discussion of mixed references which are, as the name implies, a mix of relative and absolute references.
Assume that you want to create a multiplication table. The calculation method to use is simple to figure out. Look at the formula for cell F6 in the formula bar of the table shown in Figure 3-9: =A6*F2. In B3, the formula would be: =A3*B2. As you probably can guess, the formula doesn’t have to be rewritten for each cell.
If you could lock the column or row reference, you could copy the formula created in cell B3 in the table shown in Figure 3-9 to the cells below and to the right of it. The solution:
You have to lock the column reference for the values in column A to copy the formula horizontally and to always use the values in column A. The row reference for the values in column A has to remain relative so that it can be adjusted when copied vertically. The result is the cell reference $A3.
For the reference to the values in row 2, it is the other way around. The column reference has to remain relative, and the row reference has to be locked with the $ sign. This way, the reference to the row is not changed when the formula is copied vertically, but the column references are adjusted when it is copied horizontally. This leads to the following formula for cell B3:
=$A3*B$2
Try this in your table. You will always get the correct results (see Figure 3-10).
The basic principle is this: For a mixed reference, the part after the dollar sign is locked and cannot be changed when copied. Remember these two rules:
To always apply the values in a certain column, lock the column reference with the dollar sign.
To always apply the values in a certain row, lock the row reference with the dollar sign.
You can press the F4 key to change the reference type. Click into the cell reference in a formula and press F4 several times. Each time you press F4, the syntax changes (relative to absolute to mixed to relative, and so on).
Finally, you should be aware of a new feature in Excel 2007 and Excel 2010: In list ranges marked as Table, you’ll find a new syntax for relative references, as in the following example:
=[@Price]*[@Quantity]
The example multiplies the values in the columns with the Price and Quantity headings for each line. If you enter a formula in row 1, Excel fills the adjacent empty cells below it with the new formula.
Circular references are mostly generated by input errors. A circular reference is a reference to a cell containing the formula, in other words, a reference to itself. Excel cannot resolve formulas with a circular reference. Instead you receive the message shown in Figure 3-11.
If you click OK in the error message, the circular reference toolbar appears in the table window of Excel 2003 (see Figure 3-12).
You can use the circular reference toolbar to iterate through the cells within the circular reference. Use the Trace Dependents and Trace Precedents buttons to see what caused the problem.
If you click OK in the circular reference warning in Excel 2010, a help window shows instructions on how to handle circular references (see Figure 3-13).
In Excel 2007 and Excel 2010, you can find the search and remove options for circular references on the ribbon, on the Formula tab. In the Formula Auditing group, click the Error Checking button and then the Circular References button. Then click the displayed references (see Figure 3-14). However, here (and in the status bar), only the circular reference entered last is displayed.
Caution
If you close the warning and the help windows and don’t correct the circular reference, no other warning is displayed when you enter another formula with a circular reference in a cell. However, the status bar shows circular references. So you’ll want to correct circular references immediately, because you might forget later.
