CELL

Function Reference: CELL

Syntax
=CELL(Info_type,[reference])
Purpose / Description
Returns information about the first cell in the referenced range, or if no reference is given, the active cell, as at the last calculation event.
Arguments
Name Type Description
info_type text string One of the following values, indicating the information to be retrieved for the nominated or active cell:
"address" Requests the address of the cell.
"col" Requests the column number of the cell.
"color" Requests a flag as to whether the cell is formatted to show negatives in colour.
"contents" Requests the value of the cell.
"filename" Requests the full file path and name and worksheet tab name in which the cell is stored.
"format" Requests a code to indicate which built-in number format is applied to the cell.
"parentheses" Requests a flag as to whether the cell is formatted to show negatives in parentheses.
"prefix" Requests an alignment based on old Lotus 1-2-3 label prefix characters. Obsolete.
"protect" Requests a flag indicating if the cell is protected.
"row" Requests the row number of the cell.
"type" Requests an indicator as to value type in the cell, compatible with Lotus 1-2-3. Obsolete.
"width" Requests the column width of the cell, and optionally whether that is the default width used on the worksheet.
[reference] reference The range containing the cell whose information is to be returned.  If the range contains more than one cell, the first (top-left) cell is used.  Defaults to the active cell as at the last calculation event if it is omitted.
Return Values
Type Specific Value Description
text string   For info_type "address", the address of cell in absolute format, column and row only.
text string   For info_type "filename", the full file path and name and worksheet tab name in the format path[filename]sheetname
for example: C:\Working Files\[File Name.xlsx]Sheet1
text string   For info_type "format", returns a code indicating the number format in use in the cell.  However, can be unreliable if the format is not a built-in standard format.
text string l b v For info_type "type", a flag as to type of value in cell in Lotus 1-2-3 terminology - l for text string, v for number or error values, b for blank
number   For info_type "col" or "row, the column or row number respectively of the cell.
number 1 For info_type "color" if the number format in cell formats negatives in colour.
For info_type "parentheses" if the number format in the cell formats negatives in parentheses.
For info_type "protect" indicates the cell is protected..
number For info_type "color" if the number format in cell does not format negatives in colour.
For info_type "parentheses" if the number format in the cell does not format negatives in parentheses.
For info_type "protect", indicates the cell is not protected.
array - number,logical   For info_type "width", returns a two value array.  The first value is number which is the column width of the referenced cell rounded up to a whole number, the second is a logical value indicating whether that is the standard column width on the worksheet. a rounded number
Notes / Comments / Advice / Warnings
Note: For info_type "filename", returns a blank string if the file has not been saved.
Advice: Instead of the info_type "col", prefer the COLUMN function.
Instead of the info_type "row", prefer the ROW function.
Instead of the info_type "address", prefer the ADDRESS function
Advice: In modern Excel, the only info_type that we now regularly use is "filename" which can be used for a number of very useful tasks in model building, as demonstrated in our courses.
Warning: Do not omit the optional reference, unless you plan on using values from the active cell.  Values only update on a calculation event, changing selection will require pressing F9.
Examples: Open / download sample workbook: CELL.xlsx
 
Tags: