totn Excel Functions

MS Excel: How to use the 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.

The CELL function is a built-in function in Excel that is categorized as an Information Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the CELL function can be entered as part of a formula in a cell of a worksheet.

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.

Returns

The CELL function returns information about a cell.

Applies To

  • Excel for Office 365, Excel 2019, 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:

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"
Share on: