Excel COLUMNS function examples show how to make the best use of this function, which returns the count of columns in an array or reference.
Author: Debra Dalgleish
The COLUMNS function returns the count of columns in an array or reference.
To see how the COLUMNS function works, watch this short video. It shows some of the examples from this tutorial.
The COLUMNS function has the following syntax:
There is something to keep in mind when using COLUMNS:
In this example, there is a named Excel table -- RegionSales.
When a cell in the table is selected, you can see the table name on the Table Design tab, at the top of the Excel window.
With the COLUMNS function, you can refer to that table name, and count the number of columns in the RegionSales table.
Here is the formula in cell C2:
There are 4 columns in the table, and that is the result shown in cell C2.
In this example, the COLUMNS function is in a formula with SUM and INDEX. Those functions work together, and return the total amount in the last column of a named range.
Here is the named range, MyRange, in cells B4:E8.
In cell C2, this formula sums the numbers in the last column of the named range, MyRange:
Here's how that formula works:
See more INDEX function tips and examples on the INDEX and MATCH page.
In this example, the COLUMNS function counts the number of columns across the entire worksheet.
To get that count, the formula will refer to an entire row -- 1:1
Here is the column in cell C3:
There are 16384 columns in row 1 on this worksheet, and that is the result shown in cell C3.
To see the formulas used in these examples, get the COLUMNS function sample workbook. The file is zipped, and is in Excel xlsx file format. There are no macros in the workbook
Last updated: June 18, 2022 3:26 PM