The Excel COLUMNS function counts how many columns a range or array contains. Like its vertical twin ROWS, it measures geometry rather than content — and its star turn is building VLOOKUP column indexes that keep working even when someone inserts a column into the table. Don’t confuse it with COLUMN, which returns a position rather than a count.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | A range, array, or array formula whose columns you want to count. |
Geometry, not content. =COLUMNS(B1:G1) is 6 whether those cells are full or empty. To count non-empty cells across a row, use COUNTA.
Counting columns in ranges and arrays
The table below spans A1:G3 — a label column plus six months (highlighted):
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Metric | Jan | Feb | Mar | Apr | May | Jun |
| 2 | Sales | $8,200 | $7,900 | $8,600 | $9,100 | $9,800 | $10,400 |
| 3 | Expenses | $6,100 | $6,300 | $6,200 | $6,500 | $6,900 | $7,100 |
Whole rows and typed-in arrays work too:
Try it: interactive COLUMNS demo
Pick a range and see what COLUMNS returns — note how the row numbers never affect the answer.
Practical uses: insert-proof indexes and width checks
1. An insert-proof VLOOKUP column index. The expanding-range trick: lock the first corner at the table’s first column and let the second corner sit in the column you want. COLUMNS counts the distance — and keeps counting correctly if columns are inserted inside the table:
Compare that with a hard-coded 4, which silently returns the wrong column after the insert. (Or skip counting entirely with XLOOKUP or INDEX+MATCH.)
2. How wide is the spill? Dynamic array results vary in size; measure them:
3. Running count across a row — the horizontal version of the ROWS numbering trick:
4. Audit a table’s shape before array math that needs matching widths:
Errors & common pitfalls
Pitfall: COLUMNS is not a data count. It measures the reference, not what’s in it. =COLUMNS(B1:G1) is always 6 even if the row is empty. Use COUNTA(B1:G1) to count filled cells.
Pitfall: COLUMNS vs COLUMN. COLUMN answers “where?” (B1:G1 starts at column 2); COLUMNS answers “how many?” (6). Swapping them is the classic off-by-a-lot bug in lookup indexes.
Pitfall: lock the right corner. In the expanding-range trick COLUMNS($A$1:D$1), the first corner must be absolute and the second relative. Lock both and the count freezes; lock neither and copying down a row breaks nothing — but copying right stops expanding correctly.
#NAME? — typing problem. COLUMNS exists in every Excel version; #NAME? means a typo such as =COLUMS(...).
Practice workbook
Frequently asked questions
What's the difference between COLUMNS and COLUMN?
How does COLUMNS make a VLOOKUP survive inserted columns?
=VLOOKUP($A2, $A$1:$F$50, COLUMNS($A$1:D$1), FALSE). The range $A$1:D$1 physically stretches when a column is inserted inside it, so the count grows with the table and the lookup keeps pointing at the same data. A hard-coded 4 would silently go stale.What does =COLUMNS(3:3) return?
How do I count the columns of a spilled dynamic array?
=COLUMNS(E2#). The # operator grabs the whole spilled range; =COLUMNS(E2) alone is just 1.Does COLUMNS count hidden columns?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class