How to Use ROWS Function in Excel?

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 with other Excel functions.


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 or 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 ROWS Function in Excel: 7 Easy Examples

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, 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


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


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 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 depending on the distance from $B$5 per cell.


Example 5: Find the Top 3, 5, and 10 Values Using the 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 the Lowest 3, 5, and 10 Values Using the 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 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 the Last Row Number in the Dataset Using the 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 


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

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.]

Download Practice Workbook


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo