Excel COLUMN function

Summary

The Excel COLUMN function is used to return the column number of a cell reference. It’s a straightforward function that’s especially useful in formulas where you need to know the column position of a reference. This function can handle both single cell references and ranges, providing different outputs depending on the context.
Syntax
				
					=COLUMN([reference])
				
			
  • reference: [Optional] The cell or range of cells for which you want the column number. If omitted, COLUMN returns the column number of the cell where the formula is entered.
Return value
Returns the column number(s) of the specified cell or range.

How to use

To use the COLUMN function, simply enter a cell reference as the argument. If you leave the argument blank, COLUMN returns the column number of the cell containing the formula. For ranges, it returns the column numbers for the entire range, which in Excel 365 and later versions will spill across multiple cells.

Examples

Simple COLUMN
Column Number of a Single Cell:
				
					=COLUMN(B3)

				
			
Returns 2, as B3 is in the second column.
COLUMN without Reference
Automatic Reference to Current Cell:
				
					=COLUMN()

				
			
Entered in cell E5, this formula returns 5, the column number of E5.
COLUMN with Range
Column Numbers for a Range:
				
					=COLUMN(C2:E2)
				
			
Returns {3,4,5} in Excel 365, representing the column numbers for C2, D2, and E2.
COLUMN in Excel Pre-365

First Column Number in a Range:

				
					=COLUMN(F2:H2)
				
			
In pre-Excel 365, this formula would return 6, the column number of the first cell (F2) in the range.
COLUMN with Implicit Intersection
Single Value from a Range in Excel 365:
				
					=@COLUMN(F2:H2)

				
			
Returns 6 in Excel 365 due to implicit intersection (using the @ operator), representing the first column in the range.

Additional Notes

  • COLUMN is particularly useful in dynamic formulas where the position of a cell matters.
  • In Excel 365, COLUMN with a range returns an array. Use the implicit intersection operator (@) if you need a single value.
  • This function is often used in conjunction with other functions like INDEX, MATCH, or OFFSET for advanced data manipulation.

Related Functions

Excel ROW function

The Excel ROW function returns the row number of a cell reference, aiding in identifying row positions in formulas.

Excel COLUMNS function

The Excel COLUMNS function counts the number of columns in a range, essential for dynamic formulas and adapting to data changes.

Excel ROWS function

The Excel ROWS function counts the number of rows in a range, essential for dynamic formulas and adapting to data changes.

Content Navigation