Guide on using the COLUMN and COLUMNS functions in Excel
Guide on using the COLUMN and COLUMNS functions in Excel: Do you want to know which column your reference belongs to or how many columns your reference has? The following article will provide you with a concise overview of the COLUMN function, which returns the column position of a reference, and the COLUMNS function, which returns the number of columns in a reference. Both functions can be used independently or in combination with other functions and formulas.
1. COLUMN function
a. COLUMN function syntax
The COLUMN function returns the column position of the given cell reference or the first column of a reference range.
Function syntax: =COLUMN([reference])
Where: reference is an optional argument. The cell or range of cells for which you want to return the column number.
Note:
- If you omit the reference argument, COLUMN will return the column position at the cell where the formula is entered.
- If the reference is a single cell, COLUMN will return the column position of that cell.
- If the reference is a horizontal range, COLUMN will return the column position of the first cell in that range.
- The reference argument cannot refer to multiple ranges.
b. How to use the COLUMN function
For example, let’s say we have the following data table:
If we enter the formula =COLUMN(B3), we will get the result as the column position of cell B3.
If we enter the formula =COLUMN(C3:D3), we will get the result as the column position of the first cell in the reference range. In this case, it is the column position of cell C3.
2. COLUMNS function
a. COLUMNS function syntax
The COLUMNS function returns the number of columns in an array or reference.
Function syntax: =COLUMNS(array)
Where: Array is a required argument. The array or array formula or reference to the range of cells for which you want to count the number of columns.
b. How to use the COLUMNS function
Using the same example as above, if we enter the formula =COLUMNS(C3:D3), we will get the result as the number of columns in the reference range, not the column position of the first cell in the reference range as in the COLUMN function.
Guide on using the COLUMN and COLUMNS functions in Excel
Therefore, the above article has guided you on how to use and differentiate between the COLUMN and COLUMNS functions. Good luck!
