Excel CELL function with formula examples

This Excel tutorial demonstrates the CELL function's versatility in retrieving cell information. Instead of relying on visual inspection or ribbon commands, the CELL function offers a faster, more reliable method to access various cell properties. It can reveal whether a cell is protected, its number format, column width, and even the complete file path of the workbook.

Excel CELL Function: Syntax and Basic Uses

The CELL function returns diverse information about a cell, such as its contents, formatting, and location. Its syntax is:

CELL(info_type, [reference])

Where:

• info_type (required): Specifies the type of information to retrieve.
• reference (optional): The cell to analyze. If a range is provided, it uses the top-left cell. If omitted, it defaults to the last modified cell.

Info_type Values

The following table details the info_type options:

Info_type	Description
"address"	Cell address (text).
"col"	Column number.
"color"	1 if negatively formatted; otherwise 0.
"contents"	Cell value (formula results included).
"filename"	Full file path (text). Returns "" if unsaved. (Not supported in Excel Online, Mobile, or Starter)
"format"	Number format code (see "Format Codes" section). (Not supported in Excel Online, Mobile, or Starter)
"parentheses"	1 if formatted with parentheses for positive or all values; otherwise 0. (Not supported in Excel Online, Mobile, or Starter)
"prefix"	Text alignment prefix (' for left, " for right, ^ for center, \ for fill, "" otherwise; "" for numeric values). (Not supported in Excel Online, Mobile, or Starter)
"protect"	1 if locked; 0 if unlocked (Note: "locked" ≠ "protected"). (Not supported in Excel Online, Mobile, or Starter)
"row"	Row number.
"type"	"b" (blank), "l" (label), or "v" (value).
"width"	Column width (rounded integer). (Not supported in Excel Online, Mobile, or Starter)

Format Codes

The "format" info_type returns codes representing number formats:

Format	Returned Value
General	G
0	F0
0.00	F2
#,##0	,0
#,##0.00	,2
$#,##0 or $#,##0_);($#,##0)	C0
$#,##0.00 or $#,##0.00_);($#,##0.00)	C2
0%	P0
0.00%	P2
0.00E 00	S2
m/d/yy or m/d/yy h:mm or mm/dd/yy	D4
d-mmm-yy or dd-mmm-yy	D1
... (and many more)	...

Custom formats may yield other values. The first letter often indicates the format type (G=General, C=Currency, P=Percentage, S=Scientific, D=Date). Numbers often represent decimal places. Commas and parentheses may be appended to the code.

Advanced CELL Function Examples

Beyond basic usage, CELL can be combined with other functions:

• Lookup Result Address: CELL("address", INDEX(return_column, MATCH(lookup_value, lookup_column, 0))) retrieves the address of a VLOOKUP or INDEX/MATCH result.

• Hyperlink to Lookup Result: HYPERLINK("#"&CELL("address", ...), "Link Name") creates a hyperlink to the lookup result.

• File Path Extraction: Using CELL("filename") and text functions like LEFT, MID, RIGHT, and SEARCH, you can extract parts of the file path (workbook name, sheet name, etc.).

This enhanced explanation provides a more comprehensive understanding of the CELL function's capabilities and applications within Excel. Remember that some info_type values are not supported in all Excel versions.

The above is the detailed content of Excel CELL function with formula examples. For more information, please follow other related articles on the PHP Chinese website!