Microsoft Office Tutorials and References
In Depth Information
Relative Cell References
Relative Cell References
When you copy and paste or move a formula that uses relative references, the references
in the formula change to reflect cells that are in the same relative position to the formula.
The formula is the same, but it uses the new cells in its calculation. Relative addressing
eliminates the tedium of creating new formulas for each row or column in a worksheet
filled with repetitive information.
Absolute Cell References
If you don’t want a cell reference to change when you copy a formula, make it an absolute
reference by typing a dollar sign ($) before each part of the reference that you don’t want
to change. For example, $A$1 always refers to cell A1. If you copy or fill the formula
down columns or across rows, the absolute reference doesn’t change. You can add a $ be-
fore the column letter and the row number. To ensure accuracy and simplify updates, enter
constant values (such as tax rates, hourly rates, and so on) in a cell, and then use absolute
references to them in formulas.
Mixed Cell References
A mixed reference is either an absolute row and relative column or absolute column and
relative row. You add the $ before the column letter to create an absolute column or before
the row number to create an absolute row. For example, $A1 is absolute for column A and
relative for row 1, and A$1 is absolute for row 1 and relative for column A. If you copy
or fill the formula across rows or down columns, the relative references adjust, and the
absolute ones don’t adjust.
3-D References
3-D references allow you to analyze data in the same cell or range of cells on multiple
worksheets within a workbook. A 3-D reference includes the cell or range reference, pre-
ceded by a range of worksheet names. For example, =AVERAGE(Sheet1:Sheet4!A1) re-
turns the average for all the values contained in cell A1 on all the worksheets between and
including Sheet 1 and Sheet 4.
Search JabSto ::




Custom Search