Excel Rows Function

For example, =ROWS(D1:D7) returns 7, since the range D1:D7 contains 7 rows.

The array is nothing but cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more. A cell reference could be a single cell or range of cells.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Rows Function in Excel (wallstreetmojo.com)

How to Use Rows Function in Excel? (with Examples)

Example #1 – Using Row Cell Reference

We got the result as 3 because we looked closely at the cell reference. It says A1: A3, i.e., three rows are selected in the range of cells.

  • In cell B3, we will open the ROWS function. Then, we will give the cell reference as A1 in an array argument. We will close the bracket and press the “Enter” key to see what we get. Since we selected only one cell, it returned the result as 1. We will change the cell reference from A1 to A1: A3. Now, close the formula and see what the result is. This time we got the result as 3.

Since we selected only one cell, it returned the result as 1.

This time we got the result as 3.

Example #2 – Using Column Cells Reference

The ROWS function counts how many rows are selected in the reference. Now, we will apply the formula in cell B3 as below.

We have given the cell reference as A1: C1. So, let us see what the result is.

Even though we have selected 3 cells, we still got the result as 1 only!

It is because we have selected 3 cells in the same row, i.e., different column cells. Since we chose the range of cells in the same row, we only got the result of 1.

So, the ROWS formula cannot count COLUMNS in excelCount COLUMNS In ExcelThe COLUMNS function returns the total number of columns in the given array or collection of references.read more here.

Example #3 – Count of Rows

The ROWS function counts only how many rows are in the reference. Now, look at this example.

We have given the cell reference as A4, i.e., the 4th row of the worksheet. Press the “Enter” key, and see what the result is.

The result is 1, even though we have selected the 4th row of the worksheet.

As we told in the beginning, the ROWS function does not return row numbers. Rather, it returns only the count of selected rows. Since we have chosen only one row, the result is 1, not 4.

Example #4 – Insert Serial Numbers

We can use the ROWS function to insert serial numbers from 1. For example, we usually insert serial numbers from cell A2, so we will show you how to insert serial numbers with the ROWS formula in Excel.

Open the ROWS function in cell A2.

Select the cell reference as A2: A2.

For the first cell, the reference refers as absolute. $A$2: A2.

Now, drag the formula down to get the serial numbers.

Since we have made the first part of the cell reference an absolute, it remains the same when we drag it down, but another cell part keeps changing from A2 to A3, A3 to A4, and so on.

Difference between ROW & ROWS

After knowing the ROWS function, it is important to understand how it differs from the ROW function in Excel. Both are so confusing that we will sort out this now.

The ROW function returns the selected cell’s row number in the worksheet. For example, if we choose the cell reference as A3 by using the ROW function.

Since A3 is the third row in the worksheet, we got the result as 3.

But on the other hand, if we insert the same cell reference using the ROWS function.

We will get the result as 1.

Because the Excel ROWS function returns the count of how many rows are selected in the range.

So, the ROW function returns the row number of the selected cell. The ROWS function returns the count of selected rows in Excel.

This article is a guide to Rows Function in Excel. Here, we discuss using the rows formula to return the count of several rows selected in a range in Excel with examples and a downloadable Excel template. You can learn more about Excel functions from the following articles: –

  • VBA Last RowVBA Last RowThe End(XLDown) method is the most commonly used method in VBA to find the last row, but there are other methods, such as finding the last value in VBA using the find function (XLDown).read moreRow HeaderRow HeaderExcel Row Header is the grey column on the left side of column 1 in the worksheet that contains the numbers (1, 2, 3, etc.). To hide or reveal row and column headers, press ALT + W + V + H.read moreConvert Rows to Columns in ExcelConvert Rows To Columns In ExcelRows can be transposed to columns by using paste special method and the data can be linked to the original data by simply selecting ‘Paste Link’ form the paste special dialog box. It could also be done by using INDIRECT formula and ADDRESS functions.read moreConvert Columns to Rows in ExcelConvert Columns To Rows In ExcelThere are two ways to convert columns to rows: 1) using the Excel Ribbon Method. 2) The Mouse Method.read more