Part of the free Module 5: Excel Formulas and Functions · Function 75 of 105 · Full Excel course
The Excel ROWS function returns the number of rows in a range or array. ROWS in Excel counts every row in the reference whether it contains data or not, so ROWS(A1:A5) is 5 and ROWS(B3:B3) is 1. It is the simplest way to make INDEX, OFFSET and running-count formulas adjust automatically as a table grows.
ROWS syntax
=ROWS(array)Arguments
| Argument | Required | Meaning |
|---|---|---|
| array | Required | A range, named range, structured Table reference or array constant whose rows you want to count. |
ROWS returns a positive whole number and is not affected by where the range sits on the sheet, only by its height.
Step-by-step example
=ROWS(A1:A5)- Excel measures the height of A1:A5.
- The range covers rows 1 to 5.
- The result is 5. Widening the range to A1:D5 still returns 5.
=ROWS(Table1) returns the number of data rows in an Excel Table, excluding the header, which makes it a convenient record counter.
Practical use cases
1. Return the last value in a range
=INDEX(B2:B100, ROWS(B2:B100))2. Sum to the last filled cell in a column
=SUM(A1:INDEX(A:A, ROWS(A:A)))ROWS(A:A) is the total row count of the sheet, so INDEX points at the very bottom and SUM covers everything above it without a volatile OFFSET.
3. Serial numbers that survive row deletion
=ROWS($A$2:A2)Fill down from A2: the expanding range returns 1, 2, 3 … and renumbers itself when rows are removed.
4. Count records in a Table
=ROWS(Sales[Amount])5. Percent of rows that meet a condition
=COUNTIF(E2:E200, "Delivered") / ROWS(E2:E200)6. Reverse a list
=INDEX($A$2:$A$20, ROWS(A2:$A$20))Fill down: the shrinking range counts down from 19 to 1, returning the list in reverse order.
Common mistakes and errors
- #REF! – the range was deleted after the formula was written.
- Counting only filled rows – ROWS counts blank rows too. Use COUNTA(A:A) to count rows that contain data.
- ROWS versus ROW – ROWS(A5:A9) is 5 (height); ROW(A5:A9) is 5 as well, but only by coincidence: ROW returns the first row number. ROWS(A10:A14) is still 5 while ROW(A10:A14) is 10.
- Union references – ROWS((A1:A3, C1:C10)) counts only the first area. Use separate calls or AREAS.
- Missing argument – ROWS() with nothing inside is invalid; the array is required.
Tips and best practices
- Use ROWS(Table[Column]) as a record counter on dashboards; it updates as rows are added or deleted.
- Prefer ROWS($A$2:A2) over ROW()-1 for serial numbers; it never depends on where the table starts.
- Combine with INDEX for the last value or a reversed list without any volatile functions.
- Check array sizes with ROWS before SUMPRODUCT or MMULT to avoid #VALUE!.
- Use COUNTA, not ROWS, when you need the number of filled rows.
Related functions
- COLUMNS – counts columns the same way.
- ROW – returns a row’s position rather than a count.
- INDEX – the function ROWS most often drives.
- OFFSET – an alternative for dynamic ranges.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
What does ROWS return for a single cell?
1. A single cell is a range one row high, so ROWS(B3) and ROWS(B3:B3) both return 1.
Does ROWS count empty rows?
Yes. ROWS measures the size of the reference, not its contents. To count rows that contain data, use COUNTA on a column that is always filled.
How do I use ROWS to create a dynamic range?
Combine it with INDEX: SUM(A1:INDEX(A:A, ROWS(A:A))) sums the whole column to the last row, and INDEX(B2:B100, ROWS(B2:B100)) returns the last cell of a fixed block.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.