Absolute and Relative References in Excel
An Excel tutorial by Peter Kalmstrom
 Formulas
can have relative or absolute references to the cells included
in the formula. Formulas with relative references don’t
behave in the same way as absolute reference formulas when
you copy or move them, so it is important to understand
the difference. This is what Peter Kalmström explains in
the demo below.
Absolute Reference
When a formula contains an absolute reference, Excel, always
calculates the value of the same cell, no matter to where
you copy or move the formula. Absolute references can be
created in two ways:
- Give the cell a custom name, and use that name in
the formula.
- Use the default name but add a dollar sign before
the letter and number.
Relative Reference
When Excel reads which cells should be included in a formula
with relative references, Excel uses the position
of the cell, for example “the cell two rows above in the
same column as the formula cell + the cell above in the
same column as the formula cell”. This means that when you
copy a formula with relative references, Excel continues
to calculate cells in the same positions compared to the
new formula cell as the positions the original formula cell.
A 3-D reference refers to the same cell or range on multiple
Excel sheets. It is useful when you work with several
sheets that follow the same pattern and when cells on each
sheet contain the same type of data.
In this tutorial Peter uses budget data from different departments
of an organization. Each department has its own tab in the
Excel workbook, but by using 3D references he can set
auto-sums for all sheets in one process.
Peter gives examples on both reference types in the demo.
|
