COLUMNS Function in Excel
COLUMNS is the horizontal twin of ROWS — it counts how many columns are in a range or array. Used mostly for dynamic-width lookups, sizing HLOOKUP arguments, and building formulas that adapt to variable-width data.
What it does: Returns the number of columns in array. =COLUMNS(A1:E1) returns 5. =COLUMNS(B2:D20) returns 3. Works on any range, named range, or array constant.
What COLUMNS does
COLUMNS answers "how wide is this range?" It counts columns, ignoring content. A range spanning 5 columns returns 5 whether those columns are empty, populated, or mixed.
Compared to ROWS, COLUMNS is used less often — most data is tall (many rows, few columns) rather than wide. But when data is wide, or when you're building formulas that iterate horizontally, COLUMNS becomes essential.
Syntax breakdown
array Required
The range or array whose column count you want. Cannot be a scalar. Named ranges, Table structured references (like Sales[#All] for the whole Table), and array constants all work.
5 real-world examples
Example 1: Count columns in a fixed range
How many columns in B1:M1?
=COLUMNS(B1:M1)Result: 12. Twelve columns (B through M).
Example 2: Count columns in an entire Table
How many columns does the Sales Table have?
=COLUMNS(Sales[#Headers])Result: Number of Table columns. Referencing the headers gives you exactly one row × N columns — perfect for column counting.
Example 3: HLOOKUP with dynamic row index
HLOOKUP needs the row number of the value to return. Use COLUMNS to compute it based on table width:
=HLOOKUP(A2, LookupTable, COLUMNS(LookupTable), FALSE)Result: Returns the bottom row of the LookupTable for the match. Rarely useful — but shows how COLUMNS helps parametrize wide-format lookups.
Example 4: Get the last column of a range
What's the value in the last column of the current row (for a range B2:M2)?
=INDEX(B2:M2, COLUMNS(B2:M2))Result: Value in M2 (position 12 of the range). Modern alternative: =TAKE(B2:M2, , -1) in Excel 365.
Example 5: Detect wide vs tall arrays
Check whether a range or array is wider than it is tall:
=IF(COLUMNS(range) > ROWS(range), "Wide", "Tall or square")Result: Text categorization. Useful in advanced array formulas that need to handle orientation dynamically — for example, deciding whether to TRANSPOSE before further processing.
Common errors and how to fix them
The argument is a scalar, not a range or array. COLUMNS needs something with column dimension. Ensure you're passing a proper range reference.
The referenced range no longer exists. Usually happens after deleting columns that were part of a range. Update the range or restore the columns.
If you have columns you don't want counted (like sequential IDs), reference a specific subset: =COLUMNS(Sales[[Region]:[Amount]]) counts only the range between two named columns.
Because A:A is a single column reference — one column wide, entire sheet tall. That's 1. For a multi-column range, specify one: A:E returns 5.
COLUMNS vs alternatives
| Function | Counts what | Best for |
|---|---|---|
| COLUMNS | Total columns in a range | Width detection, HLOOKUP arg, array orientation |
| ROWS | Total rows in a range | Height detection (much more common) |
| COUNTA(1:1) | Non-empty cells in row 1 | Counting active columns of data |
| Number of Table columns | Table-specific count | COLUMNS(Sales[#Headers]) |
Version compatibility
Download the practice workbook
COLUMNS-based patterns including dynamic-width lookups and array-orientation detection.
Related functions
Frequently asked questions
What's the difference between COLUMN and COLUMNS?
COLUMN returns a column number — e.g., 3 for C1. COLUMNS returns a column count — e.g., 5 for A1:E1. Similar names, different jobs. Singular = one cell's position; plural = how many columns in a range.
How do I count columns with data in row 1?
Use COUNTA on row 1: =COUNTA(1:1). This counts populated cells, which usually equals populated columns if row 1 has your headers. COLUMNS on a whole row (like =COLUMNS(1:1)) returns 16,384 — the sheet's full column count.
Can COLUMNS work with spilled dynamic arrays?
Yes. =COLUMNS(A2#) counts columns in whatever spills from A2. Handy for building follow-up formulas that need to know the width of an upstream FILTER or SORT result.
What's the maximum value COLUMNS can return?
16,384 — Excel's total column count in modern versions. Whole-sheet references like =COLUMNS(1:1) return this.
Is COLUMNS volatile?
No. COLUMNS is non-volatile — recalculates only when the range changes. Same for ROWS. Safe to use extensively.
Can COLUMNS help me build resizable HLOOKUP or XLOOKUP?
Yes. Use COLUMNS to compute the row-index or column-index argument dynamically based on the lookup table's dimensions. In XLOOKUP, you rarely need this — XLOOKUP works with column references directly. In HLOOKUP, COLUMNS is more useful for parametric row counts.
How do I get columns of an entire Excel Table?
=COLUMNS(TableName[#Headers]) is the cleanest way — headers are one row × N columns. Or =COLUMNS(TableName[#All]) for total column count including hidden columns.
Build width-aware formulas — with the Sheets & Cells AI Add-in
The Add-in helps you write formulas that adapt to variable-width data — using COLUMNS for dynamic HLOOKUP arguments, orientation detection, and array sizing. All inside Excel.
Learn about the Add-in →