Page 1 of 5
Medical Laboratory Techniques Department
Lecture 12: Using Cell References in Formulas
Israa Saleh
Using Cell References in Formulas
Most formulas you create include references to cells or ranges. These references
enable your formulas to work dynamically with the data contained in those cells or
ranges. For example, if your formula refers to cell A1 and you change the value
contained in A1, the formula result changes to reflect the new value. If you didn’t
use references in your formulas, you would need to edit the formulas themselves to
change the values used in the formulas.
Using relative, absolute, and mixed references
When you use a cell (or range) reference in a formula, you can use three types of
references:
■ Relative: The row and column references can change when you copy the formula
to another cell because the references are actually offsets from the current row and
column. By default, Excel creates relative cell references in formulas.
■ Absolute: The row and column references don’t change when you copy the
formula because the reference is to an actual cell address. An absolute reference uses
two dollar signs in its address: one for the column letter and one for the row number
(for example, $A$5).
■ Mixed: Either the row or the column reference is relative, and the other is absolute.
Only one of the address parts is absolute (for example, $A4 or A$4).
The type of cell reference is important only if you plan to copy the formula to other
cells. The following examples illustrate this point. Figure (1) shows a simple
worksheet. The formula in cell D2, which multiplies the quantity by the price, is
=B2*C2
Page 2 of 5
Medical Laboratory Techniques Department
Lecture 12: Using Cell References in Formulas
Israa Saleh
Figure 1: Copying a formula that contains relative references.
This formula uses relative cell references. Therefore, when the formula is copied to
the cells below it, the references adjust in a relative manner. For example, the
formula in cell D3 is
=B3*C3
But what if the cell references in D2 contained absolute references, like this?
=$B$2*$C$2
In this case, copying the formula to the cells below would produce incorrect results.
The formula in cell D3 would be the same as the formula in cell D2.
Now I’ll extend the example to calculate sales tax, which is stored in cell B7 (see
Figure 2). In this situation, the formula in cell D2 is
=(B2*C2)*$B$7
Figure 2: Formula references to the sales tax cell should be absolute.
Page 3 of 5
Medical Laboratory Techniques Department
Lecture 12: Using Cell References in Formulas
Israa Saleh
The quantity is multiplied by the price, and the result is multiplied by the sales tax
rate stored in cell B7. Notice that the reference to B7 is an absolute reference. When
the formula in D2 is copied to the cells below it, cell D3 will contain this formula:
=(B3*C3)*$B$7
Here, the references to cells B2 and C2 were adjusted, but the reference to cell B7
was not — which is exactly what I want because the address of the cell that contains
the sales tax never changes.
Figure (3). demonstrates the use of mixed references. The formulas in the C3:F7
range calculate the area for various lengths and widths. Here’s the formula in cell
C3:
=$B3*C$2
Figure 3: Using mixed cell references.
Notice that both cell references are mixed. The reference to cell B3 uses an absolute
reference for the column ($B), and the reference to cell C2 uses an absolute reference
for the row ($2). As a result, this formula can be copied down and across, and the
calculations will be correct. For example, the formula in cell F7 is
=$B7*F$2
If C3 used either absolute or relative references, copying the formula would
produce incorrect results. Changing the types of your references you can enter
nonrelative references (that is, absolute or mixed) manually by inserting dollar
signs in the appropriate positions of the cell address. Or you can use a handy
shortcut:
Page 4 of 5
Medical Laboratory Techniques Department
Lecture 12: Using Cell References in Formulas
Israa Saleh
the F4 key. When you’ve entered a cell reference (by typing it or by pointing), you
can press F4 repeatedly to have Excel cycle through all four reference types.
For example, if you enter =A1 to start a formula, pressing F4 converts the cell
reference to=$A$1. Pressing F4 again converts it to =A$1. Pressing it again displays
=$A1. Pressing it one more time returns to the original =A1. Keep pressing F4 until
Excel displays the type of reference that you want.
Referencing cells outside the worksheet
Formulas can also refer to cells in other worksheets — and the worksheets don’t
even have to be in the same workbook. Excel uses a special type of notation to handle
these types of references.
1. Referencing cells in other worksheets
To use a reference to a cell in another worksheet in the same workbook, use this
format:
SheetName!CellAddress
In other words, precede the cell address with the worksheet name, followed by an
exclamation point. Here’s an example of a formula that uses a cell on the Sheet2
worksheet:
=A1*Sheet2!A1
This formula multiplies the value in cell A1 on the current worksheet by the value
in cell A1on Sheet2.
2. Referencing cells in other workbooks
To refer to a cell in a different workbook, use this format:
=[WorkbookName]SheetName!CellAddress
In this case, the workbook name (in square brackets), the worksheet name, and an
exclamation point precede the cell address. The following is an example of a formula
that uses a cell reference in the Sheet1 worksheet in a workbook named Budget:
=[Budget.xlsx]Sheet1!A1
If the workbook name in the reference includes one or more spaces, you must enclose
it (and the sheet name and square brackets) in single quotation marks. For example,
here’s a formula that refers to a cell on Sheet1 in a workbook named Budget For
2016:
=A1*'[Budget For 2016.xlsx]Sheet1'!A1
When a formula refers to cells in a different workbook, the other workbook doesn’t
have to be open. If the workbook is closed, however, you must add the complete
path to the reference so that Excel can find it. Here’s an example:
=A1*'C:\My Documents\[Budget For 2016.xlsx]Sheet1'!A1
Page 5 of 5
Medical Laboratory Techniques Department
Lecture 12: Using Cell References in Formulas
Israa Saleh
A linked file can also reside on another system that’s accessible on your corporate
network. The following formula refers to a cell in a workbook in the files directory
of a computer named DataServer:
='\\DataServer\files\[budget.xlsx]Sheet1'!$D$7
