Rows function in excel is different from the row function in excel, where row function gave us the row number for the selected cell instead rows function takes an array of rows as an argument and gives us the number of rows in that array, this is also a referencing function to identify the number of rows in a given array.
Rows Function in Excel (Table of Contents)
Excel Rows Function
Excel spreadsheet is a combination of rows and columns, alternative ways of calling these are “vertical for columns and horizontal for rows”. So, the intersection of rows and columns forms a CELL in a worksheet.
So, we all work with cells, isn’t it?
When we work with cells we work with a single cell and more than one cell as well. When we work with more than one cell it’s called “Range of cells”. Often times we work with a range of cells, so in those scenarios, we may need the count of the number of rows selected in the formula.
To deal with it we have a function called “ROWS” in excel. In this article, we will take you through this function in detail.
What Does Rows Function Do in Excel?
In simple words “ROWS” function returns the count of the number of rows selected in the range. Let us look at the Formula of ROWS function.
The array is nothing but cell reference. A cell reference could be a single cell or range of cells.
How to Use Rows Function in Excel?
ROWS function in Excel is very simple and easy to use. Let understand the working of ROWS formula in excel by some examples.
Example #1 – Using Rows Cell Reference
Let’s look at the simple example of ROWS function in excel. In cell B3 I will open the ROWS formula. In an array argument, I will give the cell reference as A1.
I will close the bracket and hit enter, see what we get.
Since only one cell has been selected it has returned the result as 1.
Now I 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.
The reason why we got the result as 3 because closely look at the cell reference, it says A1: A3 i.e. totally three rows are selected in the range of cells.
Example #2 – Using Column Cells Reference
ROWS function counts only how many rows are selected in the reference. Now I will apply the formula in cell B3 as below.
I have given the cell reference as A1: C1, let’s see what the result is.
Even though I have selected 3 cells still we got the result as 1 only!!!
This is because we have selected 3 cells in the same row i.e. different column cells. Since we have selected the range of cells in the same row we got the result as 1 only.
So, ROWS Formula cannot count COLUMNS here.
Example #3 – Count of Rows
ROWS function counts only how many rows are there in the reference, now look at this example.
I have given the cell reference as A4 i.e. 4th row of the worksheet, hit enter and see what the result is.
Oh!!! The result is 1 even though we have selected the 4th row of the worksheet.
As we told in the beginning ROWS function doesn’t return row number rather it returns only the count of selected rows. In this case, since we have selected only one row the result is 1, not 4.
Example #4 – Insert Serial Numbers
We can use 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 ROWS formula in excel.
Open ROWS function in cell A2.
Select the cell reference as A2: A2.
For the first cell, the reference makes the reference as absolute. $A$2: A2.
Now hit the enter key. By hitting enter key we should get the result as 1.
Now simply drag the formula down to get the serial numbers.
Since we have made the first part of the cell reference as absolute it remains same when we drag down but another cell part of the cell reference keeps changing from A2 to A3, A3 to A4 and so on.
Difference between ROW & ROWS
After knowing ROWS function it is important to know how it differs from ROW function in excel. Both are so confusing, so we will sort out this now.
The ROW function returns the selected cell’s row number in the worksheet. For example, if we select the cell reference as A3 by using the ROW function.
Since A3 is the 3rd row in the worksheet we got the result as 3.
But on the other hand, if we enter the same cell reference by using ROWS function.
we will get the result as 1.
Because Excel ROWS formula returns the count of how many rows are selected in the range.
So, ROW Function returns the row number of the selected cell and ROWS formula returns the count of selected rows in excel.
Recommended Articles
This has been a guide to Rows Function in Excel. Here we discuss how to use rows formula to returns the count of a number of rows selected in a range in excel with examples and downloadable excel template. You can learn more about excel functions from the following articles –
- Move Columns in Excel Using Drag Option
- How to Compare and Match Columns in Excel?
- How to Turn Off Scroll Lock in Excel?
- AVERAGEIFS Function in Excel | Examples
- Find Last Row using VBA
- Show Row Header in Excel
- Convert Rows to Columns in Excel
- Convert Columns to Rows in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
