Summary

The ROWS function returns the number of rows in a range or array. If a range is 15 rows tall, ROWS returns 15. It does not care whether the cells are full, empty, or contain formulas. It only counts the size of the range.

That makes ROWS useful when a formula needs to know how tall a range is. It is often used for table size checks, expanding counters, spill-range measurements, and formulas that need to stay inside the limits of a source range.

Syntax

=ROWS(array)

The function needs one argument: the range or array you want to measure. For example, =ROWS(A1:A15) returns 15. If the range is horizontal, like A1:Z1, the result is 1 because there is only one row.

ROWS can also work with array constants and spilled ranges, which makes it useful in newer Excel formulas as well as older worksheet patterns.

Arguments

• array - The range, array formula, spilled range, or array constant you want to measure.

The argument is required. Unlike ROW(), ROWS does not default to the current cell. It always needs a range or array to count. That is because the function is answering a size question, not a position question.

ROWS vs Other Functions

ROWS is about size. Related functions answer different questions.

Using ROWS

The most direct use of ROWS is measuring the height of a range. A formula can use that count to display table size, check whether a result has the expected number of rows, or decide how far another formula should go.

ROWS is also a common way to build a running counter. A formula like =ROWS($A$1:A1) returns 1 in the first row, then 2, then 3 as the formula is copied down. Because the second reference expands, the count grows with it. This is a simple and reliable numbering pattern.

Another useful pattern is boundary checking. If a formula depends on a row number staying inside a source range, ROWS can tell you the maximum allowed size first. That makes it useful in INDEX-based formulas and other setups where the range size matters just as much as the data inside it.

• Use ROWS when you need the height of a range.
• Use an expanding range with ROWS to build a running counter.
• Use ROWS in checks when another formula should stay inside the size of a table or array.

Example 1 - Count the Rows in a Fixed Range

This formula returns the height of the range, not the number of filled cells. If the range runs from row 1 to row 15, ROWS returns 15. This is useful when a worksheet needs to know the physical size of a table.

=ROWS(A1:A15) // Returns 15

Example 2 - Count a Spill Range

When a formula spills into multiple rows, ROWS can measure the height of that live result with the # operator. That makes it useful in newer Excel models where the result size can change over time.

=ROWS(B1#) // Returns the current spill height

Example 3 - Count Rows in an Array Constant

ROWS also works with array constants. In {1,2;3,4;5,6}, there are three horizontal lines of data, so the function returns 3. This is useful when a formula contains a fixed array inside it.

=ROWS({1,2;3,4;5,6}) // Returns 3

Example 4 - Build a Running Counter

The range $A$1:A3 starts at a fixed top cell and expands downward. That means ROWS returns 3 here. If the formula is filled down one more row, the range becomes taller and the result increases automatically.

=ROWS($A$1:A3) // Returns 3

Conclusion Recap

• Main job: ROWS counts how many rows are in a range or array.
• Important difference: ROWS returns size, while ROW returns position.
• Useful patterns: Table size checks, spill measurement, running counters, and boundary checks.
• Argument rule: ROWS always needs a range or array to measure.