COLUMNS

What it does

COLUMNS(array)

COLUMNS(array) returns the number of columns in an array or reference. It is a measurement of shape, not of content: no cell is read, no value is coerced, and empty cells inside the range count exactly as much as filled ones.

The distinction worth stating at the top, because the names invite the mistake: COLUMNS counts columns, COLUMN locates one. COLUMNS(C1:E1) is 3; COLUMN(C1:E1) is the array 3, 4, 5.

Arguments

Microsoft's page names this argument array and describes it as an array, an array formula, or a reference to a range of cells. Two consequences readers trip over:

• A single cell is a 1×1 shape, so COLUMNS(A1) is 1 rather than an error.
• A multi-area reference — COLUMNS((A1:B2,D1:E2)) — has no single width. What COLUMNS reports for a union is not established here, and it is not obvious: the plausible answers (the first area's width, the bounding box's width, an error) are all defensible.

What COLUMNS does with a bare scalar that is neither array nor reference is likewise not established here.

Try it

Quirks

Result and edge cases

Return kind: Number — a positive integer.

• No coercion of contents. Element kinds are irrelevant. An array of errors has a width.
• Nothing is scanned. Unlike COUNT-style functions, COLUMNS does not visit elements, so an error inside the range does not become the result. This is the practical difference between a shape query and an aggregate: see the scan-policy discussion in coercion and lifting.
• A spill anchor (B1#) has a width that changes when the spill changes; COLUMNS(B1#) is the idiomatic way to ask how wide a dynamic result currently is, and its dependency behaviour is a property of the spill-anchor reference shape rather than of this function.
• A structured reference to several table columns has a width that tracks the table.

Errors

Microsoft's page does not publish an error table for COLUMNS. The expected failure is an argument that is neither an array nor a reference, for which #VALUE! is this family's conventional code — recorded here as expected, not verified. An error value passed as the argument propagates under the universal rule in coercion and lifting; note that this is different from an error inside the array, which does not affect the count.

How to implement

Notes for implementers

• Answer from the reference, not from the values. Materializing a whole-column reference to count its columns is correct and catastrophic. The RefsVisibleInAdapter preparation profile exists so this function can avoid exactly that.
• The multi-area case needs a declared policy, because there is no single right answer. An implementation must choose and record its choice rather than fall out of whatever its reference representation happens to make easy.
• 1×1 arrays. The pipeline's argument preparation collapses a single-cell reference to a scalar for values-only functions, but COLUMNS is not values-only, and the unit-array preservation caveat described in the call pipeline is exactly the sort of normalization that can silently change a shape query's answer.

Rust code

eager(
    COLUMNS,
    "COLUMNS",
    CallableArityShape::exact(1),
    &[Parameter::value_or_reference("array")],
    columns,
)

/// COLUMNS(array): ROWS for columns.
fn columns(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(COLUMNS, services);
    Site::finish(extent(&site, &arguments[0], false))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• ROWS is the same measurement on the other axis. Together they give an array's shape.
• COLUMN locates rather than counts, and the name similarity is the standing trap.
• AREAS counts the rectangles in a reference where COLUMNS measures extent. For a multi-area reference the two questions come apart, which is why AREAS exists.
• INDEX, OFFSET, TAKE, DROP, WRAPROWS all take width or height arguments that are frequently computed with COLUMNS.
• HLOOKUP's row_index_num and VLOOKUP's col_index_num are commonly written as COLUMNS($A$1:B$1) or similar so that the index advances as the formula is copied — one of the oldest idioms in spreadsheet practice, and one that XLOOKUP removes the need for.
• SEQUENCE(1, COLUMNS(a)) is the modern way to build an index vector matching an array's width.

Named variants

No named variants are recorded yet.