Definitions - Absolute Vs Relative References

Absolute Cell Reference

Spreadsheets like Excel identify the locations of cells or ranges of cells which are used in formulas, functions, charts, and so forth. Some are general, some are specific; some allow changes without affecting the outcome, while some locations of references are required to remain in place in order to have an accurate result. An absolute cell reference makes sure your result is firmly pointing to the exact place each and every time.

In other words, an absolute cell reference is used when you want a cell reference to stay fixed on a specific cell. This means that as a formula or function is copied and pasted to other locations, the cell references in the formula or function do not change.

Normally, most cell references in a spreadsheet are relative cell references, which change when copied and pasted to other cells.

The dollar sign ($) is the symbol used to denote an absolute cell reference. Here are some examples:

$A$1, $B$15, or $C$12

If you think about it for a moment, the only reason you would want an absolute cell reference is for the times that you need to copy a formula and want the referenced cell to always be calculated.

The best way to understand the difference is to see an example and for you to follow along in your own blank worksheet. Enter the figures in columns A, B, C and D in your worksheet so that they look like the following example. The formula to enter in cell D3 is shown also:



Now, try copying the formula in cell D3 down into cell D4 (click in cell D4 and grab the Fill handle at the lower right hand corner, then drag down one cell). You will get the following result:



This error result (#VALUE!) is because the formula, when copied, continued in its "relative" location, "relative" to where you pointed the formula. Thus, although the formula in cell D3 was correct-- C3*B1, the next relative location was C4*B2, and there is no VALUE in cell B2. More accurately, our intention was to calculate the Agent's Sales times the commission rate in cell B1, 7%.

A WORSE result might show NO ERROR because Excel would multiply the referenced cell by a value that might just be in the next cell referenced, returning a result that MIGHT look right, but is still wrong.

Now, let's make the formula in cell D3 ABSOLUTE, meaning, ALWAYS (or ONLY) pointing at the cell where the commission rate is located. The formula would read =SUM(C3*$B$1). You might read this aloud like this: "The SUM of the value in C3 multiplied by the value that is (ALWAYS or ONLY) in Column B and (ALWAYS or ONLY) in Row 1". The next example shows the correct result when you copy the new formula from cell D3 down to cells D4,D5, and D6:



Note now that this is only a PARTIAL ABSOLUTE reference; that is, when you copy the formula down, one part of the formula is RELATIVE (the values in cells C3:C6) while the REST of the formula is ABSOLUTE ("ALWAYS or ONLY Column B" and "ALWAYS or ONLY Row 1"). This is known as a MIXED ABSOLUTE formula, in that it mixes both RELATIVE and ABSOLUTE locations. When you copy down, the relative reference remains the same as normal; that is, cell C3 is referenced in the formula in D3, cell C4 is referenced in the formula in D4, and cell C5 is referenced in the formula in D5, while at the same time, each formula ONLY calculates that reference against the value in B1.

A FULLY ABSOLUTE reference would make sure, for you, that the value relating to each specific cell is calculated against another specific cell, but you have to be careful. Your intent might be to calculate ONLY one cell against ONLY another cell. That Fully Absolute Reference might look like this:

=SUM($C$3*$B$1)

BUT if you tried copying that formula down, you would get a different result in our example. Here's what would happen:



So, you would have to make sure, in this example, that each cell's formula reflects your intention. That is, what you are trying to do.

Using Excel's cell references are only tricky if you don't stop to think what it is you're trying to accomplish. Think slowly, especially in complicated calculations, and take them step by step. After a while, understanding will come easier and your work will progress faster.

Here's an example of a more complicated worksheet where mixed Absolute references would come in handy:



The agents in this example earn different commissions, and you can tell at a glance what their totals are. There are, of course, other ways to uncomplicate the view and make your worksheet easier to read and understand, such as grouping the commission rates in one location, the sales agents and their sales in another group, and so forth, requiring perhaps a different formula, one that might use what are called IF statements (IF sales exceed X amount, then the COMMISSION is Y).

COLUMN ABSOLUTE

Here is an example using a formula where the references are ABSOLUTE to the column:



Do you see the difference from our previous examples? In our previous example, the formula was a mixed absolute of a relative reference, a column absolute reference, and a row absolute reference (C3*$B$1). The above example shows you what a column absolute by itself would look like. You're able to copy this formula across without errors! Now, here's a Row Absolute Reference:



The Row Absolute example shows you how you can copy down without errors.

Here's another example of Mixed Absolute References:



Now another example of Fully Absolute References:



Can you read the yellow pasted note? It says:

"Note: Excel will cycle through Absolute references if you click F4 when entering a formula. Try it: click on cell B10. Then, in the formula bar above, click again so that your insertion point I-beam is blinking. Then press F4."

This means that Excel allows you to select the correct Absolute reference you're looking for. Click on a cell to get into "Edit Mode" and then press your F4 key on your keyboard. Click the F4 key again, then again, then again--until you come up with the Absolute Reference you're looking for.