Excel COLUMNS Function (Example + Video)
When to use Excel COLUMNS Function
Excel COLUMNS function can be used when you want to get the number of columns in a specified range or array.
What it Returns
It returns a number that represents the total number of columns in the specified range or array.
Syntax
=COLUMNS(array)
Input Arguments
- array – it could be an array, an array formula or a reference to a contiguous range of cells.
Additional Notes
- Even if the array contains multiple rows and columns, only the columns are counted. For example:
-
COLUMNS(A1:B1) returns 2.
-
COLUMNS(A1:B100) also returns 2.
-
- This formula can be useful when you want to get a sequence of numbers as you go to the right in your worksheet.
- For example, if you want 1 in A1, 2 in B1, 3 in C1 and so on, use the following formula =COLUMNS($A$1:A1). As you would drag this to the right, the reference inside it would change and the number of columns in the reference would get incremented by one. For example, when you drag it to column B1, the formula becomes COLUMNS($A$1:B1) which then returns 2.
Excel COLUMNS Function – Examples
Here are two examples of using the Excel COLUMNS function.
Example 1: Finding the number of Columns in an Array
In the example above, =COLUMNS(A1:A1) returns 1 as it covers one row (which is A1). Similarly, =COLUMNS(A1:C1) returns 3 as the array A1:C1 covers four columns in it.
Also, note that it only counts the number of columns. Hence, whether the array is A1:C1, or A1:C5, it would return 3 in both the cases.
Example 2: Getting a Sequence of Numbers in a Column
Excel COLUMNS function can be used to get a sequence of numbers. Since the first reference is fixed, as you copy the formula down, the second reference changes and so does the row numbers in the array.
Excel COLUMNS Function – Video Tutorial
Related Excel Functions:
Other articles you may also like:
