5.1: Absolute versus Static References

Last updated
Save as PDF

Page ID: 14246

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $ $ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $$\newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\kernel}{\mathrm{null}\,}$ $ \newcommand{\range}{\mathrm{range}\,}$ $ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$ $ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$ $ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$ $ \newcommand{\Span}{\mathrm{span}}$ $\newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\kernel}{\mathrm{null}\,}$ $ \newcommand{\range}{\mathrm{range}\,}$ $ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$ $ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$ $ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$ $ \newcommand{\Span}{\mathrm{span}}$$\newcommand{\AA}{\unicode[.8,0]{x212B}}$

One of the first things that new Excel users need to realize is that Excel is so much more powerful than a calculator. Some users insist in setting up a worksheet as if it is merely a calculator. These people fail to realize the power of cell references. Instead they use static references to constant values. A static reference is a reference to a value that does not change.

In the example above, instead of using static references (= 9000-3952.96) for the formula in D5, the user should employ cell references. (= B5-C5). The main benefit of use cell references is that the user can manipulate the input values in columns B and C, and the formulas in column D will remain accurate. If the user changes either input values in columns B or C with static references, the formulas in column D will need to be manually updated.

Cell referencing is an extremely useful feature when formulas need to be copied across ranges in a worksheet. When creating formulas that contain references to cells or cell ranges, the default cell reference is considered to be a relative cell reference. When formulas with these type of cell references are copied to other cells the formulas will automatically change relative to the cells that they are copied to. However, there may be instances where it is necessary for Excel to keep the exact cell referenced in a formula when copying to other cells. A cell reference that does not change when copied is called an absolute cell reference (sometimes called a fixed cell reference). The indicator that a cell reference is absolute is the presence of a dollar sign symbol in front of both the column letter and row number, such as $A$3. To create an absolute cell reference, either type the dollar sign manually, or press the F4 after entering the cell reference. Repeatedly pressing the F4 key cycles the reference through the four different combinations of relative, absolute, and mixed cell references.

Mixed cell references are a combination of relative and absolute: either the column is relative and the row fixed (absolute), for example D$2, or the column is fixed and the row relative: $D2. Mixed cell references are rarely used, but they play a significant role when it is necessary to keep a single row or column unchanged while copying the formula. Mixed cell references are commonly used when creating a table of values, like a multiplication table or a mortgage rate table.

Whenever possible, utilize relative and/or absolute references instead of static cell references.

In the worksheet above, the formula in C6 is using relative cell references to both the Sales amount (B6), and the Commission Rate (B3). Unfortunately, if this formula is copied (perhaps by using the fill handle) to rows 7:16, the resulting formulas will be wrong. Some cells will display zeros or a #VALUE! error, while others will display inflated amounts because the Rate percentage is being replaced by higher sales values. Therefore, the reference to B3 in the formula should be an absolute cell reference, i.e. =B6*$B$3. It may be tempting to construct the formula using a static reference to the constant value of .075 (i.e. =B6*.075). However, if the rate needs to be changed to 7.25%, the formulas with the static references would each need to be changed manually. Conversely, if the formulas in column C are using absolute cell references to $B$3, a single edit to B3 is all that is needed to update all of the cells in column C. Knowing the differences between the various type of cell reference will make the worksheet more scalable and flexible for future expansion.