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

Get FREE Advanced Excel Exercises with Solutions!

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. This article will provide a complete idea of how the ROWS function works in Excel independently and also with other Excel functions.


Download Practice Workbook


Excel ROWS Function

ROWS Function in Excel (Quick View)

Excel ROES Function in Quick View

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.

7 Examples to Understand and Use ROWS Function in Excel

This section will cover a complete explanation of the ROWS function with relevant examples. The application of the ROWS function combined with other Excel functions can be used to serve specific purposes.


Example 1: Using Row Cell Reference

We can easily find out how many rows are in our dataset by using row cell reference in the ROWS function. For this let’s consider we have a dataset of some orders with their Order ID, Product, and Price. Now our task is to find out the total number of orders by counting the Rows.

Steps

  • First of all, type the formula below in cell G8.

=ROWS(B5:B12)

  • Now, press ENTER and the cell will get you the total number of rows in the defined array.

Excel ROWS Function Using Row Cell Reference

Read More: Find Text in Excel Range and Return Cell Reference (3 Ways)


Example 2: Using Column Cells Reference

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

Just enter the formula in cell G8 and press ENTER.

=ROWS(B5:D12)

Excel ROWS Function Using Column Cell Reference

Read More: How to Reference Cell in Another Sheet Dynamically in Excel


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:

Counting Row Number with Excel ROWS Function

According to the picture, the Row 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.

Apply the formula below in cell C5.

=ROWS(C5)

Now we can observe that though we have passed a cell index of the 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.

Serial Numbers with Excel ROWS Function

Apply the following formula in cell B5.

=ROWS($B$5:B5)

 💡  Formula Explanation

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


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, and 10 orders based on their price from the dataset. We will use the ROWS function nested in the LARGE function.

Enter the formula in cell F5 and copy it down up 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. Also specifies the position from the largest value.

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

Excel ROWS Function for Finding Top Values


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

Now let’s find the lowest 3, 5, and 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.

Just 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. Also specifies the position from the largest value.

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

Excel ROWS Function to Find Lowest Values


Example 7: Find Last Row Number in 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 and will apply a combination of MIN, ROW, and ROWS functions.

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

Finally, the function will return the last row number.

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

Read More: How to Use ROW Function in Excel (With 8 Examples)


Basic Difference 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

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.


Related Articles

Md. Abdullah Al Murad

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo