ROWS Function in Excel
ROWS counts how many rows are in a range or array. Not the row number of a cell — how many rows the range spans. Essential for dynamic-range sizing, array formulas, and any calculation that needs to know "how tall is this data?"
What it does: Returns the number of rows in array. =ROWS(A1:A10) returns 10. =ROWS(B5:D20) returns 16. Works on any range, named range, Table column, or array constant.
What ROWS does
ROWS answers the question "how many rows does this range span?" It doesn't care about the contents — a range with 100 rows returns 100 whether the cells are empty, full, or a mix. This distinguishes it from COUNTA (counts non-empty cells) and COUNT (counts numeric cells).
The classic use is knowing the size of a dynamically-defined range — a Table column, a named range, or the output of another function. Combine ROWS with functions like INDEX to build formulas that adapt to data size.
Syntax breakdown
array Required
The range, named range, Table column, or array whose row count you want. Cannot be a single value or a scalar — must be something with a range dimension. Named ranges and Table structured references work: =ROWS(Sales[Amount]) counts rows in that Table column.
5 real-world examples
Example 1: Count rows in a fixed range
How many rows in A2:A50?
=ROWS(A2:A50)Result: 49. Simple range measurement. Note: counts total rows, not non-empty ones — for that use COUNTA.
Example 2: Count rows in a Table column
How many data rows in the Sales Table?
=ROWS(Sales[Amount])Result: Number of data rows (not including the header). Automatically adjusts as rows are added or removed from the Table.
Example 3: Cap a k-parameter to avoid errors
Prevent LARGE from failing when k exceeds the range size:
=LARGE(A2:A100, MIN(10, ROWS(A2:A100)))Result: The 10th-largest value — or the smallest if the range has fewer than 10 rows. Prevents the #NUM! error that LARGE returns when k is out of range.
Example 4: Number a spilled array
Get sequential numbers matching the size of a filtered result:
=SEQUENCE(ROWS(FILTER(A2:A100, B2:B100="Active")))Result: A number series matching however many rows the filter returned. Foundation of many patterns that pair sequence generation with dynamic filtering.
Example 5: Detect if a range has more than one row
Check whether a lookup returned multiple matches:
=IF(ROWS(FILTER(Sales, Sales[Status]="Active")) > 1, "Multiple", "Single or none")Result: Text indicating whether the filter had multiple matches. Useful for validation formulas that need to detect ambiguous or duplicate results.
Common errors and how to fix them
The argument isn't a range or array — you passed a scalar like a single value or text. ROWS needs something with rows to count. Ensure you're passing a range reference.
The referenced range no longer exists (e.g., after a rows-columns delete). Update the reference or restore the range.
ROWS counts all rows in the range, empty or not. For non-empty rows only, use COUNTA on a specific column: =COUNTA(A2:A100). Or use Excel Tables which automatically size to data.
Actually — it does when referencing a Table column: =ROWS(Sales[Amount]) updates. But =ROWS(A2:A100) with a hard-coded range stays fixed. Reference the Table column, not the underlying cells.
ROWS vs alternatives
| Function | Counts what | Best for |
|---|---|---|
| ROWS | Total rows in a range | Range size, k caps, array sizing |
| COUNTA | Non-empty cells | Actual data count |
| COUNT | Numeric cells only | Counting numbers, skipping text |
| COUNTBLANK | Empty cells | Finding gaps |
| ROWS + FILTER | Rows meeting criteria | Modern conditional counting |
Version compatibility
Download the practice workbook
ROWS-based patterns including k caps for LARGE/SMALL, dynamic sequences, and validation formulas.
Related functions
Frequently asked questions
Is ROWS the same as COUNTA?
No. ROWS counts every row in the range, empty or full. COUNTA counts only non-empty cells. For a range A2:A100 where only 47 cells have data, ROWS returns 99 (the range spans 99 rows), COUNTA returns 47 (the data count).
Does ROWS work with a Table's structured reference?
Yes. =ROWS(Sales[Amount]) returns the number of data rows in the Amount column, automatically updating as the Table grows. This is one of the cleanest ways to get "how many rows of data" — no COUNTA needed.
Can ROWS accept an array formula's output?
Yes. =ROWS(FILTER(A2:A100, B2:B100="Active")) returns the count of matching rows. Requires FILTER, so Excel 365 / 2021+.
Why does ROWS(A:A) return 1,048,576?
Because the entire column A has 1,048,576 rows (the sheet's total). Whole-column references count the whole column, not just the data. Use COUNTA for the data count, or a Table column for a data-bound reference.
What's the largest value ROWS can return?
1,048,576 for whole-column references in modern Excel. Older Excel (before 2007) had 65,536 rows. If you're on very old Excel, adjust accordingly.
Is ROWS volatile?
No. ROWS is non-volatile — only recalculates when its input changes. Safe to use in performance-critical workbooks.
How do I count the number of rows in a spilled array?
Reference the spill with the # operator: =ROWS(A2#) counts rows in whatever spills from cell A2. Handy for building dependent formulas on top of dynamic-array output.
Build size-aware formulas — with the Sheets & Cells AI Add-in
The Add-in helps you build formulas that adapt to data size — using ROWS for k caps, dynamic sequences, and validation. No more #NUM! errors from LARGE when your data shrinks. All inside Excel.
Learn about the Add-in →