Row Function in Excel

Below is the ROW formula for Excel.

Reference is the argument accepted by the ROW formula in 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 cell's row number in which we typed the row formula in Excel. In the second case, we have provided the cell reference B1, so the row number of cell B1 is row 1. Suppose the reference is a range of cells inserted as a vertical array (in the third case) that is E2:E10. Then, the ROW function in Excel will return the row number of the topmost rows in the specified range. Hence, the output in the third case will be 2.

The ROW function in Excel accepts one input (reference), which is optional. In addition, whenever there is a square bracket in an argument, it denotes that the argument is optional. So, if we do not provide any input, the ROW function in Excel will return the row number of the active cell.

The ROW function in Excel is very simple and easy to use. Let us understand the working of ROW in Excel by some examples.

If we write the ROW formula in Excel, e.g., in the third row in any cell, it will return the number 3, which is the row number.

As shown in the figure above, we have written the ROW function in cells B3, C3, D3, and E3. In all cells, the ROW function in Excel returns row number 3. If we write the ROW formula in 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 offer 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 us check out the applications of the ROW function in Excel.

Suppose we want to generate the serial number from 1 to any number from any cell and their adjacent cells downwards. Suppose, starting from cell D4. Then, we want to create serial numbers 1,2,3,4…so on.

We will use the ROW function in Excel. For example, in D4, we will write the ROW function that =ROW(D4).

Now, we will drag the ROW formula in Excel downwards. For example, suppose we drag it till D15. Then, we get all the row numbers starting from row number 4 to 15.

If we want this series to start from 1, we must subtract the row number just above the cell from where we begin the series. So, we will subtract row number 3 from each row number to get the desired output.

Another example of the ROW function in Excel. Suppose we have a list of names in a column. We want to highlight those names in even rows . We want to highlight those names in rows 2,4,6,8,…so on.

We have a list of names in column A, "Name 1," "Name 2," and "Name 3"…."Name 21." You can see below in the image that we used the Excel row and the previous Excel ROW function application example to generate the list of cell names 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 highlighted names in cells A2, A4, A6, and A8…A20.

We will use conditional formatting in excel to achieve the task in this case. We will use another function, EVEN(number), apart from the Excel ROW function. The ROW function in Excel returns a round up positive number and a round down negative number to the nearest even integer.

So, only to format even rows, we will use the conditional formula =EVEN(ROW())=ROW()

We will select the rows that we want to format.

Click the "Conditional Formatting" dropdown 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, type:

=EVEN(ROW())=ROW().

Click "Format."

Specify any of the available formats. For instance, to shade all even rows red, click on the "File" 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 functions.

It works like this: if the even of a row is equal to the row number, then print even. If the odd of a row equals the row number, print odd. So, the ROW formula in Excel goes like this.

=IF(EVEN(ROW(A2))=ROW(),”Even”,IF(ODD(ROW(A2)=ROW()),”Odd”))

There is another method for alternate shading rows. We can use the MOD function along with the ROW function in Excel.

=MOD(ROW(),2)=0

The ROW formula above returns the ROW number. The MOD function returns the remainder of its first argument divided by its second. For cells in even-numbered rows, the MOD function returns 0, and the cells in that row are formatted.

Result:

Shading groups of Rows using the row function in Excel

Another example, we want to shade alternate groups of rows. Let us say if we want to shade four rows, four rows of un-shaded rows, four more shaded rows, and so on. We will use Excel's ROW, MOD, and INT function to round off the numeric value obtained.

=MOD(INT((ROW()-1)/4)+1,2)

Result:

For different-sized groups, we can customize the ROW formula in Excel accordingly. If we change to 2 rows grouping, the formula would be as follows:

=MOD(INT((ROW()-1)/2)+1,2)

Result:

Similarly, for 3 rows grouping,

=MOD(INT((ROW()-1)/3)+1,2)

Result:

• If we take the input reference as a range of cells, the Excel ROW function returns the row number of the topmost rows in the specified range. For example, if =ROW(D4:G9), the Excel ROW function would return 4 as the top 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, the ROW function in Excel returns the row number of all the rows in the array.