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
A number representing column count.
Syntax
=COLUMNS (array)
Arguments
- array - A reference to a range of cells.
Usage notes
Use the COLUMNS function to get the number of columns in a reference. For example, the formula =COLUMNS(A1:F1) returns the number 6.
Array can be an array, an array formula, or a reference to a single contiguous group of cells.
COLUMNS formula examples
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...
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:
=...
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 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(...
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...
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),...
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...
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
