CELL

Find information about a cell in Excel
Returns information about the formatting, location, or contents of a cell

Rational

Find information about a cell.

Description

The Excel CELL function returns with information of a cell about the formatting, location, or contents. The type of information required about a cell indicated within the info_type parameter.
See below for the list of info_type arguments and the respective results.

Syntax

=CELL(info_type, [reference])

Parameters

info_type – specific code for information
reference [optional] – text value to get cell information

Return Value

The cell information code.

Key Notes

The CELL formula is useful in extracting a wide array of information about a specified cell (reference). The type of facts of a cell is determined as per the argument specified in the info_type parameter.
For example: “=CELL(“row”,”Sheet”)” return “5” as the text “sheet” is in the cell B5 (B – column and 5th – Row). Note that the info_type argument must be in a double quotation if not within a cell reference.

The complete list of the type of cell information which a user can find about cell are below:

info_tye                                                                                           Cell information
“address”Find the address of a cell or first cell of a range given in reference, as a text
“col”Find the column number of a cell or the first cell of a range given in the reference.
“color”Returns 1 if the reference cell or the first cell of a range is formatted in color for negative values; otherwise returns 0 (zero).
“contents”Find the value of the upper left cell in reference. Display the result of formula rather than formula.
“filename”Return the file name and full path that contains reference cell, as text. If the worksheet contains the reference argument that has not yet been saved, the return value will be empty text (“”).
“format”Return a specific code of a number format of the reference cell. Below is a list of codes for the respective number format. If the reference cell is formatted in color for negative values or values that are formatted with parentheses, the result adds “-” and “()” at the end of the text value, respectively.
“parentheses”Return 1 if the cell is formatted with parentheses for values; otherwise returns 0.
“prefix”Returns the text value corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) for left-aligned text, double quotation mark (“) for right-aligned text, caret (^) for centred text, backslash (\) for fill-aligned text , and empty text (“”) for anything else in the reference cell.
“protect”Returns 1 if the first reference cell is locked; otherwise returns 0.
“row”Returns the row number of the reference cell.
“type”Returns type of data of the corresponding text value in the cell, “b” for blank in case cell is empty, “l” for the label in case the cell contains a text constant, and “v” for any other value.
“width”Find the column width of the reference cell, rounded off to the nearest integer. In case of default font size, each unit of column width is equal to the width of one character.

The following table describes the text codes that are returned based on the format of the reference cell when “format” is used for info_type:

Reference cell formatReturn code
General“G”
0“F0”
#,##0“,0”
0“F2”
#,##0.00“,2”
$#,##0_);($#,##0)“C0”
$#,##0_);[Red]($#,##0)“C0-“
$#,##0.00_);($#,##0.00)“C2”
$#,##0.00_);[Red]($#,##0.00)“C2-“
0“P0”
0“P2”
0“S2”
# ?/? or # ??/??“G”
m/d/yy or m/d/yy h:mm or mm/dd/yy“D4”
d-mmm-yy or dd-mmm-yy“D1”
d-mmm or dd-mmm“D2”
mmm-yy“D3”
mm/dd“D5”
h:mm AM/PM“D7”
h:mm:ss AM/PM“D6”
h:mm“D9”
h:mm:ss“D8”

Keep Reading Similar Functions:

Find about the current operating environment in Excel

INFO

The INFO function get specifications about the current operating environment, include the operating system, Excel version, num. of active worksheets.. so on.

Read More »

SHEET

The SHEET function returns a sequence number of an Excel worksheet in a workbook of a given reference, worksheet, range name or table name.

Read More »
Find count of worksheets in a workbook in Excel

SHEETS

The SHEETS function finds the total number of sheets in a workbook. In the case of no argument the function returns total count of active workbook.

Read More »