Cell() Function

Cell() Function

The Excel Cell function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. 

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
",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.

Examples of the Excel Cell Function

In the following example spreadsheet, the Excel Cell function is used to return different properties of the cell A1. It should be noted that, in the spreadsheet, cell A1 is formatted with the Custom Format   #,##0; [Red] - #,##0.

Formulas:

	A	B
1	- 9,999	=CELL( "address", A1 )
2		=CELL( "col", A1 )
3		=CELL( "color", A1 )
4		=CELL( "contents", A1 )
5		=CELL( "filename", A1 )
6		=CELL( "format", A1 )
7		=CELL( "parentheses", A1 )
8		=CELL( "prefix", A1 )
9		=CELL( "protect", A1 )
10		=CELL( "row", A1 )
11		=CELL( "type", A1 )
12		=CELL( "width", A1 )

Results:

	A	B
1	- 9,999	$A$1
2		1
3		1
4		-9999
5		C:\[Book1.xlsx]Sheet1
6		,0-
7		0
8
9		1
10		1
11		v
12		8

Note that, in cell B6 of the above example spreadsheet, the "format" type is returned as ",0-". This is comprised of two parts: the ",0" part, which represents the #,##0 number format, and the "-" part, which indicates that the cell is formatted in color for negative values.

Note:

If the formatting of cell A1 in the above spreadsheet is changed, the formulas in cells B1-B12 will not immediately recalculate. You can force them to recalculate by pressing the F9 key.