Excel COLUMNS function

Summary

The Excel COLUMNS function is designed to return the number of columns in a given reference. It is particularly useful in situations where you need to determine the column count within a range, which can be a single range, multiple ranges, or even an entire row. This function is commonly used in array formulas, dynamic ranges, and when creating formulas that need to adapt to changing data dimensions.
Syntax
				
					=COLUMNS(array)
				
			
  • array: The range or array from which you want to count the number of columns.
Return value
Returns the count of columns in the specified range or array.

How to use

Use the COLUMNS function by specifying a range or array as the argument. The function then calculates and returns the number of columns in that range. It’s particularly useful in formulas where the range size might vary or when working with data tables where the column count can change.

Examples

Simple COLUMNS
Count Columns in a Single Range:
				
					=COLUMNS(A1:C1)

				
			
This formula returns 3, indicating there are three columns in the range A1 to C1.
COLUMNS in Dynamic Range
Adjusting to a Dynamic Range:
				
					=COLUMNS(B2:H2)
				
			
Assuming B2:H2 is a dynamic range that can change, this formula will return the number of columns currently in that range.
COLUMNS with Multiple Ranges
Counting Columns Across Multiple Ranges:
				
					=COLUMNS((A1:B1, D1:F1))
				
			
In Excel 365, this formula counts columns in both ranges A1:B1 and D1:F1, returning 5.
COLUMNS for Entire Row
Counting Columns in an Entire Row:
				
					=COLUMNS(3:3)
				
			
This returns the total number of columns in row 3, which is equal to the total number of columns in the worksheet.

Additional Notes

  • COLUMNS is often used in conjunction with other functions like OFFSET or INDEX for creating dynamic ranges.
  • In array formulas or dynamic array-enabled versions of Excel, COLUMNS can be used to handle arrays or ranges that change size.
  • Remember that COLUMNS counts all columns in a range, including hidden columns.

Related Functions

Excel COLUMN function

The Excel COLUMN function returns the column number of a cell reference, aiding in formulas needing column position awareness.

Excel ROWS function

The Excel ROWS function counts the number of rows in a range, essential for dynamic formulas and adapting to data changes.

Excel COLUMNS function

The Excel COLUMNS function counts the number of columns in a range, essential for dynamic formulas and adapting to data changes.

Content Navigation