Reference Functions

Reference functions are used to return the addresses, column numbers, and row numbers of individual cells. The number of columns or rows in a cell range can be counted. Indirect cell references can be made, and a range of cells can be offset by a given number of columns and rows.

ADDRESS (row, column, ref_type): Returns the cell address for the row and column numbers provided. The cell reference type is determined by ref_type. Set ref_type  to 1 for an absolute reference ($A$1), 2 for an absolute row and relative column (A$1), 3 for a relative row and absolute column ($A1), or 4 for a relative reference (A1). If ref_type is omitted, an absolute cell reference will be used.

COLUMN (reference): Returns the column number of the supplied cell reference.

COLUMNS (range): Returns the number of columns in the supplied cell range.

ROW (reference): Returns the row number of the supplied cell reference.

ROWS (range): Returns the number of rows in the supplied cell range.

Examples:

Formula

Result

=ADDRESS(3, 4)

$D$3

=ADDRESS(2, 7, 1)

$G$2

=ADDRESS(2, 7, 2)

G$2

=ADDRESS(1,1,4)

A1

=COLUMN(C4)

3

=COLUMNS(D2:D8)

1

=COLUMNS(A1:D4)

4

=ROW(B3)

3

=ROWS(B2:F6)

5

 

Indirect Cell References

INDIRECT (ref_text): Looks in the cell given by ref_text for a second cell reference. Returns the contents of the cell given by the second cell reference.  

Examples:

 

A

B

1

water

A2

2

fire

A3

3

metal

A1

 

Formula

Result

=INDIRECT(B1)

fire

=INDIRECT(B2)

metal

=INDIRECT(B3)

water

=INDIRECT(A1)

#REF! (A1 does not contain a cell reference)

 

Offset Cell References

OFFSET (reference, rows, columns): Returns the values in the cell or cell range at reference offset by a given number of columns and rows. If rows is positive, the row reference will be shifted down, while if rows is negative, the row reference will be shifted up. If columns is position, the column reference will be shifted to the right, while if columns is negative, the column reference will be shifted to the left.

 Examples:

Formula

Result

=OFFSET(A1, 2, 4)

value in cell E3

=OFFSET(F4, -1, -3)

value in cell C3

=OFFSET(B2:D5, 0, -1)

cell range A2:C5

=OFFSET(B2:D5, -3, 0)

#REF! (offset cell range is outside of sheet)

=OFFSET(C1:D5, 4, 3)

cell range F5:G9

=SUM(OFFSET(A1:A10, 2, 0)

sum of values in cell range A3:A12