CELL Function
The CELL function is one of the information functions. It is used to return information about the formatting, location, or contents of a cell.
Syntax
CELL(info_type, [reference])
The CELL function has the following arguments:
| Argument |
Description |
| info_type |
A text value that specifies which information about the cell you want to get. This is the required argument. The available values are listed in the table below. |
| reference |
A cell that you want to get information about. If it is omitted, the information is returned for the last changed cell. If the reference argument is specified as a range of cells, the function returns the information for the upper-left cell of the range. |
The info_type argument can be one of the following:
| Text value |
Type of information |
| "address" |
Returns the reference to the cell. |
| "col" |
Returns the column number where the cell is located. |
| "color" |
Returns 1 if the cell is formatted in color for negative values; otherwise returns 0. |
| "contents" |
Returns the value that the cell contains. |
| "filename" |
Returns the filename of the file that contains the cell. |
| "format" |
Returns a text value corresponding to the number format of the cell. The text values are listed in the table below. |
| "parentheses" |
Returns 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0. |
| "prefix" |
Returns the single quotation mark (') if the text in the cell is left-aligned, the double quotation mark (") if the text is right-aligned, the caret (^) if the text is centered, and an empty text ("") if the cell contains anything else. |
| "protect" |
Returns 0 if the cell is not locked; returns 1 if the cell is locked. |
| "row" |
Returns the row number where the cell is located. |
| "type" |
Returns "b" for an empty cell, "l" for a text value, and "v" for any other value in the cell. |
| "width" |
Returns the width of the cell, rounded off to an integer. |
Below you can see the text values which the function returns for the "format" argument
| Number format |
Returned text value |
| 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 AM/PM |
D7 |
| h:mm:ss AM/PM |
D6 |
| h:mm |
D9 |
| h:mm:ss |
D8 |
Notes
Please note that this is an array formula. To learn more, please read the Insert array formulas article.
How to apply the CELL function.
Examples
The figure below displays the result returned by the CELL function.
Return to previous page