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
The ROWS function returns the count of rows in a given reference as a number. For example, =ROWS(A1:A5) returns 5, since the range A1:A5 contains 5 rows. ROWS takes just one argument, called array, which can be a range or array.
Examples
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:F10 so the formula below returns 10:
=ROWS(A1:F10) // returns 10
The range A1:Z100 contains 100 rows, so the formula below returns 100:
=ROWS(A1:Z100) // returns 100
You can also use the ROWS function to get a row count for an array constant:
=ROWS({1;2;3;4;5}) // returns 5
Although there is no built-in function to count the number of cells in a range, you can use the ROWS function together with the COLUMNS function like this:
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.
- To count columns, see the COLUMNS function.
- To get row numbers, see the ROW function.
- To lookup a row number, see the MATCH function.
ROWS formula examples
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 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...
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...
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 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...
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...
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....
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 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...
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:
=...
Related videos
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.
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 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.
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.
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.
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.
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
