How to Use ROWS Function in Excel?

The ROWS function is a popular Excel built-in function that can be categorized under the LOOKUP and REFERENCE Functions. This function returns the number of rows existing within a specified range.

ROWS Function in Excel (Quick View)

Syntax & Arguments

Summary

The function returns the number of rows in a reference or array.

Syntax

=ROWS(array)

Arguments

Argument	Required or Optional	Value
array	Required	An array, an array formula, or a reference to a range of cells for which we require the number of rows.

Note:

The array can be an array constant or an array generated by a different formula.
An array can be a range or a reference to a single contiguous group of cells.

The ROWS function can be combined with other Excel functions to serve specific purposes.

Example 1 – Using Row Cell Reference

To find out the total number of orders by counting the Rows in the sample dataset,

Enter the formula below in cell G8.

=ROWS(B5:B12)

Press ENTER and it will output the total number of rows in the defined array.

Example 2 – Using Column Cells Reference

Enter the formula in cell G8 and press ENTER.

=ROWS(B5:D12)

Example 3 – Counting Rows Using ROWS Function

The ROWS function doesn’t return the current row number or index value. It returns the number of rows from the array that is assigned in its parameter.

Let’s see the example:

In the sample image, the Row of the cell is 5 and the Column is C. Use the ROWS function and pass this cell index, and see what it will return.

Apply the formula below in cell C5.

=ROWS(C5)

Although we have passed a cell index of the 5^th row, the ROWS function returned 1 as there is only one cell passed in its parameter.

Example 4 – Insert Serial Numbers Using ROWS Function

Apply the following formula in cell B5.

=ROWS($B$5:B5)

Use the Fill Handle tool to AutoFill the formula downward.

💡 Formula Explanation

We are counting rows from $B$5 to any cell, so we have locked the starting index $B$5.

Example 5 – Find the Top 3, 5, and 10 Values Using the LARGE and ROWS Function

Enter the formula in cell F5 and copy it down to F7.

=LARGE($D$5:$D$16, ROWS(B$5:B5))

💡 Formula Explanation

$D$5:$D$16 This is the price range where the LARGE function will search for the large value.
ROWS(B$5:B5) Using this we are defining the row number for each row. It also specifies the position from the largest value.

Apply the same formula in cell H5 and copy it down to the next 5 cells,
Enter the formula in cell J5 and copy it down to the next 10 cells.

Example 6 – Find the Lowest 3, 5, and 10 Values Using the SMALL and ROWS Function

Enter the formula in cell F5 and copy it down to F7.

=SMALL($D$5:$D$16, ROWS(B$5:B5))

💡 Formula Explanation

$D$5:$D$16 This is the price range where the SMALL function will search for the minimum value.
ROWS(B$5:B5) Using this we are defining the row number for each row. It also specifies the position from the largest value.

Enter the same formula in cell H5 and copy it down to the next 5 cells.
Enter the formula in cell J5 and copy it down to the next 10 cells.

Example 7 – Find the Last Row Number in the Dataset Using the ROWS Function

Enter the formula in cell G10.

=MIN(ROW(B5:B16))+ROWS(B5:B16)-1

💡 Formula Explanation

The ROW(B5:B16) portion returns the rows from the assigned B5:B16 range => {5;6;7;8;9;10;11;12;13;14;15;16}.

The MIN function will return the minimum value among them => 5.

ROWS(B5:B16) This portion will return the number of total rows, which is 12. After subtracting 1 it will return ROWS(B5:B16)-1 = 12-1 = 11

The function will return the last row number.

MIN(ROW(B5:B16))+ROWS(B5:B16)-1 = (5+11) = 16

What Are the Basic Differences Between ROW and ROWS Function?

ROW	ROWS
The ROW function returns the selected cell’s row number in the worksheet	The ROWS function returns the count of how many rows are selected in the range
Used for getting the row number	Used for counting rows