The COLUMN function returns the column number of a specified cell in Google Sheets. When used with ArrayFormula, it can handle cell ranges. The first column, column A, is numbered 1.
This function can be used as part of other functions, particularly with the ADDRESS function. It also plays a major role in conditional formatting, allowing you to dynamically refer to cells in another sheet. We’ll explore tips and tricks related to these uses.
Syntax
COLUMN([cell_reference])The function has only one argument, cell_reference, which is optional. If omitted, the default reference is the cell containing the formula.
For example, if you enter =COLUMN() in cell A1 or anywhere in column A, it will return 1. If you enter it in column Z, it will return 26.
If cell_reference is a range, you should enter it as an array formula:
=ArrayFormula(COLUMN(A1:Z1))This will return the column numbers 1 to 26. You can use this formula in any cell, provided there are 26 blank cells across.
Here are some tips and tricks for using the COLUMN function in Google Sheets.
Using the COLUMN Function with the ADDRESS Function
As you may know, a cell address is identified by its column letter and row number. For example, B10 refers to the cell at the intersection of column B and row 10.
If you specify row and column numbers, the ADDRESS function can return the cell address.
For example, the following formula will return B10:
=ADDRESS(10, 2)Here, 10 is the row number, and 2 is the column number.
Tricky Use:
If you enter the following formula in any cell and drag it across, it will return $A$1, $A$2, $A$3, and so on across the row:
=ADDRESS(COLUMN(A1), 1)Try replacing COLUMN(A1) with COLUMN(A1) + 10 and see what happens. Also, replace 1 with 2 and try again.
Using the COLUMN Function in Conditional Formatting
Assume you want to highlight a particular value, such as the current date, in a cell range (e.g., A1:Z).
You can select A1:Z and apply the following formula by clicking Format > Conditional Formatting and selecting Custom Formula Is:
=A1=TODAY()But what should you do when you want to highlight a different sheet? For example, if you want to match today’s date in the range A1:Z in Sheet1 and highlight the same cells in Sheet2 instead of Sheet1.
You can’t use a formula like =Sheet1!A1=TODAY() in Sheet2, as Google Sheets doesn’t support direct cell references when referring to a different sheet in conditional formatting.
In this case, you need to use the INDIRECT function, like this:
=INDIRECT("Sheet1!A1")=TODAY()However, the issue with this approach is that the cell reference is not dynamic because it’s a string. To make it dynamic, you can use the following formula:
=INDIRECT(ADDRESS(ROW(A1), COLUMN(A1), 4, ,"Sheet1"))=TODAY()Here, we used the ROW and COLUMN functions within the ADDRESS function to dynamically refer to each corresponding cell in Sheet1 from Sheet2.
