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 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...
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...
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 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(...
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 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
