Description
Use this function to determine the number of columns in a specified array or reference.
Syntax
COLUMNS(array)
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
array |
Yes | The range or array to use for calculating the number of columns. | A cell range, a formula that returns a cell range, or a reference to a cell containing a cell range. |
Examples
Sample data
A |
B |
C |
D |
|
|---|---|---|---|---|
| 1 | Name | Age | City | Salary |
| 2 | John | 35 | New York | 75000 |
| 3 | Alice | 28 | Chicago | 65000 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Count the number of columns in the full data set. | =COLUMNS(A1:D3) |
This formula counts the number of columns in the range A1:D3. For this data set, it returns 4 (Name, Age, City, Salary). |
| Count the number of columns using just the header row. | =COLUMNS(A1:D1) |
COLUMNS only depends on how wide a range is, not how many rows it has, so this returns the same value as counting the full table. Result: 4 |
| Dynamically return the header name of the last column, so the formula still works if columns are added or removed. | =INDEX(A1:D1, COLUMNS(A1:D1)) |
COLUMNS(A1:D1) returns 4, so INDEX returns the value in the 4th position of the header row. For this data set, it returns Salary. If a column were added to the table, COLUMNS would return 5 and this formula would automatically point at the new last column instead of Salary. |
| Dynamically retrieve a specific employee's value from the last column, so a lookup keeps working if columns are added or removed. | =INDEX(A2:D2, COLUMNS(A2:D2)) |
COLUMNS(A2:D2) returns 4, so INDEX returns the 4th value in John's row.Result: 75000 |
| Count how many populated columns exist in a row, to confirm no columns are missing data. | =COUNTA(A2:D2) |
Counts the non-blank cells in John's row. For this data set, it returns 4, matching the 4 columns identified by COLUMNS(A2:D2) above — confirming none of the columns are empty. |
| Build a lookup whose search range and return column both expand automatically as columns are added to the table. | =VLOOKUP(A3, A1:INDEX(A1:D3, COLUMNS(A1:D1)), COLUMNS(A1:D1), FALSE) |
This formula works in the following manner:
For this data set, it returns 65000 (Alice's Salary). If a column E were added, both |
Notes
- Column numbers start from 1, not 0. So "A" is column 1.
Tips
- COLUMNS can be combined with other functions, including:
- FILTER: To select specific columns from filtered data.
- SORT: To rearrange columns.
- UNIQUE: To extract unique values from selected columns.
- SEQUENCE: To create dynamic column selections.
- INDEX: For advanced column referencing.
- MATCH: For dynamic column selection based on criteria.
- TRANSPOSE: To reorient selected columns.
- CHOOSEROWS: For more complex data selection.
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.