Share this page:

MS Excel: CELL Function (WS)

This Excel tutorial explains how to use the Excel CELL function with syntax and examples.

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 Microsoft Excel CELL function is:

CELL( type, [range] )

Parameters or Arguments

type is 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.

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 is 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

The CELL function can be used in the following versions of Microsoft Excel:

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

Type of Excel Function

The CELL function can be used in Microsoft Excel as the following 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:

Microsoft Excel

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

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

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

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