1. Home
  2. MS Office

Excel 2010: Address Function

Excel 2010 includes two types of referencing, i.e, Absolute and Relative, these referencing types are very important when you are dealing with formulas and their addresses. If you want to find out the different type of data cell address, then you can easily show it by providing certain parameters. Excel ADDRESS function provide list of options to show address in desired ways; with row/column headers or row/column numbers, with absolute, relative or mixed format, etc. This function is very useful especially when you are dealing with different and large-scale spreadsheets in Excel 2010 workbook.

Launch Excel 2010 spreadsheet in which you want to show the address of the cell locations. For instance we have included a table containing single field, as shown in the screenshot below.

table

Now we will be finding out the address of the different data cells. The syntax of ADDRESS function is;

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

The first argument row_number refers to the number of row in which data cell is existed, next argument column_num refers to column number in which it is present, third argument [abs_num] refers to list which provide different options; Absolute, Absolute row/Relative column, Relative row/Absolute column, and Relative. On choosing desired option from the list, you will see the address in specified format, the [a1] refers to two different ways, i.e, with row/column number(R1C1) or row/column headers(A1B1) in which you want to show address. The last argument shows sheet text in the address.

We are finding out Microsoft in table field, as you can see in the screenshot below that row and column num are 4 and 1 respectively. On entering [abs_num] argument, it will automatically show different options, from which we will choose 1-Absolute.

absolute row

For passing [a1] argument, we chose 1 for row/column header style. It will show the address of the cell as shown in the screenshot below.

a4

If you want to show a relative reference and row/column number in the address then you have got to change the argument values as;.

=ADDRESS(4,1,4,0)

relative

You can also check out previously reviewed Excel function; MAX,MIN, MAXA, MINA and EXACT, Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, & LEN.

Leave a comment