Excel COLUMNS Function
Summary
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.
Purpose
Get the number of columns in an array or reference.
Return value
Number of columns
Syntax
=COLUMNS (array)
Arguments
- array - A reference to a range of cells.
Version
Usage notes
The COLUMNS function returns the count of columns in a given reference as a number. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns. COLUMNS takes just one argument, called array, which should be a range or array.
Examples
Use the COLUMNS function to get the column count for a given reference or range. For example, there are 6 columns in the range A1:F1 so the formula below returns 6:
=COLUMNS(A1:F1) // returns 6
The range A1:Z100 contains 26 columns, so the formula below returns 100:
=COLUMNS(A1:Z100) // returns 26
You can also use the COLUMNS function to get a column count for an array constant:
=COLUMNS({1,2,3,4,5}) // returns 5
Although there is no built-in function to count the number of cells in a range, you can use the COLUMNS function together with the ROWS function like this:
Notes
- Array can be a range or a reference to a single contiguous group of cells.
- Array can be an array constant or an array created by another formula.
- To count rows, see the ROW function.
- To get column numbers, see the COLUMN function.
- To lookup a column number, see the MATCH function.
COLUMNS formula examples
Note: this formula uses two named ranges: "names" refers to C5:C11, and "groups" refers to B5:B11. These names are defined in the screen shot above as well.
The gist of this formula is this: we are using the SMALL...
The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14).
To get the last row used, we use...
The core of this formula is the ADDRESS function, which is used to return a cell address based on a given row and column. Unfortunately, the formula gets somewhat complicated because we need to use ADDRESS twice: once...
When working with Excel formulas, there are some situations where you want an array of sums rather than a single sum. Functions like SUM won't work, because they aggregate results and return a single value. The MMULT...
The COLUMNS function is fully automatic. When you provide a range to COLUMNS, it will return a count of all columns in the range. In the example, the formula in F6 returns 2, because there are 2 columns in the range B5...
In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to....
This formula uses structured referencing, a syntax that allows table parts to be referred to by name. When a table is referred to by the name only, Excel returns a reference to the data region of the table only. In...
There is no built-in function for counting the total numbers of cells in a range, so you need to use the ROWS and COLUMNS functions together. In the example, ROWS returns the total number of rows in B5:C10 (6), and...
When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains...
Although you can easily combine ranges in Excel with Power Query, it has traditionally been difficult to do with standard Excel formulas. However, the new dynamic array formulas in Excel 365 make it possible without too...
When working with Excel formulas, there are some situations where you want an array of sums rather than a single sum. Functions like SUM won't work, because they aggregate results and return a single value. The MMULT...
The first COLUMN function generates an array of 7 numbers like this:
{2,3,4,5,6,7,8}
The second COLUMN function generates an array with just one item like this:
{2}
which is then subtracted from the first array to...
Related videos
One of the most useful features of Excel Tables is that they create a dynamic range. In this video, we'll take a closer look at how table ranges work.
Because tables support structured references, you can learn a lot about a table with simple formulas. In this video, we'll look at some formulas you can use to query a table.
This video provides a brief introduction to structured references. Structured references are an easy way to refer to tables in Excel formulas.
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
