COLUMNS function
COLUMNS counts the columns in a range or array and returns that number. It is useful for sizing a range or checking spill output, and it counts columns only; ROWS counts rows.
Example
Read it as a sentence: count how many columns are in B1:E7, which gives 4.
Excel shows 4
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
Syntax
COLUMNS(array)
-
array(required) - A range or array.
Returns: The number of columns.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=COLUMNS({1,2,3;4,5,6}) | 3 | Array constant |
=COLUMNS(5) | 1 | Scalar |
=COLUMNS("abc") | 1 | Text |
=COLUMNS(Items!B1:E7) | 4 | Range |
=COLUMNS(Data!A:XFD) | 16384 | All columns |
=COLUMNS(Data!F1) | 1 | Blank cell |
The Items sheet these formulas read
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
Data
Watch out for
- Counts columns only; ROWS counts rows.
Where it works
- Excel
- COLUMNS is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .