|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

COLUMNS

COLUMNS

  • Last updated on

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:

  • COLUMNS(A1:D1) returns 4, based on the current width of the header row.
  • INDEX(A1:D3, COLUMNS(A1:D1)) extends the lookup range's right edge to column D (today's last column), making the search range A1:D3.
  • VLOOKUP then searches for "Alice" (A3) in the first column of that range and returns the value from the column identified by COLUMNS(A1:D1) — currently column 4.

For this data set, it returns 65000 (Alice's Salary). If a column E were added, both COLUMNS(A1:D1) references would update to 5, expanding the lookup range and the returned column automatically — no formula edits needed.

 

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. 

DescriptionUse this function to determine the number of columns in a specified array or reference.SyntaxCOLUMNS(array...