Purpose
Get the number of columns in an array or reference.
Return value
Number of columns
Syntax
=COLUMNS(array)- array - A reference to a range of cells.
How to use
The COLUMNS function returns the count of columns in a given reference as a number. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns. COLUMNS takes just one argument, called array, which should be a range or array.
Examples
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:F1 so the formula below returns 6:
=COLUMNS(A1:F1) // returns 6
The range A1:Z100 contains 26 columns, so the formula below returns 100:
=COLUMNS(A1:Z100) // returns 26
You can also use the COLUMNS function to get a column count for an array constant:
=COLUMNS({1,2,3,4,5}) // returns 5
Although there is no built-in function to count the number of cells in a range, you can use the COLUMNS function together with the ROWS function like this:
=COLUMNS(range)*ROWS(range) // total cells
=COLUMNS(A1:Z100)*ROWS(A1:Z100) // returns 2600
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.
- To count rows, see the ROW function.
- To get column numbers, see the COLUMN function.
- To lookup a column number, see the MATCH function.
COLUMNS function examples
Sum last n columns
In this example, the goal is to sum the last n columns in a set of data, where n is a variable that can be changed at any time. In the latest version of Excel, the easiest way to solve this problem is with the TAKE function . In older versions of Excel you can use the OFFSET function , as explained...
Total columns in range
The COLUMNS function is fully automatic. When you provide a range to COLUMNS, it will return a count of all columns in the range. In the example, the formula in F6 returns 2, because there are 2 columns in the range B5:C10: =COLUMNS(B5:C10) // count columns COLUMNS counts the number of columns in...
Count cells in range
The goal is to count the number of cells in a given range, regardless of whether the cells are empty or not. Although Excel has several functions designed to count cells based on their contents, there is no built-in function for counting the total number of cells in a range. The classic solution is...
XLOOKUP match any column
In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to. The challenge is that the code may be in any one of the six columns, and...
Last column number in range
When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range. If you want only the first column number, you...
Multiple matches into separate columns
In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns using common values, which in this case are the group...
Get relative column numbers in range
The first COLUMN function generates an array of 7 numbers like this: {2,3,4,5,6,7,8} The second COLUMN function generates an array with just one item like this: {2} which is then subtracted from the first array to yield: {0,1,2,3,4,5,6} Finally, 1 is added to get: {1,2,3,4,5,6,7} With a named range...
Average last n columns
In this example, the goal is to average the last n columns in a set of data, where n is a variable entered in cell K5 that can be changed at any time. Since more data may be added, a key requirement is to average amounts by position. For convenience, the values to average are in the named range...
Get address of named range or table
The goal is to return the full address of a range or named range as text. The purpose of this formula is informational, not functional. It provides a way to inspect the current range for a named range, an Excel Table, or even the result of a formula. The core of the solutions explained below is the...
Max of every nth column
In this example, the goal is to calculate the maximum value of every "nth" column in a row of data, where n is a variable entered in the named range M2 . This problem can be solved in several ways, as explained below. The explanation below also includes a formula that will work in older versions of...
Combine ranges
In this example, the goal is to combine ranges. With the introduction of the VSTACK function and the HSTACK function, this is quite a simple task. To combine ranges vertically, stacking one range on top of another, you can use the VSTACK function like this: =VSTACK(range1,range2) To combine ranges...
Count table columns
This formula uses structured referencing , a syntax that allows table parts to be referred to by name. When a table is referred to by the name only, Excel returns a reference to the data region of the table only. In this case, the entire table range is B4:F104 so Table1 returns the range B5:F105 to...
Address of last cell in range
The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14). To get the last row used, we use the ROW function together with the MAX function like this: MAX(ROW(data)) Because...
COLUMNS function videos
How Excel Table ranges work
In this video, we'll take a closer look at how table ranges work. One of the most useful features of Excel Tables is that they create a dynamic range. A dynamic range automatically expands to handle new data, so it works well for reports, pivot tables, or charts that need to show the latest...
Formulas to query a table
In this video, we'll look at some formulas you can use to query a table. Because tables support structured references, you can learn a lot about a table with basic formulas. On this sheet, Table1 contains employee data. Let's run through some examples. To start off, you can use the ROWS function to...
Introduction to structured references
In this video, I'll give a brief introduction to structured references. A structured reference is a term for using a table name in a formula instead of a normal cell reference. Structured references are optional, and can be used with formulas both inside or outside an Excel table. Let's take a look...
Related functions
ROWS Function
The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.
ROW Function
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
COLUMN Function
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
