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.

**Table of Contents**hide

**Excel ROWS Function**

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

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

**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)`

**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:

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 **5**** ^{th}** 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.

Apply the following formula in cell **B5**.

`=ROWS($B$5:B5)`

**Use the Fill Handle**tool to**AutoFill**the formula downward.

**Â ðŸ’¡Â 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.

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

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