The CELL function returns information about a given cell. This can be information in relation to the contents, formatting or location of the cell.

Syntax

=CELL(info_type,cell_ref)

Arguments

Argument Description
info_type Specifies the type of informationto be returned. Thiscan be either:

  “address” returns the cell reference as text
  “col” returns the cell’s column number
  “color” returns 1 if the cell is formatted so that negative values are colored, or 0 otherwise
  “contents” returns the value of the cell, not a formula
  ‘filename” returns the full path and filename of the workbook and worksheet that the supplied cell is in; returns “” (blank) if the workbook containing the cell has not yet been saved
  “format” returns a text value representing the formatting style of the cell. This may be either:
 
“G” General Format or # ?/? or # ??/??
“F0” 0 “D1” d-mmm-yy or
dd-mmm-yy
“,0” #,##0 “D2” d-mmm or dd-mmm
“F2” 0.00 “D3” mmm-yy
“,2” #,##0.00 “D4” m/d/yy or mm/dd/yy or
m/d/yy h:mm
“C0” Currency Format
(no decimal places)
“D5” mm/dd
“C2” Currency Format
(2 decimal places)
“D6” h:mm:ss AM/PM
“P0” 0% “D7” h:mm AM/PM
“P2” 0.00% “D8” h:mm:ss
“S2” 0.00E+00 “D9” h:mm
Added to the end of Format Types:
“-“ indicates cell is formatted in color for negative values
“()” indicates cell is formatted with parentheses for positive or all values
  “parentheses” returns 1 if the cell is formatted with parentheses for positive or all values; returns 0 otherwise
  “prefix” returns a text value corresponding to the ‘label prefix’ of the cell
  “protect” returns 1 if the cell is locked and 0 otherwise
  “row” returns the cell’s row number
  “type” returns a text value corresponding to the type of data in the cell. This can be either “b” for blank, or empty; “l” for label, i.e. text constant, or “v” for value, for any other data type
  “width” returns the cell’s column width
cell_ref The cell that the information isto be returned for. Note:

  If a range of cells is supplied, the returned information relates to the top left cell of the range
  If the reference is omitted, the returned information relates to the last cell that was changed

Examples

  A B C D
1   Formula Result Notes
2 -6,987.0 =CELL(“address”,A2) $A$1  
3   =CELL(“col”,A2) 1  
4   =CELL(“color”,A2) 1  
5   =CELL(“contents”,A2) -6987  
6   =CELL(“filename”,A2) C:\[cell-function-examples.xlxs]Sheet1  
7   =CELL(“format”,A2) ,1- “,1” indicates number format #,##0.0; the “-” indicates color formatting for negative values
8   =CELL(“parentheses”,A2) 0  
9   =CELL(“prefix”,A2)    
10   =CELL(“protect”,A2) 1  
11   =CELL(“row”,A2) 2  
12   =CELL(“type”,A2) v  
13   =CELL(“width”,A2) 7  

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if if the supplied info_type argument is not one of the recognized types in the table above