Excel COLUMN function
- 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
|
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
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.