Author: Oscar Cronquist Article last updated on June 07, 2022

The COLUMNS function allows you to calculate the number of columns in a cell range.

1. COLUMNS Function Syntax

COLUMNS(array)

Back to top

2. COLUMNS Function Arguments

array Required. A cell range for which you want to determine the number of columns.

Back to top

3. COLUMNS Function example

The example above shows that cell range C2:J2 has 8 columns.

Formula in cell B6:

=COLUMNS(C2:J2)

Back to top

4. Count columns in an array

<span class='notranslate'>COLUMNS</span> function array

The COLUMNS function also calculates the number of rows in an array.

=COLUMNS({83,89,63,81;54,68,45,2})

The array has four columns. An array uses delimiting values to separate columns and rows.  They may differ between users based on their regional settings.

The formula above uses comma as a column delimiting character and semicolon as a row delimiting character.

83,89,63,81;
54,68,45,2

It is easier to see that the array actually contains four columns if I put values after a semicolon on a new row.

Back to top

5. Count columns in a string

<span class='notranslate'>COLUMNS</span> function string

Formula in cell B6:

=COLUMNS(TEXTSPLIT(B3,",",";"))

5.1 Explaining formula

Step 1 - Split string into an array

TEXTSPLIT(B3,",",";")

Step 2 - Count columns in the array

COLUMNS(TEXTSPLIT(B3,",",";"))

Back to top

6. Count columns in multiple cell ranges

<span class='notranslate'>COLUMNS</span> function multiple cell ranges

Formula in cell B12:

=COLUMNS(HSTACK(C2:J2,C5:F5,C8:G8))

6.1 Explaining formula

Step 1 - Join cell ranges horizontally

HSTACK(C2:J2,C5:F5,C8:G8)

Step 2 - Count columns

COLUMNS(HSTACK(C2:J2,C5:F5,C8:G8))

Back to top

7. Count columns in multiple Excel Tables

<span class='notranslate'>COLUMNS</span> function multiple Excel Tables

Formula in cell B8:

=COLUMNS(HSTACK(Table1, Table2))

7.1 Explaining formula

Step 1 - Join Excel Tables horizontally

HSTACK(Table1, Table2)

Step 2 - Count columns

COLUMNS(HSTACK(Table1, Table2))

Back to top