This Wiki article introduces the CELL function and its syntax, with examples of it is used and troubleshooting tips.
|Excel Function Category:||Information Functions|
|Valid in Standard Expression:||Yes|
|Valid in Array Expressions:||Yes|
|Collapses / Summarises Array Results:||No|
|Accepts Ranges Spanning Multiple Sheets:||No|
Returns information about an attribute of a specific cell, or the current active cell (if the reference is omitted).
- One of a predefined list of text strings that indicates about which attribute of a cell information is to be returned.
It is expected to be a string entered as either: a string literal; a reference to a cell containing a string; a nested expression returning a string; or a nested expression returning a reference to a cell containing a string.
If the value of info_type is provided by a reference to a blank cell, it is coerced to an empty string ("").
If the value type of info_type is a number or logical, the value will be coerced to a string.
If the data type of info_type is an error value, the error value will be returned by the function.
if the value of info_type is other than the following twelve listed values, an #VALUE! error is returned.
Acceptable values for into_text are: "address", "col", "color", "contents", "filename", "format", "parentheses", "prefix", "protect", "row", "type" and "width". See the discussion below for the values returned and recommended alternatives that are not volatile.
- An optional argument that references a cell about which the information is to be returned.
It is expected to be a direct reference to a range, a name that references a range, or a nested expression that returns a reference to a range.
If you attempt to enter a string, number or logical, an error is generated by the expression parser and the formula cannot be saved.
if the value of reference is an error value, that error value will be returned.
If omitted, the function defaults to a reference to the cell which is active (in whichever workbook has the focus) when the next recalculation event occurs. This was very useful when we wrote macros in XLM, but is is rarely useful in modelling and analysis applications of the function.
If the reference is to multiple cells, in a standard expression, the value returned will relate to the first cell in the range. In an array formula, an array of values will be returned with the attributes of all referenced cells.
The return value is an attribute of the referenced cell, or the active cell if the reference is omitted. Following is a list of the return values by into_type:
- Returns a string, being the address of the referenced cell as an absolute value. The address will be returned in address style currently being used. If the reference is to a cell in another worksheet or another workbook, the address will be in external reference style, including the workbook name, sheet name and address, thus: [BookName]SheetName!$A$1 or [BookName]SheetName!R1C1. A non-volatile alternative to this would be to use the ADDRESS function.
- Returns a number, being the column number of the worksheet in which the cell is located. A non-volatile alternative to this would be to use the COLUMN function.
- Returns the number 1 if the referenced cell uses a custom number format that applies a color to negative values. Otherwise returns the number 0.
- Returns the contents of the referenced cell. The data type of the returned value will be the same as the referenced cell. If the referenced cell is blank, returns the number 0.
- If the referenced cell is in a workbook that has not be saved (and therefore has no name), the return value is an empty string. If the workbook has been saved to a local, network or internet location, the value returned is a string containing the full URL or UNC to the workbook location, the workbook name in brackets and the worksheet name. thus: c:\Users\UserName\Desktop\[FileName.xlsx]Sheet1.
This is the only info_type which we find routinely useful!
- Returns a string with a format code to indicate whether the cell uses a standard built-in format, and if so, which. The string includes a base code, and then two optional codes appended.
Base Code Description G General, fraction or custom number format Fn Number format with n decimals and no grouping symbol ,n Number format with n decimals and grouping symbol Cn Currency format with n decimals Pn Percentage format with n decimals Sn Scientific (exponent) format with n decimals in the mantissa D1 Date number formatted as day month year D2 Date number formatted as day month D3 Date number formatted as month year D4 Date number formatted as month day year D5 Date number formatted as month day D6 Date number formatted as hours minutes seconds AM/PM D7 Date number formatted as hours minutes AM/PM D8 Date number formatted as hours minutes seconds D9 Date number formatted as hours minutes Suffixes Description - Number format uses a colour code for negatives. Usually [Red], but appended for any colour code. () Number format uses parentheses for negatives. Examples Description F0- Number format, no grouping sysmbol, 0 decimals and coloured negatives
,2-() Number format, grouping symbol, two decimals, parentheses and coloured negatives
C4() Currency format, dour decimals and parentheses for negatives
e.g. $ #,##0.0000;($ #,##0.0000)
- Returns 1 if positive or all values in cell are formatted with parentheses. Otherwise returns 0. Not supported in online versions - and seems an odd thing to do anyway!
- Returns string that includes a character denoting whether the cell contains a string and if so, what alignment is used. This is principally for compatibility with old DOS versions of spreadsheets which used prefix codes on strings to set alignment. The following values are returned:
empty string ("") → not a string - referenced cell's value is blank, a number, a logical or an error value
' → left-aligned string
^ → centre-aligned string
" → right-aligned string
\ → fill-aligned string
- Returns 0 if the referenced cell's locked property is off, allowing entry in the cell when the worksheet is protected. Returns 1 if the cell is locked.
- Returns a number, being the column number of the worksheet in which the cell is located. A non-volatile alternative to this would be to use the ROW function.
- Returns a string containing a character that indicates the data type of the referenced cell's value. Terminology is provided for backward compatibility with DOS spreadsheets.
b → cell is blank
v → cell value is a number, logical or an error value
l → cell value is a string - character is a lower-case L, short for label which is how some DOS spreadhseets designated text strings.
More flexible and non-volatile alternatives are the ISBLANK, ISLOGICAL, ISNUMBER, ISTEXT, ISERROR functions.
- Returns a number that is the rounded whole number value of cell width as designated under formatting column width.
- Returned when info_type is not one of the acceptable twelve string values listed above.
Returned if the value of info_type or reference is #VALUE!
- #NULL!, #DIV/0!, #REF!, #NAME?, #NUM!, #N/A!
- Returned if the value of of info_type or reference evaluates to these error values.
The following embedded Excel workbook shows samples of the function in use with different data types. You can click the buttons in the footer of the embedded worksheet to see and edit it full screen or download a copy to experiment with the formulas.
Due to the use of unsupported features in the web environment, some of the formulas will return errors in the online version that would not be returned on the desktop. This sample is best downloaded and viewed in a desktop copy of Excel.
If the embedded document above will not load,
you may download the file here: download file from OneDrive.
- James Clarkson