How to use CELL function in Excel?

The CELL function in Excel is used to retrieve specific information about a cell’s formatting, location, or contents. The syntax for the CELL function is:

CELL(info_type, [reference])

info_type: This is a text value that specifies what type of cell information you want. Some common `info_type` values include:

“address”: Returns the cell address as text.
“col”: Returns the column number of the cell.
“color”: Returns 1 if the cell is formatted in color for negative values; otherwise returns 0.
“contents”: Returns the actual content of the cell.
“filename”: Returns the file name and full path of the workbook that contains the cell.
“format”: Returns a text value corresponding to the number format.
“parentheses”: Returns 1 if the number is formatted in parentheses; otherwise 0.
“prefix”: Returns a text value corresponding to the label prefix.
“protect”: Returns 1 if the cell is locked; otherwise returns 0.
“row”: Returns the row number of the cell.
“type”: Returns a text value corresponding to the type of data in the cell (“b” for blank, “l” for label, and “v” for value).
“width”: Returns the column width of the cell, rounded off to the nearest integer.

reference: This is an optional argument that refers to the cell you want information on. If omitted, Excel uses the information of the cell where the formula resides.

Here are some examples of how to use the CELL function:

   =CELL("address", A1)

To get the address of a cell:

This will return the address “$A$1”.

   =CELL("col", B10)

To get the column number of a cell:

This will return “2” because B is the second column.

   =CELL("format", C5)

To get the format of a cell:

This will return a code representing the format (e.g., “G” for General).

   =CELL("filename", D10)

To get the filename and its path:

This will return the full path, workbook name, and current sheet name.

Keep in mind that if you use the CELL function with a recalculation (like copying the formula to another cell), the output might change dynamically based on the reference used.

facebooklinkedin