## What Is Excel ROWS Function?

The

ROWSfunction in Excel is an inbuiltLookup & Referencefunction that determines the number of rows in a specified reference or array.Users can use the

ROWS()to update serial numbers in a set of cells. And when used with other inbuilt functions, theROWS()can help determine aspects such as the smallest and largest values in the cell range specified.

For example, the table below lists students and their Mathematics test scores.

If the requirement is to calculate the total number of rows of students in the given dataset and display the output in cell B18. Then, we can use the **ROWS()** in the target cell to achieve the desired outcome.

The **ROWS() **accepts the data range **B2:B16** as input. It then counts the total rows in the data range specified and returns the total number of rows of student data as **15**.

##### Table of contents

###### Key Takeaways

- The
**ROWS**function in Excel helps calculate the total count of rows in a given cell range. - Users can use the
**ROWS()**as a standalone function to evaluate and display serial numbers in a set of cells. Also, using the**ROWS**function with other inbuilt Excel functions such as**LARGE**,**SMALL**,**ROW**,and**MIN**yields practical outcomes. - We can access the
**ROWS**function from the**Formulas**tab. Otherwise, we can type the function into the target cell to obtain the required output. - The
**ROWS**function accepts one mandatory argument,**array**, as input and returns a number value as the output.

### How To Use ROWS Function In Excel?

The syntax of the **ROWS** function in Excel is,

where,

**array**: It is a mandatory argument, which can be an array or a reference to a cell or range. The argument can also be an array formula in excel.

We can apply the **ROWS **function in Excel cell using two methods:

**Using the Excel Ribbon.****Manually Entering the Function in the Worksheet.**

#### Method #1 – Using the Excel Ribbon

**Step 1:** First, click on a target cell.

**Step 2:** Next, choose the **Formulas** tab.

**Step 3:** Then, click the **Lookup & Reference** function group drop-down button.

**Step 4:** Now, choose the **ROWS** function.

**Step 5:** The **Function Arguments** window opens. Enter the argument value in the **Array** field and click **OK**.

#### Method #2 – Manually Entering the Function in the Worksheet

