Syntax. CELL(info_type,reference)
Definition. This function returns information about the formatting, location, or contents of the upper-left cell in the range specified by the reference argument.
Arguments
info_type (required). An argument that specifies what type of information is returned.
reference (optional). Identifies the cell you want information about. If you omit this argument, the function returns the information for the last cell that was changed.
Background. To use this function, you need to know the information types and the corresponding results. These are listed in Table 11-2.
Table 11-2. Arguments Passed as Strings to the CELL() Function
Argument (Text) | Returns |
|---|---|
address | The absolute reference to the upper-left cell as text (can include the table or sheet name if the workbook is open). |
width | Column width of the cell, rounded to an integer. Each unit of column width is equal to the width of one character in the default font size if all characters have the same width. |
Full path of the workbook including the table containing the cell (an empty string if the worksheet has not yet been saved). | |
color | Returns |
format | Returns a text value corresponding to the number format of the cell. The values for the various formats are shown in Table 11-3. Returns a hyphen (-) at the end of the text if the cell is formatted in color for negative values. Returns () at the end of the text if the cell is formatted with parentheses for positive or all values. |
contents | Returns the value of the cell (not a formula). |
parentheses | Returns |
prefix | Returns a single quotation mark (‘) if the cell contains left-aligned text, a double quotation mark (“) if the cell contains right-aligned text, a caret (^) if the cell contains centered text, a backslash (\) if the cell contains fill-aligned text, and an empty string (“”) if the cell contains anything else. |
protect | Returns |
col | Returns the column number of the cell. |
type | Returns the data type of a cell: |
row | Returns the row number of the cell. |
Note
If the returned strings contain workbook names, table names, and/or cell addresses, the information structure is always the same:
Folder\[workbook name]worksheet!absolute address
For example:
F:\chapter 11\book\chp11\[Information.xls]cell
is the path to the worksheet, and
[Workbook2.xls]Table1!$A$1
is the cell reference, including the workbook and worksheet information.
The function returns some cryptic information that is explained in Table 11-3.
Table 11-3. Number Formats, Encoded and in Plain Text
Cell() Returns | Meaning |
|---|---|
General | |
| 0 |
| #,##0 |
| 0.00 |
| #,##0.00 |
| Currency without decimal places |
| Currency without decimal places; negative values are formatted in red |
| Currency with two decimal places |
| Currency with two decimal places; negative values are formatted in red |
| 0% |
| 0.00% |
| 0.00E+00 |
| # ?/? or # ??/?? |
| All dates have the format MM.DD.YY. The number of letters for M, D, and Y can be different. |
| All dates have the format MM.DD. The number of letters for M and D can be different. |
| All dates have the format MM.YY. The number of letters for M and Y can be different. |
| All dates in which the order of the day and month was changed, as in DD/MM/YY. |
| All dates without year number, in which the order of the day and month was changed, as in DD/MM. |
| h:mm:ss AM/PM |
| h:mm AM/PM |
| h:mm:ss |
| h:mm |
Note
To ensure compatibility with other spreadsheet software, the CELL() function is made available and offers some interesting possibilities.
Important
The return value is not always current. If you save a workbook, the file name is not updated. If you change the color of negative numbers, this is not immediately shown with the value 1 or 0. To update, you have to recalculate, which can be done by pressing the F9 key.
Examples. The following examples illustrate how to use the CELL() function.
Making Changes Visible. You can use visual effects for cell changes made by the user. For example, the formula
=IF(CELL("address")="$C$63","Caution","OK")shows in C64 that the adjacent cell C63 was changed. If you don’t want to use another cell, enter the condition
=(CELL("address")="$C$63")to format the cell in a color. This works because the reference in the CELL() function is missing.
This procedure doesn’t protect the cell. If you edit another cell, everything except C63 is reset. If the user responds to the message by selecting Edit/Undo to reset the cell, the warning disappears only after another cell is changed.
Simplifying the Function. The CELL() function requires a string as the first argument. In versions of Excel earlier than Excel 2007, to use the function several times you create a list with the necessary terms. Instead of =CELL(“type”,B52), you can use =CELL($B$51,B52) if B51 contains the word type. In Excel 2007 and Excel 2010, IntelliSense helps you write formulas.
Formatting locked cells. Attempting to change a locked cell in a protected worksheet causes an annoying error message. You can format locked cells in a color to make them obvious for the user. It might also make sense to format editable cells in a color. The easiest way is to specify a conditional format for one cell and use the Format Painter for the other cells. Figure 11-1 shows an example.
The formula =CELL("protect",B67) instead of =(CELL("protect",B67)=1) works because it returns 1 if the cell is locked, and Excel interprets this as TRUE (0 would be recognized as FALSE).
Consecutive Numbers. The formula
=CELL("row",C72)-71 & "."creates consecutive numbers beginning at cell C72 with 1.