Row function in excel is a worksheet function in excel which is used to show the current index number of the row of the selected or target cell, this is an inbuilt function and takes only one argument as the reference, the method to use this function is as follows,=ROW( Value ), it will only tell the row number of the cell not its value.
ROW in Excel (Table of Contents)
Row Function in Excel
ROW in Excel returns the first-row number within in a supplied reference or if no reference is supplied the ROW function in excel returns the number of the current row in currently active excel spreadsheet. The ROW function is a built-in Excel function which is categorized as LookUp/Reference function. Row function in excel always returns the positive numeric value.
Row Formula Excel
Below is the ROW Formula for Excel.
Reference is the argument accepted by the ROW formula excel which is a cell or range of cells for which we want the row number.
In the first case, we have omitted the reference value so the output will be the row number of the cell in which the row formula excel was typed. In the second case, we have provided the cell reference B1, so the row number of cell B1 is row 1 and if the reference is a range of cells entered as vertical array (in the third case) that is E2:E10, Row function in excel will return the row number of the top-most rows in the specified range, hence the output in third case will be 2.
ROW function in excel accepts one input (reference) which is optional. Whenever there is a square bracket  in an argument, it denotes that the argument is optional. So, if we don’t provide any input the Row in excel will return the row number of the active cell.
How to Use ROW Function in Excel?
ROW function in Excel is very simple and easy to use. Let understand the working of ROW in excel by some examples.
ROW in Excel Example #1
If we write the ROW formula excel, let say in the 3rd row in any cell, it will return the number 3 which is the row number.
As you can see above in the figure, we have written the ROW function in cell B3, C3, D3 and E3 and in all cell the Row in excel returns the row number 3 and if we end up writing ROW formula excel anywhere else in another cell, suppose in cell AA10, the Row function in excel will return the value 10.
When we give an input (a reference) to the Row in excel, for example, if we give input D323, it will return the row number 323 as an output because we are referring to D323.
We can use the Excel ROW Function in multiple ways.
Let’s check out the applications of ROW function in Excel
ROW in Excel Example #2
Suppose, if we want to generate the serial number starting from 1 to any number from any cell and their adjacent cells downwards. Suppose, starting from cell D4, I want to generate serial number 1,2,3,4…so on.
We will use the Row in excel, in D4 we will write the row function that =ROW(D4)
Now, we will drag the row formula excel downwards, suppose we drag it till D15, we get all the row number starting from Row number 4 to row number 15.
If we want this series starting from 1, we have to subtract the row number just above the cell from where we are starting the series. So, we will subtract row number 3 from each row number to get the desired output.
ROW in Excel Example #3
Another example of ROW function in excel, Suppose we have a list of names in a column, and we want to highlight those names that are in even Rows that in we want to highlight those names that are in Rows 2,4,6,8,…so on.
We have list of names in Column A, Name 1, Name 2, Name 3….Name 21. You can see below in the image we have used the Row in excel and previous Excel row function application example to generate the list of names in cell starting from A2 to A21. Now, using the ROW function in excel we have to highlight those names in the list that are in even rows. So, we have highlight names in Cells A2, A4, A6, A8…A20
In this case, we will be using the conditional formatting to achieve the task. Apart from the Excel ROW function, we will be using another function named EVEN(number). ROW in excel returns a round positive number up and negative number down to the nearest even integer.
So, to format even rows only, we will use the conditional formula =EVEN(ROW())=ROW()
We will select the rows that we want to format.
Click the Conditional Formatting drop-down in the Styles group and choose New Rule.
From the Select a Rule Type list, choose Use a formula to determine which Cells to format.
In the Format Values Where This Formula Is True field, enter =EVEN(ROW())=ROW().
Specify any of the available formats. For instance, to shade all even rows red, click the Fill tab, click Red, and click OK twice.
The result will be:
We can specify, the rows also that are even and odd, using EVEN, ODD, ROW and IF function
This works like this, if even of a row is equals to row number, then print even, else if odd of row equal to row number then print odd. So, the row formula excel goes like this
ROW in Excel Example #4
There is one more method for shading the alternate rows, we can use the MOD function along with the Row in excel
The ROW formula excel above returns the ROW number and the MOD function returns the remainder of its first argument divided by its second argument. For cells in even-numbered rows, the MOD function returns 0, and the cells in that row are formatted.
ROW in Excel Example #5
Shading groups of Rows using the row function in excel
Another example, we want to shade alternate groups of rows. Let’s say if we want to shade four rows, followed by four rows of un-shaded rows, followed by four more shaded rows, and so on. We will use the ROW, MOD and INT function to round off the numeric value obtained
For different sized groups, we can customize the ROW formula excel accordingly, if we change to 2 rows grouping, the formula would be as follows
Similarly, for 3 rows grouping,
Things to Remember About ROW Function in Excel
- If we are taking the input reference as a range of cells, Excel ROW function returns the row number of the topmost rows in the specified range. For example, if =ROW(D4:G9), Excel ROW Function would return 4 as the topmost row is D4 for which the row number is 4.
- Excel ROW Function accepts only one input so we cannot refer to multiple references or addresses.
- If the reference is entered as an array, Row in Excel returns the row number of all the rows in the array.
Row Function in Excel Video
This has been a guide to ROW Function in Excel. Here we discuss the ROW Formula excel and how to use ROW function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Excel VBA MOD
- How to apply Conditional Format in Pivot Table?
- How to Print in Excel?
- Excel Division for Formula – Example
- Convert Columns to Row in Excel
- Fixing Excel VLOOKUP Error
- VLOOKUP with Multiple Criteria
- Highlight Every Row in Excel
- ISERROR in Excel?
- Excel MODE
- How to use VLOOKUP Excel Function?
- HLOOKUP in Excel