Row Function in Excel

Updated on January 2, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

The ROW function in Excel is a worksheet function in Excel that is used to show the current index number of the row of the selected or target cell. It is a built-in function and takes only one argument as the reference. The method to use this function is as follows: =ROW( Value ). It will only show the cell’s row number, not its value.

For example, =ROW(A5) returns 5 because A5 is the fifth row in the Excel spreadsheet.

Row Function in Excel

The ROW function in Excel returns the first-row number within a supplied reference or if no reference is supplied. In addition, the ROW function in Excel returns the current row number in the currently active Excel spreadsheet. The ROW function is a built-in Excel function categorized as Lookup and Reference. The ROW function in Excel always returns the positive numeric value.

Row Formula Excel

Below is the ROW formula for Excel.

ROW Formula in 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.

Argument ValueCell FormulaExplanation
Nothing3Returns the ROW number of the cell the formula is types in
A Cell Reference1Returnsthe ROW number 1
A Range 2Returns the Row number 2

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 referenceWe Have Provided The Cell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use ROW Function in Excel?

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

You can download this ROW Function Excel Template here – ROW Function Excel Template

ROW in Excel Example #1

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.

ROW Function 1
ROW Function 2

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.

ROW Function 3

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.

ROW Function 4

We can use the Excel ROW function in multiple ways.

Let us check out the applications of the ROW function in Excel.

ROW in Excel Example #2

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).

ROW Function 5

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.

ROW Function 6

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.

ROW Function 7
ROW Function 8

ROW in Excel Example #3

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.

ROW Function 9

We will use conditional formatting in excelConditional Formatting In ExcelConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more 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.

ROW Function 10

Click the “Conditional Formatting” dropdown in the “Styles” group and choose “New Rule.”

ROW Function 11

From the “Select a Rule Type” list, choose “Use a formula to determine which cells to format.”

ROW Function 12

In the “Format values where this formula is true” field, type:

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

Click “Format.”

ROW Function 13

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.

ROW Function 14

The result will be:

ROW Function 15

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”))

ROW Function 16

ROW in Excel Example #4

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.

ROW Function 17

Result:

Example 18

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 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 functionINT Function In ExcelINT or integer function in excel returns the nearest integer of a given number and is used when we have many data sets and each data in a different format.read more to round off the numeric value obtained.

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

Example 19

Result:

Example 20

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)

Example 21

Result:

Example 22

Similarly, for 3 rows grouping,

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

Example 23

Result:

Example 24

Things to Remember About ROW Function in Excel

  • 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.

Row Function in Excel Video

Recommended Articles

This article is a guide to the ROW Function in Excel. Here, we discuss the ROW formula in Excel and how to use the ROW function, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *