How To Use Excel Rows Function

The excel ROWS function is used to get the rows number of the specified array, continuous cell range, or a formula that returns an array. This article will tell you how to use the excel ROWS function with examples.

1. Excel Rows Function Syntax.

  1. The excel rows function’s syntax is ROWS(array).
  2. The array parameter can be an array ( {“a”,”b”,”c”,”d”;5,6,7,8;”java”,”vba”,5,6} ), a continuous cell range ( A1: F9 ), or a formula that returns an array.
  3. The ROWS function will return an integer number that indicates the array, cell range rows count.

2. How To Use Excel Rows Function Example.

  1. Input the formula =ROWS({“a”,”b”,”c”,”d”;5,6,7,8;”java”,”vba”,5,6}) in an excel cell such as cell A1.
  2. Then it will display the array’s rows count number 3 in cell A1, the array {“a”,”b”,”c”,”d”;5,6,7,8;”java”,”vba”,5,6} contains 3 rows.
  3. Input the formula =ROWS(D1:G9) in excel cell A2, Then it will display the integer number 9 in cell A2 because there are 9 rows in cell range D1:G9.

3. How To Use Excel Rows Function To Insert Number Of Rows In Excel Automatically.

  1. Input the formula =ROWS($A$1:A1) in cell A1.
  2. Select cell A1, and move the mouse pointer to the bottom right corner of cell A1.
  3. When the mouse pointer is changed to a black cross icon, press the mouse left key and drag it down to the row that you need.
  4. Release the mouse left key, then it will add the row number in each of the cells.
    A
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
  5. Please note the formula =ROWS($A$1:A1), the first cell is $A$1, which means the absolute reference.
  6. So when you drag the first cell to other cells, the formula’s first cell is also fixed such as =ROWS($A$1:A2).

4. Difference Between Excel ROWS & ROW Function.

  1. There is also an excel ROW function.
  2. The excel ROW(cell_range) function will return the first cell’s row number for a cell or cell range.
  3. If you do not provide any cell or cell_range, it will return the current cell’s row number that contains the ROW function.
  4. Input the formula =ROW() in cell A9, it will display the number 9 in cell A9.
  5. Input the formula =ROW(A6) in cell A10, it will display the number 6 in cell A10.
  6. The formula =ROW(A6:A19) will return the number 6 ( the first cell A6‘s row number).

5. How To Use Excel ROW Function To Insert Number Of Rows Automatically.

  1. Input the formula =ROW(A1) in the first cell.
  2. Select the cell and drag the black cross icon on the bottom right corner of the cell to autofill other excel cells in the same row.
  3. Then you can see each row’s number in the column cells.

References

  1. Flash Fill In Excel & How To Autofill Formula In Excel.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Clicky