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
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 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...
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 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....
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...
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 "...
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...
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...
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...
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...
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...
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...
Related videos
In this video, we'll look at different ways to remove a table from an Excel worksheet.
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 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 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.
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'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.
Related functions
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
