COLUMN Function

Tells you which column number a cell sits in.

Category

Lookup & Reference

Available in

All versions, Microsoft 365

Returns

A column number

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

Overview

COLUMN returns the column number of a reference: column A is 1, B is 2, and so on. =COLUMN(D5) returns 4.

Leave the reference out and it returns the column of the cell the formula is in — handy for building formulas that change as you copy them across.

Syntax

=COLUMN([reference])

Arguments

ArgumentRequired?What it does
referenceOptionalThe cell or range to check. If left out, the formula's own cell is used.

Examples

Example 1 — Basic column numbers

Each formula below points at a different cell.

AB
1FormulaResult
2=COLUMN(A1)1
3=COLUMN(F10)6
4=COLUMN(AA1)27

Example 2 — A counter that grows as you copy right

We want VLOOKUP to return column 2, then 3, then 4 as the formula is copied across, without editing each one.

=VLOOKUP($A2, $G$2:$J$50, COLUMN(B1), FALSE) Explain
  1. In the first copy, COLUMN(B1) returns 2.
  2. Copied one cell right, it becomes COLUMN(C1) and returns 3, and so on.
Subtract to start from 1 anywhere: =COLUMN()-COLUMN($D$1)+1 counts 1, 2, 3… starting in column D.

Common mistakes

  • Expecting a letter. COLUMN returns a number. To get the letter, use =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "").
  • Passing several areas. COLUMN can't take a multi-area reference like (A1, C1) — it returns #REF!.

Notes

  • Given a range in Microsoft 365, COLUMN spills one number for each column: =COLUMN(B1:D1) returns 2, 3, 4.
  • Related functions: COLUMNS, ROW, ADDRESS, INDEX.
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.