WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Address Function in Excel

Address Function in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Address Function in Excel

Address function in excel is used to find the address of the cell and the value returned by this function is absolute, it is an inbuilt function, this function has two mandatory arguments which is the row number and the column number, for example, if we use =Address(1,2) we will get output as $B$1.

Syntax

Address Function Formula

Row_Num: This is the row number to use in the cell reference: Row_num=1 for row 1.

Column_Num: Is the column number to use in the excel address of cell reference: Col_num= 2 for column B.

Abs_num: [Optional] It is the reference type. If this parameter is omitted, the default ref_type is set to 1. The Absolute number can have any of the following values depending on individuals need:

Abs_Value Explanation
1 Absolute referencing.
For example, $A$1
2 Relative column; absolute row
For example, A$1
3 Absolute column; relative row
For example, $A1
4 Relative referencing.
For example A1


A1: [Optional]
It is the reference style to use either A1 or R1C1. If this parameter is omitted, the default type is set to A1.

Sheet_name – [optional] It is the name of the sheet to use in the excel address of the cell. If this parameter is omitted, then no sheet name is used in the excel address of the cell.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

How to use the Address Function in Excel? (with Example)

You can download this Address Function Excel template here – Address Function Excel template

Below we are considering all possible cases which could occur while working with the ADDRESS function. Let’s go through the given use case

Address Function Example

  1. The first observation from the above snapshot where row= 1 & column= 4 and address function excel can be rewritten in the simplified version as Address(1,4), which gives the result as $D$1.
  • The parameters Absolut number is by default set to 1, and the reference type is set to 1 (i.e., true) when these parameters have not been defined explicitly. Hence, the result has a pattern of absolute address with row and column name (i.e., $D$1).
  • Here, $D signifies the Absolut column (4), and $1 signifies the Absolute Row (1).
  1. Consider the row 5 case, here Row= 5, column= 20 & Ab_num=2 and address function excel can be rewritten in the simplified version as Address(5,20,2) which gives the result as T$5.
  • The parameter reference type is by default set to 1 (i.e., true) when the parameter has been not defined explicitly. Hence, the result has a pattern of absolute address only against the row ($5) and relative column (T).
  • Here, T signifies the relative column, and $5 signifies the Absolute Row.

Example 1

  1. Now consider the case row 7 from the below worksheet, here we are passing all the arguments of Address function excel, including the optional ones.
  • Arguments passed: row=10, column=9, Ab_num=4, A1=1, Sheet_name=Example1.
  • Address Function Excel can be rewritten in the simplified version as ADDRESS(10,9,4,1, “Example1”); this results as Example1!I10.
  • As the Absolute number parameter is set to 4 it results in Relative reference. (I10)

Example 2

We have seen how the reference of a cell can be derived using the Address Function in excel, but what if we are interested in the value stored in the excel address of cells. How we can get the actual value through the reference. The Indirect function would help us with the above questions.

Indirect Function Formula

Address function (Indirect function formula)

Reference: Is the reference of an excel address of the cell

Ref_type: [Optional] Is a logical value that specifies the style of reference, R1C1 -style =False; A1 -style= True or omitted.

Passing the address using Indirect Function

The below spreadsheet shows a further example in which using the INDIRECT function. We can get the value for the cell reference passed in the Indirect function.

Let’s go through the first observation, Address = $D$3. Rewrite the function as Indirect($D$3).

The value present in the sample data, D3 cell, is Mathematics, which is the same as the Indirect function in the B3 cell.

Address Function Example 3

Consider one more example where Reference Type is of style R7C5; for this, we must set the Ref_type to False (0), so the address function can read the reference style.

Example 4

Things to Note

  • The ADDRESS function is used to create an address from a given row and column number.
  • Depending upon the need, set the Absolute parameter to one of the following
    • 1 or omitted, Absolute reference
    • 2, Absolute row; relative column
    • 3, Relative row; absolute column
    • 4, Relative reference
  • Do not forget to set the second parameter, i.e., reference type of the INDIRECT function to zero or False when the Reference style is the R1C1 type.

Recommended Articles

This has been a guide to Address Excel Function. Here we discuss the Address Formula in excel and how to use the Address function in Excel and practical examples and downloadable excel templates.

  • Excel Convert Function
  • Excel VBA Month
  • INDIRECT Function in Excel
  • PMT Excel Function
  • Page Setup in Excel
2 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Address Function Excel template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More