CELL function

The CELL function in Excel provides information about the formatting, location, or content of a specified cell. It is often used for advanced formatting or analysis tasks.

Syntax:

CELL(info_type, [reference])

Arguments:

1. info_type: A text value specifying the type of information you want about the cell. Common options include:
   • "address": Returns the cell address as text (e.g., $A$1).
   • "col": Returns the column number of the cell.
   • "row": Returns the row number of the cell.
   • "contents": Returns the actual content of the cell.
   • "filename": Returns the full file path of the workbook, including the sheet name.
   • "format": Returns a code corresponding to the number format of the cell.
   • "type": Returns "b" if the cell is blank, "l" if it contains a label (text), and "v" if it contains a value (number).
   • "width": Returns the column width of the cell.
2. reference (optional): The cell you want information about. If omitted, it defaults to the last changed cell.

Example:

Suppose you have the following data in A1:

• A1: 100

Formulas:

1. Address:

   =CELL("address", A1)
   

   Result: "$A$1"

2. Column:

   =CELL("col", A1)
   

   Result: 1 (A is the first column).

3. Row:

   =CELL("row", A1)
   

   Result: 1

4. Contents:

   =CELL("contents", A1)
   

   Result: 100

5. Format:

   =CELL("format", A1)
   

   Result: Returns a format code such as "G" for General format.

Notes:

1. Dynamic Updates: The function recalculates only when the referenced cell or its formatting changes. If the referenced cell doesn’t change, you might need to force recalculation (e.g., by pressing F9).
2. File Paths: For "filename", the result will be blank if the workbook hasn’t been saved.
3. Format Codes:
   • "G": General format
   • "F0": 0 decimal places
   • "C2": Currency with 2 decimal places