CELL

Using the Excel CELL Function

Summary

Excel's CELL function returns information about the formatting, contents, or location of a cell. For example, the CELL function could be used to test if a cell contains a numeric value, as opposed to a text value, prior to performing a calculation.

Syntax

=CELL (info_type, [reference])

Syntax Breakdown

Info_type
Required. A text value used to specify what cell information you want to return. The possible values include:

info_type Output
"address" Reference of first cell in reference (as text)
"col" Column number of cell in reference
"color" Value one (1) if the cell is formatted in color for negative values; otherwise zero (0)
"contents" Value of the upper-left cell in the reference
"filename" Filename, including the file path, of the file that contains the reference. Will return empty text (" ") if the worksheet that contains the reference hasn't been saved.
"format" Text value corresponding to the number format of the cell. Returns "-" at the end of the value if the cell is formatted in color for negative values. Returns "()" at the end of the value if the cell is formatted with parentheses for positive or all values. Text values for the various formats are shown in the table below.
"parentheses" If the cell is formatted with parentheses for positive or all values, one (1) is returned. Zero (0) is returned otherwise.
"prefix" Text value corresponding to label prefix of the cell. Single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, backslash (\) if the cell contains fill-aligned text, caret (^) if the cell contains centered text, and empty text ("") if the cell contains anything else.
"protect" If the cell is locked, 1. If the cell is not locked, zero (0).
"row" Row number of the reference cell.
"type" Text value corresponding to the type of data in the cell. If the cell is empty, it returns "b" for blank. If the cell contains a text constant, it returns "l" for label. If the cell contains anything else, it returns "v" for value.
"width" Column width of the cell, rounded to an integer.


Reference
Optional. The cell you want information about. If the reference argument is left blank, the information specified in into_type is returned for the last cell that was changed. If a range of cells is provided, only information for the upper left cell of the range is returned.


Usage Notes

CELL returns information about a cell in a worksheet. The information to be returned is specified by the info_type argument. A full list of info_type values is shown in the above table.

CELL Format Values
The following describes the text values that CELL returns when "format" is used in the info_type argument:

Format CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% P0
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm:ss AM/PM "D6"
h:mm AM/PM "D7"
h:mm:ss "D8"
h:mm "D9"