Purpose

Return value

Syntax

=COLUMNS(array)
  • array - A reference to a range of cells.

Using the COLUMNS function

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

More details here .

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 .

Purpose

Return value

Syntax

=FIELDVALUE(value,field_name)
  • value - The data type with field values.
  • field_name - The field name provided as a text value.

Using the FIELDVALUE function

The Excel FIELDVALUE function extracts a given field value from a Data Type . The field is specified by name and provided as a text value. Use the FIELDVALUE function to retrieve a field value by name from linked data types like Stocks, Geography, Food, Currency, and more.

Examples

To retrieve a field value from a linked data type, provide the field name as text in double quotes (""). For example, with a city in cell A1, linked to a Geography data type, you can request population data like this:

=FIELDVALUE(A1,"city population")

In the example shown, the formula in cell C5, copied down, is:

=FIELDVALUE(B5,"city population")

The result is population data for the 12 cities listed in B5 to B16.

Alternative syntax

The FIELDVALUE function is an alternative the “dot” syntax for retrieving a field value from a data type. The two formulas below return the same result:

=FIELDVALUE(B5,"area")
=B5.area

Note square brackets ([]) are required for field names that contain spaces:

=FIELDVALUE(B5,"city population")
=B5.[city population]

When the field name is a single word, the brackets are not required

Trapping errors

In column D of the example, FIELDVALUE is used to extract “Area” like this:

FIELDVALUE(B5,"area")

This returns a #FIELD! error for cities where area is not available. To trap this error and return an empty string ("") where there are errors, the IFERROR function is used in cell D5 like this:

=IFERROR(FIELDVALUE(B5,"area"),"")

As a result, the cells for Cairo, Beijing, Istanbul, and Mexico City display nothing instead of a #FIELD! error.