Functions: ROW & COLUMN

Hi, let’s start new month with a post on Excel Lookup & Reference functions. In this post, we will be discussing about Excel built-in functions ROW, COLUMN. I do not use these functions as much as I use other functions in Excel lookup & reference category. Nevertheless, knowing these functions doesn't harm. You never know, sometimes these are just the functions you wish you knew. Okay, let's see about ROW function.

ROW

ROW function returns the row number of a cell reference. Let’s see the function syntax.

ROW( [reference] )

reference is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, then the ROW function assumes that the reference is the cell address in which the ROW function has been entered in.

ROW function entered in cell A1 without any reference given returns value 1 as cell A1 is in row 1.

Let’s see few examples:

Formula in cell A2 omits reference. Since cell A2 is in second row, formula returns 2 as result.  Formula in cell A3 is referring to cell A5 which is 5^th row hence the result is 5.

ROW function is most useful as an argument in other functions rather than by itself. You can see a more practical example of this function in our previous post where we were trying to get cascading lists with only unused items.

COLUMN

On very similar lines with ROW function, COLUMN function returns the column number of a reference. Function syntax is as below.

COLUMN( [reference] )

reference is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, then the COLUMN function assumes that the reference is the cell address in which the function has been entered in.

COLUMN function entered in cell B1 without any reference given returns value 2 as cell B1 is in second column.

Few examples:

Formula in cell A2 omits reference. Since cell A2 is in first, formula returns 1 as result.  Formula in cell A3 is referring to cell D5 which is 4^th column from start hence the result is 4.

COLUMN function is most useful as an argument in other functions rather than by itself.

Points to remember:

1. ROW & COLUMN functions returns the row & column number respectively starting from 1^st row and 1^st column of any sheet. That means cell B5 will always have row number 5 and column number 2. These functions won’t work if you are looking for row and column numbers relative to a range. I mean to say, you selected a range say B3:D10 and you want the row number of cell B5 relative to the range selected (i.e. taking B3 as first row, so B5 is 3^rd row in the range).
2. Instead of actual references, you can also provide named ranges as arguments. For example: If you name range A1:A5 as MyList, you can enter ROW(MyList) instead of ROW(A1:A5)
3. If you provide a range that refers to more than one cell, Excel returns ROW / COLUMN number of the first cell in that range.

In the next post, we will see about 2 related functions in the same category. Happy Excelling!!