- Choose a target cell for displaying the output.
- Type the function name as
**=ROWS(**in the target cell. [Alternatively, enter**=R**or**=RO**and double-click the**ROWS**function from the Excel options.] - Enter the argument value as the reference to a cell or array and close the brackets.
- Press
**Enter**to execute the function and get the required output.

#### Basic Example

The table below lists a firm’s employees and their swipe in/out times.

If the requirement is to determine the total number of rows of swipe in/out times and display the output in cell E3. Then, we can use the **ROWS()** in the target cell to fetch the required detail.

**Choose the target cell E3 and enter the ROWS().***=ROWS(B2:C11)***Press Enter to view the total number of rows of swipe in/out times in cell E3, 10.**

[Alternatively, choose the target cell E3 and go to**Formulas**→**ROWS**to enter the function in the chosen cell.

The above step will open the**Function Arguments**window.

Next, enter the concerned cell range reference in the field provided in the**Function Arguments**window.

Finally, clicking**OK**will close the window. And we will see the**ROWS()**output in the target cell E3.]

The**ROWS()**accepts the cell range B2:C11, containing the employees’ swipe-in and out timings. And it counts and returns the total number of rows in the data range specified, which is**10**.

### Examples

Check out the following **ROWS **function in Excel examples to understand its practical usage.

#### Example #1 – Using Row Cell Reference

The table below shows the top companies on the US market listing and their stock prices.

And the requirement is to determine the total count of rows of companies in the listing and show the output in cell B8.

Then, the method to apply the **ROWS() **using the row cell reference in the target cell to obtain the required data is as follows:

**Step 1:**Click the target cell B8, enter the following**ROWS()**, and press**Enter**.

*=ROWS(A2:A6)*

Alternatively, we can ignore the column name and mention the row numbers as a range while supplying the argument value to the **ROWS()**. And then, press **Enter** to achieve the required result.

*=ROWS(2:6)*

#### Example #2 – Using Column Cells Reference

The first table in the image below lists US states and their capitals.

And the requirement is to update the total number of rows of US states and capitals in cells D6:D7 in the second table.

Then, the process to apply the **ROWS()** using column cells reference in the target cells to achieve the required outcome is as follows:

**Step 1:**Go to the target cell B4, select it, enter the following**ROWS()**, and press**Enter**.

*=ROWS(B1:I1)*

**Step 2:**Choose the target cell B5, enter the following**ROWS()**, and press**Enter**.

*=ROWS(B2:I2)*

The **ROWS()** in cell B4 accepts the data range B1:I1, containing the US state names. And, as the specified data range contains eight cells but in one row, the **ROWS()** returns the total count of rows in the cell range specified as **1**.

Likewise, the **ROWS()** in cell B5 accepts the data range B2:I2, containing the US state capitals. And, as the specified data range contains eight cells but in one row, the **ROWS()** returns the total count of rows in the cell range specified as **1**.

#### Example #3 – Count of Rows

The table below lists paper types in the available colors.

And the requirement is to determine the total number of rows of paper types in the given dataset and display the output in cell E1.

Then, the procedure to apply the **ROWS()** using the count of rows in the target cell to achieve the required data is as follows:

**Step 1:**Go to the target cell E1 and select it, enter the**ROWS()**, and press**Enter**.

*=ROWS(A2:B8)*

The **ROWS()** accepts the data range A2:B8 as the input and counts the total rows in the cell range specified. And since the given cell range contains seven rows, the **ROWS()** returns the value of** 7** as the output.

Alternatively, we can supply the array of rows containing the paper types as the argument value to the **ROWS()**, as shown below, to achieve the required outcome.

The **ROWS()** accepts the array of row numbers as the input. It counts the total array elements and returns the required output, **7**.

Otherwise, we can supply the data range values as an array to the **ROWS()**, as shown below, and achieve the required result.

*=ROWS({“A1″,”Red”;”A4″,”Pink”;”A3″,”Blue”;”A5″,”Green”;”A2″,”Orange”;”A3″, “White”;”A6″,”Black”})*

The **ROWS()** accepts the 7×2 array containing the given dataset values as the input. So, it returns the required number of rows as **7**.

#### Example #4 – Insert Serial Numbers

The table below lists the latest tech products, their brands and price details.

And the requirement is to update the products’ serial numbers in cell range A2:A11 using the **ROWS()**. Then, the steps will be as explained below:

**Step 1:**Click the target cell A2, enter the following**ROWS()**, and press**Enter**.

*=ROWS($A$2:A2)*

**Step 2:**Using the fill handle, enter the formula in cells in the range A3:A11.

The **ROWS() **accepts the cell range such that the first cell reference is absolute. It ensures the first cell in the cell range supplied as input to the **ROWS()** in all the target cells remains the same.

And the reference to the last cell in the cell range provided is relative, as it must change according to the respective target cells.

Next, the function in each target cell determines the total rows in the cell range specified to return the count as the output.

For instance, the cell range supplied as the argument value to the **ROWS()** in cell A11 is **$A$2:A11**. And hence, the **ROWS()** returns the total count of rows in the range mentioned in the function argument as **10**.

### Difference Between ROW & ROWS

The difference between the **ROW and ROWS function in Excel **is as follows:

- The ROW excel function accepts a cell reference and returns the referenced cell’s row number. But the
**ROWS()**accepts an array or reference to a cell or range and returns the total number of rows in the range specified. - While we can enter the
**ROW()**without an argument value in a cell, we must supply an argument value to the**ROWS()**to use it in a cell. - Comparing the
**ROW and ROWS function in Excel**shows that the**ROW()**is useful when we require the specified cell’s row number instead of the value the cell contains. But the**ROWS()**is useful when we require the count of rows in the cell range specified instead of the values they contain.

### Important Things To Note

- The argument value supplied to the
**ROWS**function in Excel should be proper. Otherwise, the function output will be the**#NAME?**error. - The
**ROWS()**will not work if you try applying it in a cell without the argument value. - The
**ROWS()**will not work if the supplied argument value is not an array or a reference to a cell or range. - Use the
**ROWS()**when the requirement is to determine the total count of rows in a data range. And use the**ROW()**if the requirement is to determine the row number of the specified cell.

### Frequently Asked Questions (FAQs)

**1. What is the use of ROWS function in Excel?**

The use of **ROWS **function in Excel is that it helps display serial numbers in a set of cells.

Furthermore, we can use the **ROWS()** with other inbuilt Excel functions, such as **MIN**, **LARGE**, and **SMALL**. It helps when the requirement is to determine the last row number, the highest and lowest values in the dataset specified.

**2. Why is ROWS function in Excel not working?**

The **ROWS** function in Excel is not working, perhaps because of the following reasons:**• **The supplied argument value is not a cell reference, array, or range.**• **The supplied argument value is invalid.**• **The applied **ROWS()** is empty.

**3. What is the difference between ROWS function in Excel and COLUMNS function in Excel?**

The difference between **ROWS** function in Excel and **COLUMNS** function in Excel is that the **ROWS()** calculates the total count of rows in the cell range provided as the argument.

On the other hand, the **COLUMNS()** determines the total number of columns in the cell range provided as the function argument.

For example, the first table in the image below shows a list of fruits and their order data.

If the requirement is to determine the total number of rows and columns in the above dataset and display the two values in cells in the range E1:E2 in the second table. Then, we can use the **ROWS() **and **COLUMNS()** in the target cells as explained below:**• Step 1: **Choose the target cell E1, enter the following **ROWS()**, and press **Enter**.*=ROWS(A2:B6)***• Step 2: **Click the target cell E2, enter the following **COLUMNS()**, and press **Enter**.*=COLUMNS(***A2:B6)**

The **ROWS()** in cell E1 accepts the given data range **A2:B6** and counts the total rows in the given cell range. And since the cell range contains five rows, the **ROWS()** returns the value of** 5** as the output.

The **COLUMNS()** in cell E2 accepts the given data range **A2:B6** and counts the total columns in the given cell range. And since the cell range contains two columns, the **COLUMNS()** returns the value of **2** as the output.

### Download Template

This article must be helpful to understand the **ROWS Function in Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to ROWS Function In Excel. Here we explain how to use ROWS formula, difference from ROW(), with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply