Excel ROWS Function
Summary
The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.
Purpose
Get the number of rows in an array or reference.
Return value
Number of rows
Syntax
=ROWS (array)
Arguments
- array - A reference to a cell or range of cells.
Usage notes
Use the ROWS function to get the row count for a given reference or range. For example, there are 10 rows in the range A1:A10, so:
=ROWS(A1:A10) // returns 10
The range B1:D5 contains 5 rows:
=ROWS(B1:D5) // returns 5
You can also use the ROWS function to get a row count for an array constant:
=ROWS({1;2;3}) // returns 3
Notes
- Array can be a range or a reference to a single contiguous group of cells.
- Array can be an array constant or an array created by another formula.
ROWS formula examples
To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions.
In the example shown, the formula in G7 is:
=INDEX(data,RANDBETWEEN(1,ROWS(data...
To calculate the average for the last N values n an Excel table (i.e. last 3 rows, last 5 rows, etc.) you can use the AVERAGE function together with the INDEX and ROWS functions. In the example shown, the formula in F5...
To create a dynamic date list, you can use a formula that increments a start date to create and display additional dates. In the example shown, the formula in B5 is:
=start+ROWS($B$5:B5)-1-offset
where "start" is the...
If you need to count the total number of cells in a rectangular range, you can do so with the ROWS and COLUMNS functions. In the generic form of the formula (above) rng represents a range of cells.
In the example, the...
To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is:
{=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-...
To flag the last n rows in a range, you can use helper column with a formula based on the ROW and ROWS functions. In the example shown, the formula in cell E5, copied down, is:
=ROW()-ROW(INDEX(data,1,1))+1>ROWS(...
To get the last row number in a range, you can use a formula based on the ROW, ROWS, and MIN functions. In the example shown, the formula in cell F5 is:
=MIN(ROW(data))+ROWS(data)-1
where "data" is the named range B5...
To count rows in an Excel table, you can use the ROWS function. In the example shown, the formula in I4 is:
=ROWS(Table1)
Note: with just the table name, ROWS will count data rows only.
How this formula works
This...
To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions.
In the example shown, the formula in G6 is:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data...
To retrieve and display values sorted with a helper column, you can use an INDEX and MATCH formula, with a little help from the ROWS function. In the example shown, the formula in F5 is:
=INDEX(sales,MATCH(ROWS($D$5:$...
To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data),...
If you need to count the number of rows in a range, use the ROWS function. In the generic form of the formula (above) rng represents a range of cells.
In the example, the active cell contains this formula:
=ROWS(B4:...
You can get the first column (i.e. the starting column number) in a range with a formula based on the COLUMN function.
In the example shown, the formula in cell F5 is:
=MIN(COLUMN(data))
where data is a named range...
Related videos
In this video, we'll look at different ways to remove a table from an Excel worksheet.
In this video, we’ll look at how to perform a random sort with the SORTBY function, with help from the RANDARRAY function.
This video provides a brief introduction to structured references. Structured references are an easy way to refer to tables in Excel formulas.
Because tables support structured references, you can learn a lot about a table with simple formulas. In this video, we'll look at some formulas you can use to query a table.
One of the most useful features of Excel Tables is that they create a dynamic range. In this video, we'll take a closer look at how table ranges work.
In this video, we'll introduce the idea of a dynamic range and show you why you might want to use one. Spoiler: they adjust formulas automatically when data changes.
Related functions
