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
Dates in Excel are just serial numbers, formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past.
In the example shown, the date in the named range "...
Note: this formula uses two named ranges: "names" refers to C4:C11, and "groups" refers to B4:B11. These names are defined in the screen shot above as well.
The gist of this formula is this: we are using the SMALL...
The ROWS function is fully automatic. When you provide a range to ROWS, it will return a count of all rows in the range. In the example, the formula in F5 returns 6, because there are 6 rows in the range B5:C10:
=...
The core of this formula is the ADDRESS function, which is used to return a cell address based on a given row and column. Unfortunately, the formula gets somewhat complicated because we need to use ADDRESS twice: once...
There is no built-in function for counting the total numbers of cells in a range, so you need to use the ROWS and COLUMNS functions together. In the example, ROWS returns the total number of rows in B5:C10 (6), and...
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we're doing:
=AVERAGE(first:last)
where "first" is a reference to the first cell...
This formula replies on a helper column that already contains a sequential list of numbers to represent an established sort order. The numbers in the helper column are independent from the operation of this formula....
When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range with multiple rows, the ROW function will return an array that contains all row numbers for...
Note: this formula uses the named range "data" (B5:E104) for readability and convenience. If you don't want to use a named range, substitute $B$5:$E$104 instead.
To pull a random value out of a list or table, we'll...
This formula uses structured referencing, a syntax that allows table parts to be called out by name. When a table is called with the name only, Excel returns a reference to the data region of the table only. In this...
The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14).
To get the last row used, we use...
When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains...
The core of this formula is based on another formula that calculates the "current row" in a range:
=ROW()-ROW(rng.firstcell)+1
In brief, we get the current row in the workbook, then subtract the first row number of...
Related videos
Named ranges make formulas easier to read and understand, and reduce errors. In this video, we'll show you how to create a named range.
In this video, we'll look at different ways to remove a table from an Excel worksheet.
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.
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.
In this video, we show you how to add a message at the top of a filtered list that displays total and visible items in the list.
Related functions
