Function Columns

Columns()

This function is used to determine the number of columns in a given reference (the range of cells referred to in the function).


Syntax:

= Columns (Reference_range)

Reference_range: This is the range of cells for which the number of columns is to be determined


Examples:

The function simply returns the number of columns within the range which is passed on as reference with the function. For example:

• Columns($A$3:$A$3) - result will be: 1 {as there is only 1 column 'A' within the given range}
• Columns($A$35:$D$35) - result will be: 4
• Columns($A$3:$A$5) - result will be: 1
• Columns($A$3:$F$8) - result will be: 6

Application:

Many a times, you have to write a range of formulas (a similar formula over a range of adjacent columns), which should have a number which is dynamic and changes as the column changes. For example - you have to write a formula in cell A5 and the next formula goes in B5, C5, and so on. But in this range of formula, you want one of the parameter to increment by itself. In such a case you can use Columns() function. Let us say that

Here is the way the Columns() function would be used in the above example :

= Original formula (Parameter1, Columns($A$5:A5))

Now, when the above formula will be copied to next cell, i.e. B5, it will change to:

= Original formula (Parameter1, Columns($A$5:B5))

Note that $A$5:A5 changes to $A$5:B5 and this will result in 2 which will be passed on as a parameter to the original formula}