Lookup & Reference

COLUMN Function in Excel

Returns the column number of a reference.

Syntax

  • =COLUMN([reference])

Arguments

  • reference (optional): Cell or range reference (default: current cell)

Examples

  • =COLUMN() - Current column number - Result: Column number
  • =COLUMN(D5) - Column number of D5 - Result: 4

Excel COLUMN function documentation

  • COLUMN returns the column number of a reference — A=1, B=2, etc.
  • Syntax: =COLUMN([reference]). Empty reference returns column of formula cell.
  • Use in dynamic formulas, offset logic, and serial column indexing.
  • Related: [ROW](/functions/row/), [ROWS](/functions/rows/), [COLUMNS](/functions/columns/), [ADDRESS](/functions/address/).

COLUMN syntax and reference forms

  • reference omitted — returns column number where formula sits.
  • Single cell: =COLUMN(D5) returns 4.
  • Range: returns leftmost column number of the range.
  • Volatile only when reference arg omitted and formula moves.

Step-by-step: auto-increment without dragging

  • Step 1 — Formula in row 2: =COLUMN()-COLUMN($A$2)+1 for 1,2,3 across copied right.
  • Step 2 — Or =COLUMN(A1) copied right increments (classic pattern).
  • Step 3 — Pair with ROW for 2D index keys.
  • Step 4 — Modern alternative: [SEQUENCE](/functions/sequence/) for explicit arrays.
  • Step 5 — Document pattern so auditors understand column() trick.

COLUMN vs COLUMNS vs SEQUENCE

  • COLUMN — position of one reference.
  • COLUMNS — count of columns in range.
  • SEQUENCE — generate 1..n spill in 365.
  • Lookup hub: [/categories/lookup-reference/](/categories/lookup-reference/).

Worked examples to copy

  • This column: =COLUMN().
  • Column of D5: =COLUMN(D5).
  • Index from A: =COLUMN()-COLUMN($A$1).
  • Letter helper: =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","").

People also ask

  • COLUMN no argument? — Current column of formula cell.
  • COLUMN on sheet2!B3? — Returns 2.
  • COLUMN in structured table? — Works on cell refs into table.
  • COLUMN vs COL? — Excel uses COLUMN; COL not a function.

Common errors

  • Returns first column if range given

Use cases

  • Dynamic column references
  • Array formulas
  • Conditional formatting

Frequently asked questions

  • How do I convert column number to letter? Use =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", ""). For column 4, this returns "D". Or use =CHAR(64+COLUMN()) for columns A-Z (1-26 only).
  • How do I use COLUMN for horizontal sequences? =COLUMN(A:J) returns {1,2,3,4,5,6,7,8,9,10} horizontally. Useful for creating column-based calculations or as an index in CHOOSE or INDEX functions.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use COLUMN

  • Dynamic column references — common Lookup & Reference scenario for COLUMN.
  • Array formulas — common Lookup & Reference scenario for COLUMN.
  • Conditional formatting — common Lookup & Reference scenario for COLUMN.

COLUMN in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • COLUMN syntax: =COLUMN([reference])
  • reference (optional): Cell or range reference (default: current cell)
  • Confirm COLUMN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm COLUMN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • ROW (/functions/row/): Returns the row number of a reference.
  • COLUMNS (/functions/columns/): Returns the number of columns in a reference or array.
  • ROWS (/functions/rows/): Returns the number of rows in a reference or array.

Errors to watch for

  • Returns first column if range given — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter COLUMN on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

COLUMN worked examples to copy

  • =COLUMN() — Current column number. Expected result: Column number.
  • =COLUMN(D5) — Column number of D5. Expected result: 4.

COLUMN reference summary for crawlers and offline review

  • COLUMN belongs to the Lookup & Reference category in Excel. Returns the column number of a reference.
  • Full syntax: =COLUMN([reference]). Open /functions/column/ for parameters, FAQs, and related pages.
  • Common mistakes: Returns first column if range given
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.