COLUMN Function

Excel Functions › Lookup & Reference

All Excel versions

The Excel COLUMN function returns the column number of a cell — A is 1, B is 2, Z is 26 — or the column of the formula’s own cell when you give it no argument. Its best-known job is automating col_index_num so one VLOOKUP can be written once and dragged across many columns. It’s the sideways twin of ROW.

Quick answer: =COLUMN(D5) returns 4. With no argument, COLUMN reports the formula’s own column. The classic use — a VLOOKUP you can drag across:

=VLOOKUP($A2, $A$1:$D$5, COLUMN(B1), FALSE) // drag right: COLUMN(B1) becomes 2, 3, 4...

Each copy fetches the next column of the table without retyping the index.

Syntax

=COLUMN([reference])

Argument		Description
`reference`	Optional	The cell (or range) whose column number you want. Omit it and COLUMN returns the column of the cell containing the formula.

Given a multi-column range, COLUMN returns every column number: in Excel 365/2021 =COLUMN(C1:E1) spills 3, 4, 5 across the sheet. Older versions show only the first value outside of array formulas.

From column letters to numbers

Excel labels columns with letters, but many functions want numbers. COLUMN translates — here row 2 contains =COLUMN() entered in each cell:

	A	B	C	D	E	F
1	Product	Q1	Q2	Q3	Q4	Total
2	1	2	3	4	5	6

=COLUMN() // in cell D2 returns 4

=COLUMN(F9) // returns 6 - the row never matters

Need a position within a table rather than on the sheet? Subtract the table’s first column:

=COLUMN(E2)-COLUMN(B2)+1 // E is the 4th column of a table starting at B

Try it: interactive COLUMN demo

Live demo

Type any cell address to translate its letters into a column number — then watch what an argument-free =COLUMN() does.

reference (type a cell address)

Result:

Now enter =COLUMN() with no argument in cell…

Result:

Practical uses: drag-across VLOOKUPs and more

1. Automate VLOOKUP’s col_index_num. Pulling Name, Dept, and Salary for one employee normally means three VLOOKUPs with hand-typed indexes 2, 3, 4. Let COLUMN count instead:

	A	B	C	D
1	ID	Name	Dept	Salary
2	E-101	James Chen	Sales	$68,000
3	E-102	Maria Lopez	Finance	$74,500
4	E-103	Devon Smith	Sales	$61,200
5	E-104	Priya Patel	IT	$82,900

=VLOOKUP($F$2, $A$2:$D$5, COLUMN(B1), FALSE) // in the Name cell; drag right for Dept and Salary

COLUMN(B1) evaluates to 2; dragged one cell right it becomes COLUMN(C1) = 3, then 4. One formula, the whole record. (A variant of the same idea uses COLUMNS, which also survives column insertions.)

2. Every-other-column logic. Conditional formatting or SUMPRODUCT keyed on column parity:

=MOD(COLUMN(),2)=0 // TRUE in B, D, F, ... - stripe columns the way ROW stripes rows

3. Turn a column letter into a number (and back) when building references with INDIRECT or ADDRESS:

=COLUMN(INDIRECT("XFD1")) // returns 16384 - the last column

Errors & common pitfalls

Pitfall: the helper reference is fragile. COLUMN(B1) in the VLOOKUP trick means “2” only while column B exists where you expect. If someone inserts a worksheet column to the left, B1 shifts and your indexes silently change. The sturdier variant is COLUMNS($A$1:B$1)-style counting, or switch to XLOOKUP/INDEX+MATCH, which never count columns at all.

Pitfall: sheet position vs table position. COLUMN reports the worksheet column. For “which column of my table is this?” subtract the table’s first column: =COLUMN(cell)-COLUMN(first_col)+1.

Pitfall: COLUMN vs COLUMNS. COLUMN returns a position; COLUMNS returns a count. =COLUMN(C1:F1) relates to 3; =COLUMNS(C1:F1) is 4.

#NAME? — typing problem. COLUMN exists in every Excel version, so #NAME? means a misspelling or a stray character — check for =COLUMM() and friends.

Practice workbook

📊

Download the free COLUMN practice workbook

Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What does =COLUMN() with no argument return?

The column number of the cell the formula lives in. Enter =COLUMN() in cell D7 and it returns 4. That self-reference is what makes the drag-across VLOOKUP trick work.

How do I use COLUMN to copy a VLOOKUP across?

Replace the hard-coded col_index_num with a COLUMN reference that starts at the right number: =VLOOKUP($A2, $A$1:$D$50, COLUMN(B1), FALSE). Dragged right, COLUMN(B1) becomes COLUMN(C1), COLUMN(D1) — 2, 3, 4 — so each copy returns the next column. Lock the lookup value and table with $ signs.

How do I convert a column letter like 'AZ' to a number?

Wrap it in INDIRECT: =COLUMN(INDIRECT("AZ1")) returns 52. The reverse — number to letter — uses ADDRESS: =SUBSTITUTE(ADDRESS(1,52,4),"1","") returns "AZ".

What's the difference between COLUMN and COLUMNS?

COLUMN returns a column number (position); COLUMNS returns how many columns a range spans. =COLUMN(C1:F1) relates to position 3; =COLUMNS(C1:F1) is the count, 4.

Why does =COLUMN(C1:E1) show three numbers?

In Excel 365/2021 COLUMN on a multi-column range returns an array that spills: 3, 4, 5. In older versions you see only the first value unless it's inside an array formula. Many classic formulas use this deliberately to generate sequences.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: ROW · COLUMNS · VLOOKUP · ADDRESS · MATCH