Purpose

Return value

Syntax

=COLUMN([reference])
  • reference - [optional] A reference to a cell or range of cells.

Using the COLUMN function

The COLUMN function returns the column number of a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. COLUMN takes just one argument, called reference , which can be empty, a cell reference, or a range. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.

Examples

With a single cell reference, COLUMN returns the associated column number:

=COLUMN(A1) // returns 1
=COLUMN(C1) // returns 3

When a reference is not provided, COLUMN returns the column number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 4:

=COLUMN() // returns 4 in D6

When COLUMN is given a range, it returns the column numbers for that range:

=COLUMN(E4:G6) // returns {5,6,7}

In Excel 365 , which supports dynamic array formulas , the result is an array {5,6,7} that spills horizontally into three cells, starting with the cell the formula resides in. In earlier Excel versions, the first item of the array (5) will display in one cell only.

To get Excel 365 to return a single value, you can use the implicit intersection operator (@):

=@COLUMN(E4:G6) // returns 5

This @ symbol disables array behavior and tells Excel you want a single value.

Notes

  • Reference can be a single cell address or a range of cells.
  • Reference is optional and will default to the cell in which the COLUMN function exists.
  • Reference cannot include multiple references or addresses.
  • To get row numbers, see the ROW function .
  • To count columns, see the COLUMNS function .
  • To lookup a column number, see the MATCH function .

Purpose

Return value

Syntax

=COLUMNS(array)
  • array - A reference to a range of cells.

Using the COLUMNS function

The COLUMNS function returns the count of columns in a given reference as a number. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns. COLUMNS takes just one argument, called array , which should be a range or array .

Examples

Use the COLUMNS function to get the column count for a given reference or range. For example, there are 6 columns in the range A1:F1 so the formula below returns 6:

=COLUMNS(A1:F1) // returns 6

The range A1:Z100 contains 26 columns, so the formula below returns 100:

=COLUMNS(A1:Z100) // returns 26

You can also use the COLUMNS function to get a column count for an array constant :

=COLUMNS({1,2,3,4,5}) // returns 5

Although there is no built-in function to count the number of cells in a range, you can use the COLUMNS function together with the ROWS function like this:

=COLUMNS(range)*ROWS(range) // total cells
=COLUMNS(A1:Z100)*ROWS(A1:Z100) // returns 2600

More details here .

Notes

  • Array can be a range or a reference to a single contiguous group of cells.
  • Array can be an array constant or an array created by another formula.
  • To count rows, see the ROW function .
  • To get column numbers , see the COLUMN function .
  • To lookup a column number, see the MATCH function .