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.