Address Function in Microsoft Excel

To return the cell reference as text, we will use the Address Function in Microsoft Excel.

ADDRESS:- This function is used to creates a cell reference as text, given specified row and column numbers.

image1

There are 4 options for abs_num:-

a) Absolute cell Reference (1) :- Address returns an absolute cell reference.

b) Absolute row/ Relative column (2) :- Address returns an absolute row with a relative column.

c) Relative row/ Absolute column (3) :- Address returns a relative row with an absolute column.

d) Relative (4) :- Address returns a relative cell reference.

For example:

  • Select the cell A1, and write the formula.
  • =ADDRESS(2,1,1), press Enter on your keyboard.
  • The function will return the cell address $A$2.

img1

We can use the ADDRESS function in 5 different ways.

Syntax Description
$C$2 Absolute reference
C$2 Absolute row; relative column
R2C[3] Absolute row; relative column in R1C1 reference style
[Book1]Sheet1!R2C3 Absolute reference to another workbook and worksheet
'EXCEL SHEET'!R2C3 Absolute reference to another worksheet

 

Firstly, lets understand all the above ways of using the Address function.

Absolute Reference

The absolute reference is used where the column and row references are fixed.

Follow the below given steps:-

  • Write the Address function for absolute reference in any cell.
  • =ADDRESS(2,3), press Enter on the keyboard.
  • The function will return$C$2.
  • In this formula,2 is used to denote the 2nd row and 3 is used to denote the 3rd column, hence we get $C$2.

 
img3
 
Absolute row; Relative column

The absolute row and relative column referencesare used, where only the row reference s fixed.

Follow the below given steps:-

  • Write the Address function for Absolute row and Relative column in any cell
  • =ADDRESS(2,3,2), press Enter on the keyboard.
  • The function will return C$2.
  • In this formula, 2 is used to denote the 2nd row, 3 is used to denote the 3rd column and the 2 is used to denote the absolute row and relative column format for this reference.  Hence, we get C$2.
  • Note: As shown earlier, you can choose 1 among the 4 options for the abs_num parameter.  Hence if you use this formula =ADDRESS(2,3,3) it will return $C2 where the column reference is fixed and the row is relative.

 
img4
 
Absolute row; relative column in R1C1 reference style

The absolute row and relative column references are used and the cell references are shown in the R1C1 format.

Follow the below given steps:-

  • Write the Address function for Absolute row and Relative columnin R1C1 reference style.
  • =ADDRESS(2,3,2,FALSE), press Enter on the keyboard.
  • The function will returnR2C[3].
  • In this formula, 2 denotes the row, 3 denotes the column, 2 is to show the absolute row and relative column format for the reference.  And the FALSE is to show the cell reference in R1C1 style.  In place of False / True, you can also use 0 /1, where 0 is for R1C1 style, 1 is for A1 style. Hence we get the result as R2C[3].  So starting from the 1st cell and 1st column in the sheet, then R denotes ROW, 2 means 2nd row, C is for COLUMN and [3] denotes 3 columns to the right, which is column C.

 
img5
 
Absolute reference to another workbook and worksheet

To show the absolute reference to another workbook and worksheet, follow the below given steps:-

  • Write the Address function to show the Absolute reference to another workbook and worksheet.
  • =ADDRESS(2,3,1,FALSE,"[Book1]Sheet1"), Press Enter on the keyboard.
  • The function will return [Book1]Sheet1!R2C3.

 
img6
 
In this formula,the only difference is the last parameter where we get the cell references with the name of the workbook and worksheet.  All other parameters are the same as explained above.

Absolute reference to another worksheet

To show the absolute reference to another worksheet, follow the below given steps:-

  • Write the Address function to show the Absolute reference to another workbook and worksheet.
  • =ADDRESS(2,3,1,FALSE,"EXCEL SHEET"), Press Enter on the keyboard.
  • The function will return'EXCEL SHEET'!R2C3.

 
img7
 
In this formula, we get the absolute cell reference with the name of the worksheet which is “EXCEL SHEET”.

These are the ways to get the Cell Reference as Text in Microsoft Excel.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 

Users are saying about us...

  1. This gave me the name of the cell I was after, but how do I get to the contents of that cell? Without directly referencing it, that is. I have a summary sheet and lots of data sheets, and want to just copy down a row when I add a data sheet.

  2. "This formula will return the contents of the cell:
    =INDIRECT(ADDRESS(A1,A2,4,TRUE,A3))
    INDIRECT changes a text address to one that can be used in a formula. But be aware this will only work if the workbook you're referencing is open."

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube