🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel ROWS & COLUMNS — Count the Size of a Range (Not Its Position)

|

Excel ROWS & COLUMNS — Count the Size of a Range (Not Its Position)

TL;DR — ROWS(array) returns how many rows a range or array has, and COLUMNS(array) returns how many columns. ROWS(A1:A10) is 10; COLUMNS(A1:C1) is 3. The plural forms measure a size; the singular ROW and COLUMN report a position — a one-letter difference that changes the answer completely. The payoff is formulas that resize themselves: a VLOOKUP column index that stays correct when you insert a column, a count of the rows a FILTER returned, and "the last row" without hard-coding a number.

=ROWS(A1:A10)                 ' -> 10    how many rows in the range
=COLUMNS(A1:C1)               ' -> 3     how many columns in the range
=ROWS(A1:C10)                 ' -> 10    still 10 — it counts rows, ignores width
=COLUMNS($A$1:C$1)            ' -> 3     dragging the end grows this: 1, 2, 3, ...
=ROWS(FILTER(data, keep))     ' -> 42    how many rows the filter returned
=ROWS(A:A)                    ' -> 1048576   a whole column — the physical row count

ROWS and COLUMNS look like trivia until you notice how much fragile, hard-coded "there are 3 columns / the data ends at row 200" logic they replace. They turn a fixed number that a coworker will break into a value Excel recomputes from the range itself. The trick is keeping them straight from their singular cousins.

What you'll learn

  • The mental model: ROWS/COLUMNS measure size, not position
  • The one-letter trap: why ROW(A1:A10) and ROWS(A1:A10) return different things
  • The killer use: a VLOOKUP column index that survives inserted columns
  • Counting the rows a dynamic array or FILTER produced
  • Finding the last row / midpoint of a range without hard-coding it
  • The whole-column trap where ROWS(A:A) is over a million

The mental model: size, not position

The plural is the whole story. ROW and COLUMN answer where is this cell? — they hand back a coordinate. ROWS and COLUMNS answer a different question: how big is this block? — they hand back a count. ROWS(A1:A10) doesn't care what's in the range or where it sits; it reports that the range spans 10 rows. COLUMNS(A1:C1) reports 3 columns.

That is why ROWS(A1:C10) is 10, not 30: it counts rows, and the range is ten rows tall regardless of being three columns wide. The two functions are a tape measure for a rectangle — one gives the height, the other the width. Like their singular cousins, they are not volatile, and they happily measure a range reference or an array literal (ROWS({1;2;3}) is 3), which makes them a cheap, stable way to hand a size to another function.

The one-letter trap: ROW vs ROWS

This is the confusion worth burning into memory, because it fails silently inside array formulas:

=ROW(A1:A10)      ' -> {1;2;3;4;5;6;7;8;9;10}   an ARRAY of positions (singular)
=ROWS(A1:A10)     ' -> 10                         a single COUNT (plural)

Singular ROW given a range returns one number per row — a spilled list of coordinates. Plural ROWS returns a single total. In a plain cell the difference is obvious, but drop the wrong one into INDEX, SEQUENCE, or a SUMPRODUCT and you'll get a wrong answer with no error to warn you — SEQUENCE(ROW(data)) tries to build a sequence of the wrong length, while SEQUENCE(ROWS(data)) builds one row per data row as intended. When you mean "how many," it's the plural. When you mean "which one," it's the singular.

The killer use: a VLOOKUP column index that never breaks

The most valuable thing COLUMNS does is make VLOOKUP survive edits. VLOOKUP's third argument is a hard number — return column 2, or 3, or 4 — and that number is exactly what breaks when someone inserts a column into the table. Replace it with COLUMNS and it recomputes:

=VLOOKUP($A2, Prices, COLUMNS($B$1:B$1), FALSE)   ' first result col -> index 2
' drag right:        COLUMNS($B$1:C$1) -> 3,   COLUMNS($B$1:D$1) -> 4, ...

Anchor the left edge ($B$1) and let the right edge ride along as you drag: the range widens by one column each step, so COLUMNS counts 2, 3, 4… and feeds VLOOKUP an index that climbs on its own. Because the index is now derived from the sheet rather than typed, inserting a column shifts everything consistently and the lookups keep pointing at the right fields. This is the plural functions' whole reason to exist: a size that adjusts beats a number you have to remember to fix. (On Excel 365, INDEX/MATCH or XLOOKUP sidestep the index entirely — but for the millions of VLOOKUPs already in the wild, COLUMNS is the cheapest hardening you can add.)

Counting a dynamic array or filtered result

Wrap ROWS around a spilled formula and you get "how many did that return?" — often more directly than a COUNT variant:

