ROWS Function In Excel

What Is Excel ROWS Function?

The ROWS function in Excel is an inbuilt Lookup & Reference function 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, the ROWS() 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.

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:

1. Using the Excel Ribbon.
2. 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

1. Choose a target cell for displaying the output.
2. 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.]
3. Enter the argument value as the reference to a cell or array and close the brackets.
4. 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.

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)

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 –

• VBA Delete Row
• VBA Last Row
• COLUMNS Function in Excel