# COLUMNS function in Excel

> COLUMNS counts how many columns a range or array has, whether or not the cells contain anything.

Source: https://spreadsheetpractice.com/functions/columns · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** COLUMNS counts how many columns a range or array has, whether or not the cells contain anything.
- **Syntax:** `=COLUMNS(array)`
- **Most common use:** `=COLUMNS(B1:E1)` returns 4 (Four months across)
- **Available in:** Every current version
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** The number of columns.
- **Google Sheets:** Works the same way in Google Sheets.

## What it does

COLUMNS counts how many columns a range or array has, whether or not the cells contain anything. It is handy for averaging across a row of months that includes blanks, checking how wide a lookup table is, and building column numbers that grow as a formula is filled to the right.

## Syntax

`=COLUMNS(array)`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | A range, an array, or a formula that returns an array. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | `=COLUMNS(B1:E1)` | 4 | Four months across |
| G3 | `=SUM(B2:E2)/COLUMNS(B2:E2)` | 42.75 | Average per month for North |
| G4 | `=COLUMNS($B$1:C1)` | 2 | A range that grows as you fill right: 2 by the second month |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Apr |  | Result |
| 2 | North | 41 | 38 | 45 | 47 |  | 4 |
| 3 | South | 52 | 49 | 55 | 60 |  | 42.75 |
| 4 |  |  |  |  |  |  | 2 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| Expecting a count of filled cells | COLUMNS counts columns whether or not they hold data. Use COUNTA across the row to count filled cells. |
| Mixing up COLUMNS and COLUMN | COLUMN returns a column number, such as 3 for column C. COLUMNS returns how many columns a range spans. |

## Tips

- In VLOOKUP, COLUMNS($A$1:B1) as col_index_num returns 2, then 3 and 4 as you fill right, so one formula pulls several columns.

## Related functions

- [ROWS](https://spreadsheetpractice.com/functions/rows): ROWS counts how many rows a range or array has, whether or not the cells contain anything.
- [COLUMN](https://spreadsheetpractice.com/functions/column): COLUMN returns the column number of a cell: COLUMN(D1) is 4, and COLUMN() with nothing inside returns the column of the formula's own cell.
- [COUNTA](https://spreadsheetpractice.com/functions/counta): COUNTA counts every cell that isn't empty: text, numbers, dates, errors and even formulas that return empty text.

## Questions

### How do I count the columns in a range?

=COLUMNS(A1:F1) returns 6, the number of columns in the range, whether they are filled or not.

### How many columns does my table have?

=COLUMNS(Table1) returns the width of an Excel table, and =ROWS(Table1) returns the number of data rows.

## Practice

A graded COLUMNS exercise ("Average over all months") is on [the COLUMNS page](https://spreadsheetpractice.com/functions/columns). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
