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.
Version
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
The first ROW function generates an array of 7 numbers like this:
{5;6;7;8;9;10;11}
The second ROW function generates an array with just one item like this:
{5}
which is then subtracted from the first array to...
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...
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...
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 "...
Although you can easily combine ranges in Excel with Power Query, it has traditionally been difficult to do with standard Excel formulas. However, the new dynamic array formulas in Excel 365 make it possible without too...
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...
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...
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...
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...
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...
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...
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...
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...
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:
=...
Related videos
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 create a dynamic named range with a Table. This is the simplest way to create a dynamic named range in Excel.
In this video, we’ll look at how to perform a random sort with the SORTBY function, with help from the RANDARRAY function.
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.
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.
This video provides a brief introduction to structured references. Structured references are an easy way to refer to tables in Excel formulas.
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
