Exploring Excel's Functions Part 3: CELL() Shocked
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
You might guess that the CELL() function has something to do with worksheet cells—but exactly what? It is one of Excel’s information functions, so-called because their job is to provide information about certain aspects of the worksheet.
A Hard Cell
The CELL() function can tell you a lot of things about a specified worksheet cell. It needs two pieces of information to work, and the syntax is as follows:
CELL(info_type, reference)
Reference tells the function which cell you are interested in. It can be a cell address or range name. If reference refers to a range of more than one cells, CELL() returns information about the top-left cell in the range.
Info_type is a text value that tells the function what information you want. The following table gives all the possibilities. Some of them will be explained in more detail later in the article.
|
Value of Info_type |
Information Returned by CELL() |
|
"address" |
The address of the cell as an absolute cell reference (for example, $A$2). |
|
"col" |
The column number of the cell. |
|
"color" |
1 if the cell is formatted to display negative values in color; 0 otherwise. |
|
"contents" |
The contents of the cell. If the cell contains a formula, the result is returned, not the formula itself. |
|
"filename" |
The filename, including path, and sheet name where the cell is located. The workbook must have been saved for this option to work. |
|
"format" |
A text value indicating the number format applied to the cell. For example, "G" is returned for General format, and "D3" for the mmm-yy date format. See Excel help for a complete listing of return values. |
|
"parentheses" |
1 if the cell is formatted with parentheses for positive or all values; 0 otherwise. |
|
"prefix" |
Text value corresponding to the label alignment prefix of the cell. Returns a single quotation mark (’) if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else. |
|
"protect" |
1 if the cell is locked; 0 if it is not locked |
|
"row" |
The row number of the cell. |
|
"type" |
Text value indicating the type of data in the cell. Returns "b" if the cell is empty, "l" if the cell contains text, and "v" for anything else (numbers, formulas, and so on). |
|
"width" |
The width of the cell, rounded to an integer, expressed as number of characters in the default font size. |
You can see that the CELL() function is quite flexible, but is there anything really useful you can use it for? Oh yes! Let’s look at some specific examples.