ROW Function

Returns the first row within a given reference or the number of the current row

What is the ROW Function?

The ROW function is categorized under TEXT functions. The function will return the first row within a given reference or the number of the current row.

In financial analysis, the ROW function can be useful as it simplifies the creation and readability of a row. It also facilitates conditional formatting such as highlighting alternating groups of n rows.

 

Formula

=ROW(reference)

 

The ROW function uses Reference as an argument. It is the cell or range of cells for which you want the row number.

If we omit the reference, the function will assume it to be the reference of the cell in which the ROW function appears. If Reference is a range of cells, and if ROW is entered as a vertical array, the function will return the row numbers of reference as a vertical array.

We need to keep in mind that Reference cannot refer to multiple areas.

 

How to use the ROW Function in Excel?

As a worksheet function, the ROW function can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:

 

Example

Suppose we wish to highlight rows in groups of “n” (i.e. shade every 3 rows or every 5 rows, etc.), we can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN/ISODD functions.

Suppose we are given the following data:

 

ROW Function

 

The formula to use would be =ISEVEN(CEILING(ROW()-4,3/3).

 

ROW Function - Example 1

 

We will get the results below:

 

ROW Function - Example 1a

 

In the formula above, we first “normalize” the row numbers to begin with 1 using the ROW function and an offset.

Here, we used an offset of 4. The result goes into the CEILING function, which rounds incoming values up to a given multiple of n. Essentially, the CEILING function counts by a given multiple of n.

The count is then divided by n to count by groups of n, starting with 1. Finally, the ISEVEN function is used to force a TRUE result for all even row groups, which triggers the conditional formatting.

Odd row groups return FALSE, so no conditional formatting is applied.

We can replace ISEVEN with ISODD to shade rows starting with the first group of n rows, instead of the second.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!