In this article let us understand about the Excel Row Function. The Formula Syntax of Row Function in Excel.
What is Excel Row Function
The purpose of Excel Row function is to know or get the row number for a given cell or range. This function returns the row number(s) depending on the input. Note that the return value for this function is the actual row number but not the value in a given cell or a range.
Syntax of Excel Row Function
The Syntax of ROW function in excel is as follows.
=ROW([Reference])
Arguments of Excel Row Function
Row Function has only one argument which is Reference.
Reference : This is an optional argument. You can give the cell or range of cells for which you want the row number. See the below to understand the return value of Row Function based on reference or input.
- If Reference is left Blank – Return Value will be the same row number where the function is written.
- If Reference is a single Cell Name or Cell Reference – Function returns the row number associated with the cell name or reference.
- Reference is a range or an array – Returns the row numbers for the range.
Note : when range is given as reference, In Excel365 the result will spill vertically. Where as in earlier versions of excel the first item of the array will be the return value.
Suggested Reading
Examples for using Excel Row Function
Let us see the return values of ROW Function with different examples.
Reference is blank
In the below example, reference is blank and we can see that the return value is 3. Since, the function is in Cell B3 and no reference is available the return value will be the row number of the cell where the function is.
Reference is a single Cell Name or Reference
In the below example, we can see that the return value is 100. Note that the reference argument is D100. Since the row number associated here is 100 the return value is100.
Reference is a Range or an Array
See the below image where reference is a range. The row function returns the corresponding row numbers spilling across the column. The range here is from A2 to D10. Hence the functions returns row numbers from 2 to10.
How to return single value in Excel 365 when Range is used in Reference.
We have seen that when using a range Row function returns all the rows. If you want the function to return only a single value instead of all the row numbers use “@” symbol before the start of the function.
@ Symbol tells excel that we need a single value instead of an array.
Let us take the same example which we have seen above with range in reference. See the below screenshot. The same function returns a single value i.e. only 2 instead of all row numbers in the range when we use “@”
Limitation of ROW function
Note that: The input to ROW function should always be a single contiguous Range. Row function will not take multiple ranges at once as an input. Meaning any inputs like =ROW(A1,B2:C4) or =ROW(B2:C4,A1) will return an error message stating “Too Many Arguments entered for this function”.