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.

**Table of Contents**hide

## Download the Practice WorkBook

**ROWS Function in Excel (Quick View)**

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

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

`=ROWS(B4:B11)`

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

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

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

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.

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

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

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

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

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

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

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

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

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

- How to use COLUMNS Function in Excel (3 Examples)
- How to Use COLUMN Function in Excel (4 Easy Examples)
- How to Use INDIRECT Function in Excel (12 Suitable Instances)
- How to Use OFFSET Function in Excel (3 Examples)
- How to find text in an Excel range & return cell reference (3 ways)
- Excel Reference Cell in Another Sheet Dynamically
- Offset(â€¦) Function in Excel with Examples