Row Function in Excel

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 Function in Excel

ROW in Excel returns the first-row number within a supplied reference, or if no reference is supplied, the ROW function in excel returns the number of the current row in the currently active excel spreadsheet. The ROW function is a built-in Excel function that is categorized as a Lookup/Reference Function. 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 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 row number of the cell in which the row formula excel was typed. 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, 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 the 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?

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 excel, let say in the 3rd row in any cell, it will return the number 3, which is the row number.

ROW Function 1
ROW Function 2

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 the ROW formula, 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 give 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’s check out the applications of the 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 numbers 1,2,3,4…so on.

We will use the Row in excel. In D4, we will write the row function that =ROW(D4)

ROW Function 5

Now, we will drag the row formula excel downwards. Suppose we drag it till D15. We get all the row numbers starting from Row number 4 to row number 15.

ROW Function 6

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 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, 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 a 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 cells 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.

ROW Function 9

In this case, we will be using 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. 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.

ROW Function 10

Click the Conditional Formatting drop-down in the Styles group and choose New Rule.

ROW Function 11

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

ROW Function 12

In the Format Values Where This Formula Is True field, enter =EVEN(ROW())=ROW().

Click Format.

ROW Function 13

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

This works like this, if even of a row is equals to the 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.

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

ROW Function 16

ROW in Excel Example #4

There is one more method for shading alternate rows. We can use the MOD function along with the Row in excel.

=MOD(ROW(),2)=0

The ROW formula excels 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 Function 17

Result:

ROW Function 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’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 in excelINT 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)

ROW Function 19

Result:

ROW Function 20

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.

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

ROW Function 21

Result:

ROW Function 22

Similarly, for 3 rows grouping,

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

ROW Function 23

Result:

ROW Function 24

Things to Remember About ROW Function in Excel

  • If we are taking 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), 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

Recommended Articles

This has been a guide to ROW Function in Excel. Here we discuss the ROW Formula excel and how to use the ROW function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

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