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.

**Table of Contents**hide

## Download Practice Workbook

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

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

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

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 is passed in its parameter.

**Similar Readings**

**How to find text in an Excel range & return cell reference (3 ways)****Examples of OFFSET Function in Excel (Formula+VBA )****How to Use COLUMN Function in Excel (4 Easy Examples)**

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

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

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