Summary

The COLUMNS function returns the number of columns in a range or array. If a range is 5 columns wide, COLUMNS returns 5. It counts the size of the range, not the amount of data inside it.

That makes COLUMNS useful when a formula needs to know the width of a table, array, or spill range. It is often used for size checks, horizontal counters, and formulas that need to stay inside the width of a source range.

Syntax

=COLUMNS(array)

The function needs one argument: the range or array you want to measure. For example, =COLUMNS(A1:E1) returns 5. If the range is vertical, like A1:A50, the result is 1 because there is only one column.

COLUMNS can also work with array constants and spilled ranges, so it fits both simple worksheets and newer Excel formulas.

Arguments

• array - The range, array formula, spilled range, or array constant you want to measure.

The argument is required. Unlike COLUMN(), COLUMNS does not default to the current cell. It always needs a range or array because the function is measuring size, not position.

COLUMNS vs Other Functions

COLUMNS is about width. Related functions answer different questions.

Using COLUMNS

The most direct use of COLUMNS is measuring the width of a range. A formula can use that count to check whether a table has the expected number of fields, or to decide how far another formula should move across a source range.

COLUMNS is also useful for building a running horizontal counter. A pattern like =COLUMNS($A$1:A1) returns 1 in the first position, then 2, then 3 as the formula is filled to the right. Because the second part of the range expands horizontally, the count grows with it.

Another practical use is boundary checking. If a formula depends on a valid column number inside a source range, COLUMNS can tell you how many columns are available before the formula goes too far. That makes it useful in INDEX-based formulas and other setups where the width of the source range matters.

• Use COLUMNS when you need the width of a range.
• Use an expanding range with COLUMNS to build a running horizontal counter.
• Use COLUMNS in checks when another formula should stay inside the width of a table or array.

Example 1 - Count the Columns in a Fixed Range

This formula returns the width of the range, not the number of filled cells. If the range runs from column A to column E, COLUMNS returns 5. This is useful when a worksheet needs to know the physical width of a table.

=COLUMNS(A1:E1) // Returns 5

Example 2 - Count a Spill Range

When a formula spills across multiple columns, COLUMNS can measure the width of that live result with the # operator. That is helpful in newer Excel models where the result width may change over time.

=COLUMNS(B1#) // Returns the current spill width

Example 3 - Count Columns in an Array Constant

COLUMNS also works with array constants. In {1,2,3;4,5,6}, there are three vertical positions across, so the function returns 3. This is useful when a formula includes a fixed array inside it.

=COLUMNS({1,2,3;4,5,6}) // Returns 3

Example 4 - Build a Running Horizontal Counter

The range $A$1:B1 starts at a fixed left column and expands across. That means COLUMNS returns 2 here. If the formula is copied one column to the right, the range becomes wider and the result increases automatically.

=COLUMNS($A$1:B1) // Returns 2

Conclusion Recap

• Main job: COLUMNS counts how many columns are in a range or array.
• Important difference: COLUMNS returns size, while COLUMN returns position.
• Useful patterns: Table width checks, spill measurement, horizontal counters, and boundary checks.
• Argument rule: COLUMNS always needs a range or array to measure.