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

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Reference Functions module

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

ArgumentRequired?What it does
arrayYesThe range, array or array formula whose columns you want to count.

Examples

Example 1 — Measuring a range

A few quick checks.

AB
1FormulaResult
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

Kyle Stott

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.