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.

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

The **ROWS **function can be combined with other Excel functions to serve specific purposes.

**Example 1 – Using Row Cell Reference**

To find out the total number of orders by counting the **Rows **in the sample dataset,

- Enter the formula below in cell
**G8**.

`=ROWS(B5:B12)`

- Press
**ENTER**and it will output the total number of rows in the defined array.

**Example 2 – Using Column Cells Reference**

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

In the sample image, the **Row** of the cell is **5** and the **Column **is **C**. Use the **ROWS **function and pass this cell index, and see what it will return.

Apply the formula below in cell **C5**.

`=ROWS(C5)`

Although we have passed a cell index of the **5**** ^{th}** row, the

**ROWS function**returned

**1**as there is only one cell passed in its parameter.

**Example 4 – Insert Serial Numbers Using 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**

We are counting rows from **$B$5 **to any cell, so we have locked the starting index **$B$5. **

**Example 5 – Find the Top 3, 5, and 10 Values Using the LARGE and ROWS Function**

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

- Apply the same formula in cell
**H5**and copy it down to the next**5**cells, - Enter the formula in cell
**J5**and copy it down to the next**10**cells.

**Example 6 – Find the Lowest 3, 5, and 10 Values Using the SMALL and ROWS Function**

- 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. It also specifies the position from the largest value.

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

**Example 7 – Find the Last Row Number in the Dataset Using the ROWS Function**

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

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

**<< Go Back to Excel Functions ****|**** Learn Excel**