EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Address Excel Function

By Pradeep SPradeep S

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

ADDRESS Function in Excel

ADDRESS Function in Excel(Table of Contents)

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

ADDRESS in Excel

Address Function is a pre-built integrated function in excel that is categorized under Lookup and Reference functions. Address Function in Excel is used to find out the address of a cell in a worksheet. ADDRESS Function in Excel returns or displays the address of a cell based on a given row and column number.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

ADDRESS Formula in Excel

Below is the ADDRESS Formula in Excel :

ADDRESS Formula in Excel

OR

=ADDRESS (row, column, [ref_type], [ref_style], [sheet_name] )

The ADDRESS Function in Excel has the below-mentioned arguments:

  • Row or row_num: (Compulsory or required parameter) It is a row number of the cell address.
  • Column or Column_num: (Compulsory or required parameter) It is a column number of the cell address.
  • [abs_num] or ref_type: (optional argument) It is a numeric value that is specified to obtain below-mentioned reference type.

Note: If this parameter or argument is omitted or not entered, then it returns an Absolute reference of the cell, e.g. $A$1. This argument allows you to select the below mentioned numeric value (1 to 4) based on the output requirement.

ADDRESS Formula

Different type of references

Value Explanation
1 or Default Absolute referencing, e.g. $A$1
2 Relative column & absolute row, e.g. A$1
3 Absolute column & relative row e.g. $A1
4 Relative referencing, e.g. A1
  • [ref_style] or [a1] : (optional argument) It is a logical value indicating whether to use standard (A1 style) cell reference format or R1C1 style cell reference format. This argument allows you to select the below-mentioned ref_style.

ADDRESS Formula 2

If this parameter or argument is omitted or not entered, it will consider the default value 1 or TRUE (A1 style).

Note: Excel has two types of cell reference styles, i.e. A1 & R1C1.

In the A1 reference style, the column name is mentioned as an alphabet, and row name as a number, Whereas in the R1C1 reference style, both row and column are mentioned in numbers.

  • [sheet_text]) or [sheet_name]: (optional argument) It is the name of the worksheet to return. Or worksheet name is mentioned in quotes, e.g. “Sales”.

Note: If this argument is omitted or left blank, then no sheet name appears.

Popular Course in this category
Sale
Excel Training (21 Courses, 9+ Projects)21 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (10,662 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (16 Courses, 23+ Projects)

How to Use Address Function in Excel?

ADDRESS Function in Excel is very simple and easy to use. Let us understand the working of ADDRESS Function in Excel by some ADDRESS Formula in Excel example.

You can download this ADDRESS Function Excel Template here – ADDRESS Function Excel Template

Example #1

In the below-mentioned example, I have a row number in Cell “B11” & a column number in the cell “C11”. Here I need to find out the cell address with row & column number.

Let’s apply the ADDRESS function in cell “D11”. Select the cell “D11” where the ADDRESS function needs to be applied.

ADDRESS Example 1-1

Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “ADDRESS” in the search for a function box, the ADDRESS function will appear in select a function box. Double click on the ADDRESS function.

ADD Example 1-2

A dialog box appears where arguments for the ADDRESS function needs to be filled or entered i.e.

ADD Example 1-3

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

  • Row or row_num: (Compulsory or required parameter) It is a row number of the cell address. Here it is 2.
  • Column or Column_num: (Compulsory or required parameter) It is a Column number of the cell address. Here it is, 8.
  • [abs_num] or ref_type: (optional argument) This argument is omitted or not entered. By default, it will consider 1, i.e. Absolute reference.

ADD Formula

  • [ref_style] or [a1] : (optional argument) This argument is omitted or not entered. By default, it will consider 1 or TRUE (A1 style).

ADD Formula 1

  • [sheet_text]) or [sheet_name]: (optional argument) this argument is omitted or not entered, it is left blank. By default, no sheet name appears.

Click ok, after entering all the ADDRESS function arguments.

=ADDRESS(B11,C11) or =ADDRESS(2,8)

ADDRESS Function Example 1-4

When the above parameters or arguments i.e. ([abs_num], [a1], [sheet_text]) is not mentioned, then ADDRESS function results in or returns a pattern of absolute address with row and column name (i.e. $H$2).

ADD Example 1-5

Here, $H indicates the Absolut column (8), whereas $2 indicates the Absolute Row (2).

Example #2

In the below-mentioned example, I have a row number in Cell “B16” & column number in the cell “C8” & Reference type or abs_num as 3 in cell “D16”. Here I need to find out the cell address & it should reflect as Absolute Column & Relative Row in Cell Address.

Let’s apply the ADDRESS function in cell “E16”. Select the cell “E16”. where the ADDRESS function needs to be applied.

ADD Example 2-1

Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “ADDRESS” in the search for a function box, ADDRESS function will appear in select a function box. Double click on the ADDRESS function.

ADD Example 2-2

A dialog box appears where arguments for the ADDRESS function needs to be filled or entered i.e.

ADDRESS function Dialog Box

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

  • Row or row_num: (Compulsory or required parameter) It is a row number of the cell address. Here it is 2.
  • Column or Column_num: (Compulsory or required parameter) It is a column number of the cell address. Here it is, 8.
  • [abs_num] or ref_type: (optional argument) Here in the cell address, output to be returned as absolute column & relative row in cell address. Therefore, we have to enter 3.

ADD Formula

  • [ref_style] or [a1] : (optional argument) This argument is omitted or not entered. By default, it will consider 1 or TRUE (A1 style).

ADDRESS Formula 1

  • [sheet_text]) or [sheet_name]: (optional argument) this argument is omitted or not entered, it is left blank. By default, no sheet name appears.

Click ok after entering all the ADDRESS function arguments. i.e.

=ADDRESS(2,8,3)

ADDRESS Example 2-4

ADDRESS function results in or returns a pattern of absolute column & relative row in cell address (i.e. $H2).

ADDRESS Example 2-5

Here, $H indicates the Absolut column, whereas 2 indicates the relative Row (2).

Things to Remember

#VALUE! error occurs if the row or column number argument is less than 1. If the Column number is greater than the number of columns in excel (16,384 Columns). If the row number is greater than the number of rows in excel (10,48,576 rows).

In ADDRESS function argument, if row_num, column_num or [abs_num] is nonnumeric value then it returns #VALUE! error.

ADDRESS Function Error Example

Recommended Articles

This has been a guide to ADDRESS in Excel. Here we discuss the ADDRESS Formula in excel and How to use the ADDRESS Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. POWER Function in Excel
  2. Logical Functions in Excel
  3. Time Function in Excel
  4. EOMONTH Function Excel

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

2 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (58+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

EDUCBA

Download ADDRESS Function Excel Template

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Online EXCEL COURSE Learn More