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 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 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...
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...
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:
=...
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
