Most formulas we create include references to cells, range (a collection of cells), or the name. These references enable formulas to work dynamically with the values contained in those cells.
For example, if a formula refers to cell
C6 and you change the value contained in
C6, 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. We can divide cell references into four types:
We’ll also discuss the following topics:
A relative reference is a cell address that changes when the formula is copied to another location. By default, Excel uses relative references in formulas.
For example, if you have a formula
=A1*C1 in cell
E1, and you copy it to cell
E2, the formula will change to
This is because the formula refers to the cells that are in the same position relative to the formula cell. Relative references are useful when you want to apply the same calculation to different cells.
Ctrl+` (or go to
Formula tab and click
Show Formula in Formula Auditing group) to see the formula in each cell instead of the resulting value:
The formulas for
E10 are created by copying and pasting
E1. The cell references in the formula are relative to the position of the cell containing them and are automatically updated for the new location.
You can recognize a relative reference by the absence of the
$ sign before the column and row coordinates, such as
When you refer to a cell in a formula using the absolute reference format, Excel uses the physical address of the cell, and the row & column references don’t change when you copy or fill the formula to other cells.
An absolute reference uses two dollar signs
$ in its address: one for the column letter and one for the row number, for example,
In the following example, we need to calculate the VAT for each item separately. The value of cell
C2 for the
Item1 would be
=B2*B8. If you create formulas for
C5 by copying and pasting
C2, you’ll receive the incorrect result because reference to the relative reference
B8 would be incremented to
B10 and then
To fix the error we need to fix the reference to
B8, so that it doesn’t change when the formula is copied. Let’s edit the formula in
C2 cell and place a
$ symbol in front of the row and column addresses i.e.
$B$8: Now copy
C2 cell (containing formula) and paste it on
EC5, you’ll see the reference
$B$8 doesn’t change, so the results are correct:
Shortcut Key to Convert Relative Reference into Absolute:
When entering a formula, press
⌘+T) to convert the
$ signs tell Excel that
B8 is an absolute reference. When you copy and paste this formula from
C3, the formula will change as follows:
Because we copied the formula to the cell in the next row down,
B2 has changed to
B3. But the reference to
B8 hasn’t changed because you identified it as an absolute reference.
Absolute references are helpful when you need to use a constant value in your calculations, or when you want to preserve the original references of a formula when copying it to other cells.
We already learn that relative reference changes when a formula is copied to another cell, while an absolute reference remains constant.
A mixed cell reference in Excel is a type of cell reference that combines relative and absolute references. A mixed cell reference has either an absolute column and a relative row, or an absolute row and a relative column.
$A1is a mixed-cell reference with an absolute column and a relative row. The column
Awill not change, but the row will adjust relative to the new location of the formula.
A$1is a mixed cell reference with a relative column and an absolute row. The row
1will not change, but the column will adjust.
Mixed cell references are useful when you want to fix one part of a cell reference in a formula, but allow the other part to change dynamically.
Names create absolute references to cells or range in the current worksheet. They can be used in formulas, and when these are copied (or dragged by the fill handle), the references will not be incremented or changed. To create a name for a cell or cell range:
- Select the cell (
B8in our example) you want to name
- Click the Name box (showing
B8), on the left of the Formula Bar
- Type the name that you’ll be using to refer to the selection, then press Enter
You can change an absolute reference into Named references by naming the cell or range and then use the name in the formula. In our Absolute reference example, we used the
$B$8 cell reference for VAT value, now we’ll change the name of
B8 cell into
VAT and convert our formula
To view the list of Named References in the workbook, click the
Formulas tab and select the
Move relative references without making the reference absolute
In Excel, a formula reference can be either relative or absolute, but sometimes you want to move cells that use relative references without making the references absolute. Here’s how.
If you already set up your formulas using only relative references, or perhaps a mix of relative and absolute references, you can reproduce the same formulas in either another range on the same worksheet, another sheet in the same workbook, or perhaps even another sheet in another workbook.
To do this without changing any range references inside the formulas:
- Select the range of cells you want to copy
Home » Find & Select » Replace.
- In the
Find What:box, type an equals sign (
- In the
Replace With:box, type an at sign (
- The equals sign
=in all the formulas on your worksheet will be replaced with the at
You now can simply copy this range, paste it to its desired destination, and follow these steps:
- Select the range of cells you’ve just pasted
Home » Find & Select » Replace.
- In the
Find What:box, type an at sign (
- In the
Replace With:box, type an equals sign (
- The at
@sign in all the formulas on your new location will be replaced with the
Your formulas now should be referencing the same cell references as your originals.
Understanding Workbooks and Worksheets: