Last updated Monday, Aug. 31, 2020, at 10:27 a.m. This article is based on legacy software. Cell references are an important part of creating formulas in Excel. Using cell references allows your formulas to update automatically if the value in a particular cell changes and can also assist you in updating formulas as cells are copied or moved. There are two basic types of cell references, each serving a different purpose. References that will change in
relation to the new location of the formula. Relative references identify cells based on their relationship to the cell containing the reference. EXAMPLE: In the example shown here, the function in D2 calculates Bob's pay by multiplying his hours (C2) by his rate of pay (B2). Since the rate of pay varies by student, we can use the Fill
Command to copy the function to the other rows in the spreadsheet. Because the cell references are relative, the function will adjust to calculate the values in the appropriate rows. In other words, when we copy the function to Anna's total, her pay will be based on C3*B3.Relative Cell References
Absolute Cell References
References that remain the same when a formula is copied to a new location. No matter where the formula or the values in the original cell are moved, the formula will continue to refer to the same cell.
EXAMPLE: In the example shown here, the function in C11 calculates Bob's pay by multiplying his hours (B11) by the rate of pay for all students (C9). Notice the $ before both the column and row IDs ($C$9). By making the reference to the rate of pay an absolute reference, we can use the
Fill Command to copy the function to the other rows in the spreadsheet. The same rate of pay will be multiplied by the appropriate hours for each student. In other words, when we copy the function to Anna's total, her pay will be based on B12*$C$9.
Relative and absolute cell references can be used in all situations that require cell references, including cell ranges and formulas.
A formula, cell range, or cell reference can have both relative and absolute components. By adding a dollar sign ($) before either the column or row location or both, that reference becomes absolute. When
adding dollar signs to cell references, only the portion of the reference directly following the dollar sign is absolute. To keep the entire cell reference constant, place a dollar sign before both the column and row location.
EXAMPLE: $A$12
To make a cell reference absolute:
- Within the formula, before the part of the reference (i.e., row or column reference) that you would like to be absolute, type $.
OR
In the Formula bar,- Click within the cell reference you want to change.
- Windows: Press [F4].
Macintosh: Press [Cmd] + [T].
Part of your cell reference is changed.
HINT: Pressing [F4] or [Cmd] + [T] will cause elements (i.e., row and column references) of your cell reference to toggle between being relative and absolute. If you do not get the desired reference the first time, continue pressing this key until the desired portions of your reference are relative and absolute.
EXAMPLE: When pressing [F4] or [Cmd] + [T], your cell reference may cycle through the following progression: A12, $A$12, A$12, $A12, A12.
Was this article helpful? Yes No
by Karyn Stille
Excel uses two types of cell references to create formulas. Each has its own purpose. Read on to determine which type of cell reference to use for your formula.
Relative Cell References
This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using AutoFill.
Example:
=SUM(B5:B8), as shown below, changes to =SUM(C5:C8) when copied across to the next cell.
Absolute Cell References
Situations arise in which the cell reference must remain the same when copied or when using AutoFill. Dollar signs are used to hold a column and/or row reference constant.
Example:
In the example below, when calculating commissions for sales staff, you would not want cell B10 to change when copying the formula down. You want both the column and the row to remain the same to refer to that exact cell. By using $B$10 in the formula, neither changes when copied.
A more complicated example:
Let's pretend that you need to calculate the prices of items in stock with two different price discounts. Take a look at the worksheet below.
Examine the formula in cell E4. By making the first cell reference $C4, you keep the column from changing when copied across, but allow the row to change when copying down to accommodate the prices of the different items going down. By making the last cell reference A$12, you keep the row number from changing when copied down, but allow the column to change and reflect discount B when copied across. Confused? Check out the graphics below and the cell results.
Copied Across
Copied Down
Now, you might be thinking, why not just use 10% and 15% in the actual formulas? Wouldn't that be easier? Yes, if you are sure the discount percentages will never change - which is highly unlikely. It's more likely that eventually those percentages will need to be adjusted. By referencing the cells containing 10% and 15% and not the actual numbers, when the percentage changes all you need to do is change the percentage one time in cell A12 and/or B12 instead of rebuilding all of your formulas. Excel would automatically update the discount prices to reflect your discount percentage change.
Summary of absolute cell reference uses:
| $A1 | Allows the row reference to change, but not the column reference. |
| A$1 | Allows the column reference to change, but not the row reference. |
| $A$1 | Allows neither the column nor the row reference to change. |
There is a shortcut for placing absolute cell references in your formulas!
When you are typing your formula, after you type a cell reference - press the F4 key. Excel automatically makes the cell reference absolute! By continuing to press F4, Excel will cycle through all of the absolute reference possibilities. For example, in the first absolute cell reference formula in this tutorial, =B4*$B$10, I could have typed, =B4*B10, then pressed the F4 key to change B10 to $B$10. Continuing to press F4 would have resulted in B$10, then $B10, and finally B10. Pressing F4 changes only the cell reference directly to the left of your insertion point.
I hope this tutorial has made these cell reference types "absolutely" clear!
Microsoft Office
Tutorials
//tutorials.esmartweb.com