In Microsoft Excel, COLUMN function returns the column number of the specified reference or first column of supplied reference. In case when no reference is provided, function returns column number of the cell address in which function is inserted.
COLUMN ( [reference] )
[reference]: – An optional argument refers to a cell or range of cells. When skipped, reference is assumed as the address of the cell which holds function in it.
Examples:- COLUMN function
By now you must have understood the purpose of the COLUMN function. Now we’ll explore a few examples of using COLUMN function on real data-set.
How to return column number of the active cell
COLUMN function is usually used to return the column number of the same cell in which function is inserted when using with other built in functions of Excel. The returned column number then can help other functions to perform further tasks.
To return the column number, simply type the function in any cell and close the parentheses without supplying anything in it.
The function entered in the above screenshot is returning the column number of the 2nd column. As the reference argument is skipped, so the function is referring to cell address as B2 by default.
Return column number of a specified cell
Performing COLUMN function to return a column number for the specified cell reference is as easy as we return without supplying reference argument.
Jump to any cell and start with function name and refer to a cell whose column number you would like to return. In following screenshot, function is entered in cell B2 but cell reference is supplied as K8.
The function applied in above screenshot returns column number as 11 as the column ‘K’ positioned at 11th from starting of the worksheet.
What happens when “Range of Cells” are supplied
COLUMN function will default return the column number of the first cell when the range of cells are supplied. Since function is meant to return a column number, multiple columns number cannot be returned.
In the following screenshot you can see, when range of cells supplied as B2:F2 in COLUMN function, function returns answer as 2.
Tip: Use Excel COLUMNS function when need to return total number of columns of the supplied range.
- Reference is an optional argument and default refer to the cell in which COLUMN function is entered.
- Reference can be empty, specific cell reference or a range of cells
- Multiple references cannot be supplied as reference argument.
- Use with VLOOKUP to return the value for column_index_num argument instead of supplying manually.
COLUMNS | ROW | ROWS