The COLUMN function in Google Sheets returns the column number of the active cell, a specified cell, or a range of cells.

It can be the backbone of many complex worksheet formulas in Google Sheets.

Here are a few examples.

1. With ADDRESS to return cell addresses.
2. Sequential column numbering.
3. In horizontal running calculation formulas.
4. With INDIRECT to increment row number when dragging across.
5. With SUMIF for getting the sum of matrix rows or columns.
6. To return an entire row in Vlookup, etc.

Among the above, I’ll leave examples for the first two in the COLUMN formula example below.

As a side note, there are two functions related to column numbers in Google Sheets.

One is the COLUMN, and the other is the COLUMNS. So, avoid accidentally using one in the place of another.

Column Function – Syntax and Arguments in Google Sheets

Syntax: COLUMN([cell_reference])

Argument:

cell_reference – The cell or range of cells (require ArrayFormula) for which you want to return the column number. Column A corresponds to 1, B to 2, and so on.

There is only one argument in the COLUMN function in Google Sheets. If omitted, it will return the column number of the formula applied cell.

Basic Examples

You will get #1 if you refer to any column A cell reference, e.g., A50 within the COLUMN function.

=column(A50)

So, no doubt, any column Z cell reference will return 26.

=column(Z100)

Above, you can see six columns filled with different colors to make each of them stand out from the rest.

Let’s provide an array reference including these columns to return their corresponding numbers.

When you try with =column(A1:F1), you will get 1, because COLUMN is not an array function. It will consider only the first reference in the range.

You must use the ArrayFormula function together to return the numbers 1 to 6 horizontally.

=ArrayFormula(column(A1:F1))

If you use it in a cell, e.g., A5, empty A5:F5 beforehand to avoid the #REF error due to the data overwrite issue.

Most of the functions cause circular dependency detected issues when entering the formula in the cell reference used.

The COLUMN function is an exception to this. You can use the above formula in one of the cells in A1:F1 without any issue.

Advanced Use of the Column Function in Google Sheets

Other than the tutorial listed at the beginning, here are a few more examples of the advanced use of the COLUMN function in Google Sheets.

We can use =address(row(),column()) to return the current cell ID.

Another use case is sequential column numbers.

To get 26 sequential numbers across the columns, use can use the below formula.

=ArrayFormula(column(A1:Z1))

Actually, we can use SEQUENCE or TRANSPOSE+ROW combination for the same and which seems better to me.

=ArrayFormula(transpose(row(A1:A26)))

or

=sequence(1,26)

That’s all about the COLUMN function in Google Sheets.

Thanks for the stay. Enjoy!