Excel COLUMN Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 12 of 105 · Full Excel course

The Excel COLUMN function returns the column number of a reference: 1 for column A, 2 for B, 27 for AA. With no argument it returns the column number of the cell that contains the formula. COLUMN in Excel is mostly used inside other formulas to generate sequences and make VLOOKUP or INDEX column numbers adjust automatically when copied across.

COLUMN syntax

=COLUMN([reference])

Arguments

Argument Required Meaning
reference Optional The cell or range whose column number you want. If omitted, COLUMN uses the cell where the formula sits. For a multi-column range it returns the first column number, or an array of numbers in Excel 365.

COLUMN always returns a positive whole number. It does not return the column letter; see the use cases below for that conversion.

Step-by-step example

=COLUMN(E1)
  1. Excel looks at the reference E1.
  2. Column E is the fifth column of the sheet.
  3. The formula returns 5, regardless of which cell contains it.

Typing =COLUMN() in cell E1 also returns 5, because the reference defaults to the formula’s own cell. Copy that formula to F1 and it returns 6.

Practical use cases

1. Auto-incrementing column index for VLOOKUP

=VLOOKUP($A2, $H$2:$M$500, COLUMN(B1), FALSE)

In the first output column COLUMN(B1) is 2; drag right and it becomes 3, 4, 5, so one formula fills the whole row.

2. Number headers across a row

=COLUMN()-COLUMN($B$1)+1

Starting in B1 this returns 1, 2, 3 … no matter where the table sits on the sheet.

3. Column number to column letter

=SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "")

4. Pick every other column in SUMPRODUCT

=SUMPRODUCT((MOD(COLUMN(B2:M2), 2)=0)*B2:M2)

Adds only the even-numbered columns, useful for alternating Budget/Actual layouts.

5. Return a whole row of positions in Excel 365

=COLUMN(A1:F1)

Spills {1,2,3,4,5,6}. In older Excel, wrap it in INDEX or use it inside SUMPRODUCT.

Common mistakes and errors

  • #REF! – the referenced column was deleted after the formula was written. Re-point the reference.
  • #VALUE! in older versions – a multi-cell reference was used in a single cell without array handling. Use a single-cell reference or wrap the formula in INDEX.
  • Expecting a letter – COLUMN returns a number. Convert with ADDRESS and SUBSTITUTE, or use CHAR(64+COLUMN()) for columns A to Z only.
  • Wrong offset when copied – COLUMN(B1) as a VLOOKUP index shifts as you drag; if that is not intended, use a constant or lock with MATCH.
  • Referencing another sheet – COLUMN(Sheet2!D1) works fine; it returns 4. The old advice that cross-sheet references fail is incorrect.

Tips and best practices

  • Anchor with a reference such as COLUMN(B1) rather than bare COLUMN() when the formula may be moved, so the starting number is predictable.
  • Prefer COLUMNS($A$1:A1) for horizontal counters; it survives inserted columns to the left better than COLUMN()-n.
  • Use MATCH on headers instead of COLUMN when the target column is named; the formula then survives reordering.
  • Combine with MOD to pick every nth column in SUMPRODUCT for Budget/Actual layouts.
  • In Excel 365, SEQUENCE(1, n) is a clearer way to generate horizontal number series than COLUMN(A1:E1).

Related functions

Frequently asked questions

Can COLUMN return the column letter instead of the number?

Not directly. Use =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), “1”, “”) to convert the number to its letter, which works for every column including AA and beyond.

What does COLUMN() return with no argument?

The column number of the cell that holds the formula. In cell D7 it returns 4; copy it to G7 and it returns 7.

What is the difference between COLUMN and COLUMNS?

COLUMN returns the position of a column on the sheet. COLUMNS returns how many columns a range spans; COLUMNS(B:E) is 4 regardless of where the range starts.

Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.

← PreviousExcel CODE Function: Syntax, Examples and TipsAll functionsExcel Formulas and FunctionsNext →Excel COLUMNS Function: Syntax, Examples and Tips