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
To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is:
{=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(...
If you need to count the total number of cells in a rectangular range, you can do so with the ROWS and COLUMNS functions. In the generic form of the formula (above) rng represents a range of cells.
In the example, the...
To count columns in an Excel table, you can use the COLUMNS function. In the example shown, the formula in I4 is:
=COLUMNS(Table1)
How this formula works
This formula uses structured referencing, a syntax that...
To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions.
In the example shown, the formula in G6 is:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data...
You can get the last column in a range with a formula based on the COLUMN function.
In the example shown, the formula in cell F5 is:
=MIN(COLUMN(data))+COLUMNS(data)-1
where data is a named range for B5:D10
How...
If you need to count the number of columns in a range, use the COLUMNS function. In the generic form of the formula (above) rng represents a range of cells.
In the example, the active cell contains this formula:
=...
To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data),...
Related videos
Because tables support structured references, you can learn a lot about a table with basic formulas. In this video, we run through about a dozen examples. It's a nice demo of how formulas and tables work together to save you time.
In this video gives a brief introduction to structured references. Structured references is just a fancy name for formulas that use table names instead of normal cell references. This sounds boring, but it's actually cool and useful.
Related functions
Good links
