COLUMNS in Excel: Count Width and Validate Table Layouts

• Introduced: Original introduction release not established by the official history reviewed.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official COLUMNS reference. Documentation check; examples independently calculated, not native-tested.

=COLUMNS(array)

Supply a range or an array expression. The result is a single numeric dimension. COLUMNS does not return the worksheet number of the last column, nor does it count only populated fields. Official COLUMNS reference

The plural name is important: COLUMN identifies a worksheet column position, whereas COLUMNS measures the number of columns in the supplied rectangle.

Put these headings in D2:H2: Code, Region, Status, Units, Amount. In D3:H3 enter P01, East, Paid, 12, 120. In D4:H4 enter P02, West, Pending, 5, 75.

The five fields have fixed positions. Keep the first two columns of the worksheet unused for this experiment so it is obvious that width is not the same as the final column’s worksheet number.

=COLUMNS(D2:H4)

Expected result: 5, because the rectangle spans D, E, F, G and H. Its three rows do not change the width.

=COLUMNS(D2:H2)

Expected result: 5 again. Measuring only the header row produces the same width as measuring the whole table.

Temporarily clear F2, the Status heading. Do not delete the worksheet column. Then compare:

=COLUMNS(D2:H2)
=COUNTA(D2:H2)

Expected results are 5 and 4 respectively. The width remains five, but only four headings are populated. Restore Status in F2 before continuing.

This is a useful export check: a correct width does not establish that every field has a valid heading. A production validation may need separate checks for dimension, missing names and unexpected names.

=INDEX(D3:H3,1,COLUMNS(D3:H3))

Expected result: 120. COLUMNS supplies the relative column position 5 inside D3:H3. INDEX returns that fifth value, which is Amount.

Using a worksheet column number instead would be wrong: H is column 8 on the sheet, but there is no eighth column inside this five-column reference. The function combination uses relative positions within the supplied range.

=IF(COLUMNS(D2:H4)=5,"Width OK","Check layout")

Expected result: Width OK. If the intended reference is accidentally shortened to D2:G4, the same test returns Check layout because the width is four.

This is deliberately a structural check. It does not prove that the fields are in the right order or that Amount contains numbers. Retain those separate checks rather than treating one successful dimension test as a complete data audit.

For a supported dynamic-array Excel edition:

=COLUMNS(SEQUENCE(2,4))

Expected result: 4. The inner array has two rows and four columns. Reversing its size arguments to SEQUENCE(4,2) makes COLUMNS return 2.

A TRANSPOSE changes orientation. =COLUMNS(TRANSPOSE(D2:H2)) returns 1, because the five-cell horizontal source becomes a one-column vertical array. Availability and array-entry behavior for the nested formula must be checked in the receiving Excel edition.

• A cleared field still counts toward the width because its position remains in the range.
• Hidden columns still belong to a reference unless the reference itself changes.
• COLUMNS alone cannot identify which field is missing.
• An error from an inner expression should be investigated separately from the dimension function.

For a stable report, keep its expected field count near the schema description and update both deliberately when the export layout changes.

• INDEX in Excel
• IF in Excel
• COUNTA in Excel
• SEQUENCE in Excel

Verification: These are fictional inputs and independently calculated expected results, not native Excel observations. See the editorial testing standards.