In this article let us understand about the Excel COLUMN Function. The Formula Syntax of Column Function in Excel.
What is Excel Column Function
The purpose of Excel Column function is to know or get the column number for a given cell or range. This function returns the column number(s) depending on the input. Note that the return value for this function is the actual column number but not the value in a given cell or a range.
Syntax of Excel Column Function
The Syntax of COLUMN function in excel is as follows.
=COLUMN([Reference])
Arguments of Excel Column Function
Column Function has only one argument which is Reference.
Reference : This is an optional argument. You can give the cell or range of cells for which you want the column number. See the below to understand the return value of column Function based on reference or input.
- If Reference is left Blank – Return Value will be the same column number where the function is written.
- If Reference is a single Cell Name or Cell Reference – Function returns the column number associated with the cell name or reference.
- Reference is a range or an array – Returns the column numbers of the given range.
Note : when range is given as reference, In Excel365 the result will spill horizontally. Where as in earlier versions of excel the first item of the array will be the return value.
Suggested Reading
Examples for using Excel Column Function
Let us see the return values of COLUMN Function with different examples.
Reference is blank
In the below example, reference is blank and we can see that the return value is 3. Since, the function is in Cell C3 and no reference is available the return value will be the Column number of the cell where the function is.
Reference is a single Cell Name or Reference
In the below example, we can see that the return value is 5. Note that the reference argument is E100. Since the Column number associated here is E the return value is 5 since E is 5th column.
Reference is a Range or an Array
See the below image where reference is a range. The column function returns the corresponding column numbers spilling across the row. The range here is from A100 to F100. Hence the functions returns column numbers from A to F which is 1 to 6.
How to return single value in Excel 365 when Range is used in Reference.
We have seen that when using a range Column function returns all the Columns in the given range. If you want the function to return only a single value instead of all the Column numbers use “@” symbol before the start of the function.
@ Symbol tells excel that we need a single value instead of an array.
Let us take the same example which we have seen above with range in reference. See the below screenshot. The same function returns a single value i.e. only 2 instead of all column numbers in the range when we use “@”
Limitation of COLUMN function
Note that: The input to COLUMN function should always be a single contiguous Range. COLUMN function will not take multiple ranges at once as an input. Meaning any inputs like =COLUMN(A1,B2:C4) or =COLUMN(B2:C4,A1) will return an error message stating “Too Many Arguments entered for this function”.