Excel CELL Function
Summary
The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. See below for a full list of information available.
Purpose
Get information about a cell
Return value
A text value
Syntax
=CELL (info_type, [reference])
Arguments
- info_type - The type of information to return about the reference.
- reference - [optional] The reference from which to extract information.
Usage notes
Use CELL to extract a wide range of information about reference. The type of information to be returned is specified as info_type. Always wrap the info_type in double quotes. For the formula: =CELL("col", C10) will return 3. See below for a full list of info_types and a key to the codes that CELL returns when the info_type is format.
Note that when reference refers to more than one cell, CELL will return information about the first cell in reference.
The following info_types can be used with the CELL function:
| Info_type | Description |
|---|---|
| address | returns the address of the first cell in reference (as text). |
| col | returns the column number of the first cell in reference. |
| color | returns the value 1 if the first cell in reference is formatted using color for negative values; or zero if not. |
| contents | returns the value of the upper-left cell in reference. Formulas are not returned. Instead, the result of the formula is returned. |
| filename | returns the file name and full path as text. If the worksheet that contains reference has not yet been saved, an empty string is returned. |
| format | returns a code that corresponds to the number format of the cell. See below for a list of number format codes. If the first cell in reference is formatted with color for values < 0, then "-" is appended to the code. If the cell is formatted with parentheses, returns "() - at the end of the code value. |
| parentheses | returns 1 the first cell in reference is formatted with parentheses and 0 if not. |
| prefix | returns a text value that corresponds to the label prefix - of the cell: a single quotation mark (') if the cell text os left-aligned, a double quotation mark (") if the cell text is right-aligned, a caret (^) if the cell text is centered text, a backslash () if the cell text is fill-aligned, and an empty string if the label prefix is anything else. |
| protect | returns 1 if the first cell in reference is locked or 0 if not. |
| row | returns the row number of the first cell in reference. |
| type | returns a text value that corresponds to the type of data in the first cell in reference: "b" for blank when the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else. |
| width | returns the column width of the cell, rounded to the nearest integer. A unit of column width is equal to the width of one character in the default font size. |
The following table is a key for the text codes that are returned from CELL when "format" is used for info_type.
| Format code returned | Format code meaning |
|---|---|
| G | General |
| F0 | 0 |
| ,0 | #,##0 |
| F2 | 0 |
| ,2 | #,##0.00 |
| C0 | $#,##0_);($#,##0) |
| C0- | $#,##0_);[Red]($#,##0) |
| C2 | $#,##0.00_);($#,##0.00) |
| C2- | $#,##0.00_);[Red]($#,##0.00) |
| P0 | 0% |
| P2 | 0.00% |
| S2 | 0.00E+00 |
| G | # ?/? or # ??/?? |
| D1 | d-mmm-yy or dd-mmm-yy |
| D2 | d-mmm or dd-mmm |
| D3 | mmm-yy |
| D4 | m/d/yy or m/d/yy h:mm or mm/dd/yy |
| D5 | mm/dd |
| D6 | h:mm:ss AM/PM |
| D7 | h:mm AM/PM |
| D8 | h:mm:ss |
CELL formula examples
If you want to get the current workbook's full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks...
To create hyperlinks to the first match in a lookup, you can use a formula based on the HYPERLINK function, with help from CELL, INDEX and MATCH.
In the example shown, the formula in C5 is:
=HYPERLINK("#"...
If you want to get the workbook path (directory) only, you can do so with a formula that uses the LEFT and the FIND function. The result will look something like this:
Drive:\path\to\file\ //Windows
Drive:path:to:...
To highlight unprotected cells (cells that are unlocked) with conditional formatting, you can use a formula based on the CELL function. In the example shown, the custom formula used to set up conditional formatting is...
To get the name of the current worksheet (i.e. current tab) you can use a formula based on the CELL function. CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the...
To count visible columns in a range, you can use a helper formula based on the CELL function with IF, then tally results with the SUM function. In the example shown, the formula in I4 is:
=SUM(key)
where "key" is the...
To create hyperlinks to the first match in a lookup, you can use a formula based on the HYPERLINK function, with help from CELL, INDEX and MATCH.
In the example shown, the formula in C5 is:
=HYPERLINK("#"...
If you want to get the current workbook's full path, name, and sheet with a formula, you can use the CELL function and a reference to any cell in the workbook. CELL will return the name in this format:
path[workbook....
If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function.
How the formula works
The cell...
To increment a reference created as text inside the INDIRECT function, you can use the CELL function. In the example shown, the formula in D5 is:
=INDIRECT($B$5&"!"&CELL("address",A1))...
To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions.
In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data))
How...
To get the address of a lookup result derived with INDEX, you can use the CELL function. In the example shown, the formula in cell G8 is:
=CELL("address",INDEX(B5:D11,MATCH(G6,B5:B11,0),2))
Which returns an...
Good links
