COLUMN Function in Excel
COLUMN is ROW's horizontal twin — it returns the column number of a reference. A = 1, B = 2, Z = 26, AA = 27, and so on up to XFD = 16,384. Essential for horizontal auto-numbering, VLOOKUP column arguments, and array formulas that iterate across columns.
What it does: Returns the column number of reference (A = 1, B = 2, and so on). If reference is omitted, returns the column number of the cell containing the formula. If reference is a range, returns an array of column numbers.
What COLUMN does
Everything ROW does vertically, COLUMN does horizontally. =COLUMN(C1) returns 3. =COLUMN() returns the column of the formula's own cell. =COLUMN(A1:E1) returns an array of {1,2,3,4,5}.
The most common use in the wild: as the third argument to VLOOKUP or the column argument to INDEX, so that copying a formula across cells automatically shifts which column is returned. This makes wide-format lookups scale gracefully.
=VLOOKUP($A2, LookupTable, COLUMN(B1), FALSE). As you drag this formula right, COLUMN(B1) becomes COLUMN(C1) which is 3, then COLUMN(D1) which is 4 — so VLOOKUP pulls from progressively further columns of the lookup table. This is how you build a whole row of lookups from a single formula.
Syntax breakdown
reference Optional
The cell or range whose column number you want. If omitted, returns the column of the current cell. Range references return an array of column numbers spanning the range.
5 real-world examples
Example 1: Get a specific column's number
Find the column number of E7:
=COLUMN(E7)Result: 5. E is the 5th column.
Example 2: Auto-number columns horizontally
Put in cell A1 and drag right for a numeric header row (1, 2, 3, ...):
=COLUMN()Result: The column number of each cell — 1, 2, 3, 4, ... For 1-indexed regardless of starting column, use =COLUMN() - COLUMN($A$1) + 1.
Example 3: VLOOKUP that scales across columns
In row 2, look up A2 in a table with 5 columns of return values. Fill right:
=VLOOKUP($A2, $F$2:$J$100, COLUMN(B1), FALSE)Result: First cell returns column 2 of the table. Drag right and get columns 3, 4, 5. The trick: COLUMN(B1) starts at 2 and increments as you copy right.
Example 4: Array of column numbers for array formulas
Generate {1,2,3,4,5} as a horizontal array:
=COLUMN(A1:E1)Result: Horizontal array {1,2,3,4,5}. Similar to ROW(1:5) but horizontal. In Excel 365, =SEQUENCE(1, 5) is cleaner.
Example 5: Banded columns (every other column highlighted)
In conditional formatting, apply to even-numbered columns:
=MOD(COLUMN(), 2) = 0Result: TRUE for B, D, F... FALSE for A, C, E... Combined with a fill color, this creates vertical banding — useful for wide tables that scroll horizontally.
Common errors and how to fix them
Most common with COLUMN(A1) when you meant COLUMN(B1) as the starting reference. Verify the argument matches the starting column you want minus the offset needed.
You forgot to lock the lookup range with dollar signs, so both the range and the COLUMN reference shift. Use absolute references: =VLOOKUP($A2, $F$2:$J$100, COLUMN(B1), FALSE) — note the mixed and absolute references.
In pre-2021 Excel, =COLUMN(A1:E1) in a single cell returns only 1. Enter as an array formula (Ctrl+Shift+Enter) to spread, or use SEQUENCE(1, 5) in modern Excel.
Excel column labels are letters; COLUMN returns numbers. If you want to display "A", "B", "C" as headers, use ADDRESS or SUBSTITUTE to convert: =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "").
COLUMN vs alternatives
| Approach | Best for | Trade-off |
|---|---|---|
| COLUMN | Column-based iteration; scaling VLOOKUP arg | Returns number, not letter |
| SEQUENCE(1, N) | Horizontal number series | Excel 365 / 2021+ only |
| Hard-coded numbers | Never (breaks when data shifts) | Doesn't reflow |
| XLOOKUP with return_array | Modern replacement for scaling VLOOKUP | Excel 2021+ only |
Version compatibility
Download the practice workbook
Every example above plus VLOOKUP-scaling and banded-column formatting templates.
Related functions
Frequently asked questions
How do I convert a column number to its letter (A, B, C)?
Use ADDRESS with SUBSTITUTE: =SUBSTITUTE(ADDRESS(1, N, 4), "1", "") where N is the column number. Returns "A" for 1, "B" for 2, "AA" for 27, and so on. Handles the letter transitions correctly past Z.
Why is COLUMN(A1) equal to 1?
Because Excel's columns are 1-indexed: A = 1, B = 2, C = 3, ... Z = 26, AA = 27. If you're coming from a language where indexing starts at 0, this trips you up initially. All Excel positional functions use 1-based counting.
What's the maximum column number Excel supports?
16,384 (which is column XFD). Modern Excel worksheets have 16,384 columns × 1,048,576 rows. COLUMN returns numbers within this range.
Can COLUMN help me detect the last column with data?
Not by itself — use it with COUNTA on row 1 (assuming headers): =COUNTA(1:1) gives you the count of populated columns. To get the letter, wrap with ADDRESS + SUBSTITUTE as shown above.
Should I use COLUMN or SEQUENCE for a horizontal number series?
SEQUENCE(1, N) in Excel 365 / 2021+ — cleaner and more explicit. COLUMN when you need the actual column of a cell (for VLOOKUP-scaling or referencing the current column), or when supporting older Excel.
Is there a difference in performance between ROW and COLUMN?
Practically none. Both are extremely lightweight — a single reference lookup. Neither is volatile. Use them freely without worrying about performance impact.
Can COLUMN work inside a Table's structured references?
Yes: =COLUMN(Sales[Amount]) returns the column number of the first cell in the Amount column. Useful for INDEX-style patterns inside Tables.
Build scaling formulas across columns — with the Sheets & Cells AI Add-in
Describe your horizontal lookup pattern and the Add-in writes the VLOOKUP or XLOOKUP with correct COLUMN references and absolute anchoring — no more debugging why your formulas break when dragged. All inside Excel.
Learn about the Add-in →