COLUMNS
Lookup & Reference
Returns the number of columns in a reference or array.
Translations
EnglishCOLUMNS
FrenchCOLONNES
SpanishCOLUMNAS
GermanSPALTEN
ItalianCOLONNE
PortugueseCOLS
DutchKOLOMMEN
PolishILE.KOLUMN
RussianЧИСЛСТОЛБ
TurkishSÜTUNSAY
CzechSLOUPCE
HungarianOSZLOPOK
SwedishKOLUMNER
DanishKOLONNER
FinnishSARAKKEET
Syntax
COLUMNS(array)Arguments
arrayRange or array
Examples
=COLUMNS(A1:E1)5
5 columns in range
=COLUMNS(Table1)Table columns
Columns in table
=COLUMNS(1:1)16384
All columns in row
Tips & Best Practices
- •Useful for dynamic formulas
- •Combine with INDEX for last column
- •Works with arrays and tables
Common Mistakes
- •Confusing COLUMNS (counts the number of columns in a range) with COLUMN (returns a single column number) - the names look nearly identical but answer completely different questions
- •Using COLUMNS on a single cell reference expecting an error - it simply returns 1, since a single cell is technically a one-column range
- •Referencing an entire row (like COLUMNS(1:1)) expecting the count of used columns only - COLUMNS returns the sheet's full column count instead, since it counts the reference's dimensions, not which cells actually contain data
Related Functions
COLUMNReturns the column number of a single cell or the first column of a range, rather than counting how many columns a range spans.ROWSCounts the number of rows in a range, the vertical counterpart to COLUMNS.COUNTACounts non-empty cells within a range, useful when you need the number of columns that actually contain data rather than the range's full dimension.Frequently Asked Questions
What's the difference between COLUMNS and COLUMN?
COLUMNS(range) counts how many columns a range spans. COLUMN(reference) returns the column number of a single cell or the first column of a range. One gives a count, the other gives a position.
Why does COLUMNS(1:1) return over 16,000?
Because you're referencing the entire row, and COLUMNS counts the full dimension of the reference - Excel's maximum column count - not just the columns containing data.
What does COLUMNS return for a single cell reference?
1, since a single cell is technically treated as a one-row, one-column range.
Need to translate a formula using COLUMNS?
Use our translator to convert your complete formula