Spreadsheet Practice

COLUMNS function

Quick answer

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)

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.

Available in
Every current version
Category
Lookup functions
Returns
The number of columns.
Google Sheets
Works the same way in Google Sheets.

Syntax

=COLUMNS(array)

ArgumentRequired?What it is
arrayRequiredA range, an array, or a formula that returns an array.

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

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

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
RegionJanFebMarAprResult
North413845474
South5249556042.75
2

Practice COLUMNS

Graded instantly, then re-checked on changed data.

Average over all months

Blank months had no spend. In H2, calculate the average spend per month over all six months: the row total divided by the number of month columns. Use COLUMNS, then fill down to H4.

Common mistakes

What you seeWhy, and how to fix it
Expecting a count of filled cellsCOLUMNS counts columns whether or not they hold data. Use COUNTA across the row to count filled cells.
Mixing up COLUMNS and COLUMNCOLUMN 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.

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.

Answers that use COLUMNS

In other languages

COLUMNS is COLUMNAS in Spanish Excel and SPALTEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .