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
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
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...
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...
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 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...
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...
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...
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...
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...
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....
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...
Related videos
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.
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.
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.
