Lookup & Reference

ROWS Function in Excel

Returns the number of rows in a reference or array.

Syntax

  • =ROWS(array)

Arguments

  • array (required): Range or array to count rows

Examples

  • =ROWS(A1:A10) - Count rows in range - Result: 10

Excel ROWS function documentation

  • ROWS returns the number of rows in a reference or array.
  • Syntax: =ROWS(array). Not the same as [ROW](/functions/row/) which returns row index.
  • Use to size dynamic ranges, validate import row counts, and drive SEQUENCE length.
  • Related: [COLUMNS](/functions/columns/), [ROW](/functions/row/), [COUNTA](/functions/counta/).

ROWS syntax and array behavior

  • array: cell range or spilled array reference.
  • ROWS(A2:A100) returns 99.
  • On spilled array # reference: =ROWS(A2#) in 365 returns spill row count.
  • Empty range edge cases — verify range includes intended rows only.

Step-by-step: dynamic last row offset

  • Step 1 — Data table Sales with variable height.
  • Step 2 — Row count: =ROWS(Sales[Amount]) or =ROWS(A2:A1000) on bounded range.
  • Step 3 — Last row index: =ROW(A2)+ROWS(A2:A100)-1 on contiguous block.
  • Step 4 — Prefer Excel Table + structured refs for auto-expand.
  • Step 5 — Compare COUNTA for non-blank vs ROWS for fixed range height.

ROWS vs ROW vs COUNTA

  • ROWS — height of range in rows.
  • ROW — index of a row on sheet.
  • COUNTA — non-empty cells may differ from ROWS if blanks inside range.
  • Use ROWS for geometry; COUNTA for data density.

Worked examples to copy

  • Count rows: =ROWS(B2:B500).
  • Spill size: =ROWS(FILTER(A:A, A:A<>"")).
  • With OFFSET legacy: =ROWS(dynamic_range).
  • Validate import: =IF(ROWS(import)=expected, "OK", "Check").

People also ask

  • ROWS vs ROW? — ROWS counts; ROW returns position.
  • ROWS whole column A:A? — Excel max rows count (1048576) — avoid full column.
  • ROWS on Table column? — Returns row count of that column range.
  • ROWS Google Sheets? — ROWS(range) supported.

Common errors

  • Must be a valid range or array

Use cases

  • Dynamic range sizing
  • Array dimensions
  • Data validation

Frequently asked questions

  • How do I count rows in a dynamic array result? Wrap the array formula in ROWS: =ROWS(FILTER(A2:A100, B2:B100>50)) counts how many rows the FILTER returns. This is useful for counting filtered results or unique values: =ROWS(UNIQUE(A2:A100)).
  • What's the difference between ROWS and COUNTA? ROWS counts all rows in a range regardless of content. COUNTA counts only non-empty cells. =ROWS(A1:A100) always returns 100, while =COUNTA(A1:A100) returns the count of cells with data.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use ROWS

  • Dynamic range sizing — common Lookup & Reference scenario for ROWS.
  • Array dimensions — common Lookup & Reference scenario for ROWS.
  • Data validation — common Lookup & Reference scenario for ROWS.

ROWS in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • ROWS syntax: =ROWS(array)
  • array (required): Range or array to count rows
  • Confirm ROWS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm ROWS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • ROW (/functions/row/): Returns the row number of a reference.
  • COLUMNS (/functions/columns/): Returns the number of columns in a reference or array.
  • COLUMN (/functions/column/): Returns the column number of a reference.

Errors to watch for

  • Must be a valid range or array — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter ROWS on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

ROWS worked examples to copy

  • =ROWS(A1:A10) — Count rows in range. Expected result: 10.

ROWS reference summary for crawlers and offline review

  • ROWS belongs to the Lookup & Reference category in Excel. Returns the number of rows in a reference or array.
  • Full syntax: =ROWS(array). Open /functions/rows/ for parameters, FAQs, and related pages.
  • Common mistakes: Must be a valid range or array
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.