Home »
Excel-Built-In-Functions »
Excel-Information-Functions »
Excel-Cell-Function
The Excel CELL Function
Function Description
The Excel CELL function returns information about a given cell. This can be information in relation
to the contents, formatting or location of the cell.
The syntax of the function is:
CELL( info_type, reference )
where the arguments are as follows:
| info_type |
- |
Specifies the type of information to be returned. This can be either:
|
| "address" |
- |
Returns the cell reference, as text. |
| "col" |
- |
Returns the cell's column number. |
| "color" |
- |
Returns 1 if the cell is formatted to have colored negative values, 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:
|
|
|
| "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. |
|
| reference |
- |
The cell that the information is to 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, then the returned
information relates to the last cell that was changed.
|
Examples of the Excel Cell Function
In the following example spreadsheet, the Excel Cell function is used to return different
properties of the cell A1. It should be noted that, in the spreadsheet, cell A1 is formatted
with the Custom Format #,##0; [Red] - #,##0.
|
Formulas:
| |
A |
B |
| 1 |
- 9,999 |
=CELL( "address", A1 ) |
| 2 |
|
=CELL( "col", A1 ) |
| 3 |
|
=CELL( "color", A1 ) |
| 4 |
|
=CELL( "contents", A1 ) |
| 5 |
|
=CELL( "filename", A1 ) |
| 6 |
|
=CELL( "format", A1 ) |
| 7 |
|
=CELL( "parentheses", A1 ) |
| 8 |
|
=CELL( "prefix", A1 ) |
| 9 |
|
=CELL( "protect", A1 ) |
| 10 |
|
=CELL( "row", A1 ) |
| 11 |
|
=CELL( "type", A1 ) |
| 12 |
|
=CELL( "width", A1 ) |
|
Results:
| |
A |
B |
| 1 |
- 9,999 |
$A$1 |
| 2 |
|
1 |
| 3 |
|
1 |
| 4 |
|
-9999 |
| 5 |
|
C:\[Book1.xlsx]Sheet1 |
| 6 |
|
,0- |
| 7 |
|
0 |
| 8 |
|
|
| 9 |
|
1 |
| 10 |
|
1 |
| 11 |
|
v |
| 12 |
|
8 |
|
Note:
If the formatting of cell A1 in the above spreadsheet is changed, the formulas in cells B1-B12 will
not immediately recalculate. You can force them to recalculate by pressing the F9 key.
Note that, in cell B6 of the above example spreadsheet, the "format" type is returned as ",0-".
This is comprised of two parts: the ",0" part, which represents the #,##0 number format,
and the "-" part, which indicates that the cell is formatted in color for negative values.
For further information and examples of the Excel Cell Function, see the
Microsoft Office website.
Excel Cell Function Error
If you get an error returned from the Excel Cell Function, this is likely to be the Excel #VALUE! error:
Common Error
| #VALUE! |
- |
Occurs if the supplied info_type argument is not one of the
recognised types (see the table at the top of this page).
|