=ROWS(FILTER(Orders, Region="West"))    ' how many West orders matched
=ROWS(UNIQUE(Customers))                 ' how many distinct customers
=COLUMNS(A1#)                            ' how wide a spill range is (the # operator)

ROWS(FILTER(...)) answers "how many records satisfy the condition" by measuring the result itself — no separate COUNTIFS that you have to keep in sync with the filter's criteria. Pointing ROWS/COLUMNS at a spill reference (A1#) tells you the live dimensions of a dynamic array as it grows and shrinks. The mental model holds: you're measuring the size of whatever the inner formula produced.

Last row, midpoint, and range size without hard-coding

Because ROWS gives a range's height, it's the clean way to reach the last item or the middle of a range that keeps changing size:

=INDEX(data, ROWS(data))            ' the LAST value in a 1-column range
=INDEX(data, ROWS(data)/2)          ' roughly the midpoint
=INDEX(data, ROWS(data)-1)          ' the second-to-last value

INDEX(data, ROWS(data)) reads as "the item at position count" — i.e. the last one — and it stays correct when the range grows, where a hard-coded INDEX(data, 200) silently misses new rows or points past the end. The failure mode ROWS prevents is the hard-typed row count that everyone forgets to update after the data expands.

The whole-column trap: ROWS(A:A) is 1,048,576

One gotcha to internalize: ROWS and COLUMNS count the physical size of what you give them, not how much of it is filled.

=ROWS(A:A)        ' -> 1048576   every row in the column exists, filled or not
=COLUMNS(A:A)     ' -> 1         column A is exactly one column wide
=COLUMNS(A:C)     ' -> 3

ROWS(A:A) is the worksheet's full height — a million-plus — because the reference is the entire column. That is almost never "how many rows of data I have"; for that you want COUNTA (non-blank cells) or a spill/FILTER count. Confusing the physical dimension of a whole-column reference with the count of used rows is the one way these functions surprise people — keep "how big is this reference" separate from "how much is filled in."

The judgment call

Reach for the plural forms whenever you're about to hard-code a dimension. A VLOOKUP index typed as 4, a "the data ends at row 500" that a coworker will invalidate next week, a "there are 12 months across" — each is a ROWS/COLUMNS waiting to happen, and each becomes self-adjusting the moment you derive it from the range instead of typing it. The only real hazard is the singular/plural mix-up: if your formula started returning a suspiciously long spilled array where you expected one number, you almost certainly wrote ROW where you meant ROWS. Measure sizes with the plural, read positions with the singular, and the hard-coded numbers disappear from your models.

How ExcelMaster helps

The COLUMNS-based VLOOKUP index is the kind of hardening people know they should do and rarely bother with. Ask ExcelMaster to "make this VLOOKUP survive inserted columns" and it rewrites the third argument as an anchored COLUMNS($B$1:B$1) that climbs as you drag — or offers the INDEX/MATCH version if you're on a modern build. Ask "how many rows did my filter return" and it wraps the result in ROWS(...) rather than bolting on a second COUNTIFS you'd have to keep synchronized. And if you paste a formula that mixes up ROW and ROWS, it catches the singular-versus-plural slip before it turns into a silent off-by-a-thousand.

Frequently asked questions

What does the ROWS function do in Excel?

ROWS(array) returns the number of rows in a range or array. =ROWS(A1:A10) returns 10, and =ROWS(A1:C10) also returns 10 because the range is ten rows tall regardless of its width. It measures a size, unlike ROW, which returns the row number of a cell.

What's the difference between ROW and ROWS (and COLUMN vs COLUMNS)?

The plural counts, the singular locates. ROWS(A1:A10) returns 10 (how many rows); ROW(A1:A10) returns the array {1;2;…;10} (each row's number). Use ROWS/COLUMNS when you need a size, and ROW/COLUMN when you need a position.

How do I count the number of columns in a range?

Use =COLUMNS(range). =COLUMNS(A1:F1) returns 6. To count how many columns of data are actually filled on a row, combine it with a COUNTA, but for the width of a defined range COLUMNS is exact.

How does COLUMNS make a VLOOKUP formula more robust?

Replace VLOOKUP's hard-coded third argument with an anchored COLUMNS($B$1:B$1). As you drag the formula right, the range widens and COLUMNS returns 2, 3, 4…, so the column index climbs automatically and keeps working when columns are inserted — no manual renumbering.

Why does ROWS(A:A) return 1,048,576?

Because a whole-column reference is every row in the sheet, and Excel worksheets have 1,048,576 rows. ROWS/COLUMNS report the physical size of the reference, not how many cells are filled. For the count of used rows, use COUNTA(A:A) or a FILTER result.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-13.

Related guides: Excel ROW & COLUMN · Excel HYPERLINK · Excel VLOOKUP · INDEX & MATCH · Excel FILTER