• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Address Function in Excel

Home » Excel » Lookup & Reference Functions in Excel » Address Function in Excel

By Roshan Waingankar Leave a Comment

Address Function in Excel

As the name suggests Address function 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.

Address in Excel (Table of Contents)

  • Address Excel Function
  • Address Formula in Excel
  • How to Use Address in Excel?

Address Function in Excel

The Address function in Excel lets you derive the reference excel address of cell or a range passed in the parameter of the Address function Excel. The value derived from the Address function in excel can be passed in the INDIRECT function to get the present value of the cell/ range. Below we will see the address excel formula and how the address function can be used in the Excel.

Address Excel Formula

Address Function Formula

Recommended Courses

  • Training on Financial Analyst
  • Program on Excel
  • Professional VBA Macros Course

Explanation of Address Function in Excel

Row_Num: Is the row number to use to 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 cell. If this parameter is omitted, then no sheet name is used in the excel address of cell.

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

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

 

How to use the Address Function in Excel?

Let’s take a few address excel examples, before using the address function in Excel workbook:

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 in Excel. Let’s go through the given use case

Address Function Example

  1. The first observation from the 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 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.

Address Function Example 1

  1. Now consider the case row 7 from 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 gives the result as Example1!I10.
  • As the Absolute number parameter is set to 4 it results in Relative reference. (I10)

Address Function Example 2

Till now 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 cell. How we can get the actual value through the reference. The Indirect function would help us with above questions.

Indirect Function Formula

Address function (Indirect function formula)

Reference: Is the reference of an excel address of 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 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).

Value present in the sample data, D3 cell is Mathematics which is same as the result of the Indirect function in 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 excel can read the reference style.

Address Function Example 4

Things to note about the Address Function Excel

  • The ADDRESS function in excel 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 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 Address function in Excel along with practical examples and downloadable excel templates.

  • INDIRECT Function in Excel
  • PMT Excel Function
  • PV Function Excel
  • XIRR Excel
  • NPV 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 >>

Filed Under: Excel, Lookup & Reference Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

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

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. 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

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Download Address Function Excel template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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