How to Use the Excel COLUMN Function


The Excel COLUMN function returns the referenced column as a number. Here’s a quick explanation of how to use the function.

COLUMN Function

COLUMN([reference])

  • reference = The cell or range of cells that you want to have the column numbered returned from. This is optional.

Here are a few notes about the function:

  1. The reference can’t refer to multiple ranges
  2. If no range is entered in the function, it assumes that you want to know the column of the cell that has the function
  3. If you use an array in the reference (by selecting the cell with the COLUMN function, pressing F2, and then CTRL + SHIFT + ENTER), the function will return the column number as a horizontal array
  4. If the reference is a range of more than one cell, and a horizontal array formula is not used, the function will return the left-most column number

Examples

Here you will see some examples of the COLUMN function including using it as a horizontal array formula. The image below shows the results of using the function.

column function

Result View of COLUMN Function

Now, here is the same spreadsheet showing the formulas:

column function

Formula View of COLUMN Function

Notice that the three formulas are all different, yet they return the same result.

=COLUMN() returns 1, since the function is located in column 1

=COLUMN(A1) also returns 1, since it is referring to cell A1, which is in column 1

=COLUMN(A1:B1) also returns 1 even though the range is over two columns. This is because it always returns the left-most column.

Now, you will see the difference by using horizontal array formulas.

In the image below, the results of the COLUMN function is shown. There are three more formulas added to help show the comparison.

column function

Result View of COLUMN Functions Including Horizontal Array Formulas

Here is the formula view of the above spreadsheet. Note that array formulas will not show the curly brace brackets in the cells, but only in the formula bar. As a result, the formulas in cells A4, B4, A5 and B5 all look identical. However, the formulas in A5 and B5 are both entered as horizontal array formulas.

column function

Formula View of COLUMN Functions Including Horizontal Array Formulas

The image below shows how the formula in cell B5 appears in the formula bar with the curly braces to indicate it is a horizontal array formula.

column function

Horizontal Array Formula

Leave a Reply