Microsoft Office Tutorials and References
In Depth Information
Creating and Editing Formulas
Figure 11-5 When we copied the formula from the top of column N to the cells beneath it, Excel
adjusted the references so that they work properly with the data in each row.
The solution is to use an absolute cell reference, which is indicated by adding a dollar sign
before the column letter and row number in the reference: =D4*(1+$A$1). When you copy
this formula, Excel adjusts the first, relative reference but leaves the absolute reference
alone. You can type the dollar signs for an absolute reference directly or click to position
the insertion point in the cell reference you want to adjust and then press F4. (To make a
range reference absolute, select the entire reference, including the range operator, before
pressing F4.)
As we noted earlier in this chapter, named cells and ranges are always treated as absolute
references. So, if you select cell A1 and define its name as Inflation_Rate, you can use that
name in your formula and copy the formula to other rows and columns without making
any adjustments.
Sometimes, a halfway approach is called for, in which either the row or the column
reference remains fixed while the other half of the reference address changes. This construction
is called a mixed range, with a dollar sign in front of either the row or column portion of the
reference to indicate that that value should not change. Imagine a sheet where you have
department names in column A identifying each row, with month names over each column
containing projected spending by that department for the month. In column B, you want
to enter a fixed amount for each department, and you want to compare each month’s total
to that amount. As you copy the formula across each row, you want it to refer to column 2
in each cell. When you copy the formulas to the rows below, you want the row reference to
change. Here’s how a mixed reference solves that problem:
Search JabSto ::




Custom Search