Learn how to use excel address function in formula with detailed example.

Type a column number :

2

Type a row number :

3

Type a sheet name :

Hello

$B$3

 =ADDRESS(F4,F3,1,TRUE)

B$3

 =ADDRESS(F4,F3,2,TRUE)

$B3

 =ADDRESS(F4,F3,3,TRUE)

B3

 =ADDRESS(F4,F3,4,TRUE)

R3C2

 =ADDRESS(F4,F3,1,FALSE)

R3C[2]

 =ADDRESS(F4,F3,2,FALSE)

R[3]C2

 =ADDRESS(F4,F3,3,FALSE)

R[3]C[2]

 =ADDRESS(F4,F3,4,FALSE)

Hello!$B$3

 =ADDRESS(F4,F3,1,TRUE,F5)

Hello!B$3

 =ADDRESS(F4,F3,2,TRUE,F5)

Hello!$B3

 =ADDRESS(F4,F3,3,TRUE,F5)

Hello!B3

 =ADDRESS(F4,F3,4,TRUE,F5)

What Does It Do ?

This function creates a cell
reference as a piece of text, based on a row and column

numbers given by the user.

This type of function is used
in macros rather than on the actual worksheet.

Syntax

=ADDRESS(RowNumber,ColNumber,Absolute,A1orR1C1,SheetName)

The RowNumber is the normal row
number from 1 to 16384.

The ColNumber is from 1 to 256,
cols A to IV.

The Absolute can be 1,2,3 or 4.

   When 1 the reference will be in the form
$A$1, column and row absolute.

   When 2 the reference will be in the form
A$1, only the row absolute.

   When 3 the reference will be in the form
$A1, only the column absolute.

   When 4 the reference will be in the form
A1, neither col or row absolute.

The A1orR1C1 is either TRUE of
FALSE.

   When TRUE the reference will be in the form
A1, the normal style for cell addresses.

   When FALSE the reference will be in the
form R1C1, the alternative style of cell address.

The SheetName is a piece of
text to be used as the worksheet name in the reference.

   The SheetName does not actually have to
exist.