COLUMN function
COLUMN returns the column number of a reference, with column A as 1. It is used to build offsets, count across, or feed the column argument of INDEX and VLOOKUP.
Example
Read it as a sentence: give the column number of D5, which is 4.
Excel shows 4
D
1
Qty
2
12
3
7
4
20
5
5
Items
Syntax
COLUMN([reference])
Arguments in [square brackets] are optional.
-
reference(optional) - Cell or range to inspect; omitted means the cell holding the formula.
Returns: The column number of the reference.
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 |
|---|---|---|
=COLUMN() | 1 | Own cell |
=COLUMN(Items!D5) | 4 | Single cell |
=COLUMN(Items!C1:E1) | 3 | Range spills |
=COLUMN(Data!A:A) | 1 | Whole column |
=COLUMNS(COLUMN(Data!3:3)) | 16384 | Whole row: width of the returned array (plain =COLUMN(Data!3:3) in column A spills 16384 columns) |
=MAX(COLUMN(Data!3:3)) | 16384 | Whole row: last column number |
=COLUMN(INDIRECT("Data!E1")) | 5 | Via INDIRECT |
=COLUMN((Items!B1,Items!D1)) | #REF! | Multi-area reference |
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
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
E
1
Data
Watch out for
- For a multi-column range in a dynamic-array version it returns an array of numbers.
- With no argument it gives the formula cell's own column.
Where it works
- Excel
- COLUMN 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 .