How to use the CELL Function (WS)

Description

The Microsoft Excel CELL function can be used to retrieve information about a cell. This can include contents, formatting, size, etc.

Syntax

The syntax for the CELL function in Microsoft Excel is:

CELL( type, [range] )

Parameters or Arguments

type

The type of information that you'd like to retrieve for the cell. type can be one of the following values:

Value	Explanation
"address"	Address of the cell. If the cell refers to a range, it is the first cell in the range.
"col"	Column number of the cell.
"color"	Returns 1 if the color is a negative value; Otherwise it returns 0.
"contents"	Contents of the upper-left cell.
"filename"	Filename of the file that contains reference.
"format"	Number format of the cell. See example formats below.
"parentheses"	Returns 1 if the cell is formatted with parentheses; Otherwise, it returns 0.
"prefix"	Label prefix for the cell. * Returns a single quote (') if the cell is left-aligned. * Returns a double quote (") if the cell is right-aligned. * Returns a caret (^) if the cell is center-aligned. * Returns a back slash (\) if the cell is fill-aligned. * Returns an empty text value for all others.
"protect"	Returns 1 if the cell is locked. Returns 0 if the cell is not locked.
"row"	Row number of the cell.
"type"	Returns "b" if the cell is empty. Returns "l" if the cell contains a text constant. Returns "v" for all others.
"width"	Column width of the cell, rounded to the nearest integer.

"format"

For the "format" value, described above, the values returned are as follows:

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

range

Optional. It is the cell (or range) that you wish to retrieve information for. If the range parameter is omitted, the CELL function will assume that you are retrieving information for the last cell that was changed.

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel CELL function examples and explore how to use the CELL function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following CELL examples would return:

=CELL("col", A1)
Result: 1

=CELL("address", A2)
Result: $A$2

=CELL("format", A2)
Result: P2