Type

Description

Example

Relative reference

Points to a cell based on its relative position to the current cell. When the cell containing the reference is copied, the reference is adjusted to point to a new cell with the same relative offset as the original referenced cell.

A1

Relative reference pointing to cell A1

Absolute reference

Points to a cell at an exact location. When a cell containing the formula is copied, the reference does not change. Absolute references are designated by placing a dollar sign (4) in front of the absolute row and absolute column.

$A$1

Absolute reference pointing to cell A1

Mixed

Points to absolute or relative rows or columns.

$A1

Absolute reference pointing to column A, relative reference pointing to row 1.

When the cell containing the formula is copied, the column reference will not change, and the row reference will be adjusted to point to a new cell with the same relative offset as the original row.

Reference to other worksheet

Points to a different worksheet in the same workbook or a different workbook.

Sheet5!A2

Points to cell A2 in worksheet Sheet5

[Book2]Sheet1!B3:D7

Points to cells in the range from cell B3 (upper left corner) to D7 (bottom right corner) in worksheet Sheet1 from workbook Book2

Reference to multiple worksheets

Points to the same cell or range reference in in worksheet in the same or different workbooks. Worksheet references must be in the same order as they appear in the worksheet tab section, from left to right.

Sheet1:Sheet2!A1

Points to cell A1 in worksheets Sheet1 and Sheet2

Sheet1:Sheet2!B5:C7

Points to cells in the range from cell B5(upper left corner) to C7 (bottom right corner) in worksheets Sheet1 and Sheet2

[Book3]Sheet2:Sheet3!B5

Points to cell B5 in sheet Sheet2 and Sheet3 from workbook Book3

External references

Points to cells in other workbooks. Workbook name must be placed in brackets, followed by the worksheet name and an exclamation point, and finally a cell or range reference.

[Orders]Sheet3!B5

Relative reference pointing to cell B5 in worksheet Sheet3 in a workbook titled Orders

[Sales]Sheet2!$B$4

Absolute reference pointing to cell B4 in worksheet Sheet2 in a workbook titled Sales