Why does COLUMNS return 1 for a single-cell reference?

A single cell is considered a 1 × 1 array, so it has exactly one column. COLUMNS therefore reports 1 regardless of the cell's content.

Can COLUMNS be used on a named range?

Yes. If you define a named range that covers a rectangular block, COLUMNS(named_range) returns the number of columns within that block.

Does COLUMNS count hidden columns?

COLUMNS counts all columns in the reference, hidden or visible. To exclude hidden columns you would need to combine other functions such as SUBTOTAL with a helper column.

What happens if the array contains error values like #N/A?

COLUMNS ignores the contents of the cells; it only inspects the array's dimensions. Therefore, error values inside the array do not affect the column count.

COLUMNS function

COLUMNS returns the number of columns in a given array or range, letting you quickly determine a table's width.

=COLUMNS(array)

Generate a COLUMNS formula

Describe what you need. The generator will reach for COLUMNS where COLUMNS is the right tool, and tell you when it is not.

Arguments

How COLUMNS reads its arguments

Argument	Required	Description
array	Required	A required range reference or array literal; if omitted or not an array, Excel returns #VALUE!.

Returns

A single numeric value representing the column count of the supplied array.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count columns in the full timesheet

Task	Owner	Start Date	Due Date	Hours Logged
Design	Alice	2024-01-01	2024-01-05	8
Development	Bob	2024-01-03	2024-01-10	12
Testing	Carol	2024-01-08	2024-01-12	6

=COLUMNS({"Task","Owner","Start Date","Due Date","Hours Logged";"Design","Alice","2024-01-01","2024-01-05",8;"Development","Bob","2024-01-03","2024-01-10",12;"Testing","Carol","2024-01-08","2024-01-12",6})

Result: 5

The array literal contains five fields across every row: Task, Owner, Start Date, Due Date, and Hours Logged. COLUMNS simply counts those fields, ignoring the number of rows, and returns 5.

2. Determine width of a trimmed view (Task and Owner only)

Task	Owner
Design	Alice
Development	Bob
Testing	Carol

=COLUMNS({"Task","Owner";"Design","Alice";"Development","Bob";"Testing","Carol"})

Result: 2

Here the array literal has been reduced to the first two columns of the timesheet. COLUMNS counts those two columns and returns 2, regardless of how many rows are present.

3. Show effect of TRANSPOSE on column count

Task

Owner

Start Date

Due Date

Hours Logged

=COLUMNS(TRANSPOSE({"Task","Owner","Start Date","Due Date","Hours Logged"}))

Result: 1

TRANSPOSE flips the single-row array into a single-column array (5 rows, 1 column). COLUMNS then reports the width of that transposed array, which is 1.

Common errors

Which COLUMNS error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The argument supplied to COLUMNS is not an array or range, such as a plain number or text string.	Wrap the value in an array literal (e.g., ={42}) or reference a proper cell range.
#REF!	COLUMNS is asked to evaluate a reference that points to a deleted sheet or an invalid range.	Correct the sheet name or range address so the reference points to an existing location.
#NULL!	Using the space operator between two non-overlapping ranges (e.g., A1:A5 B1:B5) creates an intersection that does not exist.	Replace the space with a comma to form a union (A1:A5, B1:B5) or refer to a single contiguous range.

Tips and when to use something else

Use COLUMN(A1) when you need the column number of a single cell rather than the width of a table.
If you want the number of rows, call ROWS instead of COLUMNS.
When you need the position of a specific header within the timesheet, prefer MATCH over COLUMNS.
For dynamic width calculations after filtering, combine SUBTOTAL with COUNTA rather than COLUMNS.

Related functions

COLUMNCOLUMN returns the column number of a given reference, or the column of the cell containing the formula when no reference is supplied.ROWSROWS returns the number of rows in a given array or range, counting every row including headers and blank rows within the reference.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.

Frequently asked questions

Why does COLUMNS return 1 for a single-cell reference?: A single cell is considered a 1 × 1 array, so it has exactly one column. COLUMNS therefore reports 1 regardless of the cell's content.
Can COLUMNS be used on a named range?: Yes. If you define a named range that covers a rectangular block, COLUMNS(named_range) returns the number of columns within that block.
Does COLUMNS count hidden columns?: COLUMNS counts all columns in the reference, hidden or visible. To exclude hidden columns you would need to combine other functions such as SUBTOTAL with a helper column.
What happens if the array contains error values like #N/A?: COLUMNS ignores the contents of the cells; it only inspects the array's dimensions. Therefore, error values inside the array do not affect the column count.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator