ADDRESS Formula

Previously in Excel Formulas: How to use AND Formula in Excel?

Use of this formula in Excel: It helps to create a cell reference based on the row and column numbers.

Address Formula breakdown:

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

Explanation:

=ADDRESS(row number, column number, [absolute or relative], [reference style], [name of the worksheet])

If you don’t know that you can dynamically create cell references in Excel then don’t worry in this tutorial I’m going to talk about the same. You can do this with the help of ADDRESS Formula! Additional thing about this formula is that it is one of the Lookup Formulas in Excel.

To create the cell reference the ADDRESS Formula takes this following information written below:

  • row number
  • column number
  • abs_num – if your cell reference is absolute or relative then this will reflect it. It has 4 following possibilities:
  • 1 – Absolute
  • 2 – Absolute row, Relative column
  • 3 – Relative row, Absolute column
  • 4 – Relative
  • a1 – this determines if it’s R1C1 or A1 style. 

For instance, I’m not going to use this, and it will already default to A1 Style

  • 0 – R1C1 Style
  • 1 – A1 Style
  • sheet_text – if your cell reference is populated this will add the sheet name to your cell.

STEP 1: firstly you have to enter the ADDRESS function in a blank cell of your table e.g.

=ADDRESS(

ADDRESS Formula

STEP 2: Now continue with the ADDRESS arguments:

row_num

In the place of row number you have to select the cell containing the row number:

=ADDRESS(A2,

ADDRESS Formula

column_num

In the place of column number select the cell containing the column number:

=ADDRESS(A2,B2,

ADDRESS Formula

abs_num

In the place of abs number select the cell containing the abs_num input. There are 4 modes, so we have included in all of the examples so that you can see it in action.

=ADDRESS(A2, B2, C2)

ADDRESS Formula

By dragging the lower right corner downwards apply the same formula to the rest of the cells.

ADDRESS Formula

Now you have your result cell references generated. You can notice the differences in the given pictures down below; in the 4 modes which represents by the $A$1 cell references.

ADDRESS Formula