Author: Oscar Cronquist Article last updated on April 08, 2022

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)

1. Cell Function Syntax

CELL(info_type, [reference])

Back to top

2. Cell Function 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"

Back to top

3. CELL function - example

CELL function example 1

This example demonstrates how to return a type value using the CELL function. The type value tells you if a cell is empty, contains text, or a number.

Formula in cell C3:

=CELL("type",B3)

CELL(info_type, [reference])

info_type - type
[reference] - B3

"b" - blank, cell is empty.
"l" - label (text)
"v" - value (number)

Back to top

4. How to identify formatting of a specific cell

CELL function identify formatting 1

The image above shows the CELL function calculating a code based on a cell reference, in this example, cell B3.

Formula in cell C3:

=CELL("format",B3)

CELL(info_type, [reference])

You can verify the CELL function out by selecting cell B3. Press CTRL + 1, a dialog box appears.

CELL function format cells dialog box

The category is "General" which is the same as "G". I will describe below how to get the formatting code based ont the output from the CELL function.

CELL function translate formatting

The formula in cell D3 translates the code returned from the CELL function to something easier to understand. The table in cell range B9:C30 is used to match the code to the correct format.

Formula in cell D3:

=TEXTJOIN(", ",TRUE,FILTER($B$9:$B$30,$C$9:$C$30=C3))

4.1 Explaining formula in cell D3

Step 1 - Compare values to condition

The equal sign compares values in an Excel formula, the result is  aboolean value TRUE or FALSE.

$C$9:$C$30=C3

becomes

{"G"; "F0"; ",0"; "F2"; ",2"; "C0"; "C0-"; "C2"; "C2-"; "P0"; "P2"; "S2"; "G"; "D4"; "D1"; "D2"; "D3"; "D5"; "D7"; "D6"; "D9"; "D8"}="G"

and returns

{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Step 2 - Filter matching values

The FILTER function extract values/rows based on a condition or criteria.

FILTER(arrayinclude, [if_empty])

FILTER($B$9:$B$30,$C$9:$C$30=C3)

becomes

FILTER({"General"; 0; "#,##0"; "0.000"; "#,##0.00"; "$#,##0_); ($#,##0)"; "$#,##0_); [Red]($#,##0)"; "$#,##0.00_); ($#,##0.00)"; "$#,##0.00_); [Red]($#,##0.00)"; "0%"; "0.00%"; "0.00E+00"; "# ?/? or # ??/??"; "m/d/yy or m/d/yy h:mm or mm/dd/yy"; "d-mmm-yy or dd-mmm-yy"; "d-mmm or dd-mmm"; "mmm-yy"; "mm/dd"; "h:mm AM/PM"; "h:mm:ss AM/PM"; "h:mm"; "h:mm:ss"}, {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns

{"General"; "# ?/? or # ??/??"}

Step 3 - Concatenate values

The TEXTJOIN function allows you to combine text strings from multiple cell ranges and also use delimiting characters.

TEXTJOIN(delimiterignore_emptytext1[text2], ...)

TEXTJOIN(", ",TRUE,FILTER($B$9:$B$30,$C$9:$C$30=C3))

becomes

TEXTJOIN(", ",TRUE, {"General"; "# ?/? or # ??/??"})

and returns "General, # ?/? or # ??/??" in cell D3.

Back to top

5. 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.

Back to top

4.1 VBA code

Sub TwoCellRanges()

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

End Sub

Back to top

4.2 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. Press with mouse on "Insert" on the menu.
  4. Press with left mouse button on "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.