COLUMNS Function in Google Sheets: Syntax, Examples & Troubleshooting

• Introduced: Original introduction date is not established by the official history reviewed.
• Availability: Documented in the rolling Google Sheets service. This guide covers ordinary worksheet formulas in the web workflow; it does not establish separate mobile or connected-database behavior.
• Checked: October 14, 2026, against the official COLUMNS reference. Documentation and independent calculations; no native application execution.

=COLUMNS(range)

The range argument is required. Google also documents array input. A one-cell range has width one; a tall single-column range also has width one. COLUMNS measures shape rather than the worksheet position of the first column.

The function returns one number. It does not return each column’s number or copy the source data. There is no optional direction flag or default omitted range in this syntax.

Enter these formulas away from the ranges being described:

=COLUMNS(B2:E8)
=COLUMNS(B2:B8)
=COLUMNS(E8)
=COLUMNS(Z2:AB4)

The expected answers are 4, 1, 1, 3. Z, AA and AB are three consecutive worksheet columns; crossing from one-letter to two-letter column labels does not add an extra column.

For B2:E8, count B, C, D and E. There are seven selected rows, but that number belongs to the separate ROWS calculation. A rectangular range’s width and height answer different questions.

On a blank sheet, put East in B2 and West in E2, leaving C2:D2 genuinely empty:

=COLUMNS(B2:E2)

The expected result is 4, not 2. The two blank positions remain part of the selected four-column range. This is a geometric count, not a count of labels.

If you need to count populated cells, use a suitable counting function such as COUNTA and consider how it treats formula-returned empty text. COLUMNS is useful precisely when the report requires a fixed-width shape even before all its fields have been filled.

Suppose the intended export block is B2:E6, covering four fields and five records:

=IF(COLUMNS(B2:E6)=4,"Width OK","Check width")

The expected result is Width OK. If the formula is changed to inspect B2:F6, the result is Check width, because the selected block now has five columns.

This check verifies only the supplied range width. It does not detect whether the field names are correct, whether Amount was exchanged with Quantity, or whether required values are missing. Pair a shape check with explicit heading and content checks when validating an import.

=COLUMNS(SPLIT("red|green|blue","|"))

The expected result is 3. SPLIT creates one output field per delimiter-separated piece in this uncomplicated case; COLUMNS measures that result without displaying the three fields separately.

For =COLUMNS(SPLIT("red||blue","|")), the expected result is 2, because Sheets SPLIT removes empty pieces by default. With =COLUMNS(SPLIT("red||blue","|",TRUE,FALSE)), the expected result is 3, preserving the empty middle field. The difference comes from SPLIT’s options, not from COLUMNS changing its counting rule. See the SPLIT guide.

Enter four labels in A2:A5. Then evaluate:

=COLUMNS(TRANSPOSE(A2:A5))

The expected result is 4. The source has four rows and one column; transposing it creates one row and four columns. No label text is needed to calculate that shape.

For a two-by-three source B2:D3, transposition produces three rows and two columns, so =COLUMNS(TRANSPOSE(B2:D3)) is expected to return 2. The TRANSPOSE guide shows the cell-by-cell mapping.

Check range boundaries before changing the counting function. B2:E8 and B2:F8 differ by one column even if F is currently empty. Avoid substituting COLUMN, which reports a worksheet coordinate rather than width.

If a nested array-producing expression fails, COLUMNS does not repair that earlier failure. Evaluate the inner expression on its own, inspect its shape, and then restore the wrapper.

These range counts and composed examples were independently calculated; no native Sheets run is claimed. Record the service check date and locale when reproducing them. The formulas use English-locale commas. For conventional array dimensions, use the documented Google Sheets syntax rather than importing a desktop-specific array-entry shortcut.

• Google Docs Editors Help: COLUMNS
• Editorial testing standards