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.
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.
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.
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)
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.
Hope someone can help me? When I enter a negative into a cell it changes the cell address from the normal (eg.D2) to a function. how can this be changed back to the original format. Positive numbers have no affect at all to the cell address. I am using Excel 2010.
I have a Heart Monitor which samples the HR every 2 seconds. Therefore the length of my run determines the length of the data series. I am trying to create a “templated” graph. I know the starting point of the data series. Can the ADDRESS function be used to give the end point of the data series? I have tried this and it gives me an error. Any advice?
OK, on second consideration, the function I suggested turns out to be done by index() (no cross reference to that here!) This makes the cell() function even more un-obvious. Is its real purpose to display information you already have to have in order to get it displayed? A.maze.ing.
Aside from the perfectly horrid English, which makes what should be a very simple subject totally incomprehensible, the examples are so trivial that they don’t give any idea how to actually _use_ this function.
From the prolog, it seems that the only purpose of the address() function is to display a cell reference when you _already know what the cell reference is._ Surely this is not the sole purpose of Address().
Would it be so difficult to make an example that does something useful, like, once you have found the reference for the word “Microsoft”, use it to display the contents of that cell in the cell with the formula, or some such? Perhaps a little explanation of what is meant by the different options would be in order? Either they are so obvious to the writer that he feels that no one who _doesn’t_ know should bother looking here for the answer? This is doubly sad, since the help facility in Office2010 seems to use this site as a valid reference source.The address function _should_ be usable to calculate an address, for instance, to access text in a list by index. In the text list given, using an index of 6 (for the 6th cell in the list) should return “Excel 2010”, for instance. There are four options in the list referred to as [a1] in the parameters, and each of these should have a different affect on how the index is applied–this article really (and I do mean really) provides no clue how to do any such thing. It is, sadly, utterly useless and disappointing.
What’s the point of using relative addressing in the address() function?
Can this be used for anything useful?