What Is Column Function in Google Sheets?
The COLUMN function in Google Sheets returns the column number of a specified cell or range. This function is beneficial when you must dynamically refer to the column number when calculating. If the cell reference is a range representing more than one cell and the formula is not used as an array formula, we get the position of the first column in the specified range. The function returns the column index of the given reference in the number form.
For example, column A when specified returns the number 1, column B returns 2, and so on. In case no reference is provided as an argument, COLUMN() will return the column number of the cell where we write the formula. In the example below, we see that =COLUMN(A1) returns one because A1 is in the first column of the sheet. =COLUMN() returns the number 4 because the formula is written in the fourth column of the sheet.
Key Takeaways
- The COLUMN function in Google Sheets returns the column number of the given reference in the argument. If no reference is provided, it returns the column number of the cell containing the formula.
- The syntax of the COLUMN function is as follows. =COLUMN([reference])
Here, reference is an optional parameter. You can give the cell or range whose column number you want to return as an argument.
- To get the column number of a specific cell, pass its cell reference as an argument into the COLUMN function:
Here, =COLUMN(B1) will return 2 because B is the second column.
- If you use the COLUMN function with a range, you get the output as the column number of the first column in that range.
- You can use the COLUMN function in conditional formatting to apply formatting depending on the column number.
Syntax
The COLUMN function in Google Sheets is helpful when you have to dynamically refer to the column number. Before we go ahead to study this in detail, let us look at its syntax.
=COLUMN([reference])
reference (optional): The function just needs a single argument, and that is also optional. It is the cell or range reference whose column number you wish to get. If omitted, the function will return the column number of the cell where we enter the formula.
How to Use Column Function in Google Sheets?
As with most functions in Google Sheets, you can use the COLUMN function in two ways.
- Entering the COLUMN function Google Sheets manually
- Through the Google menu bar
#1 – Entering the COLUMN Function Google Sheets Manually.
We have some data in a column. Let us try to find the column number of this column.
Step 1: To find the column number, type “=COLUMN(.”
Step 2: Now, we enter a specific cell reference or leave the argument blank. Here, we enter the range B1:B5 just for reference and close the braces.
Step 3: Press the “Enter” key. You get the column number of the specified range.
An important point to note is that if the cell_reference parameter is a range across columns and not a single cell, the formula returns only the column number of the first column of the range specified.
Using the Google Menu Bar
- This is a method where do not manually enter the function. First, go to the cell where you wish to enter the COLUMN formula.
- In the Google menu bar, click on “Insert” ➝ “Function” ➝ “LOOKUP” ➝ “COLUMN.”
- Enter the required argument. Close the bracket and press the “Enter” key.
If you don’t fill in the argument, you must enter the “()”, parenthesizes.
Examples
The COLUMN function in Google Sheets is one of the lookup functions that gives us the column number where the specified cell is located. If you are wondering where this function would be helpful, let us look at some scenarios where we can use this function.
Example #1 – Simple Column Number
Let us look at different scenarios where the function can be used. Let us say we must find the column number of a specified cell; we use the function as follows:
=COLUMN(B4)
Result: 2 (since BN is the second column).
COLUMN() without a reference
Let us simply use the COLUMN function with no arguments. Enter =COLUMN() in cell E2.
Press Enter. It will return 5 because column E is the 5th column.
COLUMN() with a Range
Let us specify a multi-column range as an argument. Enter the following formula in cell A10.
=COLUMN(B1:E6). Press Enter.
Here, you get a result of 2 because the first column in the range B1:E6 is column B.
Example #2 – Using COLUMN with ADDRESS Function
Not just by itself, the COLUMN function in Google Sheets can be combined with other functions to give meaningful results for your requirements. Here, let us combine it with the ADDRESS function in Google Sheets for some interesting results.
Note that the ADDRESS function converts a column letter to a regular number. For example, 31, 99, 100, etc. Now, look at the numbers below.
Step 1: Let us try to get the address of a cell in row 2. The column is determined dynamically based on where we place the formula.
Enter the following function in cell C8. Press Enter.
=ADDRESS(3, COLUMN())
You get the absolute address of the cell you require.
It can be repeated to get the relative address as well. Use the following function.
=ADDRESS(2, COLUMN(), 4), Press Enter. Here, the third argument 4 specifies that we need the relative address.
- COLUMN() returns the column number of the current cell where the formula is placed.
- ADDRESS(2, COLUMN()) gives the address of the cell in row 2 of the same column where the formula is located.
Example #3 – Using COLUMN Function in Conditional Formatting
Let us look at an interesting example where we use the COLUMN function for conditional formatting. We have some data displayed in columns A to D. Let us use conditional formatting to highlight the odd columns.
Step 1: First select the range for which you want to apply conditional formatting. Here, we select A1:E6.
Now, go to Format > Conditional Formatting.
Step 2: Now, in the Conditional format rules pane on your right side, under Format cells if, select Custom formula is.
Enter the following custom formula.
=ISODD(COLUMN())
This formula uses the COLUMN function in Google Sheets to get the column number of each cell and the ISODD function checks if the column number is odd. If it is, the condition will apply.
We have setthe formatting option of changing the background to yellow and clicked on Done to apply the conditional formatting.
Thus, by using the COLUMN function, you can format cells based on their column position such as highlighting odd or even columns, etc.
Important Things to Note
- By combining the COLUMN and ADDRESS functions, you can dynamically create cell references based on the current column or row.
- The COLUMN function in Conditional Formatting allows you to create dynamic rules based on the column numbers.
- If the reference argument is a range of cells, the COLUMN function returns the number of the leftmost column.
- The reference argument cannot refer to multiple areas.
- Combined with SUMPRODUCT, we can use COLUMN to get the sum of every nth column.
Frequently Asked Questions (FAQs)
The COLUMN function has many uses.
For instance, when writing formulas that need to change based on the position of data, you can use COLUMN to create dynamic references.
It can be used to perform calculations that depend on column positions. This could range from generating a column number for conditional formatting, to highlighting specific columns, etc.
No, the COLUMN function is only used to get the column number. To get the row number, you would use the ROW() function. For instance, =ROW(A2) returns the row number 2, whereas =COLUMN(A2) returns the column number 1. To get the column letter, you have to use a combination of the COLUMN and ADDRESS function as follows.
=SUBSTITUTE(ADDRESS(1, COLUMN()), “1”, “”)
This returns the column letter. For example, if the formula is in column B, you get “B.”
We can supply the following arguments to COLUMN in Google Sheets.
1. If the argument is a range of cells of multiple columns ,the COLUMN function returns the number of the leftmost column.
2. If the argument is not given, we get the reference of the cell in which the formula appears.
3. The reference argument cannot refer to multiple areas.
Recommended Articles
Guide to What Is COLUMN Function in Google Sheets. We learn its syntax & how to use COLUMN to find the column number in a range with examples. You can learn more from the following articles. –
Leave a Reply