Author: Oscar Cronquist Article last updated on March 24, 2020

The CELL function gets information about the formatting, location, or the contents of a cell.

The formula example above in cell C3 returns a value determined by the contents of the cell.

Return value Cell content
l text
b blank
v anything else

Formula in cell C3:

=CELL("type",B3)

Excel Function Syntax

CELL(info_type, [reference])

Arguments

info_type Required. A value that determines the type of cell information the function returns.

List of Info_type arguments and what they do.

info_type Returns
"address" Reference of the first cell in reference, as text.
"col" Column number of the cell in reference.
"color" The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
"contents" Value of the upper-left cell in reference; not a formula.
"filename" Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved.
"format" Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values.
"parentheses" The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.
"prefix" Text value corresponding to the "label prefix" of the cell. Returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else.
"protect" The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.
"row" Row number of the cell in reference.
"type" Text value corresponding to the type of data in the cell. Returns "b" for blank if 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" Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.

The second argument in the CELL function is [Reference].

[Reference] Optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed.

Info_type "format" returns the following codes for different cell formatting settings.

Excel format Returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "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.00% "P2"
0.00E+00 "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"

How to calculate the intersection of two cell ranges?

CELL function intersecting cell references

The CELL function is able to return the cell reference from a cell range based on two intersecting cell references.

Formula in cell B13:

=CELL("address",(B2:C7 C5:E10))

The space character can be used as an intersect operator in Excel. The formula in cell B13 returns the top-left cell address of the intersecting cell references.

Intersecting cell reference VBA macro

The image above shows how to calculate the address of two intersecting cell ranges using a VBA macro. The message box shows the address of the entire cell range.

VBA code

Sub TwoCellRanges()

MsgBox "Intersecting cell range: " & Application.Intersect(Range("B2:C7"), Range("C5:E10")).Address

End Sub

Where to put the code?

Intersecting cell references

  1. Copy above VBA code.
  2. Press shortcut keys Alt + F11 to open the Visual Basic Editor.
  3. Click on "Insert" on the menu.
  4. Click "Module".
  5. Paste code to window.
  6. Exit VB Editor and return to Excel.
Note, save your workbook with file extension *.xlsm (macro-enabled workbook) to keep the code attached to your workbook.

Excel Function Reference

This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.