COLUMNS Function
Counts how many columns are in a range or array.
Category
Lookup & Reference
Available in
All versions, Microsoft 365
Returns
A count of columns
Overview
COLUMNS answers 'how wide is this?'. =COLUMNS(B2:F10) returns 5 because B through F is five columns.
It counts columns whether or not they contain anything, which makes it a reliable building block for dynamic formulas.
Syntax
=COLUMNS(array) Explain
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range, array or array formula whose columns you want to count. |
Examples
Example 1 — Measuring a range
A few quick checks.
| A | B | |
|---|---|---|
| 1 | Formula | Result |
| 2 | =COLUMNS(A1:D1) | 4 |
| 3 | =COLUMNS(C5:C99) | 1 |
| 4 | =COLUMNS({1,2,3;4,5,6}) | 3 |
Example 2 — Always return the last column
Combine with INDEX to grab the right-most value of a row, however wide the table gets.
=INDEX(B2:M2, COLUMNS(B2:M2)) Explain
COLUMNS returns 12, so INDEX returns the 12th (last) value in the row.
A classic copy-across counter: =COLUMNS($B$1:B1) returns 1, then 2, 3… as you fill it to the right.
Common mistakes
- Confusing it with COUNTA. COLUMNS counts columns in the range, not filled cells. Use COUNTA to count values.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.