|
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

CELL

CELL

  • Last updated on

Description

Use this function to return information about the formatting, location, or contents of a cell.

This function can retrieve things such as address and filename, as well as detailed info about the formatting used in the cell. See below for a full list of information available.

Note: Formulas that use CELL have language-specific argument values and will return errors if calculated using a different language.
For example, if you create a formula containing CELL while using German, that formula will return an error if the workbook is opened using the French version. If it is important for others to open your workbook using different languages, consider either using alternative functions or allowing others to save local copies in which they revise the CELL arguments to match their language.

 

Syntax

CELL(info_type, [reference])

 

Inputs

This function has the following arguments:

Name Required Description Valid input
info_type Yes The information type to be considered.

A text string that matches the list below in quotation marks, a cell reference, or a formula which results in a cell reference.
 

Info types

The following info_types can be used with this function:

  • address – Returns the address of the cell identified in reference (as text). Note that this returns the address as a fixed value ($A$1). 
  • col – Returns the column number of the cell identified in reference.
  • contents – Returns the contents of the cell identified in reference. If the cell contains a formula, only the result of the formula is returned.
  • row – Returns the row number of the cell identified in reference.
  • type – Returns a character that corresponds to the type of data in the cell identified in reference: 
    "b" (blank) when the cell is empty
    "l" (label) if the cell contains text
    "v" (value) if the cell contains anything else
reference  Yes The address for the cell containing the information. A cell ID or a formula which results in a single cell ID. 
Note: The following Excel info_types are not supported: color, filename, format, parentheses, prefix, protect, row, width.

 

Example

Sample data

  A B C
1 red 56 address
2 green 39 col
3 orange 15 contents
4 red 92 row
5 blue 22 type

Sample formulas

Use case Formula Explanation and Result
Determine if a cell contains a specific term.

=CELL("contents",A1)="red"

=CELL("contents",A2)="red"

This formula works in the following manner:

  • CELL("contents",A1): Returns the value of A1, which is "red".
  • ="red": The comparison "red" = "red" is TRUE.

For this data set, the first formula returns TRUE.

This formula works in the following manner:

  • CELL("contents",A2): Returns the value of A2, which is "green".
  • ="red": The comparison "green" = "red" is FALSE.

For this data set, the second formula returns FALSE.
Return the address of a specified cell. =CELL("address", A2)

This formula works in the following manner:

  • CELL(: This is the function itself.
  • "address": This is the info_type, asking for the cell's address.
  • A2: This is the reference.

For this data set, this formula returns $A$2.
Return the column ID of a specified cell. =CELL("col", B1)

This formula works in the following manner:

  • CELL(: This is the function itself.
  • "col": This is the info_type, asking for the cell's column number.
  • B1: This is the reference.

For this data set, this formula returns 2 (because B is the second column).
Return the type of a specified cell.

=CELL("type",A1)

=CELL("type",B1)

This formula works in the following manner:

  • CELL("type",A1): Checks the data type of A1, which contains text. It returns "l" for "label" (text).

For this data set, this formula returns l (label)

This formula works in the following manner:

  • CELL("type",B1): Checks the data type of B1, which contains a number. It returns "v" for "value" (number).

For this data set, this formula returns v (value)
Return the contents of a specified cell. =CELL("contents", A1)

This formula works in the following manner:

  • CELL(: This is the function itself.
  • "contents": This is the info_type, asking for the cell's value.
  • A1: This is the reference.

For this data set, this formula returns red.
Return the address of a specified cell using the contents of another cell as a reference for the info_type value. =CELL(C1,B1)

This formula works in the following manner:

  • CELL(: This is the function itself.
  • C1: This is the info_type. The formula looks at C1 and finds the text "address".
  • B1: This is the reference.
  • The formula effectively becomes =CELL("address", B1) and returns the absolute address of B1.

For this data set, this formula returns $B$1.
Determine the content type for a cell, and return a specified string identifying the type. =IFS(CELL("type",B1)="l","Text", CELL("type",B1)="v", "Number",CELL("type",B1)="b", "Empty",TRUE,"Other")

This formula works in the following manner:

  • CELL("type",B1): This inner function is evaluated first, returning "v" because B1 contains a number (value).
  • IFS(:The IFS function (this sets the conditions for the call)
  • This is the function itself.
  • "v"="l","Text": The first test ("v" = "l") is FALSE.
  • "v"="v", "Number": The second test ("v" = "v") is TRUE. The formula returns "Number" and stops.
  • The remaining tests are not evaluated.

For this data set, this formula returns Number (because CELL returned "v").

 

Notes

  • CELL can only apply to a single cell.
  • CELL is a volatile function and may cause performance issues in large or complex worksheets.
  • Wildcards do not work with this function.
  • CELL returns information as a text value, regardless of the type of information requested.

 

Tips

  • Use CELL to gather information about cell formatting and contents for dynamic reporting.
  • Use CELL in conditional formatting (Example: setting =CELL("type",B1)="b" as the condition formula will apply the specified formatting to all text cells included in the conditional range. Note that B1 does not need to have a text type.)
  • You can combine CELL with functions such as IF or ISNUMBER for conditional formatting and validation.
  • CELL is useful for auditing and troubleshooting your worksheets.

 

Related functions

CELL
CHILDREFS
HLOOKUP
INDEX
MATCH
VLOOKUP
XLOOKUP
XMATCH

Was this article helpful?
0 out of 0 found this helpful
DescriptionUse this function to return information about the formatting, location, or contents of a cell.This functio...