How to Use ROWS Function in Excel (With 7 Easy Examples)

Overview of ROWS function

The ROWS function is a popular Excel built-in function that can be categorized under  Lookup or Reference Function. This function returns the number of rows that are contained within that range. This article will share the complete idea of how the ROWS function works in Excel independently and then with other Excel functions.

Download the Practice WorkBook

ROWS Function in Excel (Quick View)

 Overview of ROWS function

Excel ROWS Function: Syntax & Arguments

Excel ROWS Function 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 of an array generated by a different formula.
  • An array can be a range or a reference to a single contiguous group of cells.

How to Use the ROWS Function in Excel (7 Examples)

Example 1: Using Row Cell Reference

Using row cell reference, we easily find out how many rows are in our dataset by using the ROWS function. For this let’s consider we have a dataset of some orders with their order id, product, price. Now our task is to find out the total number of orders by counting the rows.

Using Row Cell Reference

Step 1: Enter the formula in cell H4 and press Enter

=ROWS(B4:B11)

Enter formula using ROWS function

Example 2: Using Column Cells Reference

Now we will use the same dataset and calculate the total number of orders using column cells reference.

Step 1: Enter the formula in cell H4 and press Enter

=ROWS(B4:D11)

Enter formula using rows function

Example 3: Counting Rows Using ROWS Function

The ROW function doesn’t return the current row number or index value. It returns the number of rows that is passed into its parameter.

Let’s see the example:

Counting rows using Rows Function

According to the picture, the row number of the cell is 5 and the column is C. Now if we use the ROWS function and pass this cell index then let’s see what will return.

Enter the below and then press Enter

=ROWS(C5)

formula using Rows function

Now we can observe that though we have passed a cell index of 5th row the ROWS function is returning 1 as there is only one cell is passed in its parameter.

Example 4: Insert Serial Numbers Using ROWS Function

Let’s add the serial numbers for the dataset which was used in example 1. But instead of putting the serial number manually, we can use the ROWS function.

Insert Serial Numbers Using ROWS Function

Step 1: Enter the formula in cell B4 and copy it down to up B11

=ROWS($B$4:B4)

Formula Explanation

  • Here we are counting rows from $B$4 to any cell. That’s why I have locked the starting index that is $B$4.
  • The serial number will be increased gradually dispensing on the distance from $B$4 cell.

Enter formula using ROWS function

Example 5: Find top 3, 5, and 10 values Using LARGE and ROWS Function

Let’s have the dataset of some order list like the previous example. Now we will find out the top 3, 5, 10 orders based on their price from the dataset.

Find top 3 5 10 values using Large and Rows function

Step 1: Enter the formula in cell F4 and copy it down up to F6

=LARGE($D$4:$D$15, ROWS(B$4:B4))

Formula Explanation

  • $D$4:$D$15 this is the price range where the LARGE function will search for the large value.
  • ROWS(B$4:B4) using this we are defining the row number for each row. Also specifies the position from the largest value.

Enter formula using Large and Rows function

Step 2: Enter the same formula in cell H4 and copy it down up to the next 5 cells and copy the formula in cell J4 and copy it down up to the next 10 cells

Enter formula using large and rows

Example 6: Find Lowest 3, 5, and 10 values Using LARGE and ROWS Function

Now let’s find the lowest 3, 5, 10 values based on price from the above dataset. Here the process and formula are the same but here instead of using the LARGE function we will use the SMALL function.

Find Lowest 3, 5, and 10 values Using LARGE and ROWS Function

Step 1: Enter the formula in cell F4 and copy it down up to F6

=SMALL($D$4:$D$15, ROWS(B$4:B4))

Formula Explanation

  • $D$4:$D$15 this is the price range where the SMALL function will search for the minimum value.
  • ROWS(B$4:B4) using this we are defining the row number for each row. Also specifies the position from the largest value.

Step 2: Enter the same formula in cell H4 and copy it down up to the next 5 cells and copy the formula in cell J4 and copy it down up to the next 10 cells

 Formula using small and rows function

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

Now we will see the process of finding the last row of any dataset. For this, we will consider the same dataset above.

Find the Last and First Row Number in the Dataset Using ROWS Function

Step 1: Enter the formula in cell D18

=MIN(ROW(B4:B15))+ROWS(B4:B15)-1

Formula Explanation

  • ROW(B4:B15) this portion returns the rows from this B4:B15 range.
  • ROWS(B4:B15)-1 this portion will return the total rows and after subtracting 1 it will return 11 (12-1).
  • Finally, the MIN function will return the last row number of the given dataset which is 15.

Enter formula using Min and row and rows

Basic Difference Between ROW and ROWS Function

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

Things to Remember

Common Errors When they show
#NAME? This will happen if the ROWS function’s argument is not properly entered. Like this =ROWS(A) [ here row number is missing.]

Conclusion

This is all about the ROWS function and its different applications. Overall, in terms of working with time, we need this function for various purposes. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any other method of utilizing this function, then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo