Excel COLUMNS Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 13 of 105 · Full Excel course

The Excel COLUMNS function returns the number of columns in a range or array. COLUMNS in Excel counts every column in the reference, whether or not it holds data, so COLUMNS(A1:C3) is 3 and COLUMNS(B:E) is 4. It is most useful for building formulas that adapt automatically when a table gets wider.

Table of Contents

COLUMNS syntax

=COLUMNS(array)

Arguments

Argument	Required	Meaning
array	Required	A range, named range, structured Table reference or array constant whose columns you want to count.

COLUMNS returns a positive whole number. Unlike COLUMN, it does not care where the range sits on the sheet, only how wide it is.

Step-by-step example

=COLUMNS(A1:C3)

Excel measures the width of A1:C3.
The range spans columns A, B and C.
The result is 3. The number of rows (3) has no effect on the answer.

Similarly, =COLUMNS(A1:B10) returns 2 and =COLUMNS(Table1) returns the number of fields in the table.

Practical use cases

1. Return the last column of a table with INDEX

=INDEX(A2:F2, COLUMNS(A2:F2))

Always reads the right-most column, even after you insert columns inside the range.

2. VLOOKUP that returns the final column

=VLOOKUP(H2, A2:F100, COLUMNS(A2:F100), FALSE)

3. Sequential numbers across a row (all versions)

=COLUMNS($A$1:A1)

Fill right: the expanding range produces 1, 2, 3, … and never breaks when rows are inserted above, unlike COLUMN()-based counters.

4. Check that two ranges are the same width before SUMPRODUCT

=IF(COLUMNS(B2:G2)=COLUMNS(B5:G5), SUMPRODUCT(B2:G2, B5:G5), "Width mismatch")

5. Count total cells in a range

=ROWS(A1:F20)*COLUMNS(A1:F20)

6. Dynamic HLOOKUP row and column checks

=IF(MATCH(H1, A1:F1, 0)<=COLUMNS(A1:F1), "Found", "Missing")

Common mistakes and errors

#VALUE! or a prompt for an argument – COLUMNS() with nothing inside is invalid; the array argument is required.
#REF! – the range was deleted after the formula was written.
Counting only filled columns – COLUMNS counts every column in the reference. To count columns that contain data, use COUNTA(A1:F1) on the header row instead.
Confusing COLUMNS with COLUMN – COLUMNS(D1:F1) is 3; COLUMN(D1:F1) is 4 (the first column’s position).
Union references – COLUMNS((A1:B1, D1:F1)) counts only the first area (2). Use AREAS or add separate COLUMNS calls.

Tips and best practices

Use structured references (COLUMNS(Table1)) so the count updates automatically when fields are added.
Pair with INDEX for the last column instead of hard-coding a column number in VLOOKUP.
Use COLUMNS($A$1:A1) for horizontal serial numbers that renumber themselves when columns are deleted.
Validate array sizes before SUMPRODUCT or MMULT with COLUMNS and ROWS to avoid #VALUE!.
Remember it counts structure, not data; use COUNTA on the header row for filled columns.