In this tutorial, we would unlock one of the reference functions called the COLUMNS function in Excel. The excel reference functions are nothing but those excel formulas which return a reference of the cell.
This blog covers the use of COLUMNS excel formula, function syntax and arguments, and its examples.
When to Use Excel COLUMNS Formula
The COLUMNS formula in excel is used to find the number of columns within an array or range of cells. It means it helps to return or get the count of columns in a range of cells or array.
The result of this formula is a whole number (1, 2, 3, 4, … and so on).
Syntax and Argument
=COLUMNS(array)
As seen above, the COLUMNS excel formula in contains one argument which is ‘array’.
- array – In this argument, specify the array or a reference to a range of cells.
Example of Excel COLUMNS Function
Let us consider one very basic example of the COLUMNS formula. Finding number of columns between two cells becomes very easy with this formula.
Suppose you want to count how many columns exist between two cells (A15 and AZ15). In that case, use the below formula:
=COLUMNS(A15:AZ15)
As a result, you would notice that excel returns the count of columns between these excel cell references. The result comes to 52, meaning there are in total 52 columns in between these two cell references.
Do Not Miss These Points
Below are some important points that you need to consider while using the COLUMNS function in Excel-
- This formula only counts the columns and ignores the count of rows in the array or cell reference. For example, =COLUMNS(A15:AZ15) and =COLUMNS(A15:AZ25) both the formulas would return 52 as output.
- This formula is very useful when you want to get the sequence of numbers from 1 to n in columns something like this:
- In the above image, we have used the formula =COLUMNS($A$1:A$1) to return the number of columns between $A$1 and A$1 in cell A1. We have then copied this formula to other columns (B, C, D … and so on). By keeping A as a non-absolute reference in A$1, excel increments to B, C, D, and so on as you copy it to other columns B1, C1, etc.