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.
Usage notes
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:A10, so:
=COLUMNS(A1:F1) // returns 6
The range B1:D5 contains 3 columns:
=COLUMNS(B1:D5) // returns 3
You can also use the COLUMNS function to get a column count for an array constant:
=COLUMNS({1,2,3,4,5}) // returns 5
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.
COLUMNS formula examples
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...
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...
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...
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...
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...
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...
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...
Related videos
This video provides a brief introduction to structured references. Structured references are an easy way to refer to tables in Excel formulas.
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.
Related functions
