Cellwise Try it free

Lookup function

Excel COLUMN function

COLUMN returns the column number of a cell: A is 1, B is 2, Z is 26 and AA is 27. =COLUMN() gives the column the formula is in. It is handy for turning letters into numbers and for formulas that should count across as you copy them to the right.

=COLUMN([reference])

Arguments

reference
Optional. A cell or range. Leave it out for the formula’s own column. A range such as B1:E1 returns one number per column in Excel 365.

COLUMN examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1RepQ1Q2Q3Q4
2Priya18400212501980024100
3Marcus15200149001765018300
4Lena20100224002190023750

Download this sheet as an Excel practice file (.xlsx)

  1. =COLUMN(D2)

    Result: 4

    D is the fourth column, so this returns 4.

  2. =COLUMN(B1:E1)

    Result: spills 1 row × 4 columns

    2345

    A range gives one number per column. In Excel 365 the numbers 2 to 5 spill across the row.

  3. =VLOOKUP("Lena",A2:E4,COLUMN(C1),FALSE)

    Result: 22400

    COLUMN(C1) is 3, so this returns Lena’s Q2 figure, 22,400. Copy it one cell right and C1 becomes D1, so the next copy returns Q3.

  4. =COLUMN(E1)-COLUMN(B1)+1

    Result: 4

    Counts the columns from B to E, both included: 4. COLUMNS(B1:E1) does it in one step.

How to use COLUMN

  1. Click the cell for the result.
  2. Type =COLUMN() for the formula’s own column, or =COLUMN(D2) for another cell.
  3. Press Enter.
  4. Inside VLOOKUP or INDEX, use COLUMN(C1) in place of a typed number so the column moves as you copy right.

Common COLUMN mistakes

Expecting a letter

COLUMN always returns a number. To get the letter, use =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","").

A typed column index in VLOOKUP

VLOOKUP(x,A2:E4,3,FALSE) still says 3 after you insert a column inside the table, so it returns the wrong column. COLUMN(C1) shifts with the insert, or switch to XLOOKUP.

Mixing up COLUMN and COLUMNS

COLUMN(B1:E1) gives the column numbers 2 to 5. COLUMNS(B1:E1) counts them and returns 4.

COLUMN FAQ

How do I convert a column letter to a number in Excel?

Use =COLUMN(AB1), which returns 28. If the letter is text in a cell, =COLUMN(INDIRECT(A2&"1")) does the same.

How do I find which column a header is in?

=MATCH("Q3",A1:E1,0) returns 4 because Q3 is the fourth cell of A1:E1. If the range does not start in column A, add COLUMN of its first cell minus 1.

What does COLUMN() return with nothing in the brackets?

The column number of the cell the formula is in. In D5 it returns 4.

Related functions

Updated