Excel COLUMN function

Description

Syntax and Arguments

Usage and Examples

• Example1: Obtain column number of a given cell reference
• Example2: Obtain column number which the formula is in
• Example3: Obtain the leftmost column number while reference is a range of cells
• Example4: Obtain the first cell of a data range

---

Description

The COLUMN function returns the number of column which formula appears or returns the column number of given reference. For example, formula =COLUMN(BD) returns 56.

Syntax and arguments

Formula syntax

COLUMN([reference])

Arguments

Reference: Optional. It’s a cell or a range of cells whose column number you want to return, which cannot include multiple references or addresses in the COLUMN function.

Return Value

The COLUMN function returns a number representing the column.

Usage and Examples

Here I list some examples to show how to use the COLUMN function.

Example1: Obtain column number of a given cell reference

Formula

=COLUMN(KG5)

Result: 293

---

Example2: Obtain column number which the formula is in

Formula

=COLUMN())

Result: 1

---

Example3: Obtain the leftmost column number while reference is a range of cells

Formula

=COLUMN(G1:J4)

Result: 7

---

Example4: Obtain the first cell of a data range

Supposing you have a defined name in a sheet which you want to obtain its first cell reference, the COLUMN function combined with the ROW and ADDRESS functions can help you.

Formula

=ADDRESS(ROW(Data),COLUMN(Data))

Explain

ROW(Data)：row_num in the ADDRESS function, obtain the topmost row number of the name range of “Data”.

COLUMN(Data): column_num in the ADDRESS function, obtain the leftmost column number of the named range of “Data”.

Result: $B$2

---

Sample File

---

Excel Productivity Tools