ROWS Function

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.

ROWS is mainly a size check. It tells the workbook how tall a range is, which is useful in dynamic formulas, spill boundaries, and counters that depend on how many rows are included in a selected block rather than on the row number itself.

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.

This is a good starting example because it shows the main job of ROWS very clearly. The function measures how tall the range is, regardless of what data is inside it.

=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.

This is practical in dynamic-array workbooks because the spill size may change from one calculation to the next. ROWS can track that current height automatically.

=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.

That helps show that ROWS is not only for worksheet ranges. It can also measure the size of built-in arrays inside formulas.

=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.

This is one of the most useful ROWS patterns in everyday spreadsheets. It creates a counter that grows as the formula moves down, which is great for numbering and indexing.

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

Conclusion Recap

ROWS is simple, but it helps a lot when a sheet needs the height of a range instead of one row number. This lesson used it to measure fixed tables, spill ranges, array blocks, and running counters that grow as the formula moves down.

It is best to think of ROWS as a size tool. It does not care what values are inside the range, it only answers one question: how many rows does this range cover?

• 